| about explain plan [message #19695] |
Thu, 04 April 2002 00:43  |
Nimit
Messages: 6
Registered: December 2001
|
Junior Member |
|
|
|
I havent't understood any thing about EXPALIN PLAN so can any one explain it with sone code if possible.
|
|
|
|
| Re: about explain plan [message #19703 is a reply to message #19695] |
Thu, 04 April 2002 05:45  |
Su
Messages: 154
Registered: April 2002
|
Senior Member |
|
|
It explains about the execution plan which oracle has chosen to execute your SQL statement. Normally it is used to analyze what optimizer mode has been set and how it has executed your SQL statement. As you might be aware of, there are a few optimizer modes such as COST , RULE, FULL etc.
You can see the execution plan how ORACLE has executed your SQL statement by EXPLAIN PLAN.
Check this out.
EXPLAIN PLAN SET STATEMENT_ID = 'MYTEST' FOR SELECT your_columns FROM your_table WHERE your_condition_criteria.
Make sure the default PLAN_TABLE has been created on your system. If not, try to locate the UTLXPLAN.SQL in your oraclerdbmsadmin folder and execute the text contained in it as it is. And then try the above EXPLAIN PLAN example.
Otherwise, an easier way to all this is, to see the complete output using TKPROF utility provided by oracle.
Set the following.
SQL> ALTER SESSION SET SQL_TRACE TRUE;
SQL> SELECT your_columns FROM you_table WHERE your_condition_criteria;
Since you set the SQL_TRACE true, it automatically trace all your activities and dumps into a .trc file, which normally exists in your oracle database admin folder, like c:ORACLEADMINyourdatabasenameUDUMP
Find the file whose name looks like ORA9999.TRC, where 9999 is a four digit number, and created very recently.
Once after you locate your trace file, use the following statement at SQL prompt.
SQL> TKPROF yourtracefile outputfile EXPLAIN=username/password SORT=(options);
The options can be any of the following:
prs - sort on values during parse details
exe - sort on values during execute calls
fch - sort on values during fetch calls
The outputfile can be any of your text file where the TKPROF utility dumps the output in.
Try it out. Let me know if you still need any help.
Good luck :)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
