SORT_AREA_SIZE Affecting Sorts to Disk with PGA_AGGREGATE_TARGET Set 2?

23 12 2009

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.








Follow

Get every new post delivered to your Inbox.

Join 144 other followers