Re: newbie, getting cost plan

On 7 Sep, 16:45, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -

Oracle version 9 for me (and in some cases 8!)

Sorry, I didn't mention it as I'm from a Microsoft SQL background and didn't think this
would be a new thing but something thats been there for a large number of years. Received on Sat Sep 08 2007 - 02:31:08 CDT