1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Use Explain Plan in TOAD

Discussion in 'Other Development Tools' started by Julian, Jan 15, 2009.

  1. Julian

    Julian Forum Advisor

    Messages:
    48
    Likes Received:
    1
    Trophy Points:
    90
    Hi guys, i realise that we can use explain plan in Toad also, but some of my colleagues say that explain plans should only be used from SQL Plus. Is it that Explain Plan from Toad are not reliable?
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Toad Explain plan information is absolutely reliable .

    Check these links in case of any issues

    link1
    link2
     
  3. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    TOAD issues 'explain plan set statement_id = ... into plan_table for 'select ...' in the background when you use the Explain Plan, so there's really no difference whatsoever.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    There can be differences, as the PLAN_TABLE definition provided by Quest may be out of date. Yes, Oracle can still use the table, and what plan information it can insert will be current, but you are likely to miss details a current PLAN_TABLE definition would contain, such as predicate and filter information, estimated TEMP space consumption, estimated execution time and the option to gather plan statistics to produce a more detailed report:

    SQL> select * from table(dbms_xplan.display_cursor('dmfdcmvwkgfqh',0,'ALL ALLSTATS'));

    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    --
    SQL_ID dmfdcmvwkgfqh, child number 0
    -------------------------------------
    select /*+ gather_plan_statistics */ distinct owner from v$access

    Plan hash value: 174934893

    ------------------------------------------------------------------------------------------------------------------------------------------------------
    ---
    | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | OMem | 1Mem | O/1/M
    |
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    ---
    | 1 | HASH UNIQUE | | 1 | 15 | 1305 | 5 (100)| 00:00:01 | 4 |00:00:00.10 | | |
    |
    | 2 | NESTED LOOPS | | 1 | 433 | 37671 | 4 (100)| 00:00:01 | 394 |00:00:00.09 | | |
    |
    | 3 | NESTED LOOPS | | 1 | 433 | 29877 | 3 (100)| 00:00:01 | 394 |00:00:00.06 | | |
    |
    |* 4 | HASH JOIN | | 1 | 433 | 16021 | 1 (100)| 00:00:01 | 335 |00:00:00.03 | 1236K| 1236K| 1/0
    /0|
    |* 5 | FIXED TABLE FULL | X$KSUSE | 1 | 170 | 2040 | 0 (0)| | 170 |00:00:00.01 | | |
    |
    | 6 | FIXED TABLE FULL | X$KGLLK | 1 | 433 | 10825 | 1 (100)| 00:00:01 | 335 |00:00:00.01 | | |
    |
    |* 7 | FIXED TABLE FIXED INDEX| X$KGLDP (ind:1) | 335 | 1 | 32 | 0 (0)| | 394 |00:00:00.03 | | |
    |
    |* 8 | FIXED TABLE FIXED INDEX | X$KGLOB (ind:1) | 394 | 1 | 18 | 0 (0)| | 394 |00:00:00.03 | | |
    |
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    ---

    Query Block Name / Object Alias (identified by operation id):
    -------------------------------------------------------------

    1 - SEL$5C160134
    5 - SEL$5C160134 / S@SEL$3
    6 - SEL$5C160134 / L@SEL$3
    7 - SEL$5C160134 / D@SEL$3
    8 - SEL$5C160134 / O@SEL$3

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    4 - access("L"."KGLLKUSE"="S"."ADDR")
    5 - filter("S"."INST_ID"=USERENV('INSTANCE'))
    7 - filter(("L"."KGLLKHDL"="D"."KGLHDADR" AND "L"."KGLNAHSH"="D"."KGLNAHSH"))
    8 - filter(("O"."KGLNAHSH"="D"."KGLRFHSH" AND "O"."KGLHDADR"="D"."KGLRFHDL"))

    Column Projection Information (identified by operation id):
    -----------------------------------------------------------

    1 - "O"."KGLNAOWN"[VARCHAR2,64]
    2 - "O"."KGLNAOWN"[VARCHAR2,64]
    3 - "D"."KGLRFHDL"[RAW,8], "D"."KGLRFHSH"[NUMBER,22]
    4 - (#keys=1) "L"."KGLNAHSH"[NUMBER,22], "L"."KGLLKHDL"[RAW,8]
    5 - "S"."ADDR"[RAW,8], "S"."INST_ID"[NUMBER,22]
    6 - "L"."KGLLKUSE"[RAW,8], "L"."KGLLKHDL"[RAW,8], "L"."KGLNAHSH"[NUMBER,22]
    7 - "D"."KGLHDADR"[RAW,8], "D"."KGLNAHSH"[NUMBER,22], "D"."KGLRFHDL"[RAW,8], "D"."KGLRFHSH"[NUMBER,22]
    8 - "O"."KGLHDADR"[RAW,8], "O"."KGLNAOWN"[VARCHAR2,64], "O"."KGLNAHSH"[NUMBER,22]


    48 rows selected.

    Remember, too, that EXPLAIN PLAN is an estimated execution path for the query, based upon cursory examination of relevant statistics; the query is not executed so it doesn't go through the same process of parsing and plan generation as an executed query would. Setting autotrace on returns a plan based upon the actual execution of the query, and the plans returned by EXPLAIN PLAN and autotrace can differ.
     
    mgraceparth@gmail.com likes this.