Home » RDBMS Server » Performance Tuning » dbms_xplan (Oracle 11.2.0.3)
dbms_xplan [message #644054] Tue, 27 October 2015 07:02 Go to next message
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
Re: dbms_xplan [message #644056 is a reply to message #644054] Tue, 27 October 2015 08:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: dbms_xplan [message #644639 is a reply to message #644054] Wed, 11 November 2015 05:50 Go to previous messageGo to next message
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.

Re: dbms_xplan [message #645042 is a reply to message #644639] Tue, 24 November 2015 05:54 Go to previous message
pointers
Messages: 451
Registered: May 2008
Senior Member
Thank you LNossov and Michel
Previous Topic: Locking modes
Next Topic: Execution plan cost
Goto Forum:
  


Current Time: Tue Mar 19 03:23:17 CDT 2024