Charles Hooper wrote:
> On Aug 20, 12:10 pm, DA Morgan <damor..._at_psoug.org> wrote:
>> 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..._at_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..._at_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;
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.
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
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Mon Aug 20 2007 - 15:54:20 CDT
