| 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 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'));
| 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
---------------- ---------- ----------------------
---------------------- ---------------- ---------- -----------------
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
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
---------------- ---------- ----------------------
---------------------- ---------------- ---------- -----------------
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
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
---------------- ---------- ----------------------
---------------------- ---------------- ---------- -----------------
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
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
---------------- ---------- ----------------------
---------------------- ---------------- ---------- -----------------
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
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
 |
 |