You have a query that takes too long and you want help to analyze the execution plan?
Then you need to get it with relevant information, and correctly formatted. Autotrace is not a good option as it does not bind the variables in the same way as your application. Explain plan only shows estimations, but if we have a performance issue, this probably means that the estimation is wrong. I prefer SQL Monitoring when we have Tuning Pack, or the cursor plan with row-source statistics in all other cases. If you post it in a forum, don't forget to keep it formatted or it's impossible to read.
Here are the two ways I prefer to get an execution plan, depending on whether you have the tuning pack licence or not.
Method 1 - Without Tuning Pack
I set the sqlplus environment to nicely spool to text file and set the STATISTICS_LEVEL to ALL in order to gather plan execution statistics:
set pagesize 10000 linesize 300 trimspool on serveroutput off
alter session set statistics_level=all;
Then I execute the query. Don't forget to set the current_schema to the user that executes the statement, and to bind variables:
-- set the schema
alter session set current_schema=SCOTT;
-- define variables
variable minimum_salary number
-- bind values
exec :minimum_salary := 3000
-- execute the query
SELECT * FROM DEPT JOIN EMP USING(DEPTNO) WHERE SAL>:minimum_salary;
Finally I get the execution plan to a text file:
select * from table(dbms_xplan.display_cursor(format=>'allstats last +outline +peeked_binds +cost'));
The plan will have both the estimations (E-Rows) and the actual number of rows (A-Rows) from the last execution. Note that if it is a parallel query statement, you must omit the 'last' in the format or you will have statistics only for the coordinator process.
Method 2 - With Tuning Pack
When you have tuning pack, you have access to the great SQL monitoring feature.