Automatic Indexing is a new feature in Oracle 19c which automatically creates, rebuilds, and drops indexes in a database based on the application workload.
The index management task is now dynamically performed by the database itself via a task which executes in the background every 15 minutes.
Automatic indexing task analyzes the current workload and identifies candidates for indexes.
It then creates the indexes as invisible indexes and evaluates the identified candidate SQL statements. If the performance is improved then the indexes are made visible and can be then used by the application. If there is no improvement in the performance, then the indexes are marked as unusable and dropped after a predefined interval.
The automatic indexing feature is managed via the DBMS_AUTO_INDEX package.
Note that this feature is currently available only on the Oracle Engineered Systems platform.
Let us have a look at an example of this new Oracle 19c feature.
Enable automatic indexing for the DEMO schema, but create any new auto indexes only as invisible indexes
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA','DEMO',TRUE);
PL/SQL procedure successfully completed.
We run a few queries against a table with 20 million rows – this table currently has no indexes
SQL> conn demo/demo
Connected.
SQL> select * from mysales where id=4711;
ID FLAG PRODUCT CHANNEL_ID CUST_ID AMOUNT_SOLD
---------- ---------- ----------------- ---------- ---------- -----------
ORDER_DAT SHIP_DATE
--------- ---------
4711 4712 Samsung Galaxy S7 1 711 5000
08-JUL-19 14-JAN-05
SQL> select * from mysales where id=4713;
ID FLAG PRODUCT CHANNEL_ID CUST_ID AMOUNT_SOLD
---------- ---------- ----------------- ---------- ---------- -----------
ORDER_DAT SHIP_DATE
--------- ---------
4713 4714 Samsung Galaxy S7 3 713 5000
08-JUL-19 16-JAN-05
SQL> select * from mysales where id=4715;
ID FLAG PRODUCT CHANNEL_ID CUST_ID AMOUNT_SOLD
---------- ---------- ----------------- ---------- ---------- -----------
ORDER_DAT SHIP_DATE
--------- ---------
4715 4716 Samsung Galaxy S7 0 715 5000
08-JUL-19 18-JAN-05
..
..
Obtain information about the automatic indexing operations via the REPORT_ACTIVITY and REPORT_LAST_ACTIVITY functions of the DBMS_AUTO_INDEX package.
Because automatic indexing has been configured with the REPORT option, the indexes are created as INVISIBLE indexes.
SQL> SET LONG 1000000 PAGESIZE 0
SQL> SELECT DBMS_AUTO_INDEX.report_activity() FROM dual;
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start : 08-JUL-2019 11:05:20
Activity end : 09-JUL-2019 11:05:20
Executions completed : 4
Executions interrupted : 0
Executions with fatal error : 0
-------------------------------------------------------------------------------
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates : 1
Indexes created (visible / invisible) : 1 (0 / 1)
Space used (visible / invisible) : 394.26 MB (0 B / 394.26 MB)
Indexes dropped : 0
SQL statements verified : 14
SQL statements improved (improvement factor) : 14 (167664.6x)
SQL plan baselines created : 0
Overall improvement factor : 167664.6x
-------------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
Unused indexes : 0
Space used : 0 B
Unusable indexes : 0
-------------------------------------------------------------------------------
INDEX DETAILS
-------------------------------------------------------------------------------
1. The following indexes were created:
-------------------------------------------------------------------------------
----------------------------------------------------------------------
| Owner | Table | Index | Key | Type | Properties |
----------------------------------------------------------------------
| DEMO | MYSALES | SYS_AI_bmqt0qthw74kg | ID | B-TREE | NONE |
----------------------------------------------------------------------
-------------------------------------------------------------------------------
VERIFICATION DETAILS
-------------------------------------------------------------------------------
1. The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name : DEMO
SQL ID : 06wuaj97jms49
SQL Text : select * from mysales where id=4713
Improvement Factor : 167667x
Execution Statistics:
-----------------------------
Original Plan Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s): 379501 3634
CPU Time (s): 377495 854
Buffer Gets: 167667 4
Optimizer Cost: 45698 4
Disk Reads: 0 2
Direct Writes: 0 0
Rows Processed: 1 1
Executions: 1 1
PLANS SECTION
--------------------------------------------------------------------------------
-------------
- Original
-----------------------------
Plan Hash Value : 3597614299
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 45698 | |
| 1 | TABLE ACCESS STORAGE FULL | MYSALES | 1 | 56 | 45698 | 00:00:02 |
--------------------------------------------------------------------------------
- With Auto Indexes
-----------------------------
Plan Hash Value : 2047064025
--------------------------------------------------------------------------------
-----------------------
| Id | Operation | Name | Rows | By
tes | Cost | Time |
--------------------------------------------------------------------------------
-----------------------
| 0 | SELECT STATEMENT | | 1 |
56 | 4 | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | MYSALES | 1 |
56 | 4 | 00:00:01 |
| * 2 | INDEX RANGE SCAN | SYS_AI_bmqt0qthw74kg | 1 |
| 3 | 00:00:01 |
--------------------------------------------------------------------------------
-----------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("ID"=4713)
Notes
-----
- Dynamic sampling used for this statement ( level = 11 )
...
...
...
By reviewing the generated Automatic Indexing report we can now configure automatic indexing to create any new auto indexes as VISIBLE indexes so that they can be used in SQL statements.
We can allocate a dedicated tablespace to store any automatic indexes which will be created and we can also stipulate a quota in the tablespace which can be used for creating any automatic indexes.
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE','TEST_IND');
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
PL/SQL procedure successfully completed.
We now execute the same queries once again against the 20 million row MYSALES table.
Now a new index has been automatically created – note the execution plan.
The index has also been created in assigned tablespace for automatic indexes.
SQL> select * from mysales where id=4711;
4711 4712 Samsung Galaxy S7 1 711 5000 08-JUL-19 14-JAN-05
SQL> select * from table (dbms_xplan.display_cursor);
SQL_ID fc177w86zpdbb, child number 1
-------------------------------------
select * from mysales where id=4711
Plan hash value: 2047064025
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| MYSALES | 1 | 56 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SYS_AI_bmqt0qthw74kg | 1 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=4711)
SQL> select owner,tablespace_name from dba_indexes
where index_name='SYS_AI_bmqt0qthw74kg';
OWNER
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
DEMO
TEST_DATA
Generate the report on automatic indexing (by default for the past 24 hour period).
Note now that the report shows that the indexes have been created as visible indexes.
SQL> SELECT DBMS_AUTO_INDEX.report_activity() FROM dual;
DBMS_AUTO_INDEX.REPORT_ACTIVITY()
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start : 08-JUL-2019 11:41:53
Activity end : 09-JUL-2019 11:41:53
Executions completed : 6
Executions interrupted : 0
Executions with fatal error : 0
-------------------------------------------------------------------------------
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
DBMS_AUTO_INDEX.REPORT_ACTIVITY()
--------------------------------------------------------------------------------
Index candidates : 1
Indexes created (visible / invisible) : 1 (1 / 0)
Space used (visible / invisible) : 394.26 MB (394.26 MB / 0 B)
Indexes dropped : 0
SQL statements verified : 14
SQL statements improved (improvement factor) : 14 (167664.6x)
SQL plan baselines created : 0
Overall improvement factor : 167664.6x
-------------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES)
DBMS_AUTO_INDEX.REPORT_ACTIVITY()
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Unused indexes : 0
Space used : 0 B
Unusable indexes : 0
-------------------------------------------------------------------------------
INDEX DETAILS
-------------------------------------------------------------------------------
1. The following indexes were created:
-------------------------------------------------------------------------------
----------------------------------------------------------------------
DBMS_AUTO_INDEX.REPORT_ACTIVITY()
--------------------------------------------------------------------------------
| Owner | Table | Index | Key | Type | Properties |
----------------------------------------------------------------------
| DEMO | MYSALES | SYS_AI_bmqt0qthw74kg | ID | B-TREE | NONE |
----------------------------------------------------------------------
-------------------------------------------------------------------------------
VERIFICATION DETAILS
-------------------------------------------------------------------------------
1. The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name : DEMO
DBMS_AUTO_INDEX.REPORT_ACTIVITY()
--------------------------------------------------------------------------------
SQL ID : 06wuaj97jms49
SQL Text : select * from mysales where id=4713
Improvement Factor : 167667x
Execution Statistics:
-----------------------------
Original Plan Auto Index Plan
DBMS_AUTO_INDEX.REPORT_ACTIVITY()
--------------------------------------------------------------------------------
---------------------------- ----------------------------
Elapsed Time (s): 379501 3634
CPU Time (s): 377495 854
Buffer Gets: 167667 4
Optimizer Cost: 45698 4
Disk Reads: 0 2
Direct Writes: 0 0
Rows Processed: 1 1
Executions: 1 1
DBMS_AUTO_INDEX.REPORT_ACTIVITY()
--------------------------------------------------------------------------------
PLANS SECTION
--------------------------------------------------------------------------------
-------------
- Original
-----------------------------
Plan Hash Value : 3597614299
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
DBMS_AUTO_INDEX.REPORT_ACTIVITY()
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 45698 | |
| 1 | TABLE ACCESS STORAGE FULL | MYSALES | 1 | 56 | 45698 | 00:00:02 |
--------------------------------------------------------------------------------
- With Auto Indexes
DBMS_AUTO_INDEX.REPORT_ACTIVITY()
--------------------------------------------------------------------------------
-----------------------------
Plan Hash Value : 2047064025
--------------------------------------------------------------------------------
-----------------------
| Id | Operation | Name | Rows | By
tes | Cost | Time |
--------------------------------------------------------------------------------
-----------------------
| 0 | SELECT STATEMENT | | 1 |
56 | 4 | 00:00:01 |
DBMS_AUTO_INDEX.REPORT_ACTIVITY()
--------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | MYSALES | 1 |
56 | 4 | 00:00:01 |
| * 2 | INDEX RANGE SCAN | SYS_AI_bmqt0qthw74kg | 1 |
| 3 | 00:00:01 |
--------------------------------------------------------------------------------
-----------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("ID"=4713)
DBMS_AUTO_INDEX.REPORT_ACTIVITY()
--------------------------------------------------------------------------------
Notes
-----
- Dynamic sampling used for this statement ( level = 11 )
