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.
Charles Hooper's Oracle Notes 
Leave a comment