jump to navigation

Oracle 19c Automatic Indexing: A More Complex Example (How Does The Grass Grow) June 16, 2020

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, CBO.
trackback

In this post I’m going to put together in a slightly more complex SQL example a number of the concepts I’ve covered thus far in relation to the current implementation of Oracle Automatic Indexing.

I’ll begin by creating three tables, a larger TABLE1 and two smaller TABLE2 and TABLE3 lookup tables. Each table is created with only a Primary Key, Unique index and currently have no secondary indexes (this demo was run in an ATP Autonomous Cloud environment hence the odd parallel execution plans):

SQL> create table table1 (id number not null, code1 number not null, grade1 number not null, name1 varchar2(42));

Table created.

SQL> insert into table1 select rownum, mod(rownum, 1000)+1, mod(rownum, 200000)+1, 'David Bowie '|| rownum
from dual connect by level <=1000000;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> create unique index table1_id_i on table1(id);

Index created.

SQL> alter table table1 add primary key(id);

Table altered.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'TABLE1');

PL/SQL procedure successfully completed.


SQL> create table table2 (id number not null, stuff number not null, name2 varchar2(42));

Table created.

SQL> insert into table2 select rownum, mod(rownum, 100)+1, 'Ziggy Stardust ' || rownum from dual connect by level <=10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL> create unique index table2_id_i on table2(id);

Index created.

SQL> alter table table2 add primary key(id);

Table altered.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'TABLE2');

PL/SQL procedure successfully completed.


SQL> create table table3 (id number not null, code3 number not null, name3 varchar2(42));

Table created.

SQL> insert into table3 select rownum, mod(rownum, 500)+1, 'Thin White Duke ' || rownum from dual connect by level <=1000;

1000 rows created.

SQL> commit;

Commit complete.

SQL> create unique index table3_id_i on table3(id);

Index created.

SQL> alter table table3 add primary key(id);

Table altered.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'TABLE3');

PL/SQL procedure successfully completed.

 

I’ll next run the following “complex” query a number of times:

 

SQL> select code1, grade1, name1, name2 from table1, table2
where table1.code1=table2.id and
table1.grade1 in (select table3.id from table3 where table3.code3=42);

10 rows selected.

Execution Plan
------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name        | Rows  | Bytes | Cost (%CPU) | Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |             |    10 |   600 |     857  (7)| 00:00:01 |
|   1 |  PX COORDINATOR                  |             |       |       |             |          |
|   2 |   PX SEND QC (RANDOM)            | :TQ10000    |    10 |   600 |     857  (7)| 00:00:01 |
|   3 |    NESTED LOOPS                  |             |    10 |   600 |     857  (7)| 00:00:01 |
|   4 |     NESTED LOOPS                 |             |    10 |   600 |     857  (7)| 00:00:01 |
|*  5 |      HASH JOIN                   |             |    10 |   360 |     852  (7)| 00:00:01 |
|   6 |       JOIN FILTER CREATE         | :BF0000     |     2 |    16 |       2  (0)| 00:00:01 |
|*  7 |        TABLE ACCESS STORAGE FULL | TABLE3      |     2 |    16 |       2  (0)| 00:00:01 |
|   8 |       JOIN FILTER USE            | :BF0000     |  1000K|    26M|     833  (5)| 00:00:01 |
|   9 |        PX BLOCK ITERATOR         |             |  1000K|    26M|     833  (5)| 00:00:01 |
|* 10 |   TABLE ACCESS STORAGE FULL      | TABLE1      |  1000K|    26M|     833  (5)| 00:00:01 |
|* 11 |      INDEX UNIQUE SCAN           | TABLE2_ID_I |     1 |       |       0  (0)| 00:00:01 |
|  12 |     TABLE ACCESS BY INDEX ROWID  | TABLE2      |     1 |    24 |       1  (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

      5 - access("TABLE1"."GRADE1"="TABLE3"."ID")
      7 - storage("TABLE3"."CODE3"=42)
          filter("TABLE3"."CODE3"=42)
     10 - storage(SYS_OP_BLOOM_FILTER(:BF0000,"TABLE1"."GRADE1"))
          filter(SYS_OP_BLOOM_FILTER(:BF0000,"TABLE1"."GRADE1"))
     11 - access("TABLE1"."CODE1"="TABLE2"."ID")

Statistics
----------------------------------------------------------
         6  recursive calls
         0  db block gets
      5777  consistent gets
         0  physical reads
         0  redo size
      1224  bytes sent via SQL*Net to client
       588  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
        10  rows processed

 

We note there are three predicates listed in which a column access could potentially benefit from an index being created:

table1.code1=table2.id (note that table2.id already has a unique index defined)

table1.grade1 in (Select…)

table3.code3=42

 

Let’s have a look at the corresponding Automatic Indexing report to see what the Oracle Automatic Indexing process made of this example:

 

REPORT

--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------

Activity start                  : 26-JUN-2019 08:56:08
Activity end                    : 26-JUN-2019 08:56:53
Executions completed            : 1
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)                : 18.94 MB (18.94 MB / 0 B)
Indexes dropped                                 : 0
SQL statements verified                         : 2
SQL statements improved (improvement factor)    : 1 (192.7x)
SQL plan baselines created                      : 0
Overall improvement factor                      : 170.2x

-------------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------

Unused indexes    : 0
Space used        : 0 B
Unusable indexes  : 0

 

We note there are 3 index candidates that were considered, BUT only 2 new indexes were actually created. Overall, the created indexes resulted in an estimated 192.7x improvement in the above SQL performance.

If we look further on in the report and at the actual indexes created:

 

INDEX DETAILS

-------------------------------------------------------------------------------
The following indexes were created:
------------------------------------------------------------------------
| Owner | Table  | Index                | Key    | Type   | Properties |
------------------------------------------------------------------------
| BOWIE | TABLE1 | SYS_AI_0p5nn18dt9bn1 | GRADE1 | B-TREE | NONE       |
| BOWIE | TABLE3 | SYS_AI_b4ntgxt6pdbfh | CODE3  | B-TREE | NONE       |
------------------------------------------------------------------------

VERIFICATION DETAILS

-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------

Parsing Schema Name  : BOWIE
SQL ID               : 21v8yqs9jrnzm
SQL Text             : select code1, grade1, name1, name2 from table1, table2
                       where table1.code1=table2.id and table1.grade1 in (select table3.id from table3 where table3.code3=42)
Improvement Factor   : 192.7x

Execution Statistics:
-----------------------------

                                Original Plan                 Auto Index Plan
                                ----------------------------  ----------------------------

Elapsed Time (s):               299396                        2170
CPU Time (s):                   289510                        1036
Buffer Gets:                    28912                         43
Optimizer Cost:                 857                           27
Disk Reads:                     0                             4
Direct Writes:                  0                             0
Rows Processed:                 50                            10
Executions:                     5                             1

 

We note the report states the two new indexes are created on the TABLE1.GRADE1 and TABLE3.CODE3 columns.

 

If look down further in the report and compare the before and after execution plans in the PLANS SECTION:

 

PLANS SECTION

- Original

-----------------------------
Plan Hash Value  : 1597150496
------------------------------------------------------------------------------------------------
| Id | Operation                          | Name        | Rows    | Bytes    | Cost | Time     |
------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                   |             |         |          |  857 |          |
|  1 |  PX COORDINATOR                    |             |         |          |      |          |
|  2 |    PX SEND QC (RANDOM)             | :TQ10000    |      10 |      600 |  857 | 00:00:01 |
|  3 |     NESTED LOOPS                   |             |      10 |      600 |  857 | 00:00:01 |
|  4 |      NESTED LOOPS                  |             |      10 |      600 |  857 | 00:00:01 |
|  5 |       HASH JOIN                    |             |      10 |      360 |  852 | 00:00:01 |
|  6 |        JOIN FILTER CREATE          | :BF0000     |       2 |       16 |    2 | 00:00:01 |
|  7 |         TABLE ACCESS STORAGE FULL  | TABLE3      |       2 |       16 |    2 | 00:00:01 |
|  8 |        JOIN FILTER USE             | :BF0000     | 1000000 | 28000000 |  833 | 00:00:01 |
|  9 |         PX BLOCK ITERATOR          |             | 1000000 | 28000000 |  833 | 00:00:01 |
| 10 |  TABLE ACCESS STORAGE FULL         | TABLE1      | 1000000 | 28000000 |  833 | 00:00:01 |
| 11 |       INDEX UNIQUE SCAN            | TABLE2_ID_I |       1 |          |    0 |          |
| 12 |      TABLE ACCESS BY INDEX ROWID   | TABLE2      |       1 |       24 |    1 | 00:00:01 |
------------------------------------------------------------------------------------------------

- With Auto Indexes

-----------------------------
Plan Hash Value  : 2014256176
----------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                 | Rows | Bytes | Cost | Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                      |   10 |   600 |   27 | 00:00:01 |
|   1 |   NESTED LOOPS                           |                      |   10 |   600 |   27 | 00:00:01 |
|   2 |    NESTED LOOPS                          |                      |   10 |   600 |   27 | 00:00:01 |
|   3 |     NESTED LOOPS                         |                      |   10 |   360 |   17 | 00:00:01 |
|   4 |      TABLE ACCESS BY INDEX ROWID BATCHED | TABLE3               |    2 |    16 |    3 | 00:00:01 |
| * 5 |       INDEX RANGE SCAN                   | SYS_AI_b4ntgxt6pdbfh |    2 |       |    1 | 00:00:01 |
|   6 |      TABLE ACCESS BY INDEX ROWID BATCHED | TABLE1               |    5 |   140 |    7 | 00:00:01 |
| * 7 |       INDEX RANGE SCAN                   | SYS_AI_0p5nn18dt9bn1 |    5 |       |    2 | 00:00:01 |
| * 8 |     INDEX UNIQUE SCAN                    | TABLE2_ID_I          |    1 |       |    0 |          |
|   9 |    TABLE ACCESS BY INDEX ROWID           | TABLE2               |    1 |    24 |    1 | 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------

* 5 - access("TABLE3"."CODE3"=42)
* 7 - access("TABLE1"."GRADE1"="TABLE3"."ID")
* 8 - access("TABLE1"."CODE1"="TABLE2"."ID")

 

We can see that the new plan uses both the new automatic indexes and has a new improved cost of 27 (down from 857).

 

If we look at all the indexes that have been created on these tables:

 

SQL> select table_name, index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor
from user_indexes where table_name like 'TABLE%';

TABLE_NAME   INDEX_NAME             AUT CON VISIBILIT COMPRESSION   STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
------------ ---------------------- --- --- --------- ------------- -------- ---------- ----------- -----------------
TABLE1       TABLE1_ID_I            NO  NO  VISIBLE   DISABLED      VALID       1000000        2088              5202
TABLE1       SYS_AI_85dcbcnkgj0w0   YES NO  INVISIBLE DISABLED      UNUSABLE    1000000        2171            415525
TABLE1       SYS_AI_0p5nn18dt9bn1   YES NO  VISIBLE   DISABLED      VALID       1000000        2221           1000000
TABLE2       TABLE2_ID_I            NO  NO  VISIBLE   DISABLED      VALID         10000          20                44
TABLE3       TABLE3_ID_I            NO  NO  VISIBLE   DISABLED      VALID          1000           2                 5
TABLE3       SYS_AI_b4ntgxt6pdbfh   YES NO  VISIBLE   DISABLED      VALID          1000           3               999

SQL> select table_name, index_name, column_name, column_position from user_ind_columns
where table_name like 'TABLE%' order by table_name, index_name, column_position;

TABLE_NAME   INDEX_NAME             COLUMN_NAME     COLUMN_POSITION
------------ ---------------------- --------------- ---------------
TABLE1       SYS_AI_0p5nn18dt9bn1   GRADE1                        1
TABLE1       SYS_AI_85dcbcnkgj0w0   CODE1                         1
TABLE1       TABLE1_ID_I            ID                            1
TABLE2       TABLE2_ID_I            ID                            1
TABLE3       SYS_AI_b4ntgxt6pdbfh   CODE3                         1
TABLE3       TABLE3_ID_I            ID                            1

 

We note that both indexes listed as created in the Auto Indexing report on the TABLE1.GRADE1 (and TABLE3.CODE3 columns are both listed as being VISIBLE and VALID. Both of these indexes have been proven to improve the performance of the SQL statement by reducing the number of logical reads.

 

However, there is also an Auto Indexed defined on the other potential indexed column table1.code1, called “SYS_AI_85dcbcnkgj0w0” that has been left in an INVISIBLE, UNUSABLE state. This index has been shown to be ineffective in improving SQL performance and has been converted back to an UNUSABLE state.

If we run the query again and look at the resultant execution plan:

 

SQL> select code1, grade1, name1, name2
from table1, table2
where table1.code1=table2.id and
table1.grade1 in (select table3.id from table3 where table3.code3=24);

10 rows selected.

Execution Plan
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                      |    10 |   600 |    15   (0)| 00:00:01 |
|   1 |  PX COORDINATOR                          |                      |       |       |            |          |
|   2 |   PX SEND QC (RANDOM)                    | :TQ10000             |    10 |   600 |    15   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                          |                      |    10 |   600 |    15   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                         |                      |    10 |   600 |    15   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                        |                      |    10 |   360 |    10   (0)| 00:00:01 |
|   6 |       PX BLOCK ITERATOR                  |                      |       |       |            |          |
|*  7 |        TABLE ACCESS STORAGE FULL         | TABLE3               |     2 |    16 |     2   (0)| 00:00:01 | 
|   8 |       TABLE ACCESS BY INDEX ROWID BATCHED| TABLE1               |     5 |   140 |     4   (0)| 00:00:01 |
|*  9 |        INDEX RANGE SCAN                  | SYS_AI_0p5nn18dt9bn1 |     5 |       |     1   (0)| 00:00:01 |
|* 10 |      INDEX UNIQUE SCAN                   | TABLE2_ID_I          |     1 |       |     0   (0)| 00:00:01 |
|  11 |     TABLE ACCESS BY INDEX ROWID          | TABLE2               |     1 |    24 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - storage("TABLE3"."CODE3"=24)
       filter("TABLE3"."CODE3"=24)
   9 - access("TABLE1"."GRADE1"="TABLE3"."ID")
  10 - access("TABLE1"."CODE1"="TABLE2"."ID")

Statistics
----------------------------------------------------------
        10  recursive calls
         4  db block gets
        59  consistent gets
         0  physical reads
         0  redo size
      1122  bytes sent via SQL*Net to client
       588  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
        10  rows processed

 

We note the new execution plan now only uses one of newly created Automatic Indexes (SYS_AI_0p5nn18dt9bn1) and at just 59 consistent gets, is significantly more efficient than it was previously where it required 5777 consistent gets.

However, the other VISIBLE automatic index (SYS_AI_b4ntgxt6pdbfh on the BOWIE3 table) is NOT actually used in the new plan. Why?

Because as discussed previously, it’s entirely possible for the Auto Indexing process to consider a new plan with Auto Index to be more efficient because it uses less consistent gets BUT the CBO not use the plan because the plan has a higher cost. The plan generated by the Auto Index process has a cost of 27 and uses 43 buffer gets but the CBO uses the plan without the second Auto Index because it has a reduced cost of only 15, even though it uses 59 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…

Comments»

1. Rajeshwaran Jeyabal - August 24, 2020

Richard:

We note the new execution plan now uses both newly created Automatic Indexes and at just 59 consistent gets,

Post the automatically created indexes in place, could see only one of the automatically created indexes “SYS_AI_0p5nn18dt9bn1” was used.

but don’t see both as you mentioned. kindly clarify.

Like

2. Rajeshwaran Jeyabal - August 24, 2020

Richard:

“…We note the new execution plan now uses both newly created Automatic Indexes and at just 59 consistent gets,…”

Post the automatically created indexes in place, could see only one of the automatically created indexes “SYS_AI_0p5nn18dt9bn1” was used.
but dont see both as you mentioned. kindly clarify.

Liked by 1 person

Richard Foote - August 24, 2020

Hi Rajeshwaran

Thanks so much for your comment. I missed a key point which your comment has highlighted.

As you noted, only one of the Visible indexes is actually used in the new execution plan, not both as I mistaken said.

This is because the Auto Index process created both indexes because it came up with a plan with the lowest consistent gets, BUT the CBO comes up with a plan with the lowest Cost and these two may not always be the same as I discussed previously in this post:

https://richardfoote.wordpress.com/2020/03/30/oracle-19c-automatic-indexing-index-created-but-not-actually-used-because-your-young/

I’ve updated the blog piece to reflect these changes.

Thanks again

Richard

Like


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: