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

19 01 2010

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.

Session 1:

SELECT SID FROM V$MYSTAT WHERE ROWNUM<=1;

       SID
----------
       303

SET AUTOTRACE TRACEONLY EXPLAIN

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;

Plan hash value: 3251203018

-------------------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes |TempSpc| Cost (%CPU)|  Time    |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |   497K|    82M|       |   825K  (1)| 02:45:02 |
|   1 |  SORT ORDER BY       |      |   497K|    82M|    86M|   825K  (1)| 02:45:02 |
|*  2 |   HASH JOIN ANTI NA  |      |   497K|    82M|    73M|   806K  (1)| 02:41:14 |
|*  3 |    HASH JOIN ANTI NA |      |   499K|    68M|    71M|   668K  (1)| 02:13:46 |
|*  4 |     TABLE ACCESS FULL| T1   |   500K|    65M|       |   543K  (1)| 01:48:42 |
|   5 |     TABLE ACCESS FULL| T2   |    10M|    57M|       |   113K  (1)| 00:22:39 |
|   6 |    TABLE ACCESS FULL | T3   |    10M|   295M|       |   113K  (1)| 00:22:39 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."C2"="C2")
   3 - access("T1"."C1"="C1")
   4 - filter("T1"."C1"<=500000 AND "T1"."C1">=1)

SET AUTOTRACE OFF

ALTER SESSION SET STATISTICS_LEVEL=ALL;

@PGAMemoryFill2.sql

Session 2:

SET PAGESIZE 2000
SET LINESIZE 150

COLUMN ID FORMAT 99
COLUMN PASSES FORMAT 999999
COLUMN OPERATION_TYPE FORMAT A12
COLUMN WA_SIZE FORMAT 9999999990
SPOOL SQL_WORKAREA.TXT

SELECT
  SQL_ID,
  OPERATION_ID ID,
  OPERATION_TYPE,
  WORK_AREA_SIZE WA_SIZE,
  ACTUAL_MEM_USED,
  NUMBER_PASSES PASSES,
  TEMPSEG_SIZE
FROM
  V$SQL_WORKAREA_ACTIVE
ORDER BY
  SQL_ID,
  OPERATION_ID;

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
57vx5p5xq42jq   2 HASH-JOIN       98526208         4239360       0
57vx5p5xq42jq   3 HASH-JOIN       95463424        80363520       0

So far, the hash join at ID 2 is consuming about 4.04MB, and the hash join at ID 3 is consuming about 76.64MB.  Now we repeat the query of V$SQL_WORKAREA_ACTIVE roughly every 10 seconds:

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
57vx5p5xq42jq   2 HASH-JOIN       98526208         4239360       0
57vx5p5xq42jq   3 HASH-JOIN       95463424        80363520       0

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
57vx5p5xq42jq   2 HASH-JOIN       98526208         4239360       0
57vx5p5xq42jq   3 HASH-JOIN       95463424        80363520       0

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
57vx5p5xq42jq   2 HASH-JOIN       98526208         4239360       0
57vx5p5xq42jq   3 HASH-JOIN       95463424        80363520       0

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
57vx5p5xq42jq   2 HASH-JOIN       98526208         4239360       0
57vx5p5xq42jq   3 HASH-JOIN       95463424        80363520       0

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
57vx5p5xq42jq   2 HASH-JOIN       98526208         4239360       0
57vx5p5xq42jq   3 HASH-JOIN       95463424        80363520       0

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
57vx5p5xq42jq   2 HASH-JOIN       98526208         4239360       0
57vx5p5xq42jq   3 HASH-JOIN       95463424        80363520       0

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
57vx5p5xq42jq   2 HASH-JOIN       98526208         4239360       0
57vx5p5xq42jq   3 HASH-JOIN       95463424        80363520       0

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
57vx5p5xq42jq   2 HASH-JOIN       98526208         4239360       0
57vx5p5xq42jq   3 HASH-JOIN       95463424        80363520       0

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
57vx5p5xq42jq   2 HASH-JOIN       98526208         4239360       0
57vx5p5xq42jq   3 HASH-JOIN       95463424        80363520       0

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
57vx5p5xq42jq   2 HASH-JOIN       98526208         4239360       0
57vx5p5xq42jq   3 HASH-JOIN       95463424        80363520       0

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
57vx5p5xq42jq   2 HASH-JOIN       98526208         4239360       0
57vx5p5xq42jq   3 HASH-JOIN       95463424        80363520       0

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
57vx5p5xq42jq   2 HASH-JOIN       98526208         4239360       0
57vx5p5xq42jq   3 HASH-JOIN       95463424        80363520       0

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
57vx5p5xq42jq   2 HASH-JOIN       98526208         4239360       0
57vx5p5xq42jq   3 HASH-JOIN       95463424        80363520       0

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
57vx5p5xq42jq   2 HASH-JOIN       98526208         4239360       0
57vx5p5xq42jq   3 HASH-JOIN       95771648        97603584       0

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
57vx5p5xq42jq   2 HASH-JOIN       98526208         4239360       0
57vx5p5xq42jq   3 HASH-JOIN       95771648        97603584       0

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
57vx5p5xq42jq   2 HASH-JOIN       98526208         4239360       0
57vx5p5xq42jq   3 HASH-JOIN       95771648        97603584       0

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
57vx5p5xq42jq   2 HASH-JOIN       87232512        89805824       0

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
57vx5p5xq42jq   2 HASH-JOIN       87232512        89805824       0

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:

SELECT
  SN.NAME,
  SS.VALUE
FROM
  V$STATNAME SN,
  V$SESSTAT SS
WHERE
  SS.SID=303
  AND SS.STATISTIC#=SN.STATISTIC#
  AND SN.NAME LIKE '%pga%';

NAME                          VALUE
----------------------- -----------
session pga memory      139,489,936
session pga memory max  217,477,776

NAME                          VALUE
----------------------- -----------
session pga memory        8,417,936
session pga memory max  217,477,776

Let’s check the DBMS_XPLAN output:

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR('57vx5p5xq42jq',0,'ALLSTATS LAST'));

Plan hash value: 3251203018

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |    400K|00:02:33.07 |    2833K|   2833K|       |       |          |
|   1 |  SORT ORDER BY       |      |      1 |    497K|    400K|00:02:33.07 |    2833K|   2833K|    68M|  2873K|   61M (0)|
|*  2 |   HASH JOIN ANTI NA  |      |      1 |    497K|    400K|00:02:32.75 |    2833K|   2833K|    74M|  7919K|   85M (0)|
|*  3 |    HASH JOIN ANTI NA |      |      1 |    499K|    450K|00:02:09.73 |    2416K|   2416K|    82M|  7919K|   93M (0)|
|*  4 |     TABLE ACCESS FULL| T1   |      1 |    500K|    500K|00:01:46.14 |    2000K|   1999K|       |       |          |
|   5 |     TABLE ACCESS FULL| T2   |      1 |     10M|     10M|00:00:20.03 |     416K|    416K|       |       |          |
|   6 |    TABLE ACCESS FULL | T3   |      1 |     10M|     10M|00:00:20.03 |     416K|    416K|       |       |          |
---------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."C2"="C2")
   3 - access("T1"."C1"="C1")
   4 - filter(("T1"."C1"<=500000 AND "T1"."C1">=1))

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.

Session 1:

SET AUTOTRACE TRACEONLY EXPLAIN

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 1000000
ORDER BY
  T1.C2 DESC,
  T1.C1 DESC;

Plan hash value: 3251203018

-------------------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes |TempSpc| Cost (%CPU)|  Time    |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |   995K|   165M|       |   851K  (1)| 02:50:16 |
|   1 |  SORT ORDER BY       |      |   995K|   165M|   172M|   851K  (1)| 02:50:16 |
|*  2 |   HASH JOIN ANTI NA  |      |   995K|   165M|   147M|   813K  (1)| 02:42:40 |
|*  3 |    HASH JOIN ANTI NA |      |   999K|   136M|   142M|   672K  (1)| 02:14:28 |
|*  4 |     TABLE ACCESS FULL| T1   |  1000K|   130M|       |   543K  (1)| 01:48:42 |
|   5 |     TABLE ACCESS FULL| T2   |    10M|    57M|       |   113K  (1)| 00:22:39 |
|   6 |    TABLE ACCESS FULL | T3   |    10M|   295M|       |   113K  (1)| 00:22:39 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."C2"="C2")
   3 - access("T1"."C1"="C1")
   4 - filter("T1"."C1"<=1000000 AND "T1"."C1">=1)

SET AUTOTRACE OFF

@PGAMemoryFill2.sql

Session 2:

SELECT
  SQL_ID,
  OPERATION_ID ID,
  OPERATION_TYPE,
  WORK_AREA_SIZE WA_SIZE,
  ACTUAL_MEM_USED,
  NUMBER_PASSES PASSES,
  TEMPSEG_SIZE
FROM
  V$SQL_WORKAREA_ACTIVE
ORDER BY
  SQL_ID,
  OPERATION_ID;

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
7wy7nqhbn5v7g   2 HASH-JOIN      187907072         8454144       0
7wy7nqhbn5v7g   3 HASH-JOIN      181816320       161557504       0 

We start off with the hash join at ID 2 consuming 8.06MB and the hash join at ID 3 consuming  154.07MB.  Now we continuing executing that query roughly every 10 seconds:

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
7wy7nqhbn5v7g   2 HASH-JOIN      187907072         8454144       0
7wy7nqhbn5v7g   3 HASH-JOIN      181816320       161557504       0

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
7wy7nqhbn5v7g   2 HASH-JOIN      187907072         8454144       0
7wy7nqhbn5v7g   3 HASH-JOIN      181816320       161557504       0

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
7wy7nqhbn5v7g   2 HASH-JOIN      187907072         8454144       0
7wy7nqhbn5v7g   3 HASH-JOIN      181816320       161557504       0

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
7wy7nqhbn5v7g   2 HASH-JOIN      187907072         8454144       0
7wy7nqhbn5v7g   3 HASH-JOIN      181816320       161557504       0

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
7wy7nqhbn5v7g   2 HASH-JOIN      187907072         8454144       0
7wy7nqhbn5v7g   3 HASH-JOIN      181816320       161557504       0

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
7wy7nqhbn5v7g   2 HASH-JOIN      187907072         8454144       0
7wy7nqhbn5v7g   3 HASH-JOIN      181816320       161557504       0

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
7wy7nqhbn5v7g   2 HASH-JOIN      187907072         8454144       0
7wy7nqhbn5v7g   3 HASH-JOIN      181816320       161557504       0

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
7wy7nqhbn5v7g   2 HASH-JOIN      187907072         8454144       0
7wy7nqhbn5v7g   3 HASH-JOIN      181816320       161557504       0

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
7wy7nqhbn5v7g   2 HASH-JOIN      187907072         8454144       0
7wy7nqhbn5v7g   3 HASH-JOIN      181816320       161557504       0

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
7wy7nqhbn5v7g   2 HASH-JOIN      187907072         8454144       0
7wy7nqhbn5v7g   3 HASH-JOIN      181816320       161557504       0

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
7wy7nqhbn5v7g   2 HASH-JOIN      187907072         8454144       0
7wy7nqhbn5v7g   3 HASH-JOIN      181816320       161557504       0

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
7wy7nqhbn5v7g   2 HASH-JOIN      187907072         8454144       0
7wy7nqhbn5v7g   3 HASH-JOIN      188286976       215750656       0

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
7wy7nqhbn5v7g   2 HASH-JOIN      187907072         8454144       0
7wy7nqhbn5v7g   3 HASH-JOIN      188286976       215750656       0

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
7wy7nqhbn5v7g   2 HASH-JOIN      167822336       194778112       0

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
7wy7nqhbn5v7g   2 HASH-JOIN      167822336       194778112       0

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
7wy7nqhbn5v7g   2 HASH-JOIN      167822336       194778112       0

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
7wy7nqhbn5v7g   1 SORT (v2)        1245184          486400       1    117440512

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).

So, what about the PGA memory usage?

SELECT
  SN.NAME,
  SS.VALUE
FROM
  V$STATNAME SN,
  V$SESSTAT SS
WHERE
  SS.SID=303
  AND SS.STATISTIC#=SN.STATISTIC#
  AND SN.NAME LIKE '%pga%';

NAME                          VALUE
----------------------- -----------
session pga memory      287,994,512
session pga memory max  390,558,352

NAME                          VALUE
----------------------- -----------
session pga memory        8,549,008
session pga memory max  390,558,352

The PGA memory usage hit a high of 372.47MB and dropped down to 8.15MB when the script completed.  Let’s check the DBMS_XPLAN output:

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR('7wy7nqhbn5v7g',0,'ALLSTATS LAST'));

Plan hash value: 3251203018

----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |    800K|00:02:37.04 |    2833K|   2847K|  14286 |       |       |          |         |
|   1 |  SORT ORDER BY       |      |      1 |    995K|    800K|00:02:37.04 |    2833K|   2847K|  14286 |   126M|  3808K|  116M (1)|     112K|
|*  2 |   HASH JOIN ANTI NA  |      |      1 |    995K|    800K|00:02:33.47 |    2833K|   2833K|      0 |   145M|  7919K|  185M (0)|         |
|*  3 |    HASH JOIN ANTI NA |      |      1 |    999K|    900K|00:02:09.85 |    2416K|   2416K|      0 |   161M|  7919K|  205M (0)|         |
|*  4 |     TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:01:45.86 |    2000K|   1999K|      0 |       |       |          |         |
|   5 |     TABLE ACCESS FULL| T2   |      1 |     10M|     10M|00:00:20.00 |     416K|    416K|      0 |       |       |          |         |
|   6 |    TABLE ACCESS FULL | T3   |      1 |     10M|     10M|00:00:10.03 |     416K|    416K|      0 |       |       |          |         |
----------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."C2"="C2")
   3 - access("T1"."C1"="C1")
   4 - filter(("T1"."C1"<=1000000 AND "T1"."C1">=1))

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.

Session 1:

SET AUTOTRACE TRACEONLY EXPLAIN

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 1400000
ORDER BY
  T1.C2 DESC,
  T1.C1 DESC;

Plan hash value: 1147745168

------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes |TempSpc| Cost (%CPU)|Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |  1393K|   231M|       |   872K  (1)|02:54:27 |
|   1 |  SORT ORDER BY            |      |  1393K|   231M|   242M|   872K  (1)|02:54:27 |
|*  2 |   HASH JOIN ANTI NA       |      |  1393K|   231M|   206M|   819K  (1)|02:43:49 |
|*  3 |    HASH JOIN RIGHT ANTI NA|      |  1399K|   190M|   171M|   675K  (1)|02:15:02 |
|   4 |     TABLE ACCESS FULL     | T2   |    10M|    57M|       |   113K  (1)|00:22:39 |
|*  5 |     TABLE ACCESS FULL     | T1   |  1400K|   182M|       |   543K  (1)|01:48:42 |
|   6 |    TABLE ACCESS FULL      | T3   |    10M|   295M|       |   113K  (1)|00:22:39 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."C2"="C2")
   3 - access("T1"."C1"="C1")
   5 - filter("T1"."C1"<=1400000 AND "T1"."C1">=1)

SET AUTOTRACE OFF

Session 2:

SELECT
  SQL_ID,
  OPERATION_ID ID,
  OPERATION_TYPE,
  WORK_AREA_SIZE WA_SIZE,
  ACTUAL_MEM_USED,
  NUMBER_PASSES PASSES,
  TEMPSEG_SIZE
FROM
  V$SQL_WORKAREA_ACTIVE
ORDER BY
  SQL_ID,
  OPERATION_ID;

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
a6yfcryfux22j   2 HASH-JOIN       21715968         8454144       0
a6yfcryfux22j   3 HASH-JOIN       29298688        20733952       0     19922944

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
a6yfcryfux22j   2 HASH-JOIN       21715968         8454144       0
a6yfcryfux22j   3 HASH-JOIN       29298688        20733952       0     57671680

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
a6yfcryfux22j   2 HASH-JOIN       21715968         8454144       0
a6yfcryfux22j   3 HASH-JOIN       29298688        20733952       0     96468992

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
a6yfcryfux22j   2 HASH-JOIN       21715968         8454144       0
a6yfcryfux22j   3 HASH-JOIN      132551680        97767424       1    130023424

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
a6yfcryfux22j   2 HASH-JOIN       21715968        20730880       0    173015040
a6yfcryfux22j   3 HASH-JOIN      145126400       151730176       1    169869312

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
a6yfcryfux22j   2 HASH-JOIN       21715968        20730880       0    173015040
a6yfcryfux22j   3 HASH-JOIN      145126400       151730176       1    169869312

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
a6yfcryfux22j   2 HASH-JOIN       21715968        20730880       0    173015040
a6yfcryfux22j   3 HASH-JOIN      145126400       151730176       1    169869312

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
a6yfcryfux22j   2 HASH-JOIN       21715968        20730880       0    173015040
a6yfcryfux22j   3 HASH-JOIN      145126400       151730176       1    169869312

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
a6yfcryfux22j   2 HASH-JOIN       21715968        20730880       0    173015040
a6yfcryfux22j   3 HASH-JOIN      145126400       151730176       1    169869312

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
a6yfcryfux22j   2 HASH-JOIN       21715968        20730880       0    173015040
a6yfcryfux22j   3 HASH-JOIN      145126400       151730176       1    169869312

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
a6yfcryfux22j   2 HASH-JOIN       21715968        20730880       0    173015040
a6yfcryfux22j   3 HASH-JOIN      145126400       151730176       1    169869312

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
a6yfcryfux22j   2 HASH-JOIN       21715968        20730880       0    173015040
a6yfcryfux22j   3 HASH-JOIN      145126400       151730176       1    169869312

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
a6yfcryfux22j   2 HASH-JOIN       21715968        20730880       0    173015040
a6yfcryfux22j   3 HASH-JOIN      145126400       151730176       1    169869312

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
a6yfcryfux22j   2 HASH-JOIN       21715968        20730880       0    173015040
a6yfcryfux22j   3 HASH-JOIN      145126400       151730176       1    169869312

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
a6yfcryfux22j   2 HASH-JOIN       21715968        20730880       0    173015040
a6yfcryfux22j   3 HASH-JOIN      145126400       151730176       1    169869312

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
a6yfcryfux22j   2 HASH-JOIN       21715968        20730880       0    173015040
a6yfcryfux22j   3 HASH-JOIN      145126400       151730176       1    169869312

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
a6yfcryfux22j   2 HASH-JOIN      190238720       105683968       1    189792256

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
a6yfcryfux22j   2 HASH-JOIN      202813440       204740608       1    199229440

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
a6yfcryfux22j   2 HASH-JOIN      202813440       204740608       1    220200960

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
a6yfcryfux22j   2 HASH-JOIN      202813440       204740608       1    242221056

SQL_ID         ID OPERATION_TY     WA_SIZE ACTUAL_MEM_USED  PASSES TEMPSEG_SIZE
------------- --- ------------ ----------- --------------- ------- ------------
a6yfcryfux22j   1 SORT (v2)       32429056        25973760       1    137363456
a6yfcryfux22j   2 HASH-JOIN       10075136         8312832       1    251658240

SELECT
  SN.NAME,
  SS.VALUE
FROM
  V$STATNAME SN,
  V$SESSTAT SS
WHERE
  SS.SID=303
  AND SS.STATISTIC#=SN.STATISTIC#
  AND SN.NAME LIKE '%pga%';

NAME                          VALUE
----------------------- -----------
session pga memory      377,975,440
session pga memory max  390,558,352

NAME                          VALUE
----------------------- -----------
session pga memory        8,549,008
session pga memory max  390,558,352

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR('a6yfcryfux22j',0,'ALLSTATS LAST'));

Plan hash value: 1147745168

---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |      1 |        |   1120K|00:03:03.88 |    2833K|   2902K|  68818 |       |       |          |         |
|   1 |  SORT ORDER BY            |      |      1 |   1393K|   1120K|00:03:03.88 |    2833K|   2902K|  68818 |   177M|  4474K|  116M (1)|     158K|
|*  2 |   HASH JOIN ANTI NA       |      |      1 |   1393K|   1120K|00:02:57.80 |    2833K|   2882K|  48701 |   202M|  7914K|  195M (1)|     240K|
|*  3 |    HASH JOIN RIGHT ANTI NA|      |      1 |   1399K|   1260K|00:02:23.18 |    2416K|   2436K|  19840 |   269M|    14M|  144M (1)|     162K|
|   4 |     TABLE ACCESS FULL     | T2   |      1 |     10M|     10M|00:00:20.03 |     416K|    416K|      0 |       |       |          |         |
|*  5 |     TABLE ACCESS FULL     | T1   |      1 |   1400K|   1400K|00:01:48.32 |    2000K|   1999K|      0 |       |       |          |         |
|   6 |    TABLE ACCESS FULL      | T3   |      1 |     10M|     10M|00:00:20.03 |     416K|    416K|      0 |       |       |          |         |
---------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."C2"="C2")
   3 - access("T1"."C1"="C1")
   5 - filter(("T1"."C1"<=1400000 AND "T1"."C1">=1))

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.


Actions

Information

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 147 other followers

%d bloggers like this: