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.