SORT_AREA_SIZE Affecting Sorts to Disk with PGA_AGGREGATE_TARGET Set?

20 12 2009

December 20, 2009

(Forward to the Follow-Up Post)

A couple of years ago I was under the impression that increasing the SORT_AREA_SIZE parameter could reduce the number of sorts to disk in Oracle 10g R2 with the PGA_AGGREGATE_TARGET parameter set, using dedicated server processes, and with the WORKAREA_SIZE_POLICY set to AUTO (yes, confirmed to be set in the session).  Why?  Because I saw it happen a couple of times when troubleshooting performance problems.  But, the sorts to disk still happened on occasion when there seemed to be no reason why Oracle would not be able to perform an optimal, in-memory workarea execution.

Thankfully, someone questioned my earlier testing that produced results which were inconsistent with the documentation.  I then attempted to produce a test case to prove myself wrong.  I set out to trigger a query to perform a sort to disk, and then by changing only the  SORT_AREA_SIZE parameter, have Oracle switch to perform an in-memory workarea execution for the query.  Once the test case produced that result, I attempted to identify the trigger that caused Oracle to switch.  The test case appears in the following Usenet thread:
http://groups.google.com/group/comp.databases.oracle.server/msg/e0b8bea300e45ae4

Line wrapping is a problem in the above thread, so I have reproduced the test case below:

I was able to produce a test case on the base patch of Oracle 10.2.0.2 on Win 32.  The following required roughly 10 hours of testing and analysis.  I built a new database instance with the following pfile:

############################################
aq_tm_processes=1
background_core_dump=partial
cluster_database=FALSE
compatible=10.2.0.1.0
control_files=("C:\oracle\OraData\LT\ctlLT01.ctl", "C:\oracle\flash_recovery_area\LT\ctlLT02.ctl")
control_file_record_keep_time=7
cursor_sharing=FORCE
cursor_space_for_time=true
db_block_size=8192
db_cache_advice=on
db_block_checking=false
db_block_checksum=typical
db_domain=world
db_files=200
db_file_multiblock_read_count=16
db_flashback_retention_target=1440
db_name=LT
db_keep_cache_size=400M
db_recycle_cache_size=10M
db_recovery_file_dest_size=14000M
db_recovery_file_dest=C:\oracle\flash_recovery_area
db_unique_name=LT
db_writer_processes=1
global_names=false
instance_name=LT
java_pool_size=1M
job_queue_processes=10
log_archive_format=arc%s_%r.%t
log_buffer=1048576
log_checkpoint_interval=65536
log_checkpoint_timeout=3600
log_checkpoints_to_alert=false
max_dump_file_size=202400
nls_language=american
nls_territory=america
O7_DICTIONARY_ACCESSIBILITY=TRUE
open_cursors=1000
open_links=4
optimizer_dynamic_sampling=2
optimizer_features_enable=10.1.0.4   #Needed to overcome ORA-600 on production DB
optimizer_index_caching=0
optimizer_index_cost_adj=100
optimizer_mode=ALL_ROWS
pga_aggregate_target=150M
plsql_code_type=INTERPRETED
processes=210
query_rewrite_enabled=FALSE
query_rewrite_integrity=TRUSTED
recyclebin=ON
remote_login_passwordfile=EXCLUSIVE
service_names=LT
sessions=236
session_cached_cursors=200
sga_max_size=1100M
sga_target=900M
star_transformation_enabled=FALSE
statistics_level=typical
timed_statistics=true
transactions=259
transactions_per_rollback_segment=5
undo_management=AUTO
undo_retention=1800
undo_tablespace=ROLLBACK_DATA
workarea_size_policy=auto
background_dump_dest=C:\oracle\product\10.2.0\admin\LT\bdump
core_dump_dest=C:\oracle\product\10.2.0\admin\LT\cdump
user_dump_dest=C:\oracle\product\10.2.0\admin\LT\udump
utl_file_dir=C:\oracle\product\10.2.0\admin\LT\udump
############################################

Create a new user named TESTING

Create a table for testing and fill in the table:

CREATE TABLE T1 (
  C1 VARCHAR2(20),
  C2 NUMBER(12),
  C3 NUMBER(12));

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;

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;

NAME                   VALUE
----------------- ----------
sorts (disk)               0
sorts (memory)          2092
sorts (rows)            8342

Let’s turn on a 10053 trace so that we can see what the cost-based optimizer is willing to report:

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

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;

Determine the DBMS_XPLAN for the query:

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

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1004K|   1000K|00:00:04.19 |    2709 |   5627 |   2926 |    25M|  1843K|   30M (1)|   23552 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1004K|   1000K|00:00:00.02 |    2706 |   2701 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

This query used the temp tablespace as indicated by the Used-Tmp column.

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:          43 Area size:      156672 Max Area size:     7864320
      Degree:               1
      Blocks to Sort:    3197 Row size:           26 Total Rows:        1004199
      Initial runs:         4 Merge passes:        1 IO Cost / pass:       1734
      Total IO sort cost: 4931      Total CPU sort cost: 992293034
      Total Temp space used: 48407000

PARAMETERS WITH DEFAULT VALUES
  _smm_min_size = 153 KB
  _smm_max_size = 30720 KB

Note:
 Max Area size is reported as 7,864,320 (7680 KB), while _smm_max_size is reported as 30720 KB.  On Oracle 10.2.0.3, when optimizer_features_enable is set to 10.2.0.3, both will be reported with the same values.
 5% of 150MB = 7.5MB = 7864320 bytes
 150MB/5 = 30MB = 31457280 bytes = 30720 KB

From my notes recorded during the second read through of “Cost-Based Oracle Fundamentals”:
sort width = same as the “max intermediate sort width” from a 10032 trace file
area size = amount of memory available for processing data – number reported will be smaller than the SORT_AREA_SIZE due to overhead
max area size = maximum memory available for sorting
degree = degree of parallelism for the query
blocks to sort = row_size*rows/db_block_size
row size = estimate of the average row size in bytes
rows = computed (filtered) cardinality of the table
initial runs = optimizer’s estimate of the number of sort runs that will be dumped to disk
merge passes = always at least one, even for an in-memory sort, counts the number of times the entire data set will be written to and read from disk in the event of a disk sort
IO cost/pass = cost of doing a single merge pass
total IO sort cost – combines the cost per pass with the number of passes
total CPU cost – CPU component of the cost – measured in CPU operations
total temp space used = estimated amount of temporary space needed for the sort operation

Let’s check the sort statistics:

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                    VALUE
------------------ ----------
sorts (disk)                1
sorts (memory)           3557
sorts (rows)          1015256

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;

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
         1000000   48407000               26966016                1887232         31472640 1 PASS                 23552

From the Oracle documentation:
TEMP_SPACE: Temporary space usage of the operation (sort or hash-join) as estimated by the optimizer’s cost-based approach. For statements that use the rule-based approach, this column is null.
ESTIMATED_OPTIMAL_SIZE: Estimated size (in KB) required by this work area to execute the operation completely in memory (optimal execution). This is either derived from optimizer statistics or from previous executions.
ESTIMATED_ONEPASS_SIZE: Estimated size (in KB) required by this work area to execute the operation in a single pass. This is either derived from optimizer statistics or from previous executions.
LAST_MEMORY_USED: Memory size (in KB) used by this work area during the last execution of the cursor
LAST_EXECUTION: Indicates whether this work area ran using OPTIMAL, ONE PASS, or under ONE PASS memory requirement (MULTI-PASS), during the last execution of the cursor
LAST_TEMPSEG_SIZE: Temporary segment size (in bytes) created in the last instantiation of this work area. This column is null if the last instantiation of this work area did not spill to disk.

The SQL statement required a 1 pass sort to disk.

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 /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

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

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1004K|   1000K|00:00:01.82 |    2709 |   2926 |   2926 |    25M|  1843K|   30M (1)|   23552 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1004K|   1000K|00:00:00.01 |    2706 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

This query used the temp tablespace as indicated by the Used-Tmp column.

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:          58 Area size:      208896 Max Area size:    10485760
      Degree:               1
      Blocks to Sort:    3197 Row size:           26 Total Rows:        1004199
      Initial runs:         3 Merge passes:        1 IO Cost / pass:       1734
      Total IO sort cost: 4931      Total CPU sort cost: 992293034
      Total Temp space used: 48407000

PARAMETERS WITH DEFAULT VALUES
  pga_aggregate_target = 204800 KB
  _smm_min_size        = 204 KB
  _smm_max_size        = 40960 KB

Note that the Area Size, Max Area Size, _smm_min_size, and _smm_max_size parameters increased, yet a sort to disk was still required.

Let’s check the sort statistics:

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                VALUE
-------------- ----------
sorts (disk)            2
sorts (memory)       4504
sorts (rows)      2022536

The sorts (disk) value increased again.

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;

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
         1000000   48407000               26966016                1887232         31472640 1 PASS                 23552

The SQL statement required a 1 pass sort to disk.

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 /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

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

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1004K|   1000K|00:00:01.55 |    2706 |    34M|  2086K|   30M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1004K|   1000K|00:00:00.01 |    2706 |       |       |          |
----------------------------------------------------------------------------------------------------------------

The Used-Tmp column did not print, so no sort to disk was required.

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:          88 Area size:      314368 Max Area size:    15728640
      Degree:               1
      Blocks to Sort:    3197 Row size:           26 Total Rows:        1004199
      Initial runs:         2 Merge passes:        1 IO Cost / pass:       1734
      Total IO sort cost: 4931      Total CPU sort cost: 992293034
      Total Temp space used: 48407000

PARAMETERS WITH DEFAULT VALUES
  pga_aggregate_target = 307200 KB
  _smm_min_size        = 307 KB
  _smm_max_size        = 61440 KB

 

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                    VALUE
------------------ ----------
sorts (disk)                2
sorts (memory)           6223
sorts (rows)          3035178

As shown above, no sort to disk was needed with PGA_AGGREGATE_TARGET at 300MB, but a sort to disk was required at 200MB.

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 /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

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

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1004K|   1000K|00:00:01.55 |    2706 |    34M|  2086K|   30M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1004K|   1000K|00:00:00.01 |    2706 |       |       |          |
----------------------------------------------------------------------------------------------------------------

The Used-Tmp column did not print, so no sort to disk was required.  So, increasing the PGA_AGGREGATE_TARGET from 150MB to 200MB apparently forces a sort to disk, while decreasing the PGA_AGGREGATE_TARGET from 300MB to 200MB does not require a sort to disk.

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:          58 Area size:      208896 Max Area size:    10485760
      Degree:               1
      Blocks to Sort:    3197 Row size:           26 Total Rows:        1004199
      Initial runs:         3 Merge passes:        1 IO Cost / pass:       1734
      Total IO sort cost: 4931      Total CPU sort cost: 992293034
      Total Temp space used: 48407000

PARAMETERS WITH DEFAULT VALUES
  pga_aggregate_target = 204800 KB
  _smm_min_size        = 204 KB
  _smm_max_size        = 40960 KB

The values are the same as for the first run with PGA_AGGREGATE_TARGET at 200MB.

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                   VALUE
----------------- ----------
sorts (disk)               2
sorts (memory)          7224
sorts (rows)         4043937

The sorts (disk) value did not increase.

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;

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
         1000000   48407000               35688448                2136064         31722496 OPTIMAL

Note that LAST_TEMPSEG_SIZE is null in the above, and that the last execution was OPTIMAL.

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 /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

(EXECUTED IN A DIFFERENT SESSION WHILE THE ABOVE IS EXECUTING):

SELECT
  *
FROM
  V$SORT_USAGE;

USERNAME USER     SESSION_SESSION_NUM SQLADDR     SQLHASH SQL_ID        TABLESPACE     CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#    EXTENTS     BLOCKS   SEGRFNO#
-------- -------- ------------------- -------- ---------- ------------- -------------- --------- --------- ---------- ---------- ---------- ---------- ----------
TESTING  TESTING  54C631C8          1 507DB66C 2019831986 4ww44m1w68c5k TEMPORARY_DATA1TEMPORARY SORT             201       2825         23       2944          1

(2944 blocks * 8KB block size = 24,117,248 bytes)

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

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1004K|   1000K|00:00:02.85 |    2709 |   2926 |   2926 |    25M|  1843K|   30M (1)|   23552 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1004K|   1000K|00:00:00.01 |    2706 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

This query used the temp tablespace as indicated by the Used-Tmp column.

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:          43 Area size:      156672 Max Area size:     7864320
      Degree:               1
      Blocks to Sort:       1 Row size:           76 Total Rows:              1
      Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 11511282
      Total Temp space used: 0

PARAMETERS WITH DEFAULT VALUES
  pga_aggregate_target                = 153600 KB
  _smm_min_size                       = 153 KB
  _smm_max_size                       = 30720 KB

Problem in the 10053 trace file?  Note the “Total Temp space used: 0″

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                   VALUE
----------------- ----------
sorts (disk)               3
sorts (memory)          9997
sorts (rows)         5068937

The sorts (disk) value did increase.

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;

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
         1000000   48407000               26966016                1887232         31472640 1 PASS                 23552

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 /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

(EXECUTED IN A DIFFERENT SESSION WHILE THE ABOVE IS EXECUTING):

SELECT
  *
FROM
  V$SORT_USAGE;

USERNAME USER     SESSION_SESSION_NUM SQLADDR     SQLHASH SQL_ID        TABLESPACE CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#    EXTENTS     BLOCKS   SEGRFNO#
-------- -------- ------------------- -------- ---------- ------------- -------------------------------  --------- --------- ---------- ---------- ----------
TESTING  TESTING  54C631C8          1 507DB66C 2019831986 4ww44m1w68c5k TEMPORARY_DATA1  TEMPORARY SORT       201          9         23       2944          1

The above shows a sort to disk in progress.

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

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1004K|   1000K|00:00:01.80 |    2709 |   2926 |   2926 |    25M|  1843K|   30M (1)|   23552 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1004K|   1000K|00:00:00.01 |    2706 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

The DBMS_XPLAN shows that a sort to disk was required.

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:          58 Area size:      208896 Max Area size:    10485760
      Degree:               1
      Blocks to Sort:    3197 Row size:           26 Total Rows:        1004199
      Initial runs:         3 Merge passes:        1 IO Cost / pass:       1734
      Total IO sort cost: 4931      Total CPU sort cost: 992293034
      Total Temp space used: 48407000

PARAMETERS WITH DEFAULT VALUES
  pga_aggregate_target = 204800 KB
  _smm_min_size        = 204 KB
  _smm_max_size        = 40960 KB

 

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                    VALUE
------------------ ----------
sorts (disk)                4
sorts (memory)          11776
sorts (rows)          6081798

The sorts (disk) value did increase.

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;

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
         1000000   48407000               26966016                1887232         31472640 1 PASS                 23552

The above shows a 1 pass sort to disk was required.

Now, let’s see if changing the SORT_AREA_SIZE helps remove the sort to disk, as I suggested in my previous post:

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SESSION SET SORT_AREA_SIZE=41943040;
ALTER SESSION SET SORT_AREA_RETAINED_SIZE=41943040;

SELECT /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

(EXECUTED IN A DIFFERENT SESSION WHILE THE ABOVE IS EXECUTING):

SELECT
  *
FROM
  V$SORT_USAGE;

(No rows)

 

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

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1004K|   1000K|00:00:01.57 |    2706 |    34M|  2086K|   30M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1004K|   1000K|00:00:00.01 |    2706 |       |       |          |
----------------------------------------------------------------------------------------------------------------

No sort to disk was required.

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:          58 Area size:      208896 Max Area size:    10485760
      Degree:               1
      Blocks to Sort:    3197 Row size:           26 Total Rows:        1004199
      Initial runs:         3 Merge passes:        1 IO Cost / pass:       1734
      Total IO sort cost: 4931      Total CPU sort cost: 992293034
      Total Temp space used: 48407000

PARAMETERS WITH ALTERED VALUES (for some reason, these did not show the first time the query was parsed - I had to force a second parse):
  sort_area_size                      = 41943040
  sort_area_retained_size             = 41943040

PARAMETERS WITH DEFAULT VALUES
  _smm_min_size                       = 204 KB
  _smm_max_size                       = 40960 KB

 

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                    VALUE
------------------ ----------
sorts (disk)                4
sorts (memory)          12126
sorts (rows)          7084470

The sorts (disk) value did not increase.

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;

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
          113401   48407000               35688448                2136064         31722496 OPTIMAL

Note that LAST_TEMPSEG_SIZE is null in the above, and that the last execution was OPTIMAL.  So, as I originally suggested, changing the SORT_AREA_SIZE for the session helped by removing the sort to disk.  Or not – I suspect the second time the query needed to be parsed, it would not have required a sort to disk.

—-

Now, let’s try a similar test on Oracle 10.2.0.3 with a pre-existing database that was started with the following parameters:

optimizer_features_enable=10.2.0.3
optimizer_index_caching=0
optimizer_index_cost_adj=100
pga_aggregate_target=300M
query_rewrite_enabled=FALSE
sga_max_size=1100M
sga_target=900M
sort_area_retained_size  not specified
sort_area_size  not specified
workarea_size_policy=auto

 

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

Let’s first dump a DBMS_XPLAN with the estimated statistics.

SELECT /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,NULL));

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |       |  5302 (100)|          |
|   1 |  SORT ORDER BY     |      |  1000K|    13M|    46M|  5302   (6)| 00:00:29 |
|   2 |   TABLE ACCESS FULL| T1   |  1000K|    13M|       |   604   (9)| 00:00:04 |
-----------------------------------------------------------------------------------

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:         358 Area size:      314368 Max Area size:    62914560
      Degree:               1
      Blocks to Sort:    3186 Row size:           26 Total Rows:        1000729
      Initial runs:         2 Merge passes:        1 IO Cost / pass:       1270
      Total IO sort cost: 4456      Total CPU sort cost: 981216297
      Total Temp space used: 48243000

Under PARAMETERS WITH DEFAULT VALUES:
  _smm_min_size = 307 KB
  _smm_max_size = 61440 KB    {= 62914560 bytes}

Note: 5% of 300MB = 15MB = 15728640 bytes,     300MB/5 = 60MB = 62,914,560

Oracle 10.2.0.3 with optimizer_features_enable set to 10.2.0.3 reports Max Area size at 62914560 bytes, which is identical to _smm_max_size, which is 1/5 of the pga_aggregate_target.  In the above, Oracle 10.2.0.2 reported Max Area size at roughly 5% of the pga_aggregate_target.

Let’s repeat, this time requesting the additional statistics from DBMS XPLAN:

SELECT /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

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

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:03.57 |    2712 |    34M|  2086K|   30M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:02.00 |    2712 |       |       |          |
----------------------------------------------------------------------------------------------------------------

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME              VALUE
-----------------------
sorts (disk)          0
sorts (memory)     4069
sorts (rows)    3027233

No sort to disk was required.

Repeat with pga_aggregate_target at 200MB:

ALTER SYSTEM SET PGA_AGGREGATE_TARGET=200M;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

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

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:03.56 |    2712 |    34M|  2086K|   30M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:02.00 |    2712 |       |       |          |
----------------------------------------------------------------------------------------------------------------

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:         238 Area size:      208896 Max Area size:    41943040
      Degree:               1
      Blocks to Sort:    3186 Row size:           26 Total Rows:        1000729
      Initial runs:         2 Merge passes:        1 IO Cost / pass:       1270
      Total IO sort cost: 4456      Total CPU sort cost: 981216297
      Total Temp space used: 48243000

Under PARAMETERS WITH DEFAULT VALUES:
  _smm_min_size = 204 KB
  _smm_max_size = 40960 KB

 

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                    VALUE
------------------ ----------
sorts (disk)                0
sorts (memory)           6283
sorts (rows)          4044290

No sort to disk required.

Repeat with pga_aggregate_target at 100MB:

ALTER SYSTEM SET PGA_AGGREGATE_TARGET=100M;
ALTER SYSTEM FLUSH SHARED_POOL;

 

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:07.21 |    2717 |   2928 |   2928 |    25M|  1843K|   20M (1)|   23552 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:02.00 |    2712 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

 

From 10053 trace:

    SORT resource      Sort statistics
      Sort width:         118 Area size:      131072 Max Area size:    20971520
      Degree:               1
      Blocks to Sort:    3186 Row size:           26 Total Rows:        1000729
      Initial runs:         2 Merge passes:        1 IO Cost / pass:       1270
      Total IO sort cost: 4456      Total CPU sort cost: 981216297
      Total Temp space used: 48243000

Under PARAMETERS WITH DEFAULT VALUES:
  _smm_min_size = 128 KB
  _smm_max_size = 20480 KB

 

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                        VALUE
---------------------- ----------
sorts (disk)                    1
sorts (memory)               7785
sorts (rows)              5058041

This time, a sort to disk was required.

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 /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

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

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:04.34 |    2717 |   2928 |   2928 |    25M|  1843K|   20M (1)|   23552 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:02.00 |    2712 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

 

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:         118 Area size:      131072 Max Area size:    20971520
      Degree:               1
      Blocks to Sort:    3186 Row size:           26 Total Rows:        1000729
      Initial runs:         2 Merge passes:        1 IO Cost / pass:       1270
      Total IO sort cost: 4456      Total CPU sort cost: 981216297
      Total Temp space used: 48243000

Under PARAMETERS WITH DEFAULT VALUES:
  _smm_min_size                       = 128 KB
  _smm_max_size                       = 20480 KB

 

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                        VALUE
---------------------- ----------
sorts (disk)                    2
sorts (memory)               9699
sorts (rows)              6073631

A sort to disk was still required, so changing SORT_AREA_SIZE did not help – of course the SORT_AREA_SIZE would have been double the value of _smm_max_size…

Let’s try again, this time using the optimizer setting for Oracle 10.2.0.2:

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='10.2.0.2';

SELECT /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

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

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:04.36 |    2717 |   2928 |   2928 |    25M|  1843K|   20M (1)|   23552 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:02.00 |    2712 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

 

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:         118 Area size:      131072 Max Area size:    20971520
      Degree:               1
      Blocks to Sort:    3186 Row size:           26 Total Rows:        1000729
      Initial runs:         2 Merge passes:        1 IO Cost / pass:       1270
      Total IO sort cost: 4456      Total CPU sort cost: 981216297
      Total Temp space used: 48243000

Under PARAMETERS WITH DEFAULT VALUES:
  _smm_min_size = 128 KB
  _smm_max_size = 20480 KB

 

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                        VALUE
---------------------- ----------
sorts (disk)                    3
sorts (memory)              11435
sorts (rows)              7088591

Still a sort to disk.

Let’s try again, this time using the optimizer setting for Oracle 10.1.0.4:

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='10.1.0.4';

SELECT /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

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

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:04.30 |    2717 |   2928 |   2928 |    25M|  1843K|   20M (1)|   23552 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:02.00 |    2712 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:          28 Area size:      131072 Max Area size:     5242880
      Degree:               1
      Blocks to Sort:    3186 Row size:           26 Total Rows:        1000729
      Initial runs:         5 Merge passes:        1 IO Cost / pass:       1270
      Total IO sort cost: 4456      Total CPU sort cost: 981216297
      Total Temp space used: 48243000

Under PARAMETERS WITH DEFAULT VALUES:
  _smm_min_size = 128 KB
  _smm_max_size = 20480 KB

 

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                   VALUE
----------------- ----------
sorts (disk)               4
sorts (memory)         12907
sorts (rows)         8102004

Still a sort to disk, but note that Max Area size no longer matches the value for _smm_max_size.

Let’s try again with pga_aggregate_target at 300MB:

ALTER SYSTEM SET PGA_AGGREGATE_TARGET=300M;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

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

----------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:03.58 |    2712 |    34M|  2086K|   30M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:02.00 |    2712 |       |       |          |
----------------------------------------------------------------------------------------------------------------

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:          88 Area size:      314368 Max Area size:    15728640
      Degree:               1
      Blocks to Sort:    3186 Row size:           26 Total Rows:        1000729
      Initial runs:         2 Merge passes:        1 IO Cost / pass:       1270
      Total IO sort cost: 4456      Total CPU sort cost: 981216297
      Total Temp space used: 48243000

Under PARAMETERS WITH DEFAULT VALUES:
  _smm_min_size = 307 KB
  _smm_max_size = 61440 KB

 

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                   VALUE
----------------- ----------
sorts (disk)               4
sorts (memory)         14532
sorts (rows)         9116171

No sort to disk, but SORT_AREA_SIZE is still set to roughly 40MB.

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 /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

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

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:03.56 |    2712 |    34M|  2086K|   30M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:02.00 |    2712 |       |       |          |
----------------------------------------------------------------------------------------------------------------

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:          88 Area size:      314368 Max Area size:    15728640
      Degree:               1
      Blocks to Sort:    3186 Row size:           26 Total Rows:        1000729
      Initial runs:         2 Merge passes:        1 IO Cost / pass:       1270
      Total IO sort cost: 4456      Total CPU sort cost: 981216297
      Total Temp space used: 48243000

Under PARAMETERS WITH DEFAULT VALUES:
  _smm_min_size = 307 KB
  _smm_max_size = 61440 KB

 

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                  VALUE
---------------- ----------
sorts (disk)              4
sorts (memory)        16400
sorts (rows)       10132013

No sort to disk, changing SORT_AREA_SIZE did not change anything.

Just to confirm, in a new session:

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 /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

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

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:03.56 |    2712 |    34M|  2086K|   30M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:02.00 |    2712 |       |       |          |
----------------------------------------------------------------------------------------------------------------

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:          88 Area size:      314368 Max Area size:    15728640
      Degree:               1
      Blocks to Sort:    3186 Row size:           26 Total Rows:        1000729
      Initial runs:         2 Merge passes:        1 IO Cost / pass:       1270
      Total IO sort cost: 4456      Total CPU sort cost: 981216297
      Total Temp space used: 48243000

Under PARAMETERS WITH DEFAULT VALUES:
  _smm_min_size = 307 KB
  _smm_max_size = 61440 KB

 

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                VALUE
-------------- ----------
sorts (disk)            4
sorts (memory)      17859
sorts (rows)     11145435

No sort to disk with no SORT_AREA_SIZE specified.

Try again, setting PGA_AGGREGATE_TARGET back to 200MB:

ALTER SYSTEM SET PGA_AGGREGATE_TARGET=200M;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

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

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:03.56 |    2712 |    34M|  2086K|   30M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:02.00 |    2712 |       |       |          |
----------------------------------------------------------------------------------------------------------------

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:          58 Area size:      208896 Max Area size:    10485760
      Degree:               1
      Blocks to Sort:    3186 Row size:           26 Total Rows:        1000729
      Initial runs:         3 Merge passes:        1 IO Cost / pass:       1270
      Total IO sort cost: 4456      Total CPU sort cost: 981216297
      Total Temp space used: 48243000

Under PARAMETERS WITH DEFAULT VALUES:
  _smm_min_size = 204 KB
  _smm_max_size = 40960 KB

 

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

-------------- ----------
sorts (disk)            4
sorts (memory)      19443
sorts (rows)     12159195

Still no sort to disk.

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;

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
         1000000   48243000               35688448                2136064         31722496 OPTIMAL  

This SQL statement used an optimal sort (no sort to disk).

Trying again with PGA_AGGREGATE_TARGET at 100MB:

ALTER SYSTEM SET PGA_AGGREGATE_TARGET=100M;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

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

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:04.30 |    2717 |   2928 |   2928 |    25M|  1843K|   20M (1)|   23552 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:02.00 |    2712 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:          28 Area size:      131072 Max Area size:     5242880
      Degree:               1
      Blocks to Sort:    3186 Row size:           26 Total Rows:        1000729
      Initial runs:         5 Merge passes:        1 IO Cost / pass:       1270
      Total IO sort cost: 4456      Total CPU sort cost: 981216297
      Total Temp space used: 48243000

Under PARAMETERS WITH DEFAULT VALUES:
  _smm_min_size  = 128 KB
  _smm_max_size  = 20480 KB

 

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                VALUE
-------------------------
sorts (disk)            5
sorts (memory)      27009
sorts (rows)     14227511

A sort to disk was required.

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;

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
         1000000   48243000               26984448                1887232         21013504 1 PASS                 23552

A 1 pass sort to disk was required.

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 /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

(Executed from a different session while the above was running):

SELECT
  *
FROM
  V$SORT_USAGE;

USERNAME USER     SESSION_SESSION_NUM SQLADDR     SQLHASH SQL_ID        TABLESPACE CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#   EXTENTS     BLOCKS   SEGRFNO#
-------- -------- ------------------- -------- ---------- ------------- ---------------------------------------- --------- ---------- ---------- ----------
TESTING  TESTING  54C654DC         70 50FD66AC  580783698 98h83a8j9w3kk TEMPORARY_DATA1 TEMPORARY SORT        201     895625         23      2944          1

 

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

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:12.33 |    2730 |   2931 |   2931 |    25M|  1844K| 4141K (1)|   23552 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:02.00 |    2712 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:           4 Area size:      131072 Max Area size:     1048576
      Degree:               1
      Blocks to Sort:    3186 Row size:           26 Total Rows:        1000729
      Initial runs:        25 Merge passes:        3 IO Cost / pass:       1270
      Total IO sort cost: 6996      Total CPU sort cost: 1059674733
      Total Temp space used: 48243000

Under PARAMETERS WITH DEFAULT VALUES:
  _smm_min_size                       = 128 KB
  _smm_max_size                       = 4096 KB

The execution plan shows a sort to disk was required.

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;

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
         1000000   48243000               27012096                1888256          4240384 1 PASS                 23552

Still a 1 pass sort to disk.

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

NAME            VALUE        ISDEFAULT ISMODIFIED
-------- ------------------------------------------
_SMM_MAX_SIZE   4096         TRUE      FALSE
_SMM_MIN_SIZE    128         TRUE      FALSE

—-

I performed a similar test on Oracle 10.2.0.2 with the July 2006 CPU on Win x64, and the results were similar to those of Oracle 10.2.0.3.

In summary, as the “Oracle Database Performance Tuning Guide 10g Release 2″ Pg 7-38 (PDF page 146) documentation states, “sizing of work areas for all sessions becomes automatic and the *_AREA_SIZE parameters are ignored by all sessions running in that mode.”  There is apparently an odd quirk that once in a while, the first time a SQL statement is parsed, a sort to disk may be required, at least under the base patch of Oracle 10.2.0.2.  This lead me, incorrectly, to believe that setting the SORT_AREA_SIZE to a larger value and re-executing the query actually removed the sort to disk – but it was actually the second parse that resulted in the removal of the sort to disk.  This test case disproves my suggestion that the SORT_AREA_SIZE has any impact on Oracle 10.2.0.2 when all sessions are set to auto for the WORKAREA_SIZE_POLICY.  It is possible to modify the WORKAREA_SIZE_POLICY at the session level, and then the SORT_AREA_SIZE setting takes effect for that session.

This displays the optimizer’s expected execution statistics:

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,NULL));

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |       |  5302 (100)|          |
|   1 |  SORT ORDER BY     |      |  1000K|    13M|    46M|  5302   (6)| 00:00:29 |
|   2 |   TABLE ACCESS FULL| T1   |  1000K|    13M|       |   604   (9)| 00:00:04 |
-----------------------------------------------------------------------------------

This displays the actual execution statistics for the last run:

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

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:03.57 |    2712 |    34M|  2086K|   30M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:02.00 |    2712 |       |       |          |
----------------------------------------------------------------------------------------------------------------

Here is a new test run on Oracle 10.2.0.2 July 2006 CPU Win x64.  I did not run any explain plans during this test, although I did enable a 10053 trace:

First test at 150MB:

ALTER SYSTEM SET PGA_AGGREGATE_TARGET=150M;
ALTER SYSTEM FLUSH SHARED_POOL;

Current sort statistics:

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                    VALUE
------------------ ----------
sorts (disk)               87
sorts (memory)       28169866
sorts (rows)        675027667

No other sorts in process – an otherwise idle database instance:

SELECT
  *
FROM
  V$SORT_USAGE;

no rows selected

Run the query:

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

SELECT /* FIND_ME */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

(EXECUTED IN A DIFFERENT SESSION WHILE THE ABOVE IS EXECUTING):

SELECT
  *
FROM
  V$SORT_USAGE;

USERNAME USER     SESSION_ADDR     SESSION_NUM SQLADDR             SQLHASH SQL_ID        TABLESPACE                      CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#     EXTENTS     BLOCKS   SEGRFNO#
-------- -------- ---------------- ----------- ---------------- ---------- ------------- ------------------------------- --------- --------- ---------- ---------- ---------- ---------- ----------
TESTING  TESTING  000007FF9E4C0160       61714 000007FF929494C8 1262288602 0gzdjb55mtzqu TEMPORARY_DATA1                 TEMPORARY SORT             201      37641         23       2944          1

The above shows that the query is actively sorting to the temp tablespace.

The sorts (disk) statistic increased:

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                  VALUE
---------------- ----------
sorts (disk)             88
sorts (memory)     28170875
sorts (rows)      676034807

The execution statistics for the plan shows the estimates and actuals.  A 1 pass sort to disk:

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 /* FIND_ME */%'
  AND S.SQL_ID=SP.SQL_ID;

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
                   48522000               26975232                1887232         31472640 1 PASS                 23552

Second test at 200MB:

ALTER SYSTEM SET PGA_AGGREGATE_TARGET=200M;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT /* FIND_ME */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

(EXECUTED IN A DIFFERENT SESSION WHILE THE ABOVE IS EXECUTING):

SELECT
  *
FROM
  V$SORT_USAGE;

USERNAME USER     SESSION_ADDR     SESSION_NUM SQLADDR             SQLHASH SQL_ID        TABLESPACE                      CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#     EXTENTS     BLOCKS   SEGRFNO#
-------- -------- ---------------- ----------- ---------------- ---------- ------------- ------------------------------- --------- --------- ---------- ---------- ---------- ---------- ----------
TESTING  TESTING  000007FF9E4C0160       61714 000007FF94EA7488 2019831986 4ww44m1w68c5k TEMPORARY_DATA1                 TEMPORARY SORT             201      29705        23       2944          1

 

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                VALUE
-------------- ----------
sorts (disk)           89
sorts (memory)   28172666
sorts (rows)    677048890

The execution statistics for the plan shows the estimates and actuals.  A 1 pass sort to disk:

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 /* FIND_ME */%'
  AND S.SQL_ID=SP.SQL_ID;

The execution statistics for the plan shows the estimates and actuals.  A 1 pass sort to disk the first time this query was parsed with PGA_AGGREGATE_TARGET=200M:

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
                   48522000               26975232                1887232         31472640 1 PASS                 23552

Third test at 200MB (note changing SORT_AREA_SIZE is not the cause of the sort to disk disappearing):

ALTER SESSION SET SORT_AREA_SIZE=41943040;
ALTER SESSION SET SORT_AREA_RETAINED_SIZE=41943040;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT /* FIND_ME */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

(EXECUTED IN A DIFFERENT SESSION WHILE THE ABOVE IS EXECUTING):

SELECT
  *
FROM
  V$SORT_USAGE;

no rows selected

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                VALUE
-------------- ----------
sorts (disk)           89
sorts (memory)   28173729
sorts (rows)    678058329

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 /* FIND_ME */%'
  AND S.SQL_ID=SP.SQL_ID;

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
                   48522000               40551424                2262016         36044800 OPTIMAL

Fourth test at 300MB:

ALTER SYSTEM SET PGA_AGGREGATE_TARGET=300M;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT /* FIND_ME */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

(EXECUTED IN A DIFFERENT SESSION WHILE THE ABOVE IS EXECUTING):

SELECT
  *
FROM
  V$SORT_USAGE;

no rows selected

 

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                VALUE
-------------- ----------
sorts (disk)           89
sorts (memory)   28175547
sorts (rows)    679074619

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 /* FIND_ME */%'
  AND S.SQL_ID=SP.SQL_ID;

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
                   48522000               40551424                2262016         36044800 OPTIMAL

Fifth test at 150MB:

ALTER SYSTEM SET PGA_AGGREGATE_TARGET=150M;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT /* FIND_ME */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

(EXECUTED IN A DIFFERENT SESSION WHILE THE ABOVE IS EXECUTING):

SELECT
  *
FROM
  V$SORT_USAGE;

USERNAME USER     SESSION_ADDR     SESSION_NUM SQLADDR             SQLHASH SQL_ID        TABLESPACE                      CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#     EXTENTS     BLOCKS   SEGRFNO#
-------- -------- ---------------- ----------- ---------------- ---------- ------------- ------------------------------- --------- --------- ---------- ---------- ---------- ---------- ----------
TESTING  TESTING  000007FF9E4C0160       61714 000007FF94EA7488 2019831986 4ww44m1w68c5k TEMPORARY_DATA1                 TEMPORARY SORT             201      37641         23       2944          1

 

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                VALUE
-------------- ----------
sorts (disk)           89
sorts (memory)   28172666
sorts (rows)    677048890

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 /* FIND_ME */%'
  AND S.SQL_ID=SP.SQL_ID;

 

It might be interesting to see how the test behaves on Oracle database 11.1.0.7 or 11.2.o.1.


Actions

Information

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 137 other followers

%d bloggers like this: