December 23, 2009
This is a follow-up to my post from a couple days ago (back to the previous post).
A small confession may be found below…
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.
SET PAGESIZE 2000 SET LINESIZE 140 SET ARRAYSIZE 100 SPOOL SortToDiskTest11.1.0.7-1.txt DROP TABLE T1 PURGE; CREATE TABLE T1 ( C1 VARCHAR2(20), C2 NUMBER(12), C3 NUMBER(12)); /* Fill the test table to 1,000,000 rows: */ DECLARE X NUMBER; BEGIN FOR X IN 1 .. 1000000 LOOP INSERT INTO T1 VALUES ( TO_CHAR(MOD( X ,100))||'-'||TO_CHAR(MOD( X ,100)), MOD( X ,500), X); END LOOP; END; / COMMIT; /* Gather statistics on the table: */ EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1'); /*+ Let's see where the sort statistics are right now: */ SELECT NAME, VALUE FROM V$SYSSTAT WHERE UPPER(NAME) LIKE '%SORT%' ORDER BY NAME; /*+ Let's turn on a 10053 trace so that we can see what the cost-based optimizer is willing to report: */ ALTER SESSION SET TRACEFILE_IDENTIFIER = 'Default_Opt'; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1'; SELECT 'STARTING POINT' FROM DUAL; SPOOL OFF /*+ Run a simple SQL statement to generate a sort, and gather statistics for DBMS_XPLAN: */ SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3; SPOOL SortToDiskTest11.1.0.7-1.txt APPEND /*+ Determine the DBMS_XPLAN for the query: */ SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); /* Let's check the sort statistics: */ SELECT NAME, VALUE FROM V$SYSSTAT WHERE UPPER(NAME) LIKE '%SORT%' ORDER BY NAME; /* Let's look at the plan statistics for the SQL statement: */ SELECT SP.LAST_OUTPUT_ROWS, SP.TEMP_SPACE, SP.ESTIMATED_OPTIMAL_SIZE, SP.ESTIMATED_ONEPASS_SIZE, SP.LAST_MEMORY_USED, SP.LAST_EXECUTION, SP.LAST_TEMPSEG_SIZE FROM V$SQL S, V$SQL_PLAN_STATISTICS_ALL SP WHERE S.SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */%' AND S.SQL_ID=SP.SQL_ID; /* Let's try again, this time with a 200MB PGA_AGGREGATE_TARGET rather than a 150MB: */ ALTER SYSTEM SET PGA_AGGREGATE_TARGET=200M; ALTER SYSTEM FLUSH SHARED_POOL; SELECT 'PGA 200' FROM DUAL; SPOOL OFF SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3; SPOOL SortToDiskTest11.1.0.7-1.txt APPEND SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); /*+ Let's check the sort statistics: */ SELECT NAME, VALUE FROM V$SYSSTAT WHERE UPPER(NAME) LIKE '%SORT%' ORDER BY NAME; /*+ Let's look at the plan statistics for the SQL statement: */ SELECT SP.LAST_OUTPUT_ROWS, SP.TEMP_SPACE, SP.ESTIMATED_OPTIMAL_SIZE, SP.ESTIMATED_ONEPASS_SIZE, SP.LAST_MEMORY_USED, SP.LAST_EXECUTION, SP.LAST_TEMPSEG_SIZE FROM V$SQL S, V$SQL_PLAN_STATISTICS_ALL SP WHERE S.SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */%' AND S.SQL_ID=SP.SQL_ID; /* Since we are still sorting to disk, let's increase the PGA_AGGREGATE_TARGET again and repeat the test: */ ALTER SYSTEM SET PGA_AGGREGATE_TARGET=300M; ALTER SYSTEM FLUSH SHARED_POOL; SELECT 'PGA 300' FROM DUAL; SPOOL OFF SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3; SPOOL SortToDiskTest11.1.0.7-1.txt APPEND SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); SELECT NAME, VALUE FROM V$SYSSTAT WHERE UPPER(NAME) LIKE '%SORT%' ORDER BY NAME; /* Let's continue the test, dropping PGA_AGGREGATE_TARGET back to 200MB: */ ALTER SYSTEM SET PGA_AGGREGATE_TARGET=200M; ALTER SYSTEM FLUSH SHARED_POOL; SELECT 'PGA 200' FROM DUAL; SPOOL OFF SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3; SPOOL SortToDiskTest11.1.0.7-1.txt APPEND SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); SELECT NAME, VALUE FROM V$SYSSTAT WHERE UPPER(NAME) LIKE '%SORT%' ORDER BY NAME; SELECT SP.LAST_OUTPUT_ROWS, SP.TEMP_SPACE, SP.ESTIMATED_OPTIMAL_SIZE, SP.ESTIMATED_ONEPASS_SIZE, SP.LAST_MEMORY_USED, SP.LAST_EXECUTION, SP.LAST_TEMPSEG_SIZE FROM V$SQL S, V$SQL_PLAN_STATISTICS_ALL SP WHERE S.SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */%' AND S.SQL_ID=SP.SQL_ID; /* Let's drop the PGA_AGGREGATE_TARGET to 150MB and see if we have an optimal sort (no sort to disk): */ ALTER SYSTEM SET PGA_AGGREGATE_TARGET=150M; ALTER SYSTEM FLUSH SHARED_POOL; SELECT 'PGA 150' FROM DUAL; SPOOL OFF SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3; SPOOL SortToDiskTest11.1.0.7-1.txt APPEND SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); SELECT NAME, VALUE FROM V$SYSSTAT WHERE UPPER(NAME) LIKE '%SORT%' ORDER BY NAME; SELECT SP.LAST_OUTPUT_ROWS, SP.TEMP_SPACE, SP.ESTIMATED_OPTIMAL_SIZE, SP.ESTIMATED_ONEPASS_SIZE, SP.LAST_MEMORY_USED, SP.LAST_EXECUTION, SP.LAST_TEMPSEG_SIZE FROM V$SQL S, V$SQL_PLAN_STATISTICS_ALL SP WHERE S.SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */%' AND S.SQL_ID=SP.SQL_ID; /*+ Let's again increase the PGA_AGGREGATE_TARGET to 200MB and see if we have an optimal sort (no sort to disk): */ ALTER SYSTEM SET PGA_AGGREGATE_TARGET=200M; ALTER SYSTEM FLUSH SHARED_POOL; SELECT 'PGA 200' FROM DUAL; SPOOL OFF SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3; SPOOL SortToDiskTest11.1.0.7-1.txt APPEND SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); SELECT NAME, VALUE FROM V$SYSSTAT WHERE UPPER(NAME) LIKE '%SORT%' ORDER BY NAME; SELECT SP.LAST_OUTPUT_ROWS, SP.TEMP_SPACE, SP.ESTIMATED_OPTIMAL_SIZE, SP.ESTIMATED_ONEPASS_SIZE, SP.LAST_MEMORY_USED, SP.LAST_EXECUTION, SP.LAST_TEMPSEG_SIZE FROM V$SQL S, V$SQL_PLAN_STATISTICS_ALL SP WHERE S.SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */%' AND S.SQL_ID=SP.SQL_ID; /* Now, let's see if changing the SORT_AREA_SIZE helps remove the sort to disk: */ ALTER SYSTEM FLUSH SHARED_POOL; ALTER SESSION SET SORT_AREA_SIZE=41943040; ALTER SESSION SET SORT_AREA_RETAINED_SIZE=41943040; SELECT 'ADJUST SAS' FROM DUAL; SPOOL OFF SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3; SPOOL SortToDiskTest11.1.0.7-1.txt APPEND SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); SELECT NAME, VALUE FROM V$SYSSTAT WHERE UPPER(NAME) LIKE '%SORT%' ORDER BY NAME; SELECT SP.LAST_OUTPUT_ROWS, SP.TEMP_SPACE, SP.ESTIMATED_OPTIMAL_SIZE, SP.ESTIMATED_ONEPASS_SIZE, SP.LAST_MEMORY_USED, SP.LAST_EXECUTION, SP.LAST_TEMPSEG_SIZE FROM V$SQL S, V$SQL_PLAN_STATISTICS_ALL SP WHERE S.SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */%' AND S.SQL_ID=SP.SQL_ID; /* Now, let's try a similar test with optimizer_features_enable set to 10.2.0.3 */ ALTER SESSION SET TRACEFILE_IDENTIFIER = 'OFE_10.2.0.3'; ALTER SYSTEM SET optimizer_features_enable='10.2.0.3'; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1'; SELECT 'OFE_10.2.0.3' FROM DUAL; SPOOL OFF /* Let's first dump a DBMS_XPLAN with the estimated statistics. */ SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3; SPOOL SortToDiskTest11.1.0.7-1.txt APPEND SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,NULL)); SPOOL OFF /* Let's repeat, this time requesting the additional statistics from DBMS XPLAN: */ SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3; SPOOL SortToDiskTest11.1.0.7-1.txt APPEND SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); SELECT NAME, VALUE FROM V$SYSSTAT WHERE UPPER(NAME) LIKE '%SORT%' ORDER BY NAME; /* Repeat with pga_aggregate_target at 200MB: */ ALTER SYSTEM SET PGA_AGGREGATE_TARGET=200M; ALTER SYSTEM FLUSH SHARED_POOL; SELECT 'PGA 200' FROM DUAL; SPOOL OFF SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3; SPOOL SortToDiskTest11.1.0.7-1.txt APPEND SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); SELECT NAME, VALUE FROM V$SYSSTAT WHERE UPPER(NAME) LIKE '%SORT%' ORDER BY NAME; /* Repeat with pga_aggregate_target at 100MB: */ ALTER SYSTEM SET PGA_AGGREGATE_TARGET=100M; ALTER SYSTEM FLUSH SHARED_POOL; SELECT NAME, VALUE FROM V$SYSSTAT WHERE UPPER(NAME) LIKE '%SORT%' ORDER BY NAME; /* Let's try again, this time bumping the SORT_AREA_SIZE to roughly 40MB: */ ALTER SYSTEM FLUSH SHARED_POOL; ALTER SESSION SET SORT_AREA_SIZE=41943040; ALTER SESSION SET SORT_AREA_RETAINED_SIZE=41943040; SELECT 'SAS' FROM DUAL; SPOOL OFF SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3; SPOOL SortToDiskTest11.1.0.7-1.txt APPEND SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); SELECT NAME, VALUE FROM V$SYSSTAT WHERE UPPER(NAME) LIKE '%SORT%' ORDER BY NAME; /* Let's try again, this time using the optimizer setting for Oracle 10.2.0.2: */ ALTER SYSTEM FLUSH SHARED_POOL; ALTER SESSION SET TRACEFILE_IDENTIFIER = 'OFE_10.2.0.2'; ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='10.2.0.2'; SELECT 'OFE_10.2.0.2' FROM DUAL; SPOOL OFF SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3; SPOOL SortToDiskTest11.1.0.7-1.txt APPEND SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); SELECT NAME, VALUE FROM V$SYSSTAT WHERE UPPER(NAME) LIKE '%SORT%' ORDER BY NAME; /* Let's try again, this time using the optimizer setting for Oracle 10.1.0.4: */ ALTER SYSTEM FLUSH SHARED_POOL; ALTER SESSION SET TRACEFILE_IDENTIFIER = 'OFE_10.1.0.4'; ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='10.1.0.4'; SELECT 'OFE_10.1.0.4' FROM DUAL; SPOOL OFF SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3; SPOOL SortToDiskTest11.1.0.7-1.txt APPEND SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); SELECT NAME, VALUE FROM V$SYSSTAT WHERE UPPER(NAME) LIKE '%SORT%' ORDER BY NAME; /* Let's try again with pga_aggregate_target at 300MB: */ ALTER SYSTEM SET PGA_AGGREGATE_TARGET=300M; ALTER SYSTEM FLUSH SHARED_POOL; SELECT 'PGA 300' FROM DUAL; SPOOL OFF SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3; SPOOL SortToDiskTest11.1.0.7-1.txt APPEND SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); SELECT NAME, VALUE FROM V$SYSSTAT WHERE UPPER(NAME) LIKE '%SORT%' ORDER BY NAME; /* Try again, reseting SORT_AREA_SIZE: */ ALTER SESSION SET SORT_AREA_SIZE=65536; ALTER SESSION SET SORT_AREA_RETAINED_SIZE=0; ALTER SYSTEM FLUSH SHARED_POOL; SELECT 'SAS 64KB' FROM DUAL; SPOOL OFF SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3; SPOOL SortToDiskTest11.1.0.7-1.txt APPEND SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); SELECT NAME, VALUE FROM V$SYSSTAT WHERE UPPER(NAME) LIKE '%SORT%' ORDER BY NAME; /* Just to confirm, one more time: */ ALTER SYSTEM FLUSH SHARED_POOL; ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='10.1.0.4'; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1'; SELECT 'OFE_10.1.0.4' FROM DUAL; SPOOL OFF SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3; SPOOL SortToDiskTest11.1.0.7-1.txt APPEND SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); SELECT NAME, VALUE FROM V$SYSSTAT WHERE UPPER(NAME) LIKE '%SORT%' ORDER BY NAME; /* Try again, setting PGA_AGGREGATE_TARGET back to 200MB: */ ALTER SYSTEM SET PGA_AGGREGATE_TARGET=200M; ALTER SYSTEM FLUSH SHARED_POOL; SELECT 'PGA 200' FROM DUAL; SPOOL OFF SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3; SPOOL SortToDiskTest11.1.0.7-1.txt APPEND SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); SELECT NAME, VALUE FROM V$SYSSTAT WHERE UPPER(NAME) LIKE '%SORT%' ORDER BY NAME; SELECT SP.LAST_OUTPUT_ROWS, SP.TEMP_SPACE, SP.ESTIMATED_OPTIMAL_SIZE, SP.ESTIMATED_ONEPASS_SIZE, SP.LAST_MEMORY_USED, SP.LAST_EXECUTION, SP.LAST_TEMPSEG_SIZE FROM V$SQL S, V$SQL_PLAN_STATISTICS_ALL SP WHERE S.SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */%' AND S.SQL_ID=SP.SQL_ID; /* Trying again with PGA_AGGREGATE_TARGET at 100MB: */ ALTER SYSTEM SET PGA_AGGREGATE_TARGET=100M; ALTER SYSTEM FLUSH SHARED_POOL; SELECT 'PGA 100' FROM DUAL; SPOOL OFF SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3; SPOOL SortToDiskTest11.1.0.7-1.txt APPEND SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); SELECT NAME, VALUE FROM V$SYSSTAT WHERE UPPER(NAME) LIKE '%SORT%' ORDER BY NAME; SELECT SP.LAST_OUTPUT_ROWS, SP.TEMP_SPACE, SP.ESTIMATED_OPTIMAL_SIZE, SP.ESTIMATED_ONEPASS_SIZE, SP.LAST_MEMORY_USED, SP.LAST_EXECUTION, SP.LAST_TEMPSEG_SIZE FROM V$SQL S, V$SQL_PLAN_STATISTICS_ALL SP WHERE S.SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */%' AND S.SQL_ID=SP.SQL_ID; /* Let's be cruel to Oracle to see what happens with PGA_AGGREGATE_TARGET at 20MB: */ ALTER SYSTEM SET PGA_AGGREGATE_TARGET=20M; ALTER SYSTEM FLUSH SHARED_POOL; SELECT 'PGA 20' FROM DUAL; SPOOL OFF SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3; SPOOL SortToDiskTest11.1.0.7-1.txt APPEND SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); CONNECT /@OR11 AS SYSDBA SELECT SP.LAST_OUTPUT_ROWS, SP.TEMP_SPACE, SP.ESTIMATED_OPTIMAL_SIZE, SP.ESTIMATED_ONEPASS_SIZE, SP.LAST_MEMORY_USED, SP.LAST_EXECUTION, SP.LAST_TEMPSEG_SIZE FROM V$SQL S, V$SQL_PLAN_STATISTICS_ALL SP WHERE S.SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */%' AND S.SQL_ID=SP.SQL_ID; /* Let's see the hidden parameters (adapted from a script on Jonathan Lewis' website): */ SELECT UPPER(NAM.KSPPINM) NAME, VAL.KSPPSTVL VALUE, VAL.KSPPSTDF ISDEFAULT, DECODE(BITAND(VAL.KSPPSTVF,7), 1,'MODIFIED', 4,'SYSTEM MODIFIED', 'FALSE') ISMODIFIED FROM X$KSPPI NAM, X$KSPPSV VAL WHERE NAM.INDX = VAL.INDX AND UPPER(NAM.KSPPINM) IN ('_SMM_MIN_SIZE','_SMM_MAX_SIZE') ORDER BY UPPER(NAM.KSPPINM); SPOOL OFF
The output of the above script follows, slightly cleaned up:
NAME VALUE -------------- ------- sorts (disk) 0 sorts (memory) 3746 sorts (rows) 33118 'STARTINGPOINT -------------- STARTING POINT PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------- SQL_ID auy15crnrmkc3, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3 Plan hash value: 2148421099 ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1000K|00:00:00.28 | 2712 | | | | | 1 | SORT ORDER BY | | 1 | 1000K| 1000K|00:00:00.28 | 2712 | 38M| 2211K| 34M (0)| | 2 | TABLE ACCESS FULL| T1 | 1 | 1000K| 1000K|00:00:00.01 | 2712 | | | | ---------------------------------------------------------------------------------------------------------------- NAME VALUE -------------- ------- sorts (disk) 0 sorts (memory) 6291 sorts (rows) 1196353 LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE ---------------- ---------- ---------------------- ---------------------- ---------------- ---------- ----------------- 1000000 1000000 24110000 40624128 2264064 36109312 OPTIMAL 1000000 'PGA200 ------- PGA 200 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID auy15crnrmkc3, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3 Plan hash value: 2148421099 ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1000K|00:00:00.28 | 2712 | | | | | 1 | SORT ORDER BY | | 1 | 1000K| 1000K|00:00:00.28 | 2712 | 38M| 2211K| 34M (0)| | 2 | TABLE ACCESS FULL| T1 | 1 | 1000K| 1000K|00:00:00.01 | 2712 | | | | ---------------------------------------------------------------------------------------------------------------- NAME VALUE -------------- ------- sorts (disk) 0 sorts (memory) 8948 sorts (rows) 2217360 LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE ---------------- ---------- ---------------------- ---------------------- ---------------- ---------- ----------------- 1000000 1000000 24110000 40624128 2264064 36109312 OPTIMAL 1000000 'PGA300 ------- PGA 300 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID auy15crnrmkc3, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3 Plan hash value: 2148421099 ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1000K|00:00:03.28 | 2712 | | | | | 1 | SORT ORDER BY | | 1 | 1000K| 1000K|00:00:03.28 | 2712 | 38M| 2211K| 34M (0)| | 2 | TABLE ACCESS FULL| T1 | 1 | 1000K| 1000K|00:00:00.01 | 2712 | | | | ---------------------------------------------------------------------------------------------------------------- NAME VALUE -------------- ------- sorts (disk) 0 sorts (memory) 11357 sorts (rows) 3367784 'PGA200 ------- PGA 200 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID auy15crnrmkc3, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3 Plan hash value: 2148421099 ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1000K|00:00:00.28 | 2712 | | | | | 1 | SORT ORDER BY | | 1 | 1000K| 1000K|00:00:00.28 | 2712 | 38M| 2211K| 34M (0)| | 2 | TABLE ACCESS FULL| T1 | 1 | 1000K| 1000K|00:00:00.01 | 2712 | | | | ---------------------------------------------------------------------------------------------------------------- NAME VALUE -------------- ------- sorts (disk) 0 sorts (memory) 13757 sorts (rows) 4388065 LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE ---------------- ---------- ---------------------- ---------------------- ---------------- ---------- ----------------- 1000000 1000000 24110000 40624128 2264064 36109312 OPTIMAL 1000000 'PGA150 ------- PGA 150 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID auy15crnrmkc3, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3 Plan hash value: 2148421099 -------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp| -------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1000K|00:00:00.62 | 2715 | 2927 | 2927 | | | | | | 1 | SORT ORDER BY | | 1 | 1000K| 1000K|00:00:00.62 | 2715 | 2927 | 2927 | 25M| 1845K| 30M (1)| 23552 | | 2 | TABLE ACCESS FULL| T1 | 1 | 1000K| 1000K|00:00:00.01 | 2712 | 0 | 0 | | | | | -------------------------------------------------------------------------------------------------------------------------------------------- NAME VALUE -------------- ------- sorts (disk) 1 sorts (memory) 17889 sorts (rows) 5558136 LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE ---------------- ---------- ---------------------- ---------------------- ---------------- ---------- ----------------- 1000000 1000000 24110000 27058176 1889280 31464448 1 PASS 23552 1000000 'PGA200 ------- PGA 200 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID auy15crnrmkc3, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3 Plan hash value: 2148421099 -------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp| -------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1000K|00:00:00.44 | 2715 | 2927 | 2927 | | | | | | 1 | SORT ORDER BY | | 1 | 1000K| 1000K|00:00:00.44 | 2715 | 2927 | 2927 | 25M| 1843K| 30M (1)| 23552 | | 2 | TABLE ACCESS FULL| T1 | 1 | 1000K| 1000K|00:00:00.01 | 2712 | 0 | 0 | | | | | -------------------------------------------------------------------------------------------------------------------------------------------- NAME VALUE -------------- ------- sorts (disk) 2 sorts (memory) 20280 sorts (rows) 6578105 LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE ---------------- ---------- ---------------------- ---------------------- ---------------- ---------- ----------------- 1000000 1000000 24110000 26975232 1887232 31464448 1 PASS 23552 1000000 'ADJUSTSAS ---------- ADJUST SAS PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID auy15crnrmkc3, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3 Plan hash value: 2148421099 ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1000K|00:00:00.28 | 2712 | | | | | 1 | SORT ORDER BY | | 1 | 1000K| 1000K|00:00:00.28 | 2712 | 38M| 2211K| 34M (0)| | 2 | TABLE ACCESS FULL| T1 | 1 | 1000K| 1000K|00:00:00.01 | 2712 | | | | ---------------------------------------------------------------------------------------------------------------- NAME VALUE -------------- ------- sorts (disk) 2 sorts (memory) 22832 sorts (rows) 7729292 LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE ---------------- ---------- ---------------------- ---------------------- ---------------- ---------- ----------------- 1000000 1000000 24110000 40624128 2264064 36109312 OPTIMAL 1000000 'OFE_10.2.0. ------------ OFE_10.2.0.3 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID auy15crnrmkc3, child number 1 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3 Plan hash value: 2148421099 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 5700 (100)| | | 1 | SORT ORDER BY | | 1000K| 13M| 45M| 5700 (1)| 00:01:09 | | 2 | TABLE ACCESS FULL| T1 | 1000K| 13M| | 757 (1)| 00:00:10 | ----------------------------------------------------------------------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID auy15crnrmkc3, child number 1 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3 Plan hash value: 2148421099 ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1000K|00:00:00.28 | 2712 | | | | | 1 | SORT ORDER BY | | 1 | 1000K| 1000K|00:00:00.28 | 2712 | 38M| 2211K| 34M (0)| | 2 | TABLE ACCESS FULL| T1 | 1 | 1000K| 1000K|00:00:00.01 | 2712 | | | | ---------------------------------------------------------------------------------------------------------------- NAME VALUE -------------- ------- sorts (disk) 2 sorts (memory) 23828 sorts (rows) 9864585 'PGA200 ------- PGA 200 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID auy15crnrmkc3, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3 Plan hash value: 2148421099 ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1000K|00:00:00.28 | 2712 | | | | | 1 | SORT ORDER BY | | 1 | 1000K| 1000K|00:00:00.28 | 2712 | 38M| 2211K| 34M (0)| | 2 | TABLE ACCESS FULL| T1 | 1 | 1000K| 1000K|00:00:00.01 | 2712 | | | | ---------------------------------------------------------------------------------------------------------------- NAME VALUE -------------- ------- sorts (disk) 2 sorts (memory) 26015 sorts (rows) 10884097 'SA --- SAS PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID auy15crnrmkc3, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3 Plan hash value: 2148421099 -------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp| -------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1000K|00:00:00.50 | 2717 | 2928 | 2928 | | | | | | 1 | SORT ORDER BY | | 1 | 1000K| 1000K|00:00:00.50 | 2717 | 2928 | 2928 | 25M| 1845K| 20M (1)| 23552 | | 2 | TABLE ACCESS FULL| T1 | 1 | 1000K| 1000K|00:00:00.01 | 2712 | 0 | 0 | | | | | -------------------------------------------------------------------------------------------------------------------------------------------- NAME VALUE -------------- ------- sorts (disk) 3 sorts (memory) 29925 sorts (rows) 11926332 'OFE_10.2.0. ------------ OFE_10.2.0.2 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID auy15crnrmkc3, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3 Plan hash value: 2148421099 -------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp| -------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1000K|00:00:00.41 | 2717 | 2928 | 2928 | | | | | | 1 | SORT ORDER BY | | 1 | 1000K| 1000K|00:00:00.41 | 2717 | 2928 | 2928 | 25M| 1843K| 20M (1)| 23552 | | 2 | TABLE ACCESS FULL| T1 | 1 | 1000K| 1000K|00:00:00.01 | 2712 | 0 | 0 | | | | | -------------------------------------------------------------------------------------------------------------------------------------------- NAME VALUE -------------- ------- sorts (disk) 4 sorts (memory) 32075 sorts (rows) 13076448 'OFE_10.1.0. ------------ OFE_10.1.0.4 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID auy15crnrmkc3, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3 Plan hash value: 2148421099 -------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp| -------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1000K|00:00:00.50 | 2717 | 2928 | 2928 | | | | | | 1 | SORT ORDER BY | | 1 | 1000K| 1000K|00:00:00.50 | 2717 | 2928 | 2928 | 25M| 1845K| 20M (1)| 23552 | | 2 | TABLE ACCESS FULL| T1 | 1 | 1000K| 1000K|00:00:00.01 | 2712 | 0 | 0 | | | | | -------------------------------------------------------------------------------------------------------------------------------------------- NAME VALUE -------------- ------- sorts (disk) 5 sorts (memory) 34911 sorts (rows) 14099400 'PGA300 ------- PGA 300 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID auy15crnrmkc3, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3 Plan hash value: 2148421099 -------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp| -------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1000K|00:00:00.41 | 2717 | 2928 | 2928 | | | | | | 1 | SORT ORDER BY | | 1 | 1000K| 1000K|00:00:00.41 | 2717 | 2928 | 2928 | 25M| 1843K| 20M (1)| 23552 | | 2 | TABLE ACCESS FULL| T1 | 1 | 1000K| 1000K|00:00:00.01 | 2712 | 0 | 0 | | | | | -------------------------------------------------------------------------------------------------------------------------------------------- NAME VALUE -------------- ------- sorts (disk) 6 sorts (memory) 40285 sorts (rows) 15276005 'SAS64KB -------- SAS 64KB PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID auy15crnrmkc3, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3 Plan hash value: 2148421099 ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1000K|00:00:00.31 | 2712 | | | | | 1 | SORT ORDER BY | | 1 | 1000K| 1000K|00:00:00.31 | 2712 | 38M| 2211K| 34M (0)| | 2 | TABLE ACCESS FULL| T1 | 1 | 1000K| 1000K|00:00:00.01 | 2712 | | | | ---------------------------------------------------------------------------------------------------------------- NAME VALUE -------------- ------- sorts (disk) 6 sorts (memory) 42443 sorts (rows) 16295460 'OFE_10.1.0. ------------ OFE_10.1.0.4 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID auy15crnrmkc3, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3 Plan hash value: 2148421099 ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1000K|00:00:00.31 | 2712 | | | | | 1 | SORT ORDER BY | | 1 | 1000K| 1000K|00:00:00.31 | 2712 | 38M| 2211K| 34M (0)| | 2 | TABLE ACCESS FULL| T1 | 1 | 1000K| 1000K|00:00:00.01 | 2712 | | | | ---------------------------------------------------------------------------------------------------------------- NAME VALUE -------------- ------- sorts (disk) 6 sorts (memory) 44626 sorts (rows) 17315395 'PGA200 ------- PGA 200 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID auy15crnrmkc3, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3 Plan hash value: 2148421099 ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1000K|00:00:00.28 | 2712 | | | | | 1 | SORT ORDER BY | | 1 | 1000K| 1000K|00:00:00.28 | 2712 | 38M| 2211K| 34M (0)| | 2 | TABLE ACCESS FULL| T1 | 1 | 1000K| 1000K|00:00:00.01 | 2712 | | | | ---------------------------------------------------------------------------------------------------------------- NAME VALUE -------------- ------- sorts (disk) 6 sorts (memory) 48666 sorts (rows) 18487624 LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE ---------------- ---------- ---------------------- ---------------------- ---------------- ---------- ----------------- 1000000 1000000 48210000 40624128 2264064 36109312 OPTIMAL 1000000 'PGA100 ------- PGA 100 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID auy15crnrmkc3, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3 Plan hash value: 2148421099 -------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp| -------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1000K|00:00:00.56 | 2717 | 2928 | 2928 | | | | | | 1 | SORT ORDER BY | | 1 | 1000K| 1000K|00:00:00.56 | 2717 | 2928 | 2928 | 25M| 1844K| 20M (1)| 23552 | | 2 | TABLE ACCESS FULL| T1 | 1 | 1000K| 1000K|00:00:00.01 | 2712 | 0 | 0 | | | | | -------------------------------------------------------------------------------------------------------------------------------------------- NAME VALUE -------------- ------- sorts (disk) 7 sorts (memory) 58650 sorts (rows) 22038592 LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE ---------------- ---------- ---------------------- ---------------------- ---------------- ---------- ----------------- 1000000 1000000 48210000 27012096 1888256 21013504 1 PASS 23552 1000000 'PGA20 ------ PGA 20 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID auy15crnrmkc3, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3 Plan hash value: 2148421099 -------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp| -------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1000K|00:00:00.90 | 2746 | 3816 | 3816 | | | | | | 1 | SORT ORDER BY | | 1 | 1000K| 1000K|00:00:00.90 | 2746 | 3816 | 3816 | 25M| 1848K| 4141K (3)| 26624 | | 2 | TABLE ACCESS FULL| T1 | 1 | 1000K| 1000K|00:00:00.01 | 2712 | 0 | 0 | | | | | -------------------------------------------------------------------------------------------------------------------------------------------- LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE ---------------- ---------- ---------------------- ---------------------- ---------------- ---------- ----------------- 1000000 1000000 48210000 27150336 1892352 4240384 3 PASSES 26624 1000000 NAME VALUE ISDEFAULT ISMODIFIED --------------- ----- --------- ---------- _SMM_MAX_SIZE 4096 TRUE FALSE _SMM_MIN_SIZE 128 TRUE FALSE
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)?
'PGA200 ------- PGA 200 PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------- SQL_ID auy15crnrmkc3, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3 Plan hash value: 2148421099 ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1000K|00:00:00.28 | 2712 | | | | | 1 | SORT ORDER BY | | 1 | 1000K| 1000K|00:00:00.28 | 2712 | 38M| 2211K| 34M (0)| | 2 | TABLE ACCESS FULL| T1 | 1 | 1000K| 1000K|00:00:00.01 | 2712 | | | | ---------------------------------------------------------------------------------------------------------------- NAME VALUE -------------- ------- sorts (disk) 0 sorts (memory) 14062 sorts (rows) 4390586 LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE ---------------- ---------- ---------------------- ---------------------- ---------------- ---------- ----------------- 1000000 1000000 24110000 40624128 2264064 36109312 OPTIMAL 1000000 'PGA150 ------- PGA 150 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID auy15crnrmkc3, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3 Plan hash value: 2148421099 -------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp| -------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1000K|00:00:00.47 | 2715 | 2927 | 2927 | | | | | | 1 | SORT ORDER BY | | 1 | 1000K| 1000K|00:00:00.47 | 2715 | 2927 | 2927 | 25M| 1846K| 30M (1)| 23552 | | 2 | TABLE ACCESS FULL| T1 | 1 | 1000K| 1000K|00:00:00.01 | 2712 | 0 | 0 | | | | | -------------------------------------------------------------------------------------------------------------------------------------------- NAME VALUE -------------- ------- sorts (disk) 1 sorts (memory)16553 sorts (rows)5541481 LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE ---------------- ---------- ---------------------- ---------------------- ---------------- ---------- ----------------- 1000000 1000000 24110000 27076608 1890304 31464448 1 PASS 23552 1000000 'PGA200 ------- PGA 200 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID auy15crnrmkc3, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3 Plan hash value: 2148421099 ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1000K|00:00:00.31 | 2712 | | | | | 1 | SORT ORDER BY | | 1 | 1000K| 1000K|00:00:00.31 | 2712 | 38M| 2211K| 34M (0)| | 2 | TABLE ACCESS FULL| T1 | 1 | 1000K| 1000K|00:00:00.01 | 2712 | | | | ---------------------------------------------------------------------------------------------------------------- NAME VALUE -------------- ------- sorts (disk) 1 sorts (memory)18956 sorts (rows)6561447 LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE ---------------- ---------- ---------------------- ---------------------- ---------------- ---------- ----------------- 1000000 1000000 24110000 40624128 2264064 36109312 OPTIMAL 1000000
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:
SET PAGESIZE 2000 SET LINESIZE 140 SET ARRAYSIZE 100 SPOOL SortToDiskTest11.1.0.7-3.txt DROP TABLE T1 PURGE; CREATE TABLE T1 ( C1 VARCHAR2(20), C2 NUMBER(12), C3 NUMBER(12)); /* Fill the test table to 1,000,000 rows: */ DECLARE X NUMBER; BEGIN FOR X IN 1 .. 1000000 LOOP INSERT INTO T1 VALUES ( TO_CHAR(MOD( X ,100))||'-'||TO_CHAR(MOD( X ,100)), MOD( X ,500), X); END LOOP; END; / COMMIT; /* Gather statistics on the table: */ EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1'); /*+ Let's see where the sort statistics are right now: */ SELECT NAME, VALUE FROM V$SYSSTAT WHERE UPPER(NAME) LIKE '%SORT%' ORDER BY NAME; /*+ Let's turn on a 10053 trace so that we can see what the cost-based optimizer is willing to report: */ ALTER SESSION SET TRACEFILE_IDENTIFIER = 'SORT_AREA'; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1'; SELECT 'STARTING POINT' FROM DUAL; SPOOL OFF /*+ Run a simple SQL statement to generate a sort, and gather statistics for DBMS_XPLAN: */ SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3; SPOOL SortToDiskTest11.1.0.7-3.txt APPEND /*+ Determine the DBMS_XPLAN for the query: */ SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); /* Let's check the sort statistics: */ SELECT NAME, VALUE FROM V$SYSSTAT WHERE UPPER(NAME) LIKE '%SORT%' ORDER BY NAME; /* Let's look at the plan statistics for the SQL statement: */ SELECT SP.LAST_OUTPUT_ROWS, SP.TEMP_SPACE, SP.ESTIMATED_OPTIMAL_SIZE, SP.ESTIMATED_ONEPASS_SIZE, SP.LAST_MEMORY_USED, SP.LAST_EXECUTION, SP.LAST_TEMPSEG_SIZE FROM V$SQL S, V$SQL_PLAN_STATISTICS_ALL SP WHERE S.SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */%' AND S.SQL_ID=SP.SQL_ID; ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL; /* Let's try again, this time with a 20MB SORT_AREA_SIZE: */ ALTER SESSION SET SORT_AREA_SIZE=20971520; ALTER SYSTEM FLUSH SHARED_POOL; host sleep 30 SELECT 'SORT_AREA_SIZE=20971520' FROM DUAL; SPOOL OFF SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3; SPOOL SortToDiskTest11.1.0.7-3.txt APPEND SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); /*+ Let's check the sort statistics: */ SELECT NAME, VALUE FROM V$SYSSTAT WHERE UPPER(NAME) LIKE '%SORT%' ORDER BY NAME; /*+ Let's look at the plan statistics for the SQL statement: */ SELECT SP.LAST_OUTPUT_ROWS, SP.TEMP_SPACE, SP.ESTIMATED_OPTIMAL_SIZE, SP.ESTIMATED_ONEPASS_SIZE, SP.LAST_MEMORY_USED, SP.LAST_EXECUTION, SP.LAST_TEMPSEG_SIZE FROM V$SQL S, V$SQL_PLAN_STATISTICS_ALL SP WHERE S.SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */%' AND S.SQL_ID=SP.SQL_ID; /* Since we are still sorting to disk, let's increase the SORT_AREA_SIZE to 25MB again and repeat the test: */ ALTER SESSION SET SORT_AREA_SIZE=26214400; ALTER SYSTEM FLUSH SHARED_POOL; SELECT 'SORT_AREA_SIZE=26214400' FROM DUAL; SPOOL OFF SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3; SPOOL SortToDiskTest11.1.0.7-3.txt APPEND SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); SELECT NAME, VALUE FROM V$SYSSTAT WHERE UPPER(NAME) LIKE '%SORT%' ORDER BY NAME; /* Since we are still sorting to disk, let's increase the SORT_AREA_SIZE to 30MB again and repeat the test: */ ALTER SESSION SET SORT_AREA_SIZE=31457280; ALTER SYSTEM FLUSH SHARED_POOL; SELECT 'SORT_AREA_SIZE=31457280' FROM DUAL; SPOOL OFF SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3; SPOOL SortToDiskTest11.1.0.7-3.txt APPEND SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); SELECT NAME, VALUE FROM V$SYSSTAT WHERE UPPER(NAME) LIKE '%SORT%' ORDER BY NAME; /* Let's increase the SORT_AREA_SIZE to 40MB again and repeat the test: */ ALTER SESSION SET SORT_AREA_SIZE=41943040; ALTER SYSTEM FLUSH SHARED_POOL; SELECT 'SORT_AREA_SIZE=41943040' FROM DUAL; SPOOL OFF SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3; SPOOL SortToDiskTest11.1.0.7-3.txt APPEND SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); SELECT NAME, VALUE FROM V$SYSSTAT WHERE UPPER(NAME) LIKE '%SORT%' ORDER BY NAME; /* Let's change the SORT_AREA_SIZE to 20MB again and repeat the test: */ ALTER SESSION SET SORT_AREA_SIZE=20971520; ALTER SYSTEM FLUSH SHARED_POOL; SELECT 'SORT_AREA_SIZE=20971520 #2' FROM DUAL; SPOOL OFF SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3; SPOOL SortToDiskTest11.1.0.7-3.txt APPEND SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); SELECT NAME, VALUE FROM V$SYSSTAT WHERE UPPER(NAME) LIKE '%SORT%' ORDER BY NAME; /* Let's change the SORT_AREA_SIZE to 40MB again and repeat the test: */ ALTER SESSION SET SORT_AREA_SIZE=41943040; ALTER SYSTEM FLUSH SHARED_POOL; SELECT 'SORT_AREA_SIZE=41943040 #2' FROM DUAL; SPOOL OFF SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3; SPOOL SortToDiskTest11.1.0.7-3.txt APPEND SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); SELECT NAME, VALUE FROM V$SYSSTAT WHERE UPPER(NAME) LIKE '%SORT%' ORDER BY NAME; /* Let's change the SORT_AREA_SIZE to 20MB again, sleep, and repeat the test: */ ALTER SESSION SET SORT_AREA_SIZE=20971520; ALTER SYSTEM FLUSH SHARED_POOL; host sleep 30 SELECT 'SORT_AREA_SIZE=20971520 #3' FROM DUAL; SPOOL OFF SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3; SPOOL SortToDiskTest11.1.0.7-3.txt APPEND SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); SELECT NAME, VALUE FROM V$SYSSTAT WHERE UPPER(NAME) LIKE '%SORT%' ORDER BY NAME; /* Let's change the SORT_AREA_SIZE to 40MB again, sleep, and repeat the test: */ ALTER SESSION SET SORT_AREA_SIZE=41943040; ALTER SYSTEM FLUSH SHARED_POOL; host sleep 30 SELECT 'SORT_AREA_SIZE=41943040 #3' FROM DUAL; SPOOL OFF SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3; SPOOL SortToDiskTest11.1.0.7-3.txt APPEND SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); SELECT NAME, VALUE FROM V$SYSSTAT WHERE UPPER(NAME) LIKE '%SORT%' ORDER BY NAME; SPOOL OFF
The output of the above script follows:
NAME VALUE --------------- ------- sorts (disk) 0 sorts (memory) 3591 sorts (rows) 33734 'STARTINGPOINT -------------- STARTING POINT PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID auy15crnrmkc3, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3 Plan hash value: 2148421099 ------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1000K|00:00:00.50 | 2712 | 2707 | | | | | 1 | SORT ORDER BY | | 1 | 1000K| 1000K|00:00:00.50 | 2712 | 2707 | 38M| 2211K| 34M (0)| | 2 | TABLE ACCESS FULL| T1 | 1 | 1000K| 1000K|00:00:00.03 | 2712 | 2707 | | | | ------------------------------------------------------------------------------------------------------------------------- NAME VALUE --------------- ------- sorts (disk) 0 sorts (memory) 6019 sorts (rows) 1066679 LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE ---------------- ---------- ---------------------- ---------------------- ---------------- ---------- ----------------- 1000000 1000000 24110000 40624128 2264064 36109312 OPTIMAL 1000000 'SORT_AREA_SIZE=2097152 ----------------------- SORT_AREA_SIZE=20971520 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID auy15crnrmkc3, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3 Plan hash value: 2148421099 -------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp| -------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1000K|00:00:00.50 | 2715 | 2926 | 2926 | | | | | | 1 | SORT ORDER BY | | 1 | 1000K| 1000K|00:00:00.50 | 2715 | 2926 | 2926 | 25M| 1844K| 18M (1)| 23552 | | 2 | TABLE ACCESS FULL| T1 | 1 | 1000K| 1000K|00:00:00.01 | 2712 | 0 | 0 | | | | | -------------------------------------------------------------------------------------------------------------------------------------------- NAME VALUE --------------- ------- sorts (disk) 1 sorts (memory) 8435 sorts (rows) 2086785 LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE ---------------- ---------- ---------------------- ---------------------- ---------------- ---------- ----------------- 1000000 1000000 24110000 27002880 1888256 18884608 1 PASS 23552 1000000 'SORT_AREA_SIZE=2621440 ----------------------- SORT_AREA_SIZE=26214400 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID auy15crnrmkc3, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3 Plan hash value: 2148421099 -------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp| -------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1000K|00:00:00.44 | 2715 | 2926 | 2926 | | | | | | 1 | SORT ORDER BY | | 1 | 1000K| 1000K|00:00:00.44 | 2715 | 2926 | 2926 | 25M| 1843K| 23M (1)| 23552 | | 2 | TABLE ACCESS FULL| T1 | 1 | 1000K| 1000K|00:00:00.01 | 2712 | 0 | 0 | | | | | -------------------------------------------------------------------------------------------------------------------------------------------- NAME VALUE --------------- ------- sorts (disk) 2 sorts (memory) 10870 sorts (rows) 3107315 'SORT_AREA_SIZE=3145728 ----------------------- SORT_AREA_SIZE=31457280 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID auy15crnrmkc3, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3 Plan hash value: 2148421099 -------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp| -------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1000K|00:00:00.47 | 2715 | 2926 | 2926 | | | | | | 1 | SORT ORDER BY | | 1 | 1000K| 1000K|00:00:00.47 | 2715 | 2926 | 2926 | 25M| 1843K| 27M (1)| 23552 | | 2 | TABLE ACCESS FULL| T1 | 1 | 1000K| 1000K|00:00:00.01 | 2712 | 0 | 0 | | | | | -------------------------------------------------------------------------------------------------------------------------------------------- NAME VALUE --------------- ------- sorts (disk) 3 sorts (memory) 13542 sorts (rows) 4127908 'SORT_AREA_SIZE=4194304 ----------------------- SORT_AREA_SIZE=41943040 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID auy15crnrmkc3, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3 Plan hash value: 2148421099 ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1000K|00:00:00.28 | 2712 | | | | | 1 | SORT ORDER BY | | 1 | 1000K| 1000K|00:00:00.28 | 2712 | 38M| 2211K| 34M (0)| | 2 | TABLE ACCESS FULL| T1 | 1 | 1000K| 1000K|00:00:00.01 | 2712 | | | | ---------------------------------------------------------------------------------------------------------------- NAME VALUE --------------- ------- sorts (disk) 3 sorts (memory) 16027 sorts (rows) 5148428 'SORT_AREA_SIZE=20971520#2 -------------------------- SORT_AREA_SIZE=20971520 #2 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID auy15crnrmkc3, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3 Plan hash value: 2148421099 -------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp| -------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1000K|00:00:00.44 | 2715 | 2926 | 2926 | | | | | | 1 | SORT ORDER BY | | 1 | 1000K| 1000K|00:00:00.44 | 2715 | 2926 | 2926 | 25M| 1843K| 18M (1)| 23552 | | 2 | TABLE ACCESS FULL| T1 | 1 | 1000K| 1000K|00:00:00.01 | 2712 | 0 | 0 | | | | | -------------------------------------------------------------------------------------------------------------------------------------------- NAME VALUE --------------- ------- sorts (disk) 4 sorts (memory) 18474 sorts (rows) 6168736 'SORT_AREA_SIZE=41943040#2 -------------------------- SORT_AREA_SIZE=41943040 #2 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID auy15crnrmkc3, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3 Plan hash value: 2148421099 ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1000K|00:00:00.28 | 2712 | | | | | 1 | SORT ORDER BY | | 1 | 1000K| 1000K|00:00:00.28 | 2712 | 38M| 2211K| 34M (0)| | 2 | TABLE ACCESS FULL| T1 | 1 | 1000K| 1000K|00:00:00.01 | 2712 | | | | ---------------------------------------------------------------------------------------------------------------- NAME VALUE --------------- ------- sorts (disk) 4 sorts (memory) 22413 sorts (rows) 7206482 'SORT_AREA_SIZE=20971520#3 -------------------------- SORT_AREA_SIZE=20971520 #3 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID auy15crnrmkc3, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3 Plan hash value: 2148421099 -------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp| -------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1000K|00:00:00.53 | 2715 | 2926 | 2926 | | | | | | 1 | SORT ORDER BY | | 1 | 1000K| 1000K|00:00:00.53 | 2715 | 2926 | 2926 | 25M| 1843K| 18M (1)| 23552 | | 2 | TABLE ACCESS FULL| T1 | 1 | 1000K| 1000K|00:00:00.01 | 2712 | 0 | 0 | | | | | -------------------------------------------------------------------------------------------------------------------------------------------- NAME VALUE --------------- ------- sorts (disk) 5 sorts (memory) 24788 sorts (rows) 8226615 'SORT_AREA_SIZE=41943040#3 -------------------------- SORT_AREA_SIZE=41943040 #3 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID auy15crnrmkc3, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1 ORDER BY C2, C3 Plan hash value: 2148421099 ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1000K|00:00:00.28 | 2712 | | | | | 1 | SORT ORDER BY | | 1 | 1000K| 1000K|00:00:00.28 | 2712 | 38M| 2211K| 34M (0)| | 2 | TABLE ACCESS FULL| T1 | 1 | 1000K| 1000K|00:00:00.01 | 2712 | | | | ---------------------------------------------------------------------------------------------------------------- NAME VALUE --------------- ------- sorts (disk) 5 sorts (memory) 27252 sorts (rows) 9247027 LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE ---------------- ---------- ---------------------- ---------------------- ---------------- ---------- ----------------- 1000000 1000000 40624128 2264064 36109312 OPTIMAL 1000000
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.
Recent Comments