Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How can I obtain the execution plan of a SQL statement?
- Thread starter 011179
- Start date
Abhivyakti
Technical User
- Dec 1, 2001
- 22
- 0
- 0
hi,
For every DML statement that you would execute at SQL promp you can obtain the execution plan for it, provided you have the plan_table created in ur schema.
If you don't have this plan_table created, then u hve to run the script file called utlxplan.sql (The name of the file may be a little different like utilxplan or utl_xplan or something like that - that you will have to check)
After running this script file it should create the plan_table.
Then you give
SET AUTOTRACE ON EXPLAIN PLAN
or
SET AUTOTRACE ON EXPLAIN STATISTICS
In the first case it will only show you plan. In the second case it will also show the plan and it will also show the statistics.
hope this helps you,
abhivyakti
For every DML statement that you would execute at SQL promp you can obtain the execution plan for it, provided you have the plan_table created in ur schema.
If you don't have this plan_table created, then u hve to run the script file called utlxplan.sql (The name of the file may be a little different like utilxplan or utl_xplan or something like that - that you will have to check)
After running this script file it should create the plan_table.
Then you give
SET AUTOTRACE ON EXPLAIN PLAN
or
SET AUTOTRACE ON EXPLAIN STATISTICS
In the first case it will only show you plan. In the second case it will also show the plan and it will also show the statistics.
hope this helps you,
abhivyakti
- Thread starter
- #3
- Thread starter
- #4
Abhivyakti
Technical User
- Dec 1, 2001
- 22
- 0
- 0
hi
at sql prompt type set autotrace on explain plan
SQL> set autotrace on explain plan<enter>
this should work
at sql prompt type set autotrace on explain plan
SQL> set autotrace on explain plan<enter>
this should work
tigreeuropeo
IS-IT--Management
Just in case you can't find it, the script is utlxplan.sql and it's located in $ORACLE_HOME/rdbms/admin
Good luck
Good luck
Guys -
PLAN_TABLE output shows the execution plan that Oracle intend to use to execute the query. There is no gaurentee that this execution plan is THE plan that is used during execution. This is because the execution environment might be different for different users executing the same query. For instance the execution environment depends on "SORT_AREA_SIZE" , "HASH_AREA_SIZE" etc. Hence looking at the plan_table is a rough way of estimating what Oracle might use to execute the query.
However there is a new feature introduced in 9i where the actual plan used by Oracle can be viewed. This is by means of querying V$SQL_PLAN. Refer Oracle manual for 9i to know more about it.
Regards
Chaan
PLAN_TABLE output shows the execution plan that Oracle intend to use to execute the query. There is no gaurentee that this execution plan is THE plan that is used during execution. This is because the execution environment might be different for different users executing the same query. For instance the execution environment depends on "SORT_AREA_SIZE" , "HASH_AREA_SIZE" etc. Hence looking at the plan_table is a rough way of estimating what Oracle might use to execute the query.
However there is a new feature introduced in 9i where the actual plan used by Oracle can be viewed. This is by means of querying V$SQL_PLAN. Refer Oracle manual for 9i to know more about it.
Regards
Chaan
- Status
