Explain Plan

[rohanem]

Hi all,
I want to know after I generate a plan for a query,how do I know things like at which point the query is taking a long time..whether its the full table access,or the size of cost etc..please help.

thanks
rohan

 

[ThomVF]

Not sure how far you have gone, so I'll start from square-1.

You need to create the PLAN_TABLE, which holds the execution plan details for a query you want to analyze. The script to do this is found in:
$ORACLE_HOME/rdbms/admin/utlxplan.sql

Once you have this table in place, you need to add the string below prior to a query (in a script) to dump the execution plan to the PLAN_TABLE table for review:

EXPLAIN PLAN FOR
select x,y,z
from table;

Run the query and the explain plan is pushed in to the PLAN_TABLE (the query is not actually run, just parsed). The following script is used to dump the content of the PLAN_TABLE to a readable format.
$ORACLE_HOME/rdbms/admin/utlxpls.sql

Note: If you are analying queries with Parallel actions, use this script instead:
$ORACLE_HOME/rdbms/admin/utlxplp.sql

See these links to understand how to interpret the results:

http://www.gers.com/whitepaper/IOUG-Muthu.pdf

http://download-east.oracle.com/docs/cd/A87860_01/doc/index.htm

Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[rcurva]

In addition to Thomas' input, you can also make use of the TKPROF utility of Oracle.

On the database side;
1. set init.ora parameter
sql_trace=true
timed_statistics=true
2. Restart database
3. Run your application or batch program.
4. Run tkprof against the tracefile (from udump directory) created by your application:
tkprof <tracefile> <outputfile> EXPLAIN=username/passwd
5. Look at formatted output of trace command and make sure that your SQL statement is using indexes correctly.

Note: if your process is a long running one, you might consider adjusting max_dump_file_size (in OS blocks) parameter also bec this will set the limit of the size of your trace file.

On session level;
1. Start tracing by the following;
DBMS_SESSION.SET_SQL_TRACE(TRUE); --could be added inside a stored procs.
2. Stop tracing;
DBMS_SESSION.SET_SQL_TRACE(FALSE);


Robbie

&quot;The rule is, not to besiege walled cities if it can possibly be avoided&quot; -- Art of War

 

[DoubleH]

Hi,
You may also try by setting the autotrace to true. After that whatever sql You fire, You will be able to get the trace at the end automatically including the time taken,etc.
Thanks & Regards,
DoubleH