On Aug 17, 10:54 am, Charles Hooper <hooperc2
...@yahoo.com> wrote:
> On Aug 16, 10:35 pm, hjr.pyth
...@gmail.com wrote:
> > I meant to add that in that article, Jonathan **does** go on to use a
> > dedicated server for testing... but he does so only to disprove an
> > assertion made by the World's Greatest Oracle Database Expert that
> > PGA_AGGREGATE_TARGET cannot be set above 200MB: "So let's see if there
> > is any difference between running the same query (through a
> > **dedicated server** this time) with a pga_aggregate_target of 200MB,
> > as opposed to a pga_aggregate_target of 500 MB, or 1000 MB."
> > I don't see him saying that _AREA_SIZE parameters are having an effect
> > on his results at that particular point.
> > Regards
> > HJR
> Howard,
> Thanks for the response.
> I re-read the article that I referenced (for a third time) and now see
> that it does _not_ state that the _AREA_SIZE parameters have an effect
> - thanks for bringing this to my attention.
> I am working on a test case to demonstrate the behavior that I noticed
> under the base patch of Oracle 10.2.0.2, where changing the value of
> the SORT_AREA_SIZE did affect the number of sorts to disk, as Oracle
> switched from a one pass sort to an optimal sort. Looking over my
> notes from the read through of "Cost-Based Oracle Fundamentals", the
> book indicates that the switch from a one pass sort to an optimal sort
> can (or will likely) increase CPU usage - this may or may not affect
> the ability of reproducing the behavior as the increase in forecasted
> CPU usage affects a plan's cost. So far, my test case on Oracle
> 10.2.0.3 is agreeing with your statement of "The old _AREA_SIZE
> parameters are silently ignored if the new [PGA_AGGREGATE_TARGET]
> parameter is present." I may need to back off the position that the
> parameters still have an effect when PGA_AGGREGATE_TARGET is set. The
> testing will continue.
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.
Caution - this is a long post! The formatting of Usenet posts
probably will not do this post any justice.
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:
CREATE TABLE T1 (
C1 VARCHAR2(20),
C2 NUMBER(12),
C3 NUMBER(12));
Fill the test table to 1,000,000 rows:
DECLARE
X NUMBER;
BEGIN
FOR X IN 1 .. 1000000
LOOP
INSERT INTO T1 VALUES (
TO_CHAR(MOD( X ,100))||'-'||TO_CHAR(MOD( X ,100)),
MOD( X ,500),
X);
END LOOP;
END;
/
COMMIT;
Gather statistics on the table:
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1');
Let's see where the sort statistics are right now:
SELECT
NAME,
VALUE
FROM
V$SYSSTAT
WHERE
UPPER(NAME) LIKE '%SORT%'
ORDER BY
NAME;
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
block 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:
EMP_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
Problem in the 10053 trace file? Note the "Total Temp space used: 0"
PARAMETERS WITH DEFAULT VALUES
pga_aggregate_target = 153600 KB
_smm_min_size = 153 KB
_smm_max_size = 30720 KB
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 | | |
| |
--------------------------------------------------------------------------- -----------------------------------------------------------------
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 | | | |
--------------------------------------------------------------------------- -------------------------------------
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 | | |
| |
--------------------------------------------------------------------------- -----------------------------------------------------------------
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.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
