Automatic Indexing - Part XVIII (stale stats)

The golden rule when working with automatic indexing (AI) is that things don’t work properly if there are stale/missing statistics on the dependent objects, stale stats can of course be problematic but they can be particularly troublesome when dealing with AI.
 
In Oracle autonomous database environments, this issue is addressed somewhat by a new feature called High Frequency statistics collection. Which helps to automatically collect stale statistics on a regular basis. However in on-prem exadata environments, where this can be more easily be turned off or collected less frequently, it is a potential issue for consideration.  
 
The following demo was run on Oracle 19c (19.14) from Exadata cloud@customer platform.
 
ai_demo@PDB19> select dbms_stats.get_prefs('AUTO_TASK_STATUS') task_status,
  2         dbms_stats.get_prefs('AUTO_TASK_MAX_RUN_TIME') max_run_time,
  3         dbms_stats.get_prefs('AUTO_TASK_INTERVAL') task_interval
  4  from dual;
 
TASK_STATU MAX_RUN_TI TASK_INTER
---------- ---------- ----------
OFF        3600       900
 
ai_demo@PDB19> create table demo020a( x1 number, x2 number, x3 varchar2(80) );
 
Table created.
 
ai_demo@PDB19> insert into demo020a( x1, x2, x3 )
  2  select  rownum, mod(rownum, 1000000) ,
  3         substr(a.object_name,1,70)
  4  from all_objects a, all_objects b,
  5                all_users
  6  where rownum <= 10000000;
 
10000000 rows created.
 
ai_demo@PDB19> commit;
 
Commit complete.
 
Importantly we haven’t collected statistics on this newly populated table.
 
ai_demo@PDB19> select num_rows,blocks,last_analyzed
  2  from user_tab_statistics
  3  where table_name ='DEMO020A';
 
  NUM_ROWS     BLOCKS LAST_ANALYZED
---------- ---------- -----------------------
 
 
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                                 NONE
X2                                 NONE
X3                                 NONE
 
 
If we run the following query a number of times, while there are no statistics on the table.
 
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          |          |   470 | 31960 | 11648   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL| DEMO020A |   470 | 31960 | 11648   (1)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - storage("X2"=17)
       filter("X2"=17)
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
 
 
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 has no choice but to use a Full table scan, IF we now wait for the next AI process to kick in and if there is still no statistics on the table.
 
REPORT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
 Activity start               : 21-JUN-2022 08:40:29
 Activity end                 : 21-JUN-2022 09:40:29
 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
-------------------------------------------------------------------------------
 
We can see no AI appears to have been created. Assuming still there are no statistics.
 
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';
 
no rows selected
 
ai_demo@PDB19> select num_rows,blocks,last_analyzed
  2  from user_tab_statistics
  3  where table_name ='DEMO020A';
 
  NUM_ROWS     BLOCKS LAST_ANALYZED
---------- ---------- -----------------------
 
 
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                                 NONE
X2                                 NONE
X3                                 NONE