Introduction
This blog post introduces you to the main concepts in automatic indexing and answers the questions that I’ve been asked the most.
How does automatic indexing work?
Auto indexing relies on a task that is invoked periodically in the background. The task creates a set of candidate “auto” indexes in each iteration based on column usage information (columns used in SQL statement predicates). These indexes are initially INVISIBLE and UNUSABLE: they are metadata only and invisible to application SQL. A subset of application SQL captured in the automatic SQL tuning set (see below) is used to validate whether candidate indexes are worth building. The validation consists of two steps:
- Compilation (to check whether the optimizer would choose the new indexes)
- Execution (to check the performance of the SQL statements after building the indexes)
SQL statements used to validate index performance (from the automatic SQL tuning set) are subject to a 30-day testing cycle, so once used, there is a 30-day period before SQL statements are potential candidates for re-test.
After the validation, there are two possible outcomes:
- If SQL statement performance is improved, all indexes built in the current iteration are marked as visible, and any SQL statements that suffer performance regression will acquire SQL plan baselines to prevent them from using the poorer plan.
- If no SQL statements have significantly improved performance, the newly-created auto indexes will remain invisible. Indexes built without delivering performance benefit will be made unusable after 24 hours, reducing the number of auto indexes maintained in the system.
Index usage is monitored, and an index will be dropped if it has not been used for 53 weeks (by default).
Checking the automatic SQL tuning set
Historical SQL execution statistics for workload queries are critical for database automation. In addition, a record of workload queries is essential to measure the performance impact of making changes in the database. For this reason, a database infrastructure component called the automatic SQL Tuning Set (ASTS) was conceived.
The ASTS is a system-maintained SQL tuning set called SYS_AUTO_STS that acts as a source of historic SQL performance metrics and execution plans. SQL statements captured can be seen in the view DBA_SQLSET_STATEMENTS:
select sql_id,sql_text
from dba_sqlset_statements
where sqlset_name = ‘SYS_AUTO_STS’;
There is a dedicated task for populating the SQL tuning set. It is important that the task is running for automatic clustering to function correctly. This can be checked as follows:
select task_name,enabled
from dba_autotask_schedule_control
where dbid = sys_context(‘userenv’,’con_dbid’)
and task_name = ‘Auto STS Capture Task’;
TASK_NAME ENABLED
——————————————————- ——-
Auto STS Capture Task TRUE
From Oracle Database 21c, the new view DBA_AUTOTASK_SETTINGS view exposes task settings instead of DBA_AUTOTASK_SCHEDULE_CONTROL.
The automatic SQL tuning set task will start without manual intervention when automatic indexing is enabled, so there is no need to start it manually. However, for your reference, it can be started as follows:
BEGIN
dbms_auto_task_admin.enable(client_name=>’Auto STS Capture Task’,
operation =>null,
window_name=>null);
END;
/
You must not disable the automatic SQL tuning set task if you want to use automatic indexing.
Checking and configuring the automatic indexing background task
A background task implements automatic indexing. It can be seen as follows:
select task_name,enabled,max_run_time
from dba_autotask_schedule_control
where dbid = sys_context(‘userenv’,’con_dbid’)
and task_name = ‘Auto Index Task’;
TASK_NAME ENABLED MAX_RUN_TIME
——————————————————– ——- ————
Auto Index Task TRUE 3600
The task is automatically enabled when auto-indexing mode is enabled with IMPLEMENT (see below). The automatic SQL tuning set task is also enabled.
The task has a maximum run time of 3600 seconds. For systems with large indexes, it may be necessary to increase this as follows:
exec dbms_auto_task_admin.modify_autotask_setting(‘Auto Index Task’, ‘MAX RUN TIME’, 7200)
You can assess the need for increasing the maximum run time by inspecting the elapsed_time value. If it is consistently matching the maximum run time, increase ‘MAX RUN TIME’:
select task_name,elapsed_time
from dba_autotask_schedule_control
where dbid = sys_context(‘userenv’,’con_dbid’)
and task_name = ‘Auto Index Task’;
TASK_NAME ELAPSED_TIME
—————————————————————- ————
Auto Index Task 3610
Controlling automatic indexing
The DBMS_AUTO_INDEX.CONFIGURE API is provided to configure automatic indexing. In Exadata environments, you will typically adjust AUTO_INDEX_DEFAULT_TABLESPACE:
exec dbms_auto_index.configure(‘auto_index_default_tablespace’,’app_ind_ts’)
You can target auto indexes at specific schemas using inclusions:
begin
— include
dbms_auto_index.configure(
parameter_name => ‘auto_index_schema’,
parameter_value => ‘HR’,
allow => TRUE);
end;
/
…or exclusions:
begin
— exclude
dbms_auto_index.configure(
parameter_name => ‘auto_index_schema’,
parameter_value => ‘SH’,
allow => FALSE);
end;
/
It is useful to adjust the report retention period to suit your requirements. Automatic indexing can generate significant metadata in the advisor framework (stored in SYSAUX)). You may want to reduce the reporting history period to reduce space consumption. In the following example, it’s set to 31 days (the default is 373):
exec dbms_auto_index.configure(‘auto_index_report_retention’,’31’)
By default, unused auto index are dropped after 373 days. Manually created indexes are not dropped by default, but a drop period can be set: six-hundred days in this example:
exec dbms_auto_index.configure(‘auto_index_retention_for_manual’,’600′)
Enable automatic indexing as follows:
exec dbms_auto_index.configure(‘auto_index_mode’,’implement’)
Disable automatic indexing as follows:
exec dbms_auto_index.configure(‘auto_index_mode’,’off’)
Note, that setting automatic indexing OFF disables housekeeping tasks, such as repository purging and dropping unused indexes.
You can check the current configuration using:
SQL> select parameter_name, parameter_value from dba_auto_index_config order by 1;
PARAMETER_NAME PARAMETER_VALUE
---------------------------------------- ----------------------------------------
AUTO_INDEX_COMPRESSION ON
AUTO_INDEX_DEFAULT_TABLESPACE
AUTO_INDEX_MODE IMPLEMENT
AUTO_INDEX_REPORT_RETENTION 373
AUTO_INDEX_RETENTION_FOR_AUTO 373
AUTO_INDEX_RETENTION_FOR_MANUAL
AUTO_INDEX_SCHEMA
AUTO_INDEX_SPACE_BUDGET 100
Optimizer statistics
Auto indexing test executes workload SQL and it demands that table optimizer statistics are not stale. Make sure optimizer statistics are fresh and maintained (using the high-frequency statstics gathering task, the maintenance window task (where active), or manually).
Evaluating automatic indexing with report-only mode
Automatic indexing can be initiated in a report-only mode as follows:
exec dbms_auto_index.configure(‘auto_index_mode’,’report only’)
In this mode, indexes will be created and tested but will not be visible to the application. Since valid indexes are created during the process, auto indexing may affect DML performance to some degree even in report-only mode. Query performance will remain unaffected because the indexes remain invisible. If the auto indexing mode is changed to IMPLEMENT, valid, invisible indexes are made visible.
If you want to evaluate auto indexing without modifying (for example) a production system, then create a clone. If the application workload is present in the automatic SQL tuning set, the clone will have it too. Enabling auto indexing on the clone will trigger the process of auto index creation. If you want to copy automatic indexes created on the clone, there is an example of how to do this in the Oracle Github repository.
The automatic index report
Reports can be retrieved using REPORT_LAST_ACTIVITY and REPORT_ACTIVITY. For example, the last two days can be reported as follows:
set linesize 300
set trims on
set pagesize 1000
set long 10000000
column report_hist format a120
var rep clob
exec :rep := dbms_auto_index.report_activity(activity_start=>sysdate-2,section=>’ALL’,level=>’ALL’)
spool report_hist
select :rep report from dual;
spool off
— A more readable HTML version can be generated as follows
BEGIN
:rep := dbms_auto_index.report_activity(activity_start=>sysdate-2,
section=>’ALL’,
level=>’ALL’,
type=>’HTML’);
END;
/
spool report_hist.html
select :rep report from dual;
spool off
Viewing automatic indexing task status
Timing and status information is exposed in the data dictionary view DBA_AUTO_INDEX_EXECUTIONS.
Name Null? Type ----------------------------------------- -------- ---------------------------- EXECUTION_NAME NOT NULL VARCHAR2(128) EXECUTION_START DATE EXECUTION_END DATE ERROR_MESSAGE VARCHAR2(4000) STATUS VARCHAR2(11)
Indentifying automatic indexes and understanding index states
Do not be alarmed if you see a large number of INVISIBLE and/or UNUSABLE indexes. This is normal!
Indexes can exist in a variety of states. This can be seen as follows:
SQL> select index_name,status,visibility from dba_indexes;
Automatic indexes have a system-generated name. You can identify them as follows:
SQL> select index_name,status,visibility from dba_indexes where auto = ‘YES’;
INDEX_NAME STATUS VISIBILITY
———- ———– —————
SYS_AI_0rn9u2kmxxbs7 VALID VISIBLE
SYS_AI_1rt4u7dmvaas8 VALID INVISIBLE
SYS_AI_9aa4u1keeagt1 UNUSABLE INVISIBLE
In the context of automatic indexing, iindex states have the following meaning:
| State |
Meaning |
| VALID, INVISIBLE A usable, invisible index |
The index is not visible to the application workload and was built in anticipation of it being of performance benefit. However, auto indexing test execution (if it has completed) established that the index did not significantly improve performance, so it was not made visible to the application workload. After 24 hours, the index will be made UNUSABLE, INVISIBLE (provided the automatic indexing task is not disabled). |
| VALID, VISIBLE A usable, visible index |
The index improves SQL performance and is visible to the application workload. |
| UNUSABLE, INVISIBLE An unusable, invisible index |
The indexed column (or columns) are used in SQL predicates, but the optimizer chose not to use the indexes during the validation step. The index remains as metadata-only, so there is no overhead associated with index maintenance, and the index is not visible to the application workload. Or: The index was previously VALID, INVISIBLE and was not found to be beneficial, and after 24 hours was made UNUSABLE. Or: The auto index task max runtime may not be long enough. See ‘Checking and configuring the automatic indexing background task’ above. |
If automatic indexing is set to ‘REPORT ONLY’ mode, indexes are still created (both VALID and UNUSABLE), but they won’t be made visible to the application workload.
Mixing manual and automatic indexes
You do not need to drop manually created indexes when you enable automatic indexing. Auto indexes are created on an as-needed basis, measuring the performance of SQL statements to decide whether any additional indexes will deliver a performance benefit.In addition, you can create or drop manual indexes any time you like.
By default, manually created indexes are never dropped by automatic indexing. Nevertheless, if you want the feature to identify and drop unused manual indexes you can use DBMS_AUTO_INDEXE.CONFIGURE to set an AUTO_INDEX_RETENTION_FOR_MANUAL period.
Function-based auto indexes
It is common to encounter SQL statements that include predicates similar to the following:
- select col from tab where upper(customer_name) = ‘SMITH’;
- select col from emp where nvl(empno,mgr) > 7800;
The performance of queries like this can be improved significantly using function-based indexes:
- CREATE INDEX cust_name_idx ON user_data (UPPER(customer_name));
- CREATE INDEX nvl_idx ON user_data (NVL(empno,mgr));
Automatic indexing from Oracle Database 23ai leverages expression tracking to identify functions used in SQL predicates. It will create and test candidate functional indexes.
Dropping automatic indexes
Automatic indexes cannot be dropped using the DROP INDEX DDL command. Instead, DROP_AUTO_INDEXES is provided instead:
exec dbms_auto_index.drop_auto_indexes(‘SH’,'”SYS_AI_612ud3j5ngf0c”‘,TRUE)
Automatic indexes have a mixed-case name, so it is important to include double quotation marks. The API will accept NULL for name and schema. For example, the following command will drop all automatic indexes for the SH schema. This is especially useful if you intend to test automatic indexing multiple times on the same schema. You will need to drop indexes with allow_recreate=>TRUE so that the test will re-run successfully.
exec dbms_auto_index.drop_auto_indexes(‘SH’,NULL,TRUE)
The third parameter, allow_recreate, is important. TRUE means that auto-indexing will recreate the index if it is deemed beneficial again later on. FALSE means that an automatic index will never be recreated.
If an index was dropped using this command:
exec dbms_auto_index.drop_auto_indexes(‘SH’,'”SYS_AI_612ud3j5ngf0c”‘,FALSE)
It is possible to undo the decision (to prevent recreation) and allow the index to be recreated as follows. Even though the index will not exist, the command will succeed:
exec dbms_auto_index.drop_auto_indexes(‘SH’,'”SYS_AI_612ud3j5ngf0c”‘,TRUE)
Automatic index types
Automatic indexes are currently non-unique b-tree only. Partitioned indexes are local if the index key is prefixed with the same columns as the partition key, global otherwise.
More about schema and table inclusion and exclusion
You can include and exclude specific schemas using the AUTO_INDEX_SCHEMA parameter in DBMS_AUTO_INDEX.CONFIGURE.
Oracle Database 21c introduced AUTO_INDEX_TABLE. At the time of writing, this has not been backported to Oracle Database 19c (including Oracle Autonomous Database).
Automatic Indexing On-demand
There’s a new API call in Oracle Database 23ai that allows you to invoke automatic indexing on-demand rather than relying on the auto task.
See DBMS_AUTO_INDEX.RECOMMEND.
Auto Indexing for Your Own SQL Tuning Set Workload
Sometimes, you may want collect a workload in your own SQL tuning set and use this to generate automatic indexes or see how the feature behaves in general. To do this, you need to execute the SQL in your SQL tuning while auto indexing is enabled. SQL performance analyzer (SPA) can do this. However, there is a snag: SPA appends the SQL it runs with a “SQL Analyze” comment, and auto indexing ignores SQL that’s annotated like this. There’s a way to prevent this comment being added. Here’s an example:
declare
tname varchar2(100);
begin
tname := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name=>'MYSQLSET')
dbms_sqlpa.set_analysis_task_parameter(tname,
'_process_context',
'<process_ctrl><parameter name="user_replay">1</parameter></process_ctrl>');
dbms_sqlpa.execute_analysis_task(
task_name=>tname, execution_type=>'test execute',
execution_params=>dbms_advisor.arglist('time_limit','7200'))
end;
/
I’ve set a time limit of 7200 seconds, and SPA will execute the SQL in the MYSQLSET tuning set without adding /* SQL Analyze */ to the SQL. Make sure you’ve enable automatic indexing before submitting the task, and then wait for the results.
DML awareness
Indexes do not come for free. In general, indexes will reduce the cost of executing queries by will increase the cost of some DML operations. Prior to Oracle Database 23ai, DML was not costed, so systems with very high insert, update and delete rates could see performance degradations in some cases. Oracle Database 23ai weighs the increased cost of DML (when indexes are present) against the reduced cost of executing queries on relevant tables. When enabled, indexes may not be created on tables subject to high DML activity.
DML costing is enabled by default, and it can be turned ON and OFF as follows:
exec dbms_auto_index.configure(‘auto_index_include_dml_cost’, ‘ON’)
exec dbms_auto_index.configure(‘auto_index_include_dml_cost’, ‘OFF’)