Automatic Indexing - Part XVI

The CBO has difficulties in recognizing (by default) that some combinations of column values are far more common than other combinations, resulting in incorrect cardinality estimates and resulting in poor execution plans.
 
I will start by a simple demo having two column X1 and X2
 
ai_demo@PDB19> create table DEMO018a
  2  nologging as
  3  select mod(rownum,100)+1 as x1,
  4         mod(rownum,100)+1 as x2,
  5         rownum as id, substr(a.object_name,1,30) as object_name
  6  from all_objects a ,
  7         all_objects b, all_users c
  8  where rownum <= 10000000;
 
Table created.
 
Both column X1 and X2 each have 100 distinct values, so that the possible combinations of data from both column is 100 x 100 = 10000, however the value of X1 and X2 are always the same so there is in fact only 100 distinct combinations of data because of this inherent relationship between columns.
 
If we run the following query for the combination of data that exists:
 
ai_demo@PDB19> select x1,x2,object_name
  2  from DEMO018a
  3  where x1 = 17
  4  and x2 = 17;
 
100000 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3250860338
 
--------------------------------------------------------------------------------------
| Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |          |  1000 | 30000 | 15208   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL| DEMO018A |  1000 | 30000 | 15208   (1)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - storage("X1"=17 AND "X2"=17)
       filter("X1"=17 AND "X2"=17)
 
 
Statistics
----------------------------------------------------------
         32  recursive calls
         26  db block gets
      55549  consistent gets
      55525  physical reads
       4628  redo size
    2835431  bytes sent via SQL*Net to client
       7872  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed
 
 
Without an index the CBO has no choice but to use a FTS, however the interesting thing to note is how the estimated cardinality is way wrong with the actuals – 100,000 rows returned by only 1000 rows got estimated, the CBO incorrectly assumes that only 1/10000 th of the data is being returned and not the actual the 1/100 (1%)
 
Optimizer estimates was = number of rows X (1/NDV of X1 column) X (1/NDV of X2 column)
                                                = 10000000 x ( 1/100 ) * (1/100 )  = 1000
 
 
If we run for a query on a combination of data that doesn’t exists
 
ai_demo@PDB19> select x1,x2,object_name
  2  from DEMO018a
  3  where x1 = 1
  4  and x2 = 2;
 
no rows selected
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3250860338
 
--------------------------------------------------------------------------------------
| Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |          |  1000 | 30000 | 15208   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL| DEMO018A |  1000 | 30000 | 15208   (1)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - storage("X1"=1 AND "X2"=2)
       filter("X1"=1 AND "X2"=2)
 
 
Statistics
----------------------------------------------------------
          2  recursive calls
          0  db block gets
      55527  consistent gets
      55520  physical reads
          0  redo size
        369  bytes sent via SQL*Net to client
        535  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
 
 
The CBO still estimates that 1000 rows are to be returned, However with no rows returned, an index would be a much more better alternative that the current full table scan in this case.
 
Lets now wait and see what the AI process makes of all this
 
 
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
 Index candidates                              : 2
 Indexes created (visible / invisible)         : 2 (2 / 0)
 Space used (visible / invisible)              : 268.44 MB (268.44 MB / 0 B)
 Indexes dropped                               : 0
 SQL statements verified                       : 4
 SQL statements improved (improvement factor)  : 2 (132665x)
 SQL plan baselines created                    : 0
 Overall improvement factor                    : 2.3x
-------------------------------------------------------------------------------
 
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
 Unused indexes    : 0
 Space used        : 0 B
 Unusable indexes  : 0
-------------------------------------------------------------------------------
INDEX DETAILS
-------------------------------------------------------------------------------
1. The following indexes were created:
-------------------------------------------------------------------------------
---------------------------------------------------------------------------
| Owner   | Table    | Index                | Key   | Type   | Properties |
---------------------------------------------------------------------------
| AI_DEMO | DEMO018  | SYS_AI_22nyp7ufnm6j4 | X1,X2 | B-TREE | NONE       |
| AI_DEMO | DEMO018A | SYS_AI_gawwp6r5fuzfr | X1,X2 | B-TREE | NONE       |
---------------------------------------------------------------------------
-------------------------------------------------------------------------------
 
So Oracle does indeed created an AI on X1,X2 columns. However notice that only 1 statement has been verified and not the above two statements that we executed during the previous period.
 
VERIFICATION DETAILS
-------------------------------------------------------------------------------
1. The performance of the following statements improved:
-------------------------------------------------------------------------------
 Parsing Schema Name  : AI_DEMO
 SQL ID               : 2h9jguqu62nz4
 SQL Text             : select x1,x2,object_name from DEMO018a where x1 = 1 and
                      x2 = 2
 Improvement Factor   : 55524x
 
Execution Statistics:
-----------------------------
                    Original Plan                 Auto Index Plan
                    ----------------------------  ----------------------------
 Elapsed Time (s):  81939                         1249
 CPU Time (s):      48057                         950
 Buffer Gets:       166575                        3
 Optimizer Cost:    15208                         4
 Disk Reads:        166560                        2
 Direct Writes:     0                             0
 Rows Processed:    0                             0
 Executions:        3                             1
 
 
So only the SQL that returned 0 rows has been reported, as expected it runs much more efficient with an index than via the previous full table scan with an improvement factor of 55524x
 
 
 
PLANS SECTION
---------------------------------------------------------------------------------------------
 
- Original
-----------------------------
 Plan Hash Value  : 3250860338
 
---------------------------------------------------------------------------------
| Id | Operation                   | Name     | Rows | Bytes | Cost  | Time     |
---------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |          |      |       | 15208 |          |
|  1 |   TABLE ACCESS STORAGE FULL | DEMO018A | 1000 | 30000 | 15208 | 00:00:01 |
---------------------------------------------------------------------------------
 
- With Auto Indexes
-----------------------------
 Plan Hash Value  : 2414111795
 
-------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                 | Rows | Bytes | Cost | Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                      |    5 |   150 |    4 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | DEMO018A             |    5 |   150 |    4 | 00:00:01 |
| * 2 |    INDEX RANGE SCAN                   | SYS_AI_gawwp6r5fuzfr |    1 |       |    3 | 00:00:01 |
-------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("X1"=1 AND "X2"=2)
 
 
Notes
-----
- Dynamic sampling used for this statement ( level = 11 )
 
 
If we look at the comparison between plans, the new plan of course uses the newly created automatic index. The critical point to notice here is the estimated cardinality for the new plan is much closer to the actuals now.
 
The reason why it is much more accurate is because the AI process session uses the new dynamic sampling levels (level=11), this enables the CBO to sample data on the fly and determine much more accurate cardinality estimate than by the default, where the dynamic sampling level = 2
 
This also explains why the other statement which returned many more rows was not “verified”, actually it was but because the AI process with dynamic sampling set to 11 correctly identified that too many rows were being returned to make any new index viable, this statement did not cause the new index to be kept.
 
So, it was only the SQL statement that returned no rows that resulted in the newly created AI. The other statement was correctly determined by the AI process to run worse with the new index and so determined that the CBO would simply ignore the index if created.
 
But this assumption of CBO ignoring the index is not correct, which we will see shortly.
 
If we look at the newly created AI
 
ai_demo@PDB19> select index_name,column_name,column_position
  2  from user_ind_columns
  3  where table_name ='DEMO018A'
  4  order by 1,3;
 
INDEX_NAME           COLUMN_NAM COLUMN_POSITION
-------------------- ---------- ---------------
SYS_AI_gawwp6r5fuzfr X1                       1
SYS_AI_gawwp6r5fuzfr X2                       2
 
ai_demo@PDB19> select owner,index_name,indexing,auto,
  2          status,visibility,clustering_factor, leaf_blocks
  3  from all_indexes
  4  where table_name ='DEMO018A'
  5  and owner ='AI_DEMO';
 
OWNER      INDEX_NAME                INDEXIN AUT STATUS   VISIBILIT CLUSTERING_FACTOR LEAF_BLOCKS
---------- ------------------------- ------- --- -------- --------- ----------------- -----------
AI_DEMO    SYS_AI_gawwp6r5fuzfr      FULL    YES VALID    VISIBLE             5552000       15362
 
 
We can see that the index is both visible and valid and can potentially be used now by any subsequent SQL statement.
 
Now the important thing to note is that the default for most session in a database is for dynamic sampling set to 2 and optimizer_adaptive_statistics = false, importantly this is also the case with Oracle cloud services.
 
ai_demo@PDB19> show parameter sampling
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     2
ai_demo@PDB19> show parameter optimizer_adaptive_statistics
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_adaptive_statistics        boolean     FALSE
 
 
So this is different to the setting for the AI process. In a standard session, the CBO will not have the capability to accurately determine the correct cardinality estimates as we saw previously.
 
If we now re-run the sql that returns no rows
 
ai_demo@PDB19> select x1,x2,object_name
  2  from DEMO018a
  3  where x1 = 1
  4  and x2 = 2;
 
no rows selected
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2414111795
 
------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |  1000 | 30000 |   560   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DEMO018A             |  1000 | 30000 |   560   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | SYS_AI_gawwp6r5fuzfr |  1000 |       |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("X1"=1 AND "X2"=2)
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        369  bytes sent via SQL*Net to client
        535  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
 
The execution uses the new index, but still the optimizer estimates the cardinality as 1000 rows to be returned, that’s sufficiently few for the index to be costed the cheaper option.
 
When we rerun the sql that return many rows
 
ai_demo@PDB19> select x1,x2,object_name
  2  from DEMO018a
  3  where x1 = 17
  4  and x2 = 17;
 
100000 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2414111795
 
------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |  1000 | 30000 |   560   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DEMO018A             |  1000 | 30000 |   560   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | SYS_AI_gawwp6r5fuzfr |  1000 |       |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("X1"=17 AND "X2"=17)
 
 
Statistics
----------------------------------------------------------
          3  recursive calls
          0  db block gets
      56203  consistent gets
          0  physical reads
          0  redo size
    2835214  bytes sent via SQL*Net to client
       7872  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed
 
 
It also uses the AI, because it still thinks that only 1000 rows to be returned and just like the pervious sql statement it is determined to be a cheaper option. But in this case it is not really the cheaper option, having to  read the table potentially piecemeal at a time via the index, rather than more efficiently with few large multiblock IOs via full table scans.
 
This is not really how AI is designed to work. It’s meant to protect us from making SQL statement regress in performance, but because there is difference in how a normal session and the AI process determine the cost of execution plan, these scenarios can occur.
 
In the next blog will see about how to address this specific scenario and then look at an example of how AI is really meant to work via the use of automated baselines…