jump to navigation

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

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

One shot single

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.

Comments»

1. Jonse - August 31, 2019

hmm! what does the future hold for the job role DBA

Like

2. Richard Foote - September 2, 2019

Hi Jonse

The future is just as bright as when Oracle said you don’t have to worry about the number and size of extents of segments anymore, or worry about managing rollback segments anymore, or worry about setting the sort area sizes anymore, etc. etc. etc. The database has always evolved with more and more automation and the same has always been true of the DBA role as well.

The other thing to note is that the Autonomous Database services will only be available (in the foreseeable future) on the Oracle Cloud and Auto Indexing only on the Oracle Cloud and Exadata, so unless all your databases you plan to manage will only be in the Oracle Cloud or on Exadata, the issue of Auto Indexing taking your job is a little moot regardless.

Like

3. Rajeshwaran Jeyabal - September 23, 2021

Hello Richard – is this behaviour got changed? the below demo is from Exacc 19c (19.12)

demo@PDB19> @demo.sql
demo@PDB19> conn ai_demo/ai_demo@pdb19
Connected.
ai_demo@PDB19>
ai_demo@PDB19> col parameter_name for a40
ai_demo@PDB19> col parameter_value for a20
ai_demo@PDB19> col last_modified for a30
ai_demo@PDB19> col modified_by for a10
ai_demo@PDB19>
ai_demo@PDB19> select * from dba_auto_index_config;

PARAMETER_NAME PARAMETER_VALUE LAST_MODIFIED MODIFIED_B
—————————————- ——————– —————————— ———-
AUTO_INDEX_COMPRESSION ON 20-SEP-21 09.34.25.000000 AM C##RAJESH
AUTO_INDEX_DEFAULT_TABLESPACE TS_INDEX_DEMO 20-SEP-21 09.33.51.000000 AM C##RAJESH
AUTO_INDEX_MODE IMPLEMENT 21-SEP-21 02.42.42.000000 AM DEMO
AUTO_INDEX_REPORT_RETENTION 373
AUTO_INDEX_RETENTION_FOR_AUTO 373
AUTO_INDEX_RETENTION_FOR_MANUAL
AUTO_INDEX_SCHEMA schema IN (AI_DEMO) 20-SEP-21 09.33.15.000000 AM C##RAJESH
AUTO_INDEX_SPACE_BUDGET 60 20-SEP-21 09.34.15.000000 AM C##RAJESH

8 rows selected.

ai_demo@PDB19>
ai_demo@PDB19> drop table t1 purge;

Table dropped.

ai_demo@PDB19> drop table stage purge;

Table dropped.

ai_demo@PDB19>
ai_demo@PDB19> create table stage as select * from all_objects;

Table created.

ai_demo@PDB19>
ai_demo@PDB19> create table t1
2 nologging as
3 select rownum as id,
4 mod(rownum,1000000) code,
5 a.*
6 from stage a,
7 stage b
8 where rownum
ai_demo@PDB19> alter table t1
2 add constraint t1_pk
3 primary key(id);

Table altered.

ai_demo@PDB19>
ai_demo@PDB19> col index_name for a20
ai_demo@PDB19> select index_name,auto,leaf_blocks,visibility,clustering_factor,
2 compression,status,num_rows
3 from user_indexes
4 where table_name =’T1′;

INDEX_NAME AUT LEAF_BLOCKS VISIBILIT CLUSTERING_FACTOR COMPRESSION STATUS NUM_ROWS
——————– — ———– ——— —————– ————- ——– ———-
T1_PK NO 22132 VISIBLE 167438 DISABLED VALID 10000000

ai_demo@PDB19>
ai_demo@PDB19> set autotrace traceonly explain statistics
ai_demo@PDB19> select * from t1 where code = 77;

10 rows selected.

Execution Plan
———————————————————-
Plan hash value: 3617692013

———————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————-
| 0 | SELECT STATEMENT | | 10 | 1130 | 45663 (1)| 00:00:02 |
|* 1 | TABLE ACCESS STORAGE FULL| T1 | 10 | 1130 | 45663 (1)| 00:00:02 |
———————————————————————————-

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

1 – storage(“CODE”=77)
filter(“CODE”=77)

Statistics
———————————————————-
107 recursive calls
144 db block gets
167561 consistent gets
0 physical reads
33432 redo size
2596 bytes sent via SQL*Net to client
545 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
10 rows processed

ai_demo@PDB19> set autotrace off
ai_demo@PDB19>
ai_demo@PDB19> host timeout /T 1000

Waiting for 0 seconds, press a key to continue …

ai_demo@PDB19>
ai_demo@PDB19> select index_name,auto,leaf_blocks,visibility,clustering_factor,
2 compression,status,num_rows
3 from user_indexes
4 where table_name =’T1′;

INDEX_NAME AUT LEAF_BLOCKS VISIBILIT CLUSTERING_FACTOR COMPRESSION STATUS NUM_ROWS
——————– — ———– ——— —————– ————- ——– ———-
T1_PK NO 22132 VISIBLE 167438 DISABLED VALID 10000000

ai_demo@PDB19>
ai_demo@PDB19> select dbms_auto_index.report_last_activity() from dual;

DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY()
——————————————————————————–
GENERAL INFORMATION
——————————————————————————-
Activity start : 23-SEP-2021 03:00:46
Activity end : 23-SEP-2021 03:00:48
Executions completed : 1
Executions interrupted : 0
Executions with fatal error : 0
——————————————————————————-

SUMMARY (AUTO INDEXES)
——————————————————————————-
Index candidates : 0
Indexes created : 0
Space used : 0 B
Indexes dropped : 0
SQL statements verified : 0
SQL statements improved : 0
SQL plan baselines created : 0
Overall improvement factor : 0x
——————————————————————————-

SUMMARY (MANUAL INDEXES)
——————————————————————————-
Unused indexes : 0
Space used : 0 B
Unusable indexes : 0
——————————————————————————-

ERRORS
——————————————————————————–
————-
No errors found.
——————————————————————————–
————-

ai_demo@PDB19>
ai_demo@PDB19> set autotrace traceonly explain statistics
ai_demo@PDB19> select * from t1 where code = 77;

10 rows selected.

Execution Plan
———————————————————-
Plan hash value: 3617692013

———————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————-
| 0 | SELECT STATEMENT | | 10 | 1130 | 45663 (1)| 00:00:02 |
|* 1 | TABLE ACCESS STORAGE FULL| T1 | 10 | 1130 | 45663 (1)| 00:00:02 |
———————————————————————————-

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

1 – storage(“CODE”=77)
filter(“CODE”=77)

Statistics
———————————————————-
0 recursive calls
0 db block gets
167461 consistent gets
0 physical reads
0 redo size
2596 bytes sent via SQL*Net to client
545 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
ai_demo@PDB19>
ai_demo@PDB19> host timeout /T 1000

Waiting for 0 seconds, press a key to continue …

ai_demo@PDB19>
ai_demo@PDB19> select index_name,auto,leaf_blocks,visibility,clustering_factor,
2 compression,status,num_rows
3 from user_indexes
4 where table_name =’T1′;

INDEX_NAME AUT LEAF_BLOCKS VISIBILIT CLUSTERING_FACTOR COMPRESSION STATUS NUM_ROWS
——————– — ———– ——— —————– ————- ——– ———-
T1_PK NO 22132 VISIBLE 167438 DISABLED VALID 10000000

ai_demo@PDB19> select dbms_auto_index.report_last_activity() from dual;

DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY()
——————————————————————————–
GENERAL INFORMATION
——————————————————————————-
Activity start : 23-SEP-2021 03:15:47
Activity end : 23-SEP-2021 03:15:48
Executions completed : 1
Executions interrupted : 0
Executions with fatal error : 0
——————————————————————————-

SUMMARY (AUTO INDEXES)
——————————————————————————-
Index candidates : 0
Indexes created : 0
Space used : 0 B
Indexes dropped : 0
SQL statements verified : 0
SQL statements improved : 0
SQL plan baselines created : 0
Overall improvement factor : 0x
——————————————————————————-

SUMMARY (MANUAL INDEXES)
——————————————————————————-
Unused indexes : 0
Space used : 0 B
Unusable indexes : 0
——————————————————————————-

ERRORS
——————————————————————————–
————-
No errors found.
——————————————————————————–
————-

ai_demo@PDB19>

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: