| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: "Depreciated" Parameters In SPFILE
On Aug 17, 10:54 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Aug 16, 10:35 pm, hjr.pyth..._at_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
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
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
optimizer_index_caching=0 optimizer_index_cost_adj=100 optimizer_mode=ALL_ROWS
undo_management=AUTO undo_retention=1800 undo_tablespace=ROLLBACK_DATA
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\udumputl_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);
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'));
| 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
---------------- ---------- ----------------------
---------------------- ---------------- ---------- -----------------
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'));
| 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
---------------- ---------- ----------------------
---------------------- ---------------- ---------- -----------------
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'));
| 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'));
| 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
---------------- ---------- ----------------------
---------------------- ---------------- ---------- -----------------
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'));
| 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
---------------- ---------- ----------------------
---------------------- ---------------- ---------- -----------------
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'));
| 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
---------------- ---------- ----------------------
---------------------- ---------------- ---------- -----------------
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'));
| 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
---------------- ---------- ----------------------
---------------------- ---------------- ---------- -----------------
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
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'));
| 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'));
| 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;
| 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'));
| 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-
| Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used- |
| 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'));
| 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'));
| 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'));
| 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'));
| 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'));
| 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;
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
---------------- ---------- ----------------------
---------------------- ---------------- ---------- -----------------
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'));
| 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
---------------- ---------- ----------------------
---------------------- ---------------- ---------- -----------------
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'));
| 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
---------------- ---------- ----------------------
---------------------- ---------------- ---------- -----------------
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 reexecuting 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.
Received on Sun Aug 19 2007 - 12:37:09 CDT
 |
 |