DBMS_XPLAN : Starts in action

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