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.
Charles Hooper's Oracle Notes 
Recent Comments