Automatic Indexing - Part III

One of the first question raised when working with automatic indexing (AI) was how many executions of a SQL  does it take for a new index to be considered ?
 
To find that out setup this below test case.
 
ai_demo@PDB19> create table demo003
  2  nologging as
  3  select rownum as id, mod(rownum,1000000)+1 as code , a.*
  4  from all_objects a ,
  5         all_users b
  6  where rownum <= 10000000;
 
Table created.
 
ai_demo@PDB19> alter table demo003
  2  add constraint demo003_pk
  3  primary key(id);
 
Table altered.
 
Then ran the following query just once and checked to see if the AI task would pickup this execution for consideration in building up new index.
 
ai_demo@PDB19> select sum(code) from demo003 where code = 42;
 
 SUM(CODE)
----------
       126
 
ai_demo@PDB19>
 
waited for nearly 30 min then the following AI report details the following
 
ai_demo@PDB19> $ timeout /T 1800
 
ai_demo@PDB19> select dbms_auto_index.report_activity() report from dual;
 
REPORT
-------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
 Activity start               : 08-FEB-2022 01:13:43
 Activity end                 : 09-FEB-2022 01:13:43
 Executions completed         : 61
 Executions interrupted       : 0
 Executions with fatal error  : 0
-------------------------------------------------------------------------------
 
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
 Index candidates                              : 3
 Indexes created (visible / invisible)         : 2 (2 / 0)
 Space used (visible / invisible)              : 94.37 MB (94.37 MB / 0 B)
 Indexes dropped                               : 0
 SQL statements verified                       : 2
 SQL statements improved (improvement factor)  : 2 (56345.8x)
 SQL plan baselines created                    : 0
 Overall improvement factor                    : 56345.8x
-------------------------------------------------------------------------------
 
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 | T     | SYS_AI_41fz8aw5b9rxu | CODE | B-TREE | NONE       |
| AI_DEMO | T1    | SYS_AI_c792bqgaq0jrq | CODE | B-TREE | NONE       |
-----------------------------------------------------------------------
-------------------------------------------------------------------------------
 
ai_demo@PDB19> select owner,index_name,tablespace_name,compression,
  2         status,visibility,indexing,auto
  3  from all_indexes
  4  where table_name ='DEMO003'
  5  and owner ='AI_DEMO' ;
 
OWNER      INDEX_NAME  TABLESPACE_NAME  COMPRESSION   STATUS   VISIBILIT INDEXIN AUT
---------- ----------- ---------------- ------------- -------- --------- ------- ---
AI_DEMO    DEMO003_PK  TS_INDEX_DEMO    DISABLED      VALID    VISIBLE   FULL    NO
 
ai_demo@PDB19>
 
So AI was not yet created, tried running it again to check if AI was possible
 
ai_demo@PDB19> select sum(code) from demo003 where code = 42;
 
 SUM(CODE)
----------
       126
 
ai_demo@PDB19> $ timeout /T 1800
 
ai_demo@PDB19> select dbms_auto_index.report_activity() report from dual;
 
REPORT
------------------------------------------------------------------------------
GENERAL INFORMATION
------------------------------------------------------------------------------
 Activity start               : 08-FEB-2022 01:43:43
 Activity end                 : 09-FEB-2022 01:43:43
 Executions completed         : 63
 Executions interrupted       : 0
 Executions with fatal error  : 0
-------------------------------------------------------------------------------
 
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
 Index candidates                              : 4
 Indexes created (visible / invisible)         : 3 (3 / 0)
 Space used (visible / invisible)              : 141.56 MB (141.56 MB / 0 B)
 Indexes dropped                               : 0
 SQL statements verified                       : 3
 SQL statements improved (improvement factor)  : 3 (56352x)
 SQL plan baselines created                    : 0
 Overall improvement factor                    : 56352x
-------------------------------------------------------------------------------
 
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 | DEMO003 | SYS_AI_96xnrbxzh2saf | CODE | B-TREE | NONE       |
| AI_DEMO | T       | SYS_AI_41fz8aw5b9rxu | CODE | B-TREE | NONE       |
| AI_DEMO | T1      | SYS_AI_c792bqgaq0jrq | CODE | B-TREE | NONE       |
-------------------------------------------------------------------------
-------------------------------------------------------------------------------
 
So the above details shows that an index on CODE column was indeed created after more than one execution.
 
For those wondering, yes elapsed and CPU time in the below statistics were actually in microseconds and not in seconds as stated. 
 
-------------------------------------------------------------------------------
 Parsing Schema Name  : AI_DEMO
 SQL ID               : bj8ugjtmvg9vw
 SQL Text             : select sum(code) from demo003 where code = 42
 Improvement Factor   : 56364.5x
 
Execution Statistics:
-----------------------------
                    Original Plan                 Auto Index Plan
                    ----------------------------  ----------------------------
 Elapsed Time (s):  505320                        1437
 CPU Time (s):      489916                        930
 Buffer Gets:       112730                        3
 Optimizer Cost:    15390                         3
 Disk Reads:        0                             5
 Direct Writes:     0                             0
 Rows Processed:    2                             1
 Executions:        2                             1
 
 
PLANS SECTION
-------------------------------------------------------------------------------
 
- Original
-----------------------------
 Plan Hash Value  : 1530605218
---------------------------------------------------------------------------------
| Id | Operation                    | Name    | Rows | Bytes | Cost  | Time     |
---------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |         |      |       | 15390 |          |
|  1 |   SORT AGGREGATE             |         |    1 |     5 |       |          |
|  2 |    TABLE ACCESS STORAGE FULL | DEMO003 |    3 |    15 | 15390 | 00:00:01 |
---------------------------------------------------------------------------------
 
- With Auto Indexes
-----------------------------
 Plan Hash Value  : 454853808
 
-------------------------------------------------------------------------------------
| Id  | Operation           | Name                 | Rows | Bytes | Cost | Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                      |    1 |     5 |    3 | 00:00:01 |
|   1 |   SORT AGGREGATE    |                      |    1 |     5 |      |          |
| * 2 |    INDEX RANGE SCAN | SYS_AI_96xnrbxzh2saf |    1 |     5 |    3 | 00:00:01 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("CODE"=42)
 
If we look at the details of the new AI:
 
ai_demo@PDB19> select owner,index_name,tablespace_name,compression,
  2         status,visibility,indexing,auto
  3  from all_indexes
  4  where table_name ='DEMO003'
  5  and owner ='AI_DEMO' ;
 
OWNER      INDEX_NAME           TABLESPACE_NAME COMPRESSION   STATUS   VISIBILIT INDEXIN AUT
---------- -------------------- --------------- ------------- -------- --------- ------- ---
AI_DEMO    DEMO003_PK           TS_INDEX_DEMO   DISABLED      VALID    VISIBLE   FULL    NO
AI_DEMO    SYS_AI_96xnrbxzh2saf TS_INDEX_DEMO   ADVANCED LOW  VALID    VISIBLE   FULL    YES
 
The newly created AI is both valid and visible and can be used globally within the database.
 
ai_demo@PDB19> set autotrace traceonly exp statistics
ai_demo@PDB19> select sum(code) from t1 where code = 42;
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2380736383
 
------------------------------------------------------------------------------------------
| Id  | Operation         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                      |     1 |     5 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                      |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| SYS_AI_96xnrbxzh2saf |     3 |    15 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("CODE"=42)
 
 
Statistics
----------------------------------------------------------
        108  recursive calls
          0  db block gets
         94  consistent gets
          0  physical reads
          0  redo size
        361  bytes sent via SQL*Net to client
        546  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
ai_demo@PDB19> set autotrace off