AUTOMATIC INDEXING IN ORACLE

INDEXING CHALLENGES

Indexes are useful for most types of workload and particularly critical for high performance in online transaction processing (OLTP) and operational data store (ODS) environments. However, identifying the best indexes is not straightforward and can require a considerable amount of manual labor. This is largely because index management is a continuous process. Applications are rarely static; they change in response to changing business requirements. For this reason, there will be changes in data volumes, of course, but also less obvious changes in data value distributions and the way data is organized, processed and accessed.

Traditionally, DBAs have been responsible to monitoring performance and deciding when and where to add, change or remove indexes in a tactical and often ad-hoc manner. This ad-doc approach to index maintenance is prone to error because it is almost impossible to quantify the effect any change – both positive and negative. This may lead to a database that has many more indexes than necessary, where indexes have been gradually added over time and there is a reluctance to remove any of them for fear of negative consequences. This will lead to an increase the system resources required to maintain indexes when data is modified and processed. In addition, over-indexed environments often suffer from less stable SQL execution plans as the sheer number of indexes make the optimizer's choice of index access path more and more finely balanced.

AUTOMATIC INDEXING

Automatic indexing addresses these issues. It is not a simple advisor, but instead it is an expert system that implements indexes based on what a performance engineer skilled in index tuning would do. The Oracle Database analyzes the application workload and identifies the queries that will benefit from additional indexes. In other words, it identifies candidate indexes and validates them before implementation, and the entire process is fully automatic.

Here is a summary of the workflow:

The steps can be summarized as follows:

Capture

 Periodically capture SQL statements from the application workload in a SQL tuning set. This tuning set called the automatic SQL tuning set (ASTS).

Identify

 Identify candidate indexes that may benefit the application workload.

 Creates unusable and invisible index candidates (this is a data dictionary metadata change only)

Verify

 The optimizer verifies which index candidates will be used by the captures SQL statements

 Materialize the successful candidates and measure their effect on the performance of the captured 

SQL statements

 The indexes remain invisible to the application workload and all verification is done outside the application workflow

Decide

 Indexes that are found to offer a significant performance improvement are made visible to the application workload.

 Indexes that are found to provide insufficient performance benefits remain invisible to the application workload.

 Automatic indexing offers a mixed solution where necessary. If there is an overall benefit in using a particular auto-index, then it will be marked visible. If individual queries suffer from a performance regression, SQL plan baselines are used to prevent them from using regressed plans.

Monitor

 SQL performance and index usage is continuously monitored. Indexes that have not been used for a configurable period of time will be dropped.

Configuring and Monitoring Automatic Indexing

Automatic indexing requires little to no manual intervention, but a package called DBMS_AUTO_INDEX package is provided for changing a small number of defaults. The feature can be enabled as follows:

SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE', 'IMPLEMENT')

And disabled:

SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE', 'OFF')

The number of days unused auto-indexes are retained is controlled as follows:

SQL> exec dbms_auto_index.configure('AUTO_INDEX_RETENTION_FOR_AUTO', '373')

It is possible to specify which schemas are subject to auto indexing:

SQL> -- Exclude SH and HR

SQL> exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'SH', FALSE)

SQL> exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'HR', FALSE)

SQL> -- Remove HR from exclusion list

SQL> exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'HR', NULL)

SQL> -- Remove all schemas from exclusion list

SQL> exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', NULL, TRUE)

SQL> -- Include SH for auto indexing but exclude HR

SQL> exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'SH', FALSE)

SQL> exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'HR', TRUE)

All parameter settings (and schemas that have been included and excluded) can be seen as follows:

SQL> select * from dba_auto_index_config;

A report on auto index activity can be generated. 

For example:

SQL> set linesize 300 trims on pagesize 1000 long 100000

SQL> column report format a120

SQL> SELECT dbms_auto_index.report_activity(sysdate-30,null,'text','all','all') report FROM dual;

Here is an extract from an example report:

REPORT

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

GENERAL INFORMATION

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

Activity start : 29-DEC-2018 05:09:37

Activity end : 28-JAN-2019 05:09:37

Executions completed : 743

Executions interrupted : 0

Executions with fatal error : 0

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

SUMMARY (AUTO INDEXES)

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

Index candidates : 16

Indexes created (visible / invisible) : 2 (2 / 0)

Space used (visible / invisible) : 268.44 MB (268.44 MB / 0 B)

Indexes dropped : 0

SQL statements verified : 6

SQL statements improved (improvement factor) : 3 (493.3x)

SQL plan baselines created : 0

Overall improvement factor : 247.4x

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

SUMMARY (MANUAL INDEXES)

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

Unused indexes : 0

Space used : 0 B

Unusable indexes : 0

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

INDEX DETAILS

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

1. The following indexes were created:

*: invisible

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

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

| Owner | Table | Index | Key | Type | Properties |

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

| AUTOI | FACT1 | SYS_AI_0rn9u2kmxxbs7 | F1 | B-TREE | NONE |

| AUTOI | FACT1 | SYS_AI_151bnmf3xsxaw | F2,F3 | B-TREE | NONE |

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

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

VERIFICATION DETAILS

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

1. The performance of the following statements improved:

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

Parsing Schema Name : AUTOI

SQL ID : 34xymh9usuxzz

SQL Text : select /* TESTQ3 */ sum(f4) from fact1 where f1 = 10

Improvement Factor : 2268.9x

PLANS SECTION

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

- Original

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

Plan Hash Value : 4087791341