Richard Foote's Oracle Blog

Oracle 19c Automatic Indexing: How Many Executions Does It Take? (One Shot) August 29, 2019

One of the first questions I asked when playing with the new Oracle Database 19c Automatic Indexing feature was how many executions of an SQL does it take for a new index to be considered?

To find out, I create the following table:

SQL> create table bowie_one (id number constraint bowie_one_pk primary key, code number, name varchar2(42));

Table created.

SQL> insert into bowie_one select rownum, mod(rownum, 1000000)+1, 'David Bowie' from dual connect by level

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

I then ran the following query just once and checked to see if the Automatic Indexing task would pick this execution up and consider building a new index:

SQL> select * from bowie_one where code=42;

10 rows selected.

Execution Plan

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |    10 |   230 |  6208   (7)| 00:00:01 |
|   1 |  PX COORDINATOR              |           |       |       |            |          |
|   2 |   PX SEND QC (RANDOM)        | :TQ10000  |    10 |   230 |  6208   (7)| 00:00:01 |
|   3 |    PX BLOCK ITERATOR         |           |    10 |   230 |  6208   (7)| 00:00:01 |
|*  4 |     TABLE ACCESS STORAGE FULL| BOWIE_ONE |    10 |   230 |  6208   (7)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

4 - storage("CODE"=42)
    filter("CODE"=42)

Statistics
----------------------------------------------------------

   12  recursive calls
    0  db block gets
39000  consistent gets
    0  physical reads
  132  redo size
  867  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

The following Automatic Indexing report detailed the following:

SQL> select dbms_auto_index.report_last_activity() report from dual;

REPORT
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start               : 26-JUN-2019 13:03:30
Activity end                 : 26-JUN-2019 21:13:06
Executions completed         : 24
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)             : 184.55 MB (184.55 MB / 0 B)
Indexes dropped                              : 0
SQL statements verified                      : 3
SQL statements improved (improvement factor) : 1 (19500x)
SQL plan baselines created                   : 0
Overall improvement factor                   : 6.9x
-------------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------

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

So an index was indeed created. Later in the report:

INDEX DETAILS

-------------------------------------------------------------------------------
The following indexes were created:
-------------------------------------------------------------------------

-------------------------------------------------------------------------
| Owner | Table     | Index                | Key  | Type   | Properties |
-------------------------------------------------------------------------
| BOWIE | BOWIE_ONE | SYS_AI_5tabfu6wtkbdh | CODE | B-TREE | NONE       |
-------------------------------------------------------------------------
-------------------------------------------------------------------------------

VERIFICATION DETAILS

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

Parsing Schema Name  : BOWIE
SQL ID               : 9n89axkwrvw4b
SQL Text             : select * from bowie_one where code=42
Improvement Factor   : 19500x

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

                    Original Plan                 Auto Index Plan
                    ----------------------------  ----------------------------
Elapsed Time (s):   198342                        961
CPU Time (s):       187768                        1112
Buffer Gets:        39000                         13
Optimizer Cost:     6208                          14
Disk Reads:         0                             2
Direct Writes:      0                             0
Rows Processed:     10                            10
Executions:         1                             1

So the above details that an index on the CODE column of the BOWIE_ONE table was indeed created after just 1 execution.

For those wondering, yes the Elaspsed and CPU times are actually in Microseconds (1 millionth of a second) and not in seconds as stated…

The final section of the report details:

PLANS SECTION
---------------------------------------------------------------------------------------------
- Original
-----------------------------

Plan Hash Value  : 227986582
------------------------------------------------------------------------------------
| Id | Operation                      | Name      | Rows | Bytes | Cost | Time     |
------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT               |           |      |       | 6208 |          |
|  1 |  PX COORDINATOR                |           |      |       |      |          |
|  2 |    PX SEND QC (RANDOM)         | :TQ10000  |   10 |   230 | 6208 | 00:00:01 |
|  3 |     PX BLOCK ITERATOR          |           |   10 |   230 | 6208 | 00:00:01 |
|  4 |      TABLE ACCESS STORAGE FULL | BOWIE_ONE |   10 |   230 | 6208 | 00:00:01 |
------------------------------------------------------------------------------------

Notes
-----

- dop_op_reason = scan of object BOWIE.BOWIE_ONE
- dop = 2
- px_in_memory_imc = no
- px_in_memory = no
- With Auto Indexes
-----------------------------

Plan Hash Value  : 2734060610
-------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                 | Rows | Bytes | Cost | Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                      |  10   |  230 |   14 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE_ONE            |  10   |  230 |   14 | 00:00:01 |
| * 2 |    INDEX RANGE SCAN                   | SYS_AI_5tabfu6wtkbdh |  10   |      |    3 | 00:00:01 |
-------------------------------------------------------------------------------------------------------

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

* 2 - access("CODE"=42)

Notes
-----

- Dynamic sampling used for this statement ( level = 11 )

It details that indeed, a new plan using the newly Automatic Index would  be substantially more efficient.

If we look at details of the new Automatic Index:

SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor  from user_indexes where table_name='BOWIE_ONE';

INDEX_NAME             AUT CON VISIBILIT COMPRESSION   STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
---------------------- --- --- --------- ------------- -------- ---------- ----------- -----------------
BOWIE_ONE_PK           NO  YES VISIBLE   DISABLED      VALID      10000000       19642             57523
SYS_AI_5tabfu6wtkbdh   YES NO  VISIBLE   DISABLED      VALID      10000000       22285          10000000

SQL> select index_name, column_name, column_position
from user_ind_columns where table_name='BOWIE_ONE' order by index_name, column_position;

INDEX_NAME             COLUMN_NAME     COLUMN_POSITION
---------------------- --------------- ---------------
BOWIE_ONE_PK           ID                            1
SYS_AI_5tabfu6wtkbdh   CODE                          1

The newly created Automatic Index is both Valid and Visible and so can be used globally within the database.

If I now re-run the original query:

SQL> select * from bowie_one where code=42;

10 rows selected.

Execution Plan
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                      |    10 |   230 |    13   (0)| 00:00:01 |
|   1 |  PX COORDINATOR                       |                      |       |       |            |          |
|   2 |   PX SEND QC (RANDOM)                 | :TQ10001             |    10 |   230 |    13   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE_ONE            |    10 |   230 |    13   (0)| 00:00:01 |
|   4 |     BUFFER SORT                       |                      |       |       |            |          |
|   5 |      PX RECEIVE                       |                      |    10 |       |     3   (0)| 00:00:01 |
|   6 |       PX SEND HASH (BLOCK ADDRESS)    | :TQ10000             |    10 |       |     3   (0)| 00:00:01 |
|   7 |        PX SELECTOR                    |                      |       |       |            |          |
|*  8 |           INDEX RANGE SCAN            | SYS_AI_5tabfu6wtkbdh |    10 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

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

8 - access("CODE"=42)

Statistics
---------------------------------------------------------

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

The CBO now uses the newly created Automatic Index.

So it only potentially takes just the one execution of an SQL statement for an Automatic Index to be created.

Therefore some caution needs to be exercised in environments where there may be a very large number of ad-hoc queries where specific indexes may not be necessary for once only executed predicate combinations.

That said, the Automatic Indexing process is highly efficient in building only the bare minimum of column indexed combinations to cater for all known SQL predicates.

More on this in a future post.

Oracle 19c Automatic Indexing: My First Auto Index (Absolute Beginners) August 19, 2019

I am SOOOO struggling with this nightmare block editor but here goes. Please excuse any formatting issues below:

I thought it was time to show the new Oracle 19c Automatic Indexing feature in action and what better way than to go through how I created my first ever Automatic Index.

To start, I create a typically simple little table:

SQL> create table bowie (id number constraint bowie_pk primary key, code number, name varchar2(42));

Table created.

SQL> insert into bowie select rownum, mod(rownum, 1000000)+1, 'David Bowie' from dual connect by level  commit;

Commit complete.

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

PL/SQL procedure successfully completed.

SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='BOWIE';

INDEX_NAME                  AUT CON VISIBILIT COMPRESSION   STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
------------------------- --- --- --------- ------------- -------- ---------- ----------- -----------------
BOWIE_PK                    NO  YES VISIBLE   DISABLED        VALID      10000000         19429               58133

The key column here is CODE, which is highly selective with just 10 rows on average per CODE value.

If I run the following query a number of times:

SQL> select * from bowie where code=42;

10 rows selected.

Execution Plan

--------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows    | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |      10 |   230 |  6208   (7)| 00:00:01 |
|   1 |  PX COORDINATOR                |          |         |       |            |          |
|   2 |   PX SEND QC (RANDOM)          | :TQ10000 |      10 |   230 |  6208   (7)| 00:00:01 |
|   3 |    PX BLOCK ITERATOR           |          |      10 |   230 |  6208   (7)| 00:00:01 |
|*  4 |     TABLE ACCESS STORAGE FULL  | BOWIE    |      10 |   230 |  6208   (7)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

4 - storage("CODE"=42)
filter("CODE"=42)

Statistics
----------------------------------------------------------
    6  recursive calls
    0  db block gets
39026  consistent gets
    0  physical reads
    0  redo size
  867  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

The query runs slowly as it performs a Full Table Scan of a 10M row table when returning just 10 runs.

Perhaps an index would be a good idea…

With Auto Indexing, we just wait approx. 15 minutes until the Auto Index task completes, or manually run exec dbms_auto_index_internal.task_proc(true);

As discussed in my previous blog post, the 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 an SQL.

We can query the results of the last Auto Index task by running the following:

SQL> select dbms_auto_index.report_last_activity() report from dual;

REPORT
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start                  : 20-JUN-2019 08:12:15
Activity end                    : 20-JUN-2019 08:12:55
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)                : 184.55 MB (184.55 MB / 0 B)
Indexes dropped                                 : 0
SQL statements verified                         : 2
SQL statements improved (improvement factor)    : 1 (39044.8x)
SQL plan baselines created                      : 0
Overall improvement factor                      : 6.9x
-------------------------------------------------------------------------------

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

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

At this point in the report, we can see Oracle has verified 2 SQL statements and has created 1 new, visible index using 184.55 MB of space. It has improved 1 SQL statement by a factor of 39044.8x and improved things overall by a factor of 6.9x. (we’ll look at how Oracle determines these values in a later post).

The report continues with the Index Details section:

INDEX DETAILS

-------------------------------------------------------------------------------
The following indexes were created:
---------------------------------------------------------------------
| Owner | Table | Index                | Key  | Type   | Properties |
---------------------------------------------------------------------
| BOWIE | BOWIE | SYS_AI_600vgjmtqsgv3 | CODE | B-TREE | NONE       |
---------------------------------------------------------------------

Oracle has decided to create a new indexed called “SYS_AI_600vgjmtqsgv3” on the CODE column of the BOWIE table. Notice the mixed case naming convention for the new Auto Index, oh what fun and games to be had…

Next the Verification Details section:

VERIFICATION DETAILS

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

Parsing Schema Name  : BOWIE

SQL ID               : dd5gzx7skf6as
SQL Text             : select * from bowie where code=42
Improvement Factor   : 39044.8x

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

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

Elapsed Time (s):    3241698                       108
CPU Time (s):        3174021                       108
Buffer Gets:         663764                        13
Optimizer Cost:      6204                          14
Disk Reads:          0                             0
Direct Writes:       0                             0
Rows Processed:      170                           10
Executions:          17                            1

So the SQL we previously ran has an improvement factor of 39044.8x with the new plan that uses the newly created Auto Index. These numbers are a little nonsensical as we’ll see in a later post, but it does sound kinda impressive…

Finally, we get to the Plans Section of the report:

PLANS SECTION
---------------------------------------------------------------------------------------------
- Original
-----------------------------

Plan Hash Value  : 3567883234
-------------------------------------------------------------------------------------
| Id | Operation                      | Name     | Rows   | Bytes | Cost | Time     |
-------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT               |          |        |       | 6204 |          |
|  1 |  PX COORDINATOR                |          |        |       |      |          |
|  2 |    PX SEND QC (RANDOM)         | :TQ10000 |    425 | 20825 | 6204 | 00:00:01 |
|  3 |     PX BLOCK ITERATOR          |          |    425 | 20825 | 6204 | 00:00:01 |
|  4 |      TABLE ACCESS STORAGE FULL | BOWIE    |    425 | 20825 | 6204 | 00:00:01 |
-------------------------------------------------------------------------------------

- With Auto Indexes

-----------------------------
Plan Hash Value  : 493118340

-------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                 | Rows | Bytes | Cost | Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                      |   10 |   230 |   14 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE                |   10 |   230 |   14 | 00:00:01 |
| * 2 |    INDEX RANGE SCAN                   | SYS_AI_600vgjmtqsgv3 |   10 |       |    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.

Note: the vast majority of my test cases were run on the Dedicated Autonomous Application Transaction Processing (ATP) environment, where parallelism is common for most plans by default.

Let’s look at details of the newly created Automatic Index:

SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor  from user_indexes where table_name='BOWIE';

INDEX_NAME                AUT CON VISIBILIT COMPRESSION   STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
------------------------- --- --- --------- ------------- -------- ---------- ----------- -----------------
BOWIE_PK                  NO  YES VISIBLE   DISABLED      VALID      10000000       19429             58133
SYS_AI_600vgjmtqsgv3      YES NO  VISIBLE   DISABLED      VALID      10000000       22419          10000000

SQL> select index_name, column_name, column_position from user_ind_columns where table_name='BOWIE' order by index_name, column_position;

INDEX_NAME                COLUMN_NAME          COLUMN_POSITION
------------------------- -------------------- ---------------
BOWIE_PK                  ID                                 1
SYS_AI_600vgjmtqsgv3      CODE                               1

There is new column column called AUTO in DBA_INDEXES to denote where an index has been automatically created by Oracle.

So the new SYS_AI_600vgjmtqsgv3 Automatic Index on the CODE column is both VISIBLE and VALID in this case,  meaning it can be globally used within the database. As we’ll see if future posts, this is not always the case with Automatic Indexes.

If we now re-run the initial SQL query I ran and look at the execution plan:

SQL> select * from bowie where code=42;

10 rows selected.

Execution Plan
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                      |    10 |   230 |    13   (0)| 00:00:01 |
|   1 |  PX COORDINATOR                       |                      |       |       |            |          |
|   2 |   PX SEND QC (RANDOM)                 | :TQ10001             |    10 |   230 |    13   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE                |    10 |   230 |    13   (0)| 00:00:01 |
|   4 |     BUFFER SORT                       |                      |       |       |            |          |
|   5 |      PX RECEIVE                       |                      |    10 |       |     3   (0)| 00:00:01 |
|   6 |       PX SEND HASH (BLOCK ADDRESS)    | :TQ10000             |    10 |       |     3   (0)| 00:00:01 |
|   7 |        PX SELECTOR                    |                      |       |       |            |          |
|*  8 |           INDEX RANGE SCAN            | SYS_AI_600vgjmtqsgv3      10         |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

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

8 - access("CODE"=42)

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

We notice the new Automatic Index has been used by the CBO to substantially improve the performance of the query (just 13 consistent gets).

OK, so that’s the end of the Intro. In the next blog article, we’ll start looking at some of the specifics of how Auto Indexing works behind the covers and why it’s important to understand some of these workings…