| 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: newbie, getting cost plan
On Sep 7, 7:43 am, sybrandb <sybra..._at_gmail.com> wrote:
> On Sep 7, 1:07 pm, codefragm..._at_googlemail.com wrote:
>
> > Hi
> > I want to get the -real- cost and plan of an sql statement, not
> > estimated I can use:
>
> > (1) Explain Plan and DBMS_XPLAN from sqlplus, I gather this isn't the
> > real plan?
> > (2) Autotrace On, I gather this will be the real plan?
> > (3) tracing and tkprof, which can use explain plan, is this the real
> > plan?
>
> > and there are other ways I've seen mentioned?
> > In my present scenario I can do what I want with the database and
> > application.
>
> > thanks
>
> tracing and tkprof is the easiest, provided you do NOT use the
> explain= option (as this will run a new explain plan) and you left
> sql*plus properly before running tkprof (ie you issued a COMMIT or
> ROLLBACK which will have Oracle dump the real explain plan)
>
> Other than that you could of course run statspack on level 6. This
> will save the real explain plan, in the statspack tables. Doing so,
> you can use sprepsql to retrieve the explain plan. Note: the method to
> do so is user unfrienly, however it works.
>
> --
> Sybrand Bakker
> Senior Oracle DBA
I have effectively used the TKPROF method mentioned by Sybrand. Once you have an understanding of how to read the STAT lines in the raw trace files (see Cary Millsap's book), you can even skip the use of TKPROF - as long as multiple cursors are not opened simultaneously it is easy to keep things straight.
If you are able to use DBMS_XPLAN, you will probably find that it
produces answers even faster than TKPROF. For example, assume that
you have the following silly SQL statement (just used to generate a
large plan with little effort - probably helps the BCHR):
SELECT
MIN(SYSDATE)
FROM
ALL_TABLES;
An estimated plan might look like this:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 499 (100)| | | | 1 | SORT AGGREGATE | | 1 | 144 | | | | |* 2 | FILTER | | | | | | | |* 3 | HASH JOIN RIGHT OUTER | | 1952 | 274K| 499 (3)| 00:00:02 | | | 4 | TABLE ACCESS FULL | USER$ | 165 | 660 | 6 (0)| 00:00:01 | TEST | |* 5 | HASH JOIN OUTER | | 1952 | 266K| 493 (3)| 00:00:02 | | |* 6 | HASH JOIN | | 1952 | 251K| 436 (3)| 00:00:02 | | | 7 | TABLE ACCESS FULL | USER$ | 165 | 660 | 6 (0)| 00:00:01 | TEST | |* 8 | HASH JOIN | | 1952 | 244K| 430 (3)| 00:00:02 | | | 9 | NESTED LOOPS OUTER | | 1952 | 223K| 371 (2)| 00:00:02 | | |* 10 | HASH JOIN RIGHT OUTER | | 1952 | 213K| 370 (2)| 00:00:02 | | | 11 | TABLE ACCESS FULL | SEG$ | 4845 | 53295 | 46 (0)| 00:00:01 | TEST | |* 12 | HASH JOIN | | 1952 | 192K| 322 (1)| 00:00:01 | | | 13 | MERGE JOIN CARTESIAN| | 10 | 710 | 73 (2)| 00:00:01 | | |* 14 | HASH JOIN | | 1 | 68 | 67 (2)| 00:00:01 | | |* 15 | FIXED TABLE FULL | X$KSPPI | 1 | 55 | 33 (0)| 00:00:01 | TEST | | 16 | FIXED TABLE FULL | X$KSPPCV | 100 | 1300 | 33 (0)| 00:00:01 | TEST | | 17 | BUFFER SORT | | 10 | 30 | 40 (3)| 00:00:01 | | | 18 | TABLE ACCESS FULL | TS$ | 10 | 30 | 6 (0)| 00:00:01 | TEST | |* 19 | TABLE ACCESS FULL | TAB$ | 1952 | 58560 | 249 (1)| 00:00:01 | TEST | |* 20 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | 5 | 1 (0)| 00:00:01 | TEST | |* 21 | TABLE ACCESS FULL | OBJ$ | 14883 | 159K| 58 (6)| 00:00:01 | TEST | | 22 | TABLE ACCESS FULL | OBJ$ | 14883 | 116K| 56 (2)| 00:00:01 | TEST | | 23 | NESTED LOOPS | | 35 | 770 | 34 (0)| 00:00:01 | | | 24 | FIXED TABLE FULL | X$KZSRO | 100 | 1300 | 33 (0)| 00:00:01 | TEST | |* 25 | INDEX RANGE SCAN | I_OBJAUTH2 | 1 | 9 | 1 (0)| 00:00:01 | TEST | |* 26 | FIXED TABLE FULL | X$KZSPR | 1 | 26| 33 (0)| 00:00:01 | TEST |
The actual execution plan statistics might look like this:
| Id | Operation | Name | Starts | E-Rows |A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.30 | 32992 | | | | |* 2 | FILTER | | 1 | | 1952 |00:00:00.30 | 32992 | | | | |* 3 | HASH JOIN RIGHT OUTER | | 1 | 1952 | 1952 |00:00:00.14 | 1403 | 1517K| 1517K| 1487K (0)| | 4 | TABLE ACCESS FULL | USER$ | 1 | 165 | 165 |00:00:00.01 | 15 | | | | |* 5 | HASH JOIN OUTER | | 1 | 1952 | 1952 |00:00:00.13 | 1388 | 1133K| 1133K| 1261K (0)| |* 6 | HASH JOIN | | 1 | 1952 | 1952 |00:00:00.09 | 1212 | 1517K| 1517K| 1484K (0)| | 7 | TABLE ACCESS FULL | USER$ | 1 | 165 | 165 |00:00:00.01 | 15 | | | | |* 8 | HASH JOIN | | 1 | 1952 | 1952 |00:00:00.09 | 1197 | 1236K| 1236K| 1269K (0)| | 9 | NESTED LOOPS OUTER | | 1 | 1952 | 1952 |00:00:00.05 | 1021 | | | | |* 10 | HASH JOIN RIGHT OUTER | | 1 | 1952 | 1952 |00:00:00.04 | 953 | 1155K| 1155K| 1357K (0)| | 11 | TABLE ACCESS FULL | SEG$ | 1 | 4845 | 4845 |00:00:00.01 | 146 | | | | |* 12 | HASH JOIN | | 1 | 1952 | 1952 |00:00:00.02 | 807 | 1593K| 1593K| 1079K (0)| | 13 | MERGE JOIN CARTESIAN| | 1 | 10 | 10 |00:00:00.01 | 15 | | | | |* 14 | HASH JOIN | | 1 | 1 | 1 |00:00:00.01 | 0 | 1452K| 1452K| 321K (0)| |* 15 | FIXED TABLE FULL | X$KSPPI | 1 | 1 | 1 |00:00:00.01 | 0 | | | | | 16 | FIXED TABLE FULL | X$KSPPCV | 1 | 100 | 1410 |00:00:00.01 | 0 | | | | | 17 | BUFFER SORT | | 1 | 10 | 10 |00:00:00.01 | 15 | 9216 | 9216 | 8192 (0)| | 18 | TABLE ACCESS FULL | TS$ | 1 | 10 | 10 |00:00:00.01 | 15 | | | | |* 19 | TABLE ACCESS FULL | TAB$ | 1 | 1952 | 1952 |00:00:00.01 | 792 | | | | |* 20 | INDEX UNIQUE SCAN | I_OBJ1 | 1952 | 1 | 66 |00:00:00.01 | 68 | | | | |* 21 | TABLE ACCESS FULL | OBJ$ | 1 | 14883 | 14846 |00:00:00.01 | 176 | | | | | 22 | TABLE ACCESS FULL | OBJ$ | 1 | 14883 | 14846 |00:00:00.01 | 176 | | | | | 23 | NESTED LOOPS | | 1536 | 35 | 317 |00:00:00.15 | 31589 | | | | | 24 | FIXED TABLE FULL | X$KZSRO | 1536 | 100 | 15024 |00:00:00.02 | 0 | | | | |* 25 | INDEX RANGE SCAN | I_OBJAUTH2 | 15024 | 1 | 317 |00:00:00.10 | 31589 | | | | |* 26 | FIXED TABLE FULL | X$KZSPR | 1 | 1 | 1 |00:00:00.01 | 0 | | | | ---------------------------------------------------------------------------------------------------------------------------------
Cost -> Time in the actual plan.
To set up for this, modify the original SQL statement with a hint like
this:
SELECT /*+ GATHER_PLAN_STATISTICS */
MIN(SYSDATE)
FROM
ALL_TABLES;
Execute the SQL statement, and then to retrieve the actual execution
statistics:
SELECT
*
FROM
TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
If instead you wanted the estimated statistics:
SELECT
*
FROM
TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,NULL));
More information can be found here:
http://jonathanlewis.wordpress.com/2006/11/09/dbms_xplan-in-10g/
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Fri Sep 07 2007 - 10:45:36 CDT
 |
 |