DBA Kevlar

Tips, tricks, (and maybe a few rants) so more DBAs become bulletproof!

A Tale of Session Parameter Settings

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;

SQL_ID Temp Tablespace Operation Type PGA SGA TEMP
09am9sr9uca5y TEMP_4 WINDOW(SORT)

0

0

1375

09am9sr9uca5y TEMP_1 WINDOW(SORT)

0

0

1405

09am9sr9uca5y TEMP_2 WINDOW(SORT)

0

0

1415

09am9sr9uca5y TEMP_3 WINDOW(SORT)

0

0

1420

 

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:

SQL_ID Operation Type PGA SGA TEMP
09am9sr9uca5y   HASH-JOIN

511

23

09am9sr9uca5y   LOAD Write Buffers

0

0

09am9sr9uca5y   HASH-JOIN

511

23

09am9sr9uca5y   HASH-JOIN

511

23

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:

SQL_ID Temp Tablespace Operation Type PGA SGA TEMP
09am9sr9uca5y TEMP_2 WINDOW (SORT)

26

95

598

09am9sr9uca5y TEMP_3 WINDOW (SORT)

26

95

598

09am9sr9uca5y TEMP_1 WINDOW (SORT)

26

95

598

09am9sr9uca5y TEMP_4 WINDOW (SORT)

26

95

598

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:

SQL_ID Temp Tablespace Operation Type PGA SGA TEMP
12bhautka6mvp LOAD WRITE BUFFERS

0

0

12bhautka6mvp HASH-JOIN

2

1

12bhautka6mvp HASH-JOIN

2

1

12bhautka6mvp HASH-JOIN

2

1

12bhautka6mvp HASH-JOIN

2

1

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.

 

Print Friendly

facebook comments:

4 thoughts on “A Tale of Session Parameter Settings
  • Ed says:

    Hi,

    Thanks for a nice post.

    Does the following information come from the AWR Report as well? Are the values under PGA and SGA in MB?

    SQL_ID Operation Type PGA SGA TEMP
    09am9sr9uca5y HASH-JOIN 511
    23

    09am9sr9uca5y LOAD Write Buffers 0
    0

    09am9sr9uca5y HASH-JOIN 511
    23

    09am9sr9uca5y HASH-JOIN 511
    23

  • dbakevlar says:

    Thanks!
    The data shown here is session level, where AWR, is at a database level with specifics shown for snapshot, but not at this kind of session level detail. The information shown in the column for SGA would be included in the SGA area, where PGA would be in the PGA totals. Temp usage is displayed differently, as you would scan PGA usage for optimal, one-pass and multiple-pass entries for how often it went to temp.

    If you chose to run a SQL_ID specific AWR report, ($ORACLE_HOME/rdbms/admin/awrsqrpt.sql) then you would see some of this information displayed, but not in this specific format. That’s why many DBAs still retain and guard their scripts with an iron fist- it allows you to present data in the format that you require to answer the question posed at that time… :)

  • Ed says:

    Hi,

    Thanks for a very quick response and explanation. Very much appreciated your more quick than MOS reply :-) Especiallyt the explanation

    And it is true, scripts are still very much indispensable. I am not a very big fan of OEM actually ‘coz I can’t customized it to what I want and in lots of cases, I ended having to use scripts for UDM. The graphs look nice though :-) It can be frustrating seeing your top SQLs are OEM ones too :(-

    I was hoping with 12c, Oracle will allow customers to customized their OEM with company logos and stuff at least to differentiate one OEM from another.

    Wishfully thinking someone will write some Oracle gurus will look at writing a text-based OEM with scripts that you want to run

    Big fan of the Enkitec and Pythian gurus too, I am so very far behind :-) Keep up the good work

    Thanks again for your reply

  • dbakevlar says:

    Your very welcome- I would spend some time reading up on EM12c and the flexibility to use your scripts as metric extensions, EM Jobs, etc. The newest version of Enterprise Manager was designed with the main purpose of letting the DBA build an enterprise monitoring system that provides what they need for the business. The advanced dashboard features are clearly what you are looking for and if you refer to one of my co-authors from the “Expert Enterprise Manager 12c” book from Apress, Gokhan Atil, he did customize the logo in his EM12c environment, similar to your request.

    Thank you for the kind words and have a great day!
    Kellyn

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

  • Facebook
  • Google+
  • LinkedIn
  • Twitter