PGA Memory – The Developer’s Secret Weapon for Stealing All of the Memory in the Server 2

19012010

January 19, 2010

This article is a follow up to the earlier article – just how much PGA memory can a SQL statement with two NOT IN clauses, and an ORDER BY clause consume? As we saw in the previous post, DBMS_XPLAN.DISPLAY_CURSOR may be a bit misleading due to the scale of the Used-Tmp column, and the fact that not all of the memory listed in the Used-Mem column is necessarily used at the same time.

So, let’s try three experiments where we modify the SQL statement in the script to have one of the following:

AND T1.C1 BETWEEN 1 AND 500000
AND T1.C1 BETWEEN 1 AND 1000000
AND T1.C1 BETWEEN 1 AND 1400000

So, for the first test, the PGAMemoryFill2.sql script will look like this:

DECLARE
CURSOR C_MEMORY_FILL IS
SELECT
T1.C1,
T1.C2,
T1.C3
FROM
T1
WHERE
T1.C1 NOT IN (
SELECT
C1
FROM
T2)
AND T1.C2 NOT IN (
SELECT
C2
FROM
T3)
AND T1.C1 BETWEEN 1 AND 500000
ORDER BY
T1.C2 DESC,
T1.C1 DESC;
TYPE TYPE_MEMORY_FILL IS TABLE OF C_MEMORY_FILL%ROWTYPE
INDEX BY BINARY_INTEGER;
T_MEMORY_FILL TYPE_MEMORY_FILL;
BEGIN
OPEN C_MEMORY_FILL;
LOOP
FETCH C_MEMORY_FILL BULK COLLECT INTO T_MEMORY_FILL LIMIT 10000000;
EXIT WHEN T_MEMORY_FILL.COUNT = 0;
FOR I IN T_MEMORY_FILL.FIRST..T_MEMORY_FILL.LAST LOOP
NULL;
END LOOP;
DBMS_LOCK.SLEEP(20);
END LOOP;
END;
/

(You two DBAs who are about to stand and clap, sit back down, didn’t you learn anything from the previous article that used bulk collect?) We will use just two sessions, and make a small adjustment to the query of V$SQL_WORKAREA_ACTIVE so that we will be able to match the memory allocation to a specific step in the execution plan. Additionally, that view will be queried once approximately every 10 seconds.

As we can see from the above, the hash join at ID 2 continued to consume 4.04MB, while the hash join at ID 3 increased to 93.09 MB. When the hash join at ID 3 disappeared, the hash join at ID 2 consumed roughly 83.19MB. The two hash joins and the sort operation completed in-memory, without spilling to the TEMP tablespace.

Two executions of this SQL statement show the total PGA memory consumed by the session jumped up to a high of 207.40MB, but dropped down to 133.03MB, and then eventually hit 8.03MB when the script ended:

The DBMS_XPLAN output indicates that all three workarea executions where optimal with the sort consuming 61MB, the hash join at ID2 consuming 85MB, and the hash join at ID 3 consuming 93MB – but remember that the memory was not all used at the same time.

Let’s repeat the test with a larger number range to see if we are able to locate the tipping point.

As we are able to see from the above, the hash join at ID 2 continued consuming 8.06MB of memory while the hash join at ID 3 grew to 205.76MB. Once the hash join at ID 3 disappeared, the hash join at ID 2 grew to 185.75MB – both of the hash joins completed using an optimal, in-memory execution. We saw in the earlier test that the SORT operation at ID 1 required about 24MB less PGA memory that the hash join at ID 2, yet this time the sort operation spilled to disk, using 112MB of space in the TEMP tablespace and just 0.46MB of PGA memory (there must be a reason why the hash join completed in memory, but the SORT operation that consumed less memory spilled to disk, but it escapes me at the moment – the old rule before the PGA_AGGREGATE_TARGET was introduced is that HASH_AREA_SIZE defaulted to twice the value for SORT_AREA_SIZE – I wonder if some of that logic is still present).

The above seems to indicate that the SORT operation at ID 1 at one point consumed 126MB 116MB of memory, and must have then spilled to disk, reducing the memory usage to the 0.46MB value that we saw with the earlier query of V$SQL_WORKAREA_ACTIVE. This output confirms that the SORT operation performed a 1 pass workarea execution, while the two hash joins performed an optimal workarea execution.

Let’s repeat the test a final time with a larger number range to see if we are able to locate the tipping point.

All three of the workarea executions became 1 pass executions, but look at the Used-Mem and the Used-Tmp columns. If you had not seen the previous test cases, you might take a look at the DBMS_XPLAN output and remark how silly Oracle is to consume 116M of PGA memory during a SORT operation and spill to the TEMP tablespace just 156KB, or how silly it is that Oracle would consume 195MB in the hash join at ID 2 and spill just 240KB to the TEMP tablespace. It should now be obious that this is not what is happening – so much for relying on the DBMS_XPLAN output with ALLSTATS LAST specified at the format parameter and STATISTICS_LEVEL set to ALL. Your results could be different with a different Oracle release (the above test results are from 11.1.0.7), different value for PGA_AGGREGATE_TARGET, or with different levels of concurrent activity in the database.

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: