Sunday, April 24, 2022

Automatic Indexing - Part XII

In this blog post, we will put together a slightly more complex SQL example in relation to the current implementation of Oracle Automatic Indexing.
 
We will begin by creating three table, a larger table “demo013a_t1” and two smaller table “demo013a_t2” and “demo013a_t3” lookup table, each got created with only a primary key and currently have no secondary indexes. 
 
ai_demo@PDB19> create table demo013a_t1
  2  nologging as
  3  select rownum as id,
  4         mod(rownum,1000)+1 as x1,
  5         mod(rownum,200000)+1 as x2,
  6         'some_really_big_text'||rownum as some_data
  7  from all_objects a, all_users
  8  where rownum <= 1000000;
 
Table created.
 
ai_demo@PDB19> alter table demo013a_t1
  2  add constraint demo013a_t1_pk
  3  primary key(id) ;
 
Table altered.
 
ai_demo@PDB19> create table demo013a_t2
  2  nologging as
  3  select rownum as id,
  4         mod(rownum,100)+1 as x1,
  5         'some_more_stuff_goes_here'||rownum as name2
  6  from all_objects
  7  where rownum <= 10000;
 
Table created.
 
ai_demo@PDB19> alter table demo013a_t2
  2  add constraint demo013a_t2_pk
  3  primary key(id) ;
 
Table altered.
 
ai_demo@PDB19>
ai_demo@PDB19> create table demo013a_t3
  2  nologging as
  3  select rownum as id,
  4         mod(rownum,500)+1 as x1,
  5         'some_more_stuff_goes_here_again'||rownum as name2
  6  from all_objects
  7  where rownum <= 1000;
 
Table created.
 
ai_demo@PDB19> alter table demo013a_t3
  2  add constraint demo013a_t3_pk
  3  primary key(id) ;
 
Table altered.
 
We will next run the following “complex” query a number of times:
 
ai_demo@PDB19> set autotrace traceonly exp statistics
ai_demo@PDB19> select t1.x1, t1.x2, t1.some_data, t2.name2
  2  from demo013a_t1 t1,
  3         demo013a_t2 t2
  4  where t1.x1 = t2.id
  5  and t1.x2 in ( select t3.id from demo013a_t3 t3 where t3.x1 = 42 );
 
10 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3852922303
 
------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |    10 |   780 |  1779   (2)| 00:00:01 |
|   1 |  NESTED LOOPS                 |                |    10 |   780 |  1779   (2)| 00:00:01 |
|   2 |   NESTED LOOPS                |                |    10 |   780 |  1779   (2)| 00:00:01 |
|*  3 |    HASH JOIN                  |                |    10 |   440 |  1769   (2)| 00:00:01 |
|   4 |     JOIN FILTER CREATE        | :BF0000        |     2 |    16 |     4   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS STORAGE FULL| DEMO013A_T3    |     2 |    16 |     4   (0)| 00:00:01 |
|   6 |     JOIN FILTER USE           | :BF0000        |  1000K|    34M|  1760   (1)| 00:00:01 |
|*  7 |      TABLE ACCESS STORAGE FULL| DEMO013A_T1    |  1000K|    34M|  1760   (1)| 00:00:01 |
|*  8 |    INDEX UNIQUE SCAN          | DEMO013A_T2_PK |     1 |       |     0   (0)| 00:00:01 |
|   9 |   TABLE ACCESS BY INDEX ROWID | DEMO013A_T2    |     1 |    34 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("T1"."X2"="T3"."ID")
   5 - storage("T3"."X1"=42)
       filter("T3"."X1"=42)
   7 - storage(SYS_OP_BLOOM_FILTER(:BF0000,"T1"."X2"))
       filter(SYS_OP_BLOOM_FILTER(:BF0000,"T1"."X2"))
   8 - access("T1"."X1"="T2"."ID")
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       6375  consistent gets
          0  physical reads
          0  redo size
       1200  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
 
ai_demo@PDB19> set autotrace off
 
we note there are three predicates listed in which a column access could potentially benefit from an index being created.
 
t1.x1 = t2.id ( note that table T2.ID already got a unique index on it)
t1.x2 in ( select … )
t3.x1 = 42
 
Let’s have a look at the corresponding AI report to see what indexing would be suggested by AI process.
 
ai_demo@PDB19> select dbms_auto_index.report_activity( activity_start=> systimestamp - 1/24 ) report from dual;
 
REPORT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
 Activity start               : 22-MAR-2022 07:08:04
 Activity end                 : 22-MAR-2022 08:08:04
 Executions completed         : 4
 Executions interrupted       : 0
 Executions with fatal error  : 0
-------------------------------------------------------------------------------
 
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
 Index candidates                              : 6
 Indexes created (visible / invisible)         : 2 (2 / 0)
 Space used (visible / invisible)              : 15.79 MB (15.79 MB / 0 B)
 Indexes dropped                               : 0
 SQL statements verified                       : 1
 SQL statements improved (improvement factor)  : 1 (213.3x)
 SQL plan baselines created                    : 0
 Overall improvement factor                    : 213.3x
-------------------------------------------------------------------------------
 
 
We note there are 6 index candidates that were considered, BUT only 2 new indexes were actually created. Overall, the created indexes resulted in an estimated 213.3x improvement in the above SQL performance.
 
INDEX DETAILS
-------------------------------------------------------------------------------
1. The following indexes were created:
-------------------------------------------------------------------------------
----------------------------------------------------------------------------
| Owner   | Table       | Index                | Key | Type   | Properties |
----------------------------------------------------------------------------
| AI_DEMO | DEMO013A_T1 | SYS_AI_fpb323xg3yzjy | X2  | B-TREE | NONE       |
| AI_DEMO | DEMO013A_T3 | SYS_AI_c114rytxdgvc5 | X1  | B-TREE | NONE       |
----------------------------------------------------------------------------
-------------------------------------------------------------------------------
 
VERIFICATION DETAILS
-------------------------------------------------------------------------------
1. The performance of the following statements improved:
-------------------------------------------------------------------------------
 Parsing Schema Name  : AI_DEMO
 SQL ID               : gw9c64b5ardfp
 SQL Text             : select t1.x1, t1.x2, t1.some_data, t2.name2 from
                      demo013a_t1 t1, demo013a_t2 t2 where t1.x1 = t2.id and
                      t1.x2 in ( select t3.id from demo013a_t3 t3 where t3.x1 =
                      42 )
 Improvement Factor   : 213.3x
 
Execution Statistics:
-----------------------------
                    Original Plan                 Auto Index Plan
                    ----------------------------  ----------------------------
 Elapsed Time (s):  239254                        2725
 CPU Time (s):      225255                        1933
 Buffer Gets:       25597                         43
 Optimizer Cost:    1779                          27
 Disk Reads:        2                             4
 Direct Writes:     0                             0
 Rows Processed:    40                            10
 Executions:        4                             1
 
 
We note the report states the two new indexes are created on the DEMO013A_T1.X2 and DEMO013A_T3.X1 columns.
 
If we look further down in the report and compare the before and after execution plans in the plan section
 
PLANS SECTION
---------------------------------------------------------------------------------------------
 
- Original
-----------------------------
 Plan Hash Value  : 3852922303
 
------------------------------------------------------------------------------------------------
| Id | Operation                       | Name           | Rows    | Bytes    | Cost | Time     |
------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                |                |         |          | 1779 |          |
|  1 |   NESTED LOOPS                  |                |      10 |      780 | 1779 | 00:00:01 |
|  2 |    NESTED LOOPS                 |                |      10 |      780 | 1779 | 00:00:01 |
|  3 |     HASH JOIN                   |                |      10 |      440 | 1769 | 00:00:01 |
|  4 |      JOIN FILTER CREATE         | :BF0000        |       2 |       16 |    4 | 00:00:01 |
|  5 |       TABLE ACCESS STORAGE FULL | DEMO013A_T3    |       2 |       16 |    4 | 00:00:01 |
|  6 |      JOIN FILTER USE            | :BF0000        | 1000000 | 36000000 | 1760 | 00:00:01 |
|  7 |       TABLE ACCESS STORAGE FULL | DEMO013A_T1    | 1000000 | 36000000 | 1760 | 00:00:01 |
|  8 |     INDEX UNIQUE SCAN           | DEMO013A_T2_PK |       1 |          |    0 |          |
|  9 |    TABLE ACCESS BY INDEX ROWID  | DEMO013A_T2    |       1 |       34 |    1 | 00:00:01 |
------------------------------------------------------------------------------------------------
 
- With Auto Indexes
-----------------------------
 Plan Hash Value  : 1751176333
 
----------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                 | Rows | Bytes | Cost | Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                      |   10 |   780 |   27 | 00:00:01 |
|   1 |   NESTED LOOPS                           |                      |   10 |   780 |   27 | 00:00:01 |
|   2 |    NESTED LOOPS                          |                      |   10 |   780 |   27 | 00:00:01 |
|   3 |     NESTED LOOPS                         |                      |   10 |   440 |   17 | 00:00:01 |
|   4 |      TABLE ACCESS BY INDEX ROWID BATCHED | DEMO013A_T3          |    2 |    16 |    3 | 00:00:01 |
| * 5 |       INDEX RANGE SCAN                   | SYS_AI_c114rytxdgvc5 |    2 |       |    1 | 00:00:01 |
|   6 |      TABLE ACCESS BY INDEX ROWID BATCHED | DEMO013A_T1          |    5 |   180 |    7 | 00:00:01 |
| * 7 |       INDEX RANGE SCAN                   | SYS_AI_fpb323xg3yzjy |    5 |       |    2 | 00:00:01 |
| * 8 |     INDEX UNIQUE SCAN                    | DEMO013A_T2_PK       |    1 |       |    0 |          |
|   9 |    TABLE ACCESS BY INDEX ROWID           | DEMO013A_T2          |    1 |    34 |    1 | 00:00:01 |
----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
------------------------------------------
* 5 - access("T3"."X1"=42)
* 7 - access("T1"."X2"="T3"."ID")
* 8 - access("T1"."X1"="T2"."ID")
 
 
We can see the new plan uses both the new AI and has a new improved cost of 27 (down from 1779)
 
If we look at all the indexes that got created on these tables:
 
ai_demo@PDB19> select table_name,index_name,tablespace_name,compression,
  2         status,visibility,indexing,auto
  3  from  user_indexes
  4  where table_name like 'DEMO013A%'
  5  order by 1,2;
 
TABLE_NAME      INDEX_NAME           TABLESPACE_NAME  COMPRESSION   STATUS   VISIBILIT INDEXIN AUT
--------------- -------------------- ---------------- ------------- -------- --------- ------- ---
DEMO013A_T1     DEMO013A_T1_PK       TS_INDEX_DEMO    DISABLED      VALID    VISIBLE   FULL    NO
DEMO013A_T1     SYS_AI_c0byf1thgqjxs TS_INDEX_DEMO    ADVANCED LOW  UNUSABLE INVISIBLE FULL    YES
DEMO013A_T1     SYS_AI_fpb323xg3yzjy TS_INDEX_DEMO    ADVANCED LOW  VALID    VISIBLE   FULL    YES
DEMO013A_T2     DEMO013A_T2_PK       TS_INDEX_DEMO    DISABLED      VALID    VISIBLE   FULL    NO
DEMO013A_T3     DEMO013A_T3_PK       TS_INDEX_DEMO    DISABLED      VALID    VISIBLE   FULL    NO
DEMO013A_T3     SYS_AI_c114rytxdgvc5 TS_INDEX_DEMO    ADVANCED LOW  VALID    VISIBLE   FULL    YES
 
6 rows selected.
 
ai_demo@PDB19> select table_name,index_name,column_name,column_position
  2  from user_ind_columns
  3  where table_name like 'DEMO013A%'
  4  order by 1,2,3;
 
TABLE_NAME   INDEX_NAME            COLUMN_NAM COLUMN_POSITION
------------ --------------------- ---------- ---------------
DEMO013A_T1  DEMO013A_T1_PK        ID                       1
DEMO013A_T1  SYS_AI_c0byf1thgqjxs  X1                       1
DEMO013A_T1  SYS_AI_fpb323xg3yzjy  X2                       1
DEMO013A_T2  DEMO013A_T2_PK        ID                       1
DEMO013A_T3  DEMO013A_T3_PK        ID                       1
DEMO013A_T3  SYS_AI_c114rytxdgvc5  X1                       1
 
However there is also an AI defined on the other potential indexed column “DEMO013A_T1. X1” called “SYS_AI_c0byf1thgqjxs” that has been left in the INVISIBLE and UNUSABLE state. This index has been shown to be ineffective in improving SQL performance and have been converted back to an UNUSABLE state.
 
If we run the query again and look at the resultant execution plan:
 
ai_demo@PDB19> set autotrace traceonly exp statistics
ai_demo@PDB19> select t1.x1, t1.x2, t1.some_data, t2.name2
  2  from demo013a_t1 t1,
  3     demo013a_t2 t2
  4  where t1.x1 = t2.id
  5  and t1.x2 in ( select t3.id from demo013a_t3 t3 where t3.x1 = 42 );
 
10 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1751176333
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                 | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                      |    10 |   780 |    27   (0)|
|   1 |  NESTED LOOPS                          |                      |    10 |   780 |    27   (0)|
|   2 |   NESTED LOOPS                         |                      |    10 |   780 |    27   (0)|
|   3 |    NESTED LOOPS                        |                      |    10 |   440 |    17   (0)|
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| DEMO013A_T3          |     2 |    16 |     3   (0)|
|*  5 |      INDEX RANGE SCAN                  | SYS_AI_c114rytxdgvc5 |     2 |       |     1   (0)|
|   6 |     TABLE ACCESS BY INDEX ROWID BATCHED| DEMO013A_T1          |     5 |   180 |     7   (0)|
|*  7 |      INDEX RANGE SCAN                  | SYS_AI_fpb323xg3yzjy |     5 |       |     2   (0)|
|*  8 |    INDEX UNIQUE SCAN                   | DEMO013A_T2_PK       |     1 |       |     0   (0)|
|   9 |   TABLE ACCESS BY INDEX ROWID          | DEMO013A_T2          |     1 |    34 |     1   (0)|
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - access("T3"."X1"=42)
   7 - access("T1"."X2"="T3"."ID")
   8 - access("T1"."X1"="T2"."ID")
 
Note
-----
   - this is an adaptive plan
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         45  consistent gets
          0  physical reads
          0  redo size
        950  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
 
ai_demo@PDB19> set autotrace off
 
 
we note the new execution plan uses both the newly created AI and at just 45 consistent gets, is significantly more efficient than it was previously where it required 6375 consistent gets.
 
So the Auto Indexing process can create any number of possible indexes for a particular query and may independently ultimately determine different states for the various candidate indexes and so only create and keep the necessary indexes to sufficiently improve an SQL.
 
We now have an SQL statement that automatic runs much more efficiently without human intervention thanks to these automatically created indexes…
 
 

No comments:

Post a Comment