"Depreciated" Parameters In SPFILE
Dereck L. Dietz
This may seem an easy question but I can't seem to find an answer.
If an Oracle instance is set up for automatic memory management and there
are some parameters, such as bitmap_merge_area_size (etc) which are non-zero
and which Oracle recommends using the PGA_AGGREGATE_TARGET instead ,should
such parameters be zeroed out in the SPFILE?
I know the manual says they aren't recommended except in Shared Server
environments but what should be done in a Dedicated Server environment?
Thanks.
hjr.p...@gmail.com
The old _AREA_SIZE parameters are silently ignored if the new
parameter is present.
You may want to leave them in the spfile at their non-zero values,
however, in case you switch to shared server mode (or, remembering
that an instance can do both shared- and dedicated-server mode at the
same time, in case one or two of your users need to connect in shared
mode).
If you really want to get rid of the old parameters, don't just zero
them out, though. Delete them from the spfile entirely:
alter system reset sort_area_size scope=spfile sid='*';
...that way, the parameter still has a non-zero default value for
those rare occasions when it might be needed.
Charles Hooper
Helpful advice, I wasn't aware of how to remove a parameter from a
SPFILE.
But, the _AREA_SIZE parameters are not silently ignored. In my
testing, the _AREA_SIZE parameters set the minimum values for those
memory areas, and Oracle is able to adjust the value of the parameter
upward as needed. If I recall correctly, SORT_AREA_SIZE may be
automatically adjusted up to 5% of the PGA_AGGREGATE_TARGET and
HASH_AREA_SIZE up to 10% of the PGA_AGGREGATE_TARGET. Prior to
implementing Oracle 10.2.0.2, I found one query that was sorting to
the temp tablespace under Oracle 10.2.0.2 (PGA_AGGREGATE_TARGET set at
4000MB, 5% = 200MB) that was not sorting to the temp tablespace under
Oracle 8.1.7.3 with a SORT_AREA_SIZE of 10MB. Oracle 10.2.0.2 was
apparently adjusting the SORT_AREA_SIZE to be just large enough for
the session to perform a one pass sort to disk. I bumped the
SORT_AREA_SIZE to 20MB, resulting in the elimination of the sort to
the temp tablespace.
Metalink Notes:223299.1, 223730.1 both agree with you that the
_AREA_SIZE parameters ARE silently ignored. But, those articles are
apparently wrong. A write-up by Burleson quoted contents of those
Metalink articles, which lead to the development of this article that
provides a test case that shows that the parameters are not ignored:
http://www.jlcomp.demon.co.uk/untested.html
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Mark D Powell
>
> - Show quoted text -
Very fine reference article.
IMHO -- Mark D Powell --
Bob Jones
"Charles Hooper" <hoope...@yahoo.com> wrote in message
news:1187183849.4...@b79g2000hse.googlegroups.com...
Wow, this guy really has too much time in his hands, or he just hate this
Don guy too much. It is pointless to set _AREA_SIZE when using
PGA_AGGREGATE_TARGET anyway.
sybr...@hccnet.nl
>Wow, this guy really has too much time in his hands, or he just hate this
>Don guy too much. It is pointless to set _AREA_SIZE when using
>PGA_AGGREGATE_TARGET anyway.
>
Did you read the article? You didn't, did you.
If you did why do you post this nonsense remark?
--
Sybrand Bakker
Senior Oracle DBA
DA Morgan
You really need to drop the attitude or learn about the "rule of holes:"
When you are in one stop digging.
Howard is 100% correct in everything he wrote. And you will find the
exact same criticism from Tom Kyte, and Jonathan Lewis, and many others
who are the experts in our community.
In case you are not familiar with Howard ... you might want to review
this:
http://apex.oracle.com/pls/otn/f?p=19297:4:4116369228746107::NO:4:P4_ID:59
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Bob Jones
"DA Morgan" <damo...@psoug.org> wrote in message
news:11872800...@bubbleator.drizzle.com...
What hole? Please be specific.
> Howard is 100% correct in everything he wrote. And you will find the exact
> same criticism from Tom Kyte, and Jonathan Lewis, and many others
> who are the experts in our community.
>
Your responses aren't always to the point, but please for one time, provide
some facts, not names.
> In case you are not familiar with Howard ... you might want to review
> this:
> http://apex.oracle.com/pls/otn/f?p=19297:4:4116369228746107::NO:4:P4_ID:59
> --
Hmmm, more names and links? You must be a world class promoter.
hjr.p...@gmail.com
Charles, I may be getting confused as to the point you're making.
Jonathan's article says 'So the sort_area_size does have some
relevance - even when all the new pga mechanisms are correctly
enabled... ****IF***** ...you (like me) are running with shared
servers (formerly multi-threaded servers) and know that for Oracle 9i,
the older parameters are still used to limit the memory that gets
allocated in the SGA (which is where the UGA is going to be held when
using shared servers)."
Jonathan is using Shared Servers. In 9i and early 10g, shared servers
absolutely relied on SORT_AREA_SIZE. That's why my earlier advice was
to leave them in the spfile so that they "still ha[ve] a non-zero
default value for those rare occasions when [they] might be needed" -
namely, when you connect with shared servers to an otherwise mostly-
dedicated server environment.
I have not seen anything in Jonathan's article that indicates that the
old parameters are anything other than silently ignored **if** you are
using dedicated server processes. I could be wrong: I find that
article quite hard to read because of its incremental addenda.
Certainly, in 11g, if you set MEMORY_TARGET and *also* set
PGA_AGGREGATE_TARGET, then the P_A_T is regarded as a minimum memory
demand that must be satisfied from the memory_target. So it's possible
that sort of behaviour did take place in 10g, despite all the
documentation saying otherwise. I haven't tested it rigorously myself,
I will admit.
Regards
HJR
hjr.p...@gmail.com
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
Charles Hooper
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
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.
DA Morgan
> 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.
Excellent work.
But then it might just be that the optimizer is telling you it thinks it
is going to do something that it isn't actually going to do.
Instead of using EXPLAIN PLAN run a couple of selected statements for
real and then look at what actually happened. Does fact equal theory?
Charles Hooper
> Puget Sound Oracle Users Groupwww.psoug.org
All DBMS_XPLANS showed the actual execution plans, except for one,
which was intentionally created to show the same plan as what appeared
in the 10053 trace files (which showed the prediction). All 1,000,000
rows from every query run was presented in SQL*Plus - that is one of
the reasons why it took roughly 10 hours to generate the test runs.
The 10053 trace files repeatedly indicated that 48MB of temp
tablespace would be required, but that estimate was consistently
incorrect. Unfortunately, some of the detail and clarity of the runs
were lost when the results were reformatted for the Usenet post.
Equally interesting was that _smm_max_size (maximum size of a single
work area) was set at 20% (1/5) of the pga_aggregate_target, rather
than the 5% of pga_aggregate_target that I was expecting.
My thanks to Howard Rogers for calling me on my incorrect statement
regarding SORT_AREA_SIZE. I could have sworn that I saw a similar
statement from a reliable source that said that the _AREA_SIZE
parameters set the minimum values when workarea_size_policy=auto, but
I have not found the source of that reference.
DA Morgan
>> Instead of using EXPLAIN PLAN run a couple of selected statements for
>> real and then look at what actually happened. Does fact equal theory?
>> --
>> Daniel A. Morgan
>> University of Washington
>> damor...@x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org
>
> All DBMS_XPLANS showed the actual execution plans, except for one,
> which was intentionally created to show the same plan as what appeared
> in the 10053 trace files (which showed the prediction).
I'm not sure you read what I intended. Explain Plans may or may not
accurately reflect reality. I am suggesting running a couple of the
statements without explain plan and then looking at the metrics of
what Oracle actually does. Does it match the explain plan?
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Charles Hooper
> Charles Hooper wrote:
> >> Instead of using EXPLAIN PLAN run a couple of selected statements for
> >> real and then look at what actually happened. Does fact equal theory?
> >> --
> >> Daniel A. Morgan
> >> University of Washington
> >> damor...@x.washington.edu (replace x with u to respond)
> >> Puget Sound Oracle Users Groupwww.psoug.org
>
> > All DBMS_XPLANS showed the actual execution plans, except for one,
> > which was intentionally created to show the same plan as what appeared
> > in the 10053 trace files (which showed the prediction).
>
> I'm not sure you read what I intended. Explain Plans may or may not
> accurately reflect reality. I am suggesting running a couple of the
> statements without explain plan and then looking at the metrics of
> what Oracle actually does. Does it match the explain plan?
> --
> Daniel A. Morgan
> University of Washington
> damor...@x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org
I am fairly certain that I understand what you intended.
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
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';
Run the query:
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;
DA Morgan
Thanks.
There are some fascinating insights on Oracle's sort behaviour that
Jonathan Lewis discusses in his 3 day Optimizing Oracle class. I hope
you can make it out here next July to attend.
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu (replace x with u to respond)