OraFAQ Forum: Performance Tuning » dbms

Home » RDBMS Server » Performance Tuning » dbms_xplan (Oracle 11.2.0.3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

dbms_xplan [message #644054]

Tue, 27 October 2015 07:02

pointers
Messages: 451
Registered: May 2008

Senior Member

Hi,

I have a query which runs for almost 2 hours against huge tables, I would like to start analysing from the execution plan. As I know explain plan shows only estimated, I thought I would run the query in a session

1. alter session set statistics_level=all
2. run query
3. select * from table(dbms_xplan.display_cursor('sqlid',null,'allstats last');

I would like to follow the above 3 steps, so far so good, but as the query runs for 2 hours (at step 2 above), I will have to wait for 2 hours.
1. Is there any other way to cut waiting time and get the actual execution plan with all the necessary details.
2. As the query returns millions of rows, I had used "set termout off" in a file and ran it --
does this make any issues as far as getting the actual execution plan.

Regards,
Pointers

Report message to a moderator

Re: dbms_xplan [message #644056 is a reply to message #644054]

Tue, 27 October 2015 08:05

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You can search if the query is still in SGA or is in DBA_HIST_SQL_PLAN (if you have the license to), you will get the actual figures.

Report message to a moderator

Re: dbms_xplan [message #644639 is a reply to message #644054]

Wed, 11 November 2015 05:50

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

1. you don't need to input sql_id, if you execute dbms_xplan.display_cursor immediately after your query

2. you can break your query, if it works to long. After that you can display the execution plan with the runtime statistics (in this case you need to input sql_id as argument)

3. alternatively to dbms_xplan you can use SQL Monitoring by call of dbms_sqltune.report_sql_monitor or directly in EM, if you have diagnostic and tuning pack license. Sometimes it is more convenient than dbms_xplan:
- you can to follow the execution of your query and don't need to break it
- in case of PQ or PDML
etc.

Report message to a moderator

Re: dbms_xplan [message #645042 is a reply to message #644639]

Tue, 24 November 2015 05:54

pointers
Messages: 451
Registered: May 2008

Senior Member

Thank you LNossov and Michel

Report message to a moderator

Previous Topic:	Locking modes
Next Topic:	Execution plan cost

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 30 13:35:13 CST 2025