DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: explain plan

  1. #1
    Join Date
    Feb 2001
    Posts
    103
    Hi ,
    Can anybody provide me with a brief desciption about the explain plan what is it and how to use it ? Any example will be of great help.
    thankx in advance.
    When the going gets tough, the tough gets going

  2. #2
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530
    Hi Akhil,
    Basically explian plan is used to find out how Oracle will execute the query.It will show whether indexes will be used or not and incase if it uses the index it will show which indexes are being used or whether the query is performing full table scans or not.
    In order to get an explain plan for a query u have to create first the plan table.The plan table is created by running the utlxplan.sql.Run this script and create the plan table.
    After u create the plan table,run the follwing query to generate the explain plan


    sql> explain plan
    set statement_id='A'
    for ;

    explained

    sql> Run the following query to get the explain plan for the
    query

    select lpad(' ',2*level)
    ||operation||' '||options||' '||object_name
    Execution_path from plan_table where
    statement_id='&statementid'
    connect by prior id=parent_id and
    statement_id='A'
    start with id=1;


    e.g. Explain plan for a sample query
    EXECUTION_PATH
    --------------------------------------------------------------------------------
    NESTED LOOPS
    TABLE ACCESS FULL AP_REQ_LETTER
    TABLE ACCESS BY INDEX ROWID M_USER_MAST
    INDEX UNIQUE SCAN M_USER_CODE_IDX


    In case of any help please be free to ask me at rohitsn@altavista.com

    Regards,
    ROhit Nirkhe,Oracle DBA,OCP 8i
    rohitsn@altavista.com
    Thanks and Regards
    Rohit S Nirkhe
    rohitsnirkhe@rediffmail.com

  3. #3
    Join Date
    Feb 2001
    Posts
    103
    Hi rohit,
    Thankx for the reply. Everything is fine except the
    sql> explain plan
    set statement_id='A'
    for ;

    explained
    The above statement does not works fine.
    Thanks for the help.
    When the going gets tough, the tough gets going

  4. #4
    Join Date
    Apr 2001
    Posts
    118
    That's because he enclosed some text in < and > symbols. Since this forum is HTML based, it thinks that is some sort of HTML tag (which it isn't) and doesn't display the text within it.

    What rohitsn was trying to get displayed was:

    sql> explain plan
    set statement_id='A'
    for <query>;

    HTH,

    Heath

  5. #5
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    to see the explain plan result in sql/plus --

    set autotrace on

    Run the query and it will display the query result and the execution path and the statistics; Example --


    SQL> set autotrace on
    SQL> select * from test where id < 20;

    ID NAME CITY DT1
    --------- --------------------------------------------- --------- ---------
    1 1 1 10-APR-01
    1 3 3 10-APR-01
    7 test7 7 10-APR-01
    8 test8 8 10-APR-01
    9 test9 9 10-APR-01
    11 test for 11

    6 rows selected.


    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE
    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
    2 1 INDEX (RANGE SCAN) OF 'TEST' (NON-UNIQUE)




    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    4 consistent gets
    0 physical reads
    0 redo size
    1523 bytes sent via SQL*Net to client
    668 bytes received via SQL*Net from client
    4 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    6 rows processed


    - Rajeev
    Rajeev Suri

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width