Warning: Creating default object from empty value in /home/customer/www/gavinsoorma.com/public_html/wp-content/themes/specular/admin/inc/class.redux_filesystem.php on line 29
Oracle 19c New Feature Automatic Indexing | Oracle DBA – Tips and Techniques
News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Oracle 19c New Feature Automatic Indexing

  • Posted by Gavin Soorma
  • On July 9, 2019
  • 0 Comments

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 )
 3

0 Comments

Leave Reply

Your email address will not be published. Required fields are marked *