Monday, July 21, 2014

CBO estimates COST = 0


Still I can’t believe that perhaps somebody noticed this horrible behavior of CBO.  I observed this in 10.2.0.5 and 11.2.0.4 and even in 12.1.0.1, that sometimes the INDEX RANGE SCAN or INDEX UNIQUE SCAN execution plan step has no cost (zero cost) leading the CBO to choose NESTED LOOP over HASH JOIN.

rajesh@PDB1> create table t1 as select * from all_objects; 

Table created. 

rajesh@PDB1> create table t2 as select * from all_objects; 

Table created. 

rajesh@PDB1> alter table t2 add constraint t2_pk
  2  primary key(object_id); 

Table altered. 

rajesh@PDB1> exec dbms_stats.gather_Table_Stats(user,'T1'); 

PL/SQL procedure successfully completed. 

rajesh@PDB1> exec dbms_stats.gather_Table_Stats(user,'T2'); 

PL/SQL procedure successfully completed. 

rajesh@PDB1> set autotrace traceonly explain

rajesh@PDB1> select count(*)
  2  from t1, t2
  3  where t1.object_id = t2.object_id ; 

Execution Plan
----------------------------------------------------------
Plan hash value: 1249090617
-----------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |     1 |    10 |   425   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE     |       |     1 |    10 |            |          |
|   2 |   NESTED LOOPS      |       | 89729 |   876K|   425   (2)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1    | 89729 |   438K|   421   (1)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN| T2_PK |     1 |     5 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------- 

Predicate Information (identified by operation id):
--------------------------------------------------- 

   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") 

rajesh@PDB1> set autotrace off
rajesh@PDB1> 

You can see there is no cost associated to step id 4. The CBO then thinks that doing and index unique scan 89729 times costs nothing.

When hinted for Hash join, CBO estimates a cost of 619, which is correct. Of course 425 is better than 619, then the CBO goes incorrectly for nested loop. 

rajesh@PDB1> set autotrace traceonly explain
rajesh@PDB1>
rajesh@PDB1> select /*+ use_hash(t1,t2) */ count(*)
  2  from t1, t2
  3  where t1.object_id = t2.object_id ; 

Execution Plan
----------------------------------------------------------
Plan hash value: 2219068312
----------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |     1 |    10 |       |   619   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE        |       |     1 |    10 |       |            |          |
|*  2 |   HASH JOIN            |       | 89729 |   876K|  1496K|   619   (1)| 00:00:01 |
|   3 |    INDEX FAST FULL SCAN| T2_PK | 89730 |   438K|       |    52   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL   | T1    | 89729 |   438K|       |   421   (1)| 00:00:01 |
---------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id):
--------------------------------------------------- 

   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") 

rajesh@PDB1> set autotrace off 

But in reality Hash joins is better than Nested loops. 

rajesh@PDB1> set autotrace traceonly explain statistics
rajesh@PDB1>
rajesh@PDB1> select count(*)
  2  from t1, t2
  3  where t1.object_id = t2.object_id ;
1 row selected. 

Execution Plan
----------------------------------------------------------
Plan hash value: 1249090617 

-----------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |     1 |    10 |   425   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE     |       |     1 |    10 |            |          |
|   2 |   NESTED LOOPS      |       | 89729 |   876K|   425   (2)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1    | 89729 |   438K|   421   (1)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN| T2_PK |     1 |     5 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       2881  consistent gets
       1510  physical reads
          0  redo size
        544  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed 

rajesh@PDB1>
rajesh@PDB1> select /*+ use_hash(t1,t2) */ count(*)
  2  from t1, t2
  3  where t1.object_id = t2.object_id ; 

1 row selected. 

Execution Plan
----------------------------------------------------------
Plan hash value: 2219068312
-------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |     1 |    10 |       |   619   (1)| 00:00:0
|   1 |  SORT AGGREGATE        |       |     1 |    10 |       |            |
|*  2 |   HASH JOIN            |       | 89729 |   876K|  1496K|   619   (1)| 00:00:0
|   3 |    INDEX FAST FULL SCAN| T2_PK | 89730 |   438K|       |    52   (0)| 00:00:0
|   4 |    TABLE ACCESS FULL   | T1    | 89729 |   438K|       |   421   (1)| 00:00:0
------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id):
--------------------------------------------------- 

   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1706  consistent gets
       1510  physical reads
          0  redo size
        544  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed 

rajesh@PDB1>
rajesh@PDB1> set autotrace off
rajesh@PDB1> 

Primary key with non-unique indexes :  Still the same


rajesh@PDB1> alter table t2 drop constraint t2_pk; 

Table altered.

rajesh@PDB1> alter table t2 add constraint t2_pk
  2  primary key(object_id)
  3  using index ( create index t2_ind on t2(object_id) ); 

Table altered. 

rajesh@PDB1>
rajesh@PDB1> set autotrace traceonly explain statistics
rajesh@PDB1>
rajesh@PDB1> select count(*)
  2  from t1, t2
  3  where t1.object_id = t2.object_id ; 

1 row selected. 

Execution Plan
----------------------------------------------------------
Plan hash value: 1573195610
------------------------------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |     1 |    10 |   425   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE     |        |     1 |    10 |            |          |
|   2 |   NESTED LOOPS      |        | 89729 |   876K|   425   (2)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1     | 89729 |   438K|   421   (1)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN | T2_IND |     1 |     5 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
--------------------------------------------------- 

   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2926  consistent gets
       1509  physical reads
          0  redo size
        544  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed 

rajesh@PDB1>
rajesh@PDB1> select /*+ use_hash(t1,t2) */ count(*)
  2  from t1, t2
  3  where t1.object_id = t2.object_id ; 

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1404746403
-----------------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |        |     1 |    10 |       |   623   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE        |        |     1 |    10 |       |            |          |
|*  2 |   HASH JOIN            |        | 89729 |   876K|  1496K|   623   (1)| 00:00:01 |
|   3 |    INDEX FAST FULL SCAN| T2_IND | 89730 |   438K|       |    56   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL   | T1     | 89729 |   438K|       |   421   (1)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
  

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1719  consistent gets
       1509  physical reads
          0  redo size
        544  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

rajesh@PDB1>
rajesh@PDB1> set autotrace off 

Only with Unique index (no-constraint) : still the same


rajesh@PDB1> alter table t2 drop constraint t2_pk;
Table altered. 

rajesh@PDB1> drop index t2_ind; 

Index dropped. 

rajesh@PDB1> create unique index t2_ind on t2(object_id); 

Index created. 

rajesh@PDB1>
rajesh@PDB1> set autotrace traceonly explain statistics
rajesh@PDB1>
rajesh@PDB1> select count(*)
  2  from t1, t2
  3  where t1.object_id = t2.object_id ; 

1 row selected. 

Execution Plan
----------------------------------------------------------
Plan hash value: 2560841438 
------------------------------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |     1 |    10 |   425   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE     |        |     1 |    10 |            |          |
|   2 |   NESTED LOOPS      |        | 89729 |   876K|   425   (2)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1     | 89729 |   438K|   421   (1)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN| T2_IND |     1 |     5 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------ 

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2881  consistent gets
       1509  physical reads
          0  redo size
        544  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed 

rajesh@PDB1>
rajesh@PDB1> select /*+ use_hash(t1,t2) */ count(*)
  2  from t1, t2
  3  where t1.object_id = t2.object_id ;
1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1404746403
-----------------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |        |     1 |    10 |       |   619   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE        |        |     1 |    10 |       |            |          |
|*  2 |   HASH JOIN            |        | 89729 |   876K|  1496K|   619   (1)| 00:00:01 |
|   3 |    INDEX FAST FULL SCAN| T2_IND | 89730 |   438K|       |    52   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL   | T1     | 89729 |   438K|       |   421   (1)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------- 

   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1706  consistent gets
       1509  physical reads
          0  redo size
        544  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed 

rajesh@PDB1>
rajesh@PDB1> set autotrace off 

With non-unique index and unique constraint : still the same


rajesh@PDB1> drop index t2_ind; 

Index dropped. 

rajesh@PDB1> create index t2_ind on t2(object_id);
Index created. 

rajesh@PDB1> alter table t2 add constraint t2_unq unique(object_id)
  2  using index t2_ind ; 

Table altered. 

rajesh@PDB1>
rajesh@PDB1> set autotrace traceonly explain statistics
rajesh@PDB1>
rajesh@PDB1> select count(*)
  2  from t1, t2
  3  where t1.object_id = t2.object_id ; 

1 row selected. 

Execution Plan
----------------------------------------------------------
Plan hash value: 1573195610
------------------------------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |     1 |    10 |   425   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE     |        |     1 |    10 |            |          |
|   2 |   NESTED LOOPS      |        | 89729 |   876K|   425   (2)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1     | 89729 |   438K|   421   (1)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN | T2_IND |     1 |     5 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2926  consistent gets
       1509  physical reads
          0  redo size
        544  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

rajesh@PDB1>
rajesh@PDB1> select /*+ use_hash(t1,t2) */ count(*)
  2  from t1, t2
  3  where t1.object_id = t2.object_id ; 

1 row selected. 

Execution Plan
----------------------------------------------------------
Plan hash value: 1404746403
-----------------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |        |     1 |    10 |       |   623   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE        |        |     1 |    10 |       |            |          |
|*  2 |   HASH JOIN            |        | 89729 |   876K|  1496K|   623   (1)| 00:00:01 |
|   3 |    INDEX FAST FULL SCAN| T2_IND | 89730 |   438K|       |    56   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL   | T1     | 89729 |   438K|       |   421   (1)| 00:00:01 |
----------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id):

--------------------------------------------------- 

   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1719  consistent gets
       1509  physical reads
          0  redo size
        544  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed 

rajesh@PDB1>
rajesh@PDB1> set autotrace off 

Unique constraint as deferrable: looks fine


rajesh@PDB1>
rajesh@PDB1> alter table t2 drop constraint t2_unq; 

Table altered.

rajesh@PDB1> drop index t2_ind;

Index dropped. 

rajesh@PDB1> alter table t2 add constraint t2_unq
  2  unique(object_id) deferrable ; 

Table altered. 

rajesh@PDB1>
rajesh@PDB1> set autotrace traceonly explain statistics
rajesh@PDB1>
rajesh@PDB1> select count(*)
  2  from t1, t2
  3  where t1.object_id = t2.object_id ; 

1 row selected. 

Execution Plan
----------------------------------------------------------
Plan hash value: 3356950652
-----------------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |        |     1 |    10 |       |   623   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE        |        |     1 |    10 |       |            |          |
|*  2 |   HASH JOIN            |        | 89729 |   876K|  1496K|   623   (1)| 00:00:01 |
|   3 |    INDEX FAST FULL SCAN| T2_UNQ | 89730 |   438K|       |    56   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL   | T1     | 89729 |   438K|       |   421   (1)| 00:00:01 |
----------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1719  consistent gets
       1509  physical reads
          0  redo size
        544  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

rajesh@PDB1>
rajesh@PDB1> select /*+ use_hash(t1,t2) */ count(*)
  2  from t1, t2
  3  where t1.object_id = t2.object_id ; 

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3356950652
-----------------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |        |     1 |    10 |       |   623   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE        |        |     1 |    10 |       |            |          |
|*  2 |   HASH JOIN            |        | 89729 |   876K|  1496K|   623   (1)| 00:00:01 |
|   3 |    INDEX FAST FULL SCAN| T2_UNQ | 89730 |   438K|       |    56   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL   | T1     | 89729 |   438K|       |   421   (1)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1719  consistent gets
       1509  physical reads
          0  redo size
        544  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

rajesh@PDB1>
rajesh@PDB1> set autotrace off
rajesh@PDB1>


Primary key constraint as deferrable: looks fine


rajesh@PDB1> alter table t2 drop constraint t2_unq;

Table altered.

rajesh@PDB1> alter table t2 add constraint t2_pk
  2  primary key(object_id) deferrable;

Table altered.

rajesh@PDB1>
rajesh@PDB1> set autotrace traceonly explain statistics
rajesh@PDB1>
rajesh@PDB1> select count(*)
  2  from t1, t2
  3  where t1.object_id = t2.object_id ;

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3356950652
-----------------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |        |     1 |    10 |       |   623   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE        |        |     1 |    10 |       |            |          |
|*  2 |   HASH JOIN            |        | 89729 |   876K|  1496K|   623   (1)| 00:00:01 |
|   3 |    INDEX FAST FULL SCAN| T2_UNQ | 89730 |   438K|       |    56   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL   | T1     | 89729 |   438K|       |   421   (1)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1719  consistent gets
       1509  physical reads
          0  redo size
        544  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

rajesh@PDB1>
rajesh@PDB1> select /*+ use_hash(t1,t2) */ count(*)
  2  from t1, t2
  3  where t1.object_id = t2.object_id ;

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3356950652
-----------------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |        |     1 |    10 |       |   623   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE        |        |     1 |    10 |       |            |          |
|*  2 |   HASH JOIN            |        | 89729 |   876K|  1496K|   623   (1)| 00:00:01 |
|   3 |    INDEX FAST FULL SCAN| T2_UNQ | 89730 |   438K|       |    56   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL   | T1     | 89729 |   438K|       |   421   (1)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1719  consistent gets
       1509  physical reads
          0  redo size
        544  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

rajesh@PDB1>
rajesh@PDB1> set autotrace off

To sum up: CBO assumes zero cost for accessing an index (by UNIQUE SCAN or RANGE SCAN) when uniqueness on the same column is enforced (by non-deferrable pk/unique constraint or unique index).