Skip to Main Content

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Chris Saxon

Thanks for the question.

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

Last updated: February 12, 2020 - 11:17 am UTC

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 Chris 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 comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions