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
- Get the real explain plan and not the approximate onet
- 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
