I’ve come to realize that most folks don’t really understand the limitations in the allocation of PGA memory to hashing and sorting. They truly believe that if they set their PGA high enough, the database will just allocate as much memory as they have in the setting towards any hash or sort process, when in truth, (and I am referencing 10g here…) there is a “threshold” to protect the database from allocating too much PGA memory to any one process. This ceiling is for sorting or hashing and this is why I am such a strong advocate of viewing temp space usage in execution plans.
Most developers and DBA’s, especially anyone working in a datawarehouse environment, where hash joins are more prevalent, should be viewing temp space usage in their explain plans. Knowing that once you hit this threshold, the database is going to “swap” to temp space usage for sorting and hashing is important to be aware of since this is when your performance most often goes out the window.
Utilizing our “trusty” query to check for temp space usage, we drill down for sorting and hashing:
select vs.sid, vs.osuser, vs. process, vs.sql_id, vtu.segtype,
from v$tempseg_usage vtu, v$session vs
and segtype in (‘HASH’,'SORT’)
order by blocks desc;
These are the sql_id’s that you should pull the statements for and identify for tuning exercises.
If you do pull the temp usage for an explain plan, it will look similar to the one below, (but hopefully in a more readable format than mine!):
Description Owner Object_Name Cost Rows Temp Usage
HASH JOIN ANTI 15806 75479 222663050
VIEW DW_ONR Hash_1 10942 75479 220021285
WINDOW SORT 10942 75479 14718405
HASH JOIN 10386 75479
PARTITION RANGE SINGLE 149 28815
TABLE ACCESS FULL DW_ONR Table_1 149 28815
VIEW DW_ONR 7216 4327766 242354896
WINDOW SORT PUSHED RANK 7216 4327766 147144044
…and so on and so forth…
If you can’t tune below the threshold, there is another option that can be examined to see if it’s feasible for your environment- alter the session to NOT use PGA and set the hash and sort area size to the appropriate sizes to manage the process efficiently BEFORE the execution of the step in the procedure that performs the large hash or sort:
EXECUTE IMMEDIATE ‘ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL’;
EXECUTE IMMEDIATE ‘ALTER SESSION SET SORT_AREA_SIZE = 536870912′;
EXECUTE IMMEDIATE ‘ALTER SESSION SET HASH_AREA_SIZE = 536870912′;
The third option and this should only be taken with the approval of Oracle support, is to actually change the threshold of the hash and sort size in the PGA with undocumented parameters. All standard precautions with undocumented parameters should apply. Below are the parameters, which are unseen in the standard list of parameters and the best practice per Oracle of how they are calculated currently in any database when not specified as an undocumented parameter.
· _PGA_MAX_SIZE which will override the 200MB allocation
· _smm_max_size, 5% of _pga_max_size
· _smm_px_max_size, (defaults to 30% of pga_aggregate_target)
· Degree of parallelism > CEILING(smm_px_max_size/_smm_max_size)
· And _smm_px_max_size=50%*PGA_AGGREGATE_TARGET
· When DOP<=5 then _smm_max_size is used. · When DOP>5 then _smm_px_max_size/DOP is used.