About Execution Plan

[Ora]

Hi all,

How to get Execution Plan for a SQL in SQL*Plus prompt?

Thanks,

[debasisdas]

In the ...\oracle\product\10.2.0\db_1\RDBMS\ADMIN folder (or in other location depending on orcl version) u get a fie

UTLXPLAN.SQL


first run the script to create the plan table

then

SQL>SET AUTOTRACE ON

aftr that try with any qeury

it will show the execution plan of the query..


Hope it works fine for u

[Ora]

Thanks alot its working

In the ...\oracle\product\10.2.0\db_1\RDBMS\ADMIN folder (or in other location depending on orcl version) u get a fie

UTLXPLAN.SQL


first run the script to create the plan table

then

SQL>SET AUTOTRACE ON

aftr that try with any qeury

it will show the execution plan of the query..


Hope it works fine for u

[debasisdas]

You are always welcome.

Feel free to put your doubts/queries in the forum.

But first give a try from your side before posting in forum.

And if possible mention what/how u have tried to solve the problem .

Then it will be helpful for the Experts in the forum in understanding/solving your problem


Best of Luck..

[Medhatithi]

In the ...\oracle\product\10.2.0\db_1\RDBMS\ADMIN folder (or in other location depending on orcl version) u get a fie

UTLXPLAN.SQL


first run the script to create the plan table

then

SQL>SET AUTOTRACE ON

aftr that try with any qeury

it will show the execution plan of the query..


Hope it works fine for u

Hi debasis,
Suppose I have a procedure which contains several select into statements. How can I see the explain plan for these individual select into statements when I execute the procedure? I don't seem to have proper rights to view the TRACE, but for individual sql statements, I find out the explain plan by seeing the plan_table, but for select statements executed inside a procedure, what should be my approach?

[debasisdas]

please follow these following steps.

1.start an user section.
2.connect to sys account.
3.run the following query

SELECT SID,SERIAL#,STATUS,NVL(USERNAME,'ORACLE')AS USERNAME,OSUSER,MACHINE,PROGRAM,ROUND(LAST_CALL_ET/60,2) LAST_CALL,TO_CHAR(LOGON_TIME,'DD-MON-YY HH24:MI') AS LOGON_TIME FROM sys.V_$SESSION 
 

4.it returns SID AND SERIAL# of the first user.
5.then run this

exec dbms_system.set_sql_trace_in_session(sid,serial#,true);
 

6.now run the package containing some select into statments.
7.then run this

exec dbms_system.set_sql_trace_in_session(sid,serial#,false);
 

8.it creates a trace file(.trc) in UDUMP folder(location deponds on the version of oracle you are using.)
9.Then use the utility tool TKPROF to generate the report.

[Medhatithi]

please follow these following steps.

1.start an user section.
2.connect to sys account.
3.run the following query

SELECT SID,SERIAL#,STATUS,NVL(USERNAME,'ORACLE')AS USERNAME,OSUSER,MACHINE,PROGRAM,ROUND(LAST_CALL_ET/60,2) LAST_CALL,TO_CHAR(LOGON_TIME,'DD-MON-YY HH24:MI') AS LOGON_TIME FROM sys.V_$SESSION 
 

4.it returns SID AND SERIAL# of the first user.
5.then run this

exec dbms_system.set_sql_trace_in_session(sid,serial#,true);
 

6.now run the package containing some select into statments.
7.then run this

exec dbms_system.set_sql_trace_in_session(sid,serial#,false);
 

8.it creates a trace file(.trc) in UDUMP folder(location deponds on the version of oracle you are using.)
9.Then use the utility tool TKPROF to generate the report.

I don't have these privileges, actually. That's what I meant by saying "I don't seem to have proper rights to view the TRACE". Are there any other way-outs? For example, in Sybase you can do so, by pasting a pl/sql block and then running the explain plan for the whole block