Mohamed Houri’s Oracle Notes

October 10, 2011

DBMS_XPLAN : Starts in action

Filed under: explain plan — hourim @ 12:50 pm

Very often, if not always, I am advocating the use of

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

in order to  

  1.  Get the real explain plan and not the approximate onet
  2.  Judge about the Estimations the CBO is doing

And today I want to add

            3.  See if an operation has been started or not by the CBO

Here below a little demonstration of point 3.

mhouri > select empno, ename
  2  from emp
  3  where empno is null;
no rows selected

mhouri > select * from table(dbms_xplan.display_cursor);
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time    |    
|   0 | SELECT STATEMENT   |      |       |       |     1 (100)|         |       
|*  1 |  FILTER            |      |       |       |            |         |      
|   2 |   TABLE ACCESS FULL| EMP  |    15 |   150 |     5   (0)| 00:00:01|
Predicate Information (identified by operation(id):
   1 - filter(NULL IS NOT NULL)                                                                                         
19 rows selected.

It’s a pity to apply a FILTER to operation 2 (TABLE ACCESS FULL) when you know that this filter will discard all the rows because NULL is never NOT NULL. So why the CBO execute a TABLE ACCESS FULL on EMP when it knows that the filter will discard all returned rows.

But wait a moment. Are you sure that the CBO did execute operation 2? Let’s then demonstrate that:

mhouri > select /*+ gather_plan_statistics */ empno, ename
  2  from emp
  3  where empno is null;
no rows selected

mhouri > select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time  |
|*  1 |  FILTER            |      |      1 |        |      0 |00:00:00.01|
|   2 |   TABLE ACCESS FULL| EMP  |      0 |     15 |      0 |00:00:00.01|
Predicate Information (identified by operation id):
   1 - filter(NULL IS NOT NULL)
19 rows selected.

Ah ha ha!!! When we look at the column Starts for operation 2, you will notice that it equals 0 which means that this operation has never been started by the CBO. This is confirmed also by the information A-Rows of the FILTER operation which equals 0 meaning that the filter has been started only one time and applied on 0 rows

 Bottom line here, is that using the ALLSTATS LAST property of the DBMS_XPLAN package can reveals valuable information one can use to understand the work done by the CBO

Create a free website or blog at

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog


Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)