Skip to Main Content

Breadcrumb

Easter

Question and Answer

Chris Saxon

Thanks for the question.

Asked: February 11, 2020 - 6:29 pm UTC

Answered by: Chris Saxon - Last updated: February 12, 2020 - 11:17 am UTC

Category: SQL - Version: 12c

Viewed 1000+ times

You Asked

Hi,
Could you please help to understand:
How can I get the execution plan with all details like A rows E rows, etc when trace only enabled.
I always get basic plan details like rows bytes and cpu% only.

It would be helpful if you could share any link/your blogs to achieve above.

and we said...

The plan from autotrace in SQL*Plus is really an explain plan. So might show the incorrect plan. And excludes the A/E rows columns etc.

You can get these with DBMS_XPlan. To do this:

- set serveroutput off
- Run with the /*+ gather_plan_statistics */ hint or set statistics_level to ALL
- Run your query
- Call DBMS_XPlan.display_cursor

e.g.

set serveroutput off

select /*+ gather_plan_statistics */* from hr.employees;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT                                                                           
SQL_ID  cjmkg0hs1am4y, child number 0                                                        
-------------------------------------                                                        
select /*+ gather_plan_statistics */* from hr.employees                                      
                                                                                             
Plan hash value: 1445457117                                                                  
                                                                                             
-----------------------------------------------------------------------------------------    
| Id  | Operation         | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |    
-----------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT  |           |      1 |        |    107 |00:00:00.01 |       7 |    
|   1 |  TABLE ACCESS FULL| EMPLOYEES |      1 |    107 |    107 |00:00:00.01 |       7 |    
-----------------------------------------------------------------------------------------    


Read more about this (along with other ways to get the plan) at: https://blogs.oracle.com/sql/how-to-create-an-execution-plan

We're not taking reviews currently, so please try again later if you want to add a review.

More to Explore

DBMS_XPLAN

More on PL/SQL routine DBMS_XPLAN here