SORT_AREA_SIZE Affecting Sorts to Disk with PGA_AGGREGATE_TARGET Set 2?

The good news is that I was able to reproduce the behavior on 64 bit Oracle 11.1.0.7 with an initial PGA_AGGREGATE_TARGET of 1.8GB. Below is the test script that I created – it took about 2 hours for SQL*Plus to scroll all of the returned data up the screen, regardless of how quickly the DBMS_XPLAN outputs indicate that the SQL statements executed.

Note in the above that the sorts to disk did not happen when PGA_AGGREGATE_TARGET was adjusted to 200MB, then to 300MB, and then to 200MB. The first sort to disk happened when the parameter was set to 150MB, and the second sort to disk happened when the parameter was bumped back up to 200MB.

Prior to posting the original blog article “SORT_AREA_SIZE Affecting Sorts to Disk with PGA_AGGREGATE_TARGET Set?”, I read a document titled “Advanced Management of Working Areas In Oracle 9I/10G” that was written by Joze Senegacnik in 2004 (http://joze-senegacnik.blogspot.com/). When I originally created the test case a couple years ago, I suspected that there was a delayed reaction when the PGA_AGGREGATE_TARGET parameter is adjusted, but I did not know what caused that delayed reaction. Joze’s article seems to indicate that the delay is caused by the CKPT process publishing the memory bounds only every three seconds. So, I re-ran the test, adding the following command after each adjustment of the PGA_AGGREGATE_TARGET parameter:

host sleep 30

The above command executes the operating system’s sleep command, causing SQL*Plus to pause for 30 seconds. The results (just displaying the 200MB to 150MB to 200MB section of the output)?

As can be seen from the above, pausing 30 seconds after each adjustment of the PGA_AGGREGATE_TARGET parameter provides CKPT enough time to perform its processing (3 seconds likely would have been sufficient).

—

I recall reading an article on Jonathan Lewis’ site about a SORT_AREA_SIZE bug (search for SAS Bug on his site). I then wondered if adjusting the SORT_AREA_SIZE parameter when WORKAREA_SIZE_POLICY = MANUAL at the session level is also subject to the roughly 3 second delay. I put together the following test case, which differs quite a bit from the one referenced on the asktom.oracle.com site:

The output shows that at least in Oracle 11.1.0.7 there is no delay in the implementation of the SORT_AREA_SIZE at the session level when WORKAREA_SIZE_POLICY = MANUAL is set at the session level and the SORT_AREA_SIZE parameter is adjusted. The test query continued to perform a sort to disk with SORT_AREA_SIZEs OF 30MB or smaller. Every time the SORT_AREA_SIZE was set to 40MB without a delay, Oracle switched to an in-memory optimal sort. I might need to repeat the final test with an older release of Oracle to see if the same behavior is present (I will update this post if I see a change).

Back on the original topic, I now wonder if the occasional sorts to disk that I saw when experiementing with the SORT_AREA_SIZE parameter with PGA_AGGREGATE_TARGET set (and not being modified) in a production environment could have been a side-effect of the “CKPT process publishing the memory bounds only every three seconds” as was described in Joze’s paper. In short, the behavior was an unrelated cause and effect.

3 responses

Greg,
I would like to share the document with you, but Joze did not grant me that privilege. You could try to post a request for the document on his website: http://joze-senegacnik.blogspot.com/ but he put a significant amount of research into the paper and may not be able to openly share it.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: