Automatic Indexing - Part XIX (stale stats)

In the previous post, we discussed how having stale stats, usually a bad idea, is especially problematic with regard auto indexing (AI) as it usually result in no AI being created.
 
If we were to now collect the missing statistics
 
ai_demo@PDB19> exec dbms_stats.gather_table_stats(user,'DEMO020A');
 
PL/SQL procedure successfully completed.
 
ai_demo@PDB19> select num_rows,blocks,last_analyzed
  2  from user_tab_statistics
  3  where table_name ='DEMO020A';
 
  NUM_ROWS     BLOCKS LAST_ANALYZED
---------- ---------- -----------------------
  10000000      42737 21-JUN-2022 09:46:57 am
 
ai_demo@PDB19> select column_name, num_distinct, density, histogram, last_analyzed
  2  from user_tab_cols
  3  where table_name ='DEMO020A';
 
COLUMN_NAM NUM_DISTINCT    DENSITY HISTOGRAM       LAST_ANALYZED
---------- ------------ ---------- --------------- -----------------------
X1              9914368 1.0086E-07 NONE            21-JUN-2022 09:46:57 am
X2              1001984 9.9802E-07 NONE            21-JUN-2022 09:46:57 am
X3                    1          1 NONE            21-JUN-2022 09:46:57 am
 
If we now repeatably re-run the problematic query many times.
 
ai_demo@PDB19> select * 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
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      42752  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
 
 
The CBO was forced to use Full table scan as no AI was created yet. If we wait for the next AI reporting period.
 
REPORT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
 Activity start               : 21-JUN-2022 09:07:58
 Activity end                 : 21-JUN-2022 10:07:58
 Executions completed         : 0
 Executions interrupted       : 0
 Executions with fatal error  : 0
-------------------------------------------------------------------------------
 
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
 Index candidates            : 0
 Indexes created             : 0
 Space used                  : 0 B
 Indexes dropped             : 0
 SQL statements verified     : 0
 SQL statements improved     : 0
 SQL plan baselines created  : 0
 Overall improvement factor  : 0x
-------------------------------------------------------------------------------
 
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
 Unused indexes    : 0
 Space used        : 0 B
 Unusable indexes  : 0
-------------------------------------------------------------------------------
 
ERRORS
---------------------------------------------------------------------------------------------
No errors found.
---------------------------------------------------------------------------------------------
 
We notice the AI process has nothing to report, even though the problematic query is repeatedly executed, the SQL is now effectively on the blacklist and not reconsidered by AI process.
 
If we look at the index details on this table.
 
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
 
So the AI is still unusable and invisible and cannot be used by the CBO.
 
If we simply rerun the same queries again when the dependent object statistics were stale, any SQL is just ignored by AI process.
 
In the next post, we will see how to make this query to fully use the AI and improve the performance.