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
 

Automatic Indexing - Part II

Now it was the time to show the new Oracle 19c automatic indexing (AI) feature in action.
 
To start we created a simple little table.
 
ai_demo@PDB19> create table t
  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 t
  2  add constraint t_pk
  3  primary key(id);
 
Table altered.
 
The key column here is CODE, which is highly selective with just 10 rows on average per code value.
 
If we run the following query a number of times
 
ai_demo@PDB19> set autotrace traceonly exp statistics
ai_demo@PDB19> select sum(code) from t where code = 42;
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
 
-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |     5 | 16097   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE            |      |     1 |     5 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| T    |     3 |    15 | 16097   (1)| 00:00:01 |
-----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - storage("CODE"=42)
       filter("CODE"=42)
 
 
Statistics
----------------------------------------------------------
         34  recursive calls
          0  db block gets
      59000  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
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
ai_demo@PDB19> set autotrace off
 
the query runs slowly as it performs the full table scan of 10M rows, when returning just 10 rows.
Perhaps an index would be a good idea, with auto indexing, we just wait approx. 15 minutes until the auto task completes.
 
This auto indexing task will look at the workload over the past 15 minutes and determine if a new index might be warranted to improve the performance of the sql.
 
We can query the results of the last auto index task by running the following
 
ai_demo@PDB19> $ timeout /T 900
 
Waiting for   0 seconds, press a key to continue ...
 
ai_demo@PDB19>
ai_demo@PDB19> col report for a180
ai_demo@PDB19> select dbms_auto_index.report_last_activity() report from dual;
 
REPORT
-----------------------------------------------------------------------------------------------
------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
 Activity start               : 05-FEB-2022 01:31:56
 Activity end                 : 05-FEB-2022 01:32:49
 Executions completed         : 1
 Executions interrupted       : 0
 Executions with fatal error  : 0
-------------------------------------------------------------------------------
 
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
 Index candidates                              : 1
 Indexes created (visible / invisible)         : 1 (1 / 0)
 Space used (visible / invisible)              : 49.28 MB (49.28 MB / 0 B)
 Indexes dropped                               : 0
 SQL statements verified                       : 4
 SQL statements improved (improvement factor)  : 4 (156604x)
 SQL plan baselines created                    : 0
 Overall improvement factor                    : 156604x
-------------------------------------------------------------------------------
 
At this point in the report, we can see Oracle has verified 4 SQL statements and created one new visible index using 49.28 MB of space. It has improved 4 sql statements by a factor of 156604x
 
Then the report continues with the index details section
 
INDEX DETAILS
-------------------------------------------------------------------------------
1. The following indexes were created:
-------------------------------------------------------------------------------
-----------------------------------------------------------------------
| Owner   | Table | Index                | Key  | Type   | Properties |
-----------------------------------------------------------------------
| AI_DEMO | T     | SYS_AI_41fz8aw5b9rxu | CODE | B-TREE | NONE       |
-----------------------------------------------------------------------
-------------------------------------------------------------------------------
 
Oracle has decided to create a new index called “SYS_AI_41fz8aw5b9rxu” on the CODE column, notice the mixed case naming convention for the new auto indexing.
 
Next the verification details section
 
VERIFICATION DETAILS
-------------------------------------------------------------------------------
1. The performance of the following statements improved:
-------------------------------------------------------------------------------
 Parsing Schema Name  : AI_DEMO
 SQL ID               : 2qrgt53b0ww5m
 SQL Text             : select sum(code) from t where code = 42
 Improvement Factor   : 58960.5x
 
Execution Statistics:
-----------------------------
                    Original Plan                 Auto Index Plan
                    ----------------------------  ----------------------------
 Elapsed Time (s):  1306196                       2080
 CPU Time (s):      1269480                       1499
 Buffer Gets:       353765                        3
 Optimizer Cost:    16097                         3
 Disk Reads:        0                             2
 Direct Writes:     0                             0
 Rows Processed:    6                             1
 Executions:        6                             1
 
So the sql we previously ran has an improvement factor of 58960.5x with the new plan that uses the newly created auto index. Finally we go into the plan section of the report.
 
PLANS SECTION
---------------------------------------------------------
 
- Original
-----------------------------
 Plan Hash Value  : 2966233522
 
------------------------------------------------------------------------------
| Id | Operation                    | Name | Rows | Bytes | Cost  | Time     |
------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |      |      |       | 16097 |          |
|  1 |   SORT AGGREGATE             |      |    1 |     5 |       |          |
|  2 |    TABLE ACCESS STORAGE FULL | T    |    3 |    15 | 16097 | 00:00:01 |
------------------------------------------------------------------------------
 
- With Auto Indexes
-----------------------------
 Plan Hash Value  : 3013999228
 
-------------------------------------------------------------------------------------
| 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_41fz8aw5b9rxu |    3 |    15 |    3 | 00:00:01 |
-------------------------------------------------------------------------------------
 
Here Oracle compares the Original plan with the new plan that uses the new index, the new plan is much more efficient and so the index is created as a valid, visible index.
 
Looking at the details of the newly created AI
 
ai_demo@PDB19> select owner,index_name,tablespace_name,compression,
  2          status,visibility,indexing,auto
  3  from all_indexes
  4  where table_name ='T'
  5  and owner ='AI_DEMO' ;
 
OWNER      INDEX_NAME            TABLESPACE_NAME  COMPRESSION   STATUS   VISIBILIT INDEXIN AUT
---------- --------------------- ---------------- ------------- -------- --------- ------- ---
AI_DEMO    T_PK                  TS_DATA          DISABLED      VALID    VISIBLE   FULL    NO
AI_DEMO    SYS_AI_41fz8aw5b9rxu  TS_INDEX_DEMO    ADVANCED LOW  VALID    VISIBLE   FULL    YES
 
There is a new column called AUTO in dba_indexes to denote where an index has been automatically created by Oracle. So the new index “SYS_AI_41fz8aw5b9rxu” on the CODE column is both valid and visible, meaning it can be globally used within the database. As we will see in future posts, this is not always the case with AI.
 
If we now rerun the initial query I ran and look at the execution plan:
 
ai_demo@PDB19> set autotrace traceonly exp statistics
ai_demo@PDB19> select sum(code) from t where code = 42;
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3013999228
 
------------------------------------------------------------------------------------------
| 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_41fz8aw5b9rxu |     3 |    15 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("CODE"=42)
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  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
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
we notice the new AI has been used by the CBO to substantially improve the performance of the query.