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,
((vtu.blocks*8)/1024)MB, vtu.tablespace
from v$tempseg_usage vtu, v$session vs
where vtu.session_num=vs.serial#
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)
· _PGA_MAX_SIZE=2*_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.

I’m preparing to build our new 11g environment and as I “study up” on all that is new in 11g, there are a few features that stand out over the rest, (at least in my book!)

I/O Calibration

I’m am challenging the upper most limits of my hardware with how much I/O I can produce. I have spent much of my time since I started with my new company correcting the I/O issues manually, (yes, ASM failed me, but I’m sure it will live up to my expectations someday!:))

The CALIBRATE_IO procedure that comes as part of the DBMS_RESOURCE_MANAGER packge is a dream come true for someone like me. This will return a report to me, after passing in the number of disks and maximum latency that is tolerable, the maximum I/O requests per second, the maximum MB per second and even the actual latency.

Reference Partitioning

Somedays I look at the choices in partitioning in my environments and shake my head. I was taught that you either “partition right or you don’t partition at all”, so when I see partitioning that causes more performance issues than gains, it leaves me a bit frustrated.

This new partitioning option allows you, when a child table with a referencing foreign key, but without the partitioned key column from the parent table existing, to still partition through the actual referencing foreign key! I have numerous tables where columns have been duplicated simply for partitioning reasons and this option will relieve this challenge.

The Pivot Operator

I don’t know how often we’ve experienced performance challenges from complex decodes. Often our data is simply not presented in the format we require for reporting and the PIVOT operator gives us the option to present the data in a crosstab format.

oracle.com has a great example of this feature:

Here is the output:
. TIMES_PURCHASED ‘NY’ ‘CT’ ‘NJ’ ‘FL’ ‘MO’
————— ———- ———- ———- ———- ———-
0 16601 90 0 0 0
1 33048 165 0 0 0
2 33151 179 0 0 0
3 32978 173 0 0 0
4 33109 173 0 1 0
… and so on …