Automatic Indexing - Part XX (stale stats)

In the part I of this series, we saw how Automatic indexing (AI) will not create an index if there are stale or missing statistics on the dependent objects, In the Part II we saw how these statements effectively become blacklisted and when segment statistics are subsequently collected, AI process will not create viable auto index when the SQL statements are run.
 
So how do we get AI to kick in and create necessary indexes on these problematic SQL’s? all we need is to run a NEW sql statement that hasn’t been backlisted that will create the necessary index to be created. An easy way to do this is to include a new comment within the pervious SQL to give the SQL a new signature.
 
If we now run the following “new” SQL statement 
 
ai_demo@PDB19> select owner,index_name,indexing,auto,
  2         status,visibility,clustering_factor,leaf_blocks
  3  from all_indexes
  4  where table_name ='DEMO020A'
  5  and owner ='AI_DEMO';
 
OWNER      INDEX_NAME                INDEXIN AUT STATUS   VISIBILIT CLUSTERING_FACTOR LEAF_BLOCKS
---------- ------------------------- ------- --- -------- --------- ----------------- -----------
AI_DEMO    SYS_AI_6z61w0j7jqqc0      FULL    YES UNUSABLE INVISIBLE           4149312       23058
 
ai_demo@PDB19>
ai_demo@PDB19> select index_name,column_name,column_position
  2  from user_ind_columns
  3  where table_name ='DEMO020A'
  4  order by 1,3;
 
INDEX_NAME                COLUMN_NAM COLUMN_POSITION
------------------------- ---------- ---------------
SYS_AI_6z61w0j7jqqc0      X2                       1
 
ai_demo@PDB19>
ai_demo@PDB19> set autotrace traceonly exp statistics
ai_demo@PDB19> select /* new */ * from demo020a where x2 = 17;
 
10 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1490821407
 
--------------------------------------------------------------------------------------
| Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |          |    10 |   260 | 11655   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL| DEMO020A |    10 |   260 | 11655   (1)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - storage("X2"=17)
       filter("X2"=17)
 
 
Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
      42754  consistent gets
          0  physical reads
          0  redo size
        612  bytes sent via SQL*Net to client
        546  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed
 
 
If we now wait to see what the new AI task make things of changes..
 
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
 Index candidates                              : 2                          
 Indexes created (visible / invisible)         : 1 (1 / 0)                  
 Space used (visible / invisible)              : 142.61 MB (142.61 MB / 0 B)
 Indexes dropped                               : 0                          
 SQL statements verified                       : 1                          
 SQL statements improved (improvement factor)  : 1 (21376x)                 
 SQL plan baselines created                    : 0                          
 Overall improvement factor                    : 21376x                     
-------------------------------------------------------------------------------
 
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
 Unused indexes    : 0  
 Space used        : 0 B
 Unusable indexes  : 0  
-------------------------------------------------------------------------------
 
INDEX DETAILS
-------------------------------------------------------------------------------
1. The following indexes were created:
*: invisible
-------------------------------------------------------------------------------
-------------------------------------------------------------------------
| Owner   | Table    | Index                | Key | Type   | Properties |
-------------------------------------------------------------------------
| AI_DEMO | DEMO020A | SYS_AI_6z61w0j7jqqc0 | X2  | B-TREE | NONE       |
-------------------------------------------------------------------------
-------------------------------------------------------------------------------
 
VERIFICATION DETAILS
-------------------------------------------------------------------------------
1. The performance of the following statements improved:
-------------------------------------------------------------------------------
 Parsing Schema Name  : AI_DEMO                                                
 SQL ID               : 5s8xsm61w2x7u                                          
 SQL Text             : select /* new */ * from demo020a where x2 = 17         
 Improvement Factor   : 21376x                                                 
 
Execution Statistics:
-----------------------------
                    Original Plan                 Auto Index Plan              
                    ----------------------------  ----------------------------
 Elapsed Time (s):  735335                        2334                        
 CPU Time (s):      696137                        1551                        
 Buffer Gets:       128256                        13                          
 Optimizer Cost:    11655                         14                          
 Disk Reads:        0                             2                           
 Direct Writes:     0                             0                           
 Rows Processed:    30                            10                          
 Executions:        3                             1                           
 
 
PLANS SECTION
---------------------------------------------------------------------------------------------
 
- Original
-----------------------------
 Plan Hash Value  : 1490821407
 
---------------------------------------------------------------------------------
| Id | Operation                   | Name     | Rows | Bytes | Cost  | Time     |
---------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |          |      |       | 11655 |          |
|  1 |   TABLE ACCESS STORAGE FULL | DEMO020A |   10 |   260 | 11655 | 00:00:01 |
---------------------------------------------------------------------------------
 
- With Auto Indexes
-----------------------------
 Plan Hash Value  : 672779957
 
-------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                 | Rows | Bytes | Cost | Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                      |   10 |   260 |   14 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | DEMO020A             |   10 |   260 |   14 | 00:00:01 |
| * 2 |    INDEX RANGE SCAN                   | SYS_AI_6z61w0j7jqqc0 |   10 |       |    3 | 00:00:01 |
-------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("X2"=17)
 
 
Notes
-----
- Dynamic sampling used for this statement ( level = 11 )
 
 
If we look at the status of this index.
 
ai_demo@PDB19> select owner,index_name,indexing,auto,
  2         status,visibility,clustering_factor,leaf_blocks
  3  from all_indexes
  4  where table_name ='DEMO020A'
  5  and owner ='AI_DEMO';
 
OWNER      INDEX_NAME                INDEXIN AUT STATUS   VISIBILIT CLUSTERING_FACTOR LEAF_BLOCKS
---------- ------------------------- ------- --- -------- --------- ----------------- -----------
AI_DEMO    SYS_AI_6z61w0j7jqqc0      FULL    YES VALID    VISIBLE            10000000       16891
 
ai_demo@PDB19> select index_name,column_name,column_position
  2  from user_ind_columns
  3  where table_name ='DEMO020A'
  4  order by 1,3;
 
INDEX_NAME                COLUMN_NAM COLUMN_POSITION
------------------------- ---------- ---------------
SYS_AI_6z61w0j7jqqc0      X2                       1
 
We can see the index is now both VISIBLE and VALID, as such AI can now potentially be used by any SQL including pervious problematic query
 
So with this viable index now in place, if we re-run the problematic query
 
ai_demo@PDB19> select * from demo020a where x2 = 17;
 
10 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 672779957
 
------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |    10 |   260 |    13   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DEMO020A             |    10 |   260 |    13   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | SYS_AI_6z61w0j7jqqc0 |    10 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("X2"=17)
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
        764  bytes sent via SQL*Net to client
        546  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed
 
 
We can see that finally, the SQL uses the new AI and indeed much more efficient as a result, with 14 logical IO required (whereas previously it was 42754 logical IO’s)