This is another blog post about how TEMP can kill you in performance.

The Program Global Area, aka PGA, is a memory region that allows Oracle to perform many processes that once fell to static calculations in parameters that had to be managed by a DBA.

As frustrating as it might be to a DBA to not have enough memory to allocate to the performance enhancing feature, I found it even more frustrating to find NO PGA allocation to window sorts.  We were experiencing poor performance in one of our environments, but I noted it was only during heavy workloads, resulting from heavy IO, NO execution plan changes, but outrageous waits on temp reads and writes.

db file sequential read   61,275,579   161,054      44.7   User I/O
direct path read temp     17,262,132    74,398      20.6   User I/O  <--TEMP is on SSD!!
CPU time                                71,971      20.0
direct path write temp    10,572,848    38,611      10.7   User I/O  <--TEMP is on SSD!!

Upon querying the work area, I discovered the following:

select vst.sql_text, swa.sql_id, swa.sid, swa.tablespace
, swa.operation_type
, trunc(swa.work_area_size/1024/1024) "PGA MB"
, trunc(swa.max_mem_used/1024/1024)"Mem MB"
, trunc(swa.tempseg_size/1024/1024)"Temp MB"
from v$sql_workarea_active swa, v$session vs, v$sqltext vst
where swa.sid=vs.sid
and swa.sql_id=vs.sql_id
and vs.sql_id=vst.sql_id
and vst.piece=0
order by swa.operation_type;

The process in question, as most processes in the database environments I work in was parallel, (degree 4) working with temp tablespace groups, (count of 4) on SSD, so quite fast, but to see this amount of processing “swapping” to temp was frustrating and painful to performance.

The IO was killing the performance, even with this running on SSD, we were 100% saturated on the disk!:

Device:       reads/s            writes/s              rsec/s                    wsec/s                  %util

Fio1              1584.00          1460.33                 101397.33            129344.00           73.97

Fio2              1603.33          1534.67                 102720.00            134776.00         100.03

FROM AWR:

%Time Total Wait wait Waits

Wait Class                      Waits  -outs         Time (s)    (ms)      /txn
-------------------- ---------------- ------ ---------------- ------- ---------
User I/O                   91,531,087     .0          279,954       3  56,223.0
Other                       4,067,544    2.9           12,718       3   2,498.5

Now where the key came in was the rest of the parallel processes when looking at their allocation:

Now even though the PGA is set to 32G and the allocating to numerous other processes, this one appeared very “odd” and why it’s the key the PGA.  Consider what the overall process would have to be to allocate 511 MB of PGA to the three processes above.   This would mean that 500MB was allocated to the process and then there was the approximate 10MB overhead.  The number was too evenly distributed and too high and no PGA was allocated to two newer features to 10g, such as analytical functions, (window sorts) .

I remembered that at one time, this company had a developer employed to it that had a penchant for the following manual session alteration:

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';

Upon a search of the code, it was found that this was in the main package, but:

1. The beginning procedure which executed from the package was actually in the center of the package, so developers never noted it as being an issue and the DBA’s never dug into the code deep enough before to know this manual alteration was present.
2. Due to a huge process change to how the code produced work, there had been a large insertion of analytical functions that caused the performance degradation upon heavy loads.

The testing process was simple.

1.  Removed the three lines from the initial procedure in the package.
2. Run the same heavy load of processing from production in test with the new code.

Change in how the PGA is allocated:

I also found the PGA savings to the hash joins interesting, as what was statically “forced” in the manual allocation wasn’t what the database really required and was simply wasted with the old settings, where the PGA dynamically set much smaller amounts:

Result:

Increase in performance:  7 fold

The fix was moved to production that day, without hesitation and everyone shook their head as to why this was ever put into place by anyone,  but I want to follow up on that question that many folks, I’m sure have.  Although I do not support this, especially in 10g and 11g databases due to what we see here, you do have to understand what the developer’s goal was:

The developer :

1.  Saw benefits to straight 500MB allocation of PGA to processing that over-rides the percentage allocation maximum per process.
2. Experienced huge gains in performance on hash joins due to this change and we did suffer some small performance hits when it was removed, but the overall performance gain was still outstanding!
3. Wasn’t aware of future enhancements and code changes that would have been vulnerable to his choice in coding session parameter changes.