select PARAMETER_NAME,PARAMETER_VALUE from DBA_AUTO_INDEX_CONFIG; PARAMETER_NAME PARAMETER_VALUE ---------------------------------------- ---------------- AUTO_INDEX_SCHEMA AUTO_INDEX_DEFAULT_TABLESPACE AUTO_INDEX_SPACE_BUDGET 100 AUTO_INDEX_REPORT_RETENTION 373 AUTO_INDEX_RETENTION_FOR_AUTO 373 AUTO_INDEX_RETENTION_FOR_MANUAL AUTO_INDEX_MODE OFF AUTO_INDEX_COMPRESSION ONFor detail explanation on setting these parameters refer the Oracle documentation.
Auto indexing configuration could be set to read only mode where indexes are created in invisible state or implement mode where indexes are created visible mode and any invisible indexes prevoiusly created are also made visible if they improve the performance. To set the configuration to implement execute the following
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
PL/SQL procedure successfully completed.
select PARAMETER_NAME,PARAMETER_VALUE from DBA_AUTO_INDEX_CONFIG WHERE PARAMETER_NAME='AUTO_INDEX_MODE';
PARAMETER_NAME PARAMETER_
-------------------- ----------
AUTO_INDEX_MODE IMPLEMENTFor read only mode do the followingEXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');
select PARAMETER_NAME,PARAMETER_VALUE from DBA_AUTO_INDEX_CONFIG WHERE PARAMETER_NAME='AUTO_INDEX_MODE';
PARAMETER_NAME PARAMETER_VALUE
-------------------- --------------------
AUTO_INDEX_MODE REPORT ONLYTo see auto indexing in use, created a test case with two tables with parent child relationship. The child table did not have any indexes created on it. Ran a select statement which used child table as the driver, multiple times with different values.create table x (a number primary key, b number); create table y (c number,d number, foreign key (c) references x(a)); insert into x select level,mod(level,100) from dual connect by level < 100000; insert into x values (0,0); insert into y select mod(level,100000),level from dual connect by level < 400000; commit; select x.a,y.d from x,y where x.a=y.c and c=500;The past and current auto index executions could be viewd on DBA_AUTO_INDEX_EXECUTIONS. When an auto indexing job is running the status would be executing.
col EXECUTION_NAME format a30 col ERROR_MESSAGE format a10 select EXECUTION_NAME,to_char(EXECUTION_START,'YYYY-MM-DD HH24:MI') exec_st, to_char(EXECUTION_END,'YYYY-MM-DD HH24:MI') exec_end,ERROR_MESSAGE,STATUS from DBA_AUTO_INDEX_EXECUTIONS order by 2; EXECUTION_NAME EXEC_ST EXEC_END ERROR_MESSAGE STATUS ------------------------------------------------------------ ---------------- ---------------- -------------------- ----------- SYS_AI_2021-10-26/11:35:25 2021-10-26 11:35 2021-10-26 11:35 COMPLETED ... EXECUTION_NAME EXEC_ST EXEC_END ERROR_MESSAGE STATUS ------------------------------------------------------------ ---------------- ---------------- -------------------- ----------- SYS_AI_2021-10-26/11:35:25 2021-10-26 11:35 2021-10-26 11:35 COMPLETED SYS_AI_2021-10-26/12:07:36 2021-10-26 12:07 EXECUTING ... EXECUTION_NAME EXEC_ST EXEC_END ERROR_MESSAGE STATUS ------------------------------------------------------------ ---------------- ---------------- -------------------- ----------- SYS_AI_2021-10-26/11:35:25 2021-10-26 11:35 2021-10-26 11:35 COMPLETED SYS_AI_2021-10-26/12:07:36 2021-10-26 12:07 2021-10-26 12:13 COMPLETED
The auto indexing job could also be viewed on the performance hub.
The result of the auto indexing job could be viewed using the last activity report, either in text
set long 1000000 pagesize 0 select dbms_auto_index.report_last_activity() from dual;or in html
select dbms_auto_index.report_last_activity(type => 'HTML') from dual;Below is the output from an HTML report. The summary section shows out of two index candidates one was created as a visible index (when auto indexing mode is set to implement).
Index detail section tells the name of the index and on which table column it was created on.
The verification section shows the justification for creating the index, i.e. performance improvment gained due to the index.
Finally the plan section shows the change of query plan due to the auto index.
Even though the report says only one index was created querying the user_indexes shows that two auto indexes were created. One that was mentioned in the report and another one in invisible mode.
SQL> select table_name,INDEX_NAME,COMPRESSION,AUTO,CONSTRAINT_INDEX,SECONDARY,VISIBILITY from user_indexes; TABLE_NAME INDEX_NAME COMPRESSION AUT CON S VISIBILIT -------------------- -------------------- ------------- --- --- - --------- X SYS_C0027706 DISABLED NO YES N VISIBLE Y SYS_AI_614y39b1xnucg ADVANCED LOW YES NO N VISIBLE Y SYS_AI_6ztznk7h3fq0b ADVANCED LOW YES NO N INVISIBLEOn the otherhand if the auto indexing mode was report only then the index would be created as an invisible index. The report shows no overall performance improvment. It could be interpreted as no performance improvment until index is made visible.
At times in addition to an index the auto indexing job could create SQL baselines as well. These are also mentioned in the report. (below screenshot is from a different set of tables than mentioned above).
The indexes created by auto index job cannot be dropped with a drop statement. To drop these indexes dbms_auto_index PL/SQL package should be used.
select table_name,INDEX_NAME,COMPRESSION,AUTO,CONSTRAINT_INDEX,SECONDARY,VISIBILITY from user_indexes;
TABLE_NAME INDEX_NAME COMPRESSION AUT CON S VISIBILIT
-------------------- -------------------- ------------- --- --- - ---------
X SYS_C0027706 DISABLED NO YES N VISIBLE
Y SYS_AI_614y39b1xnucg ADVANCED LOW YES NO N VISIBLE
Y SYS_AI_6ztznk7h3fq0b ADVANCED LOW YES NO N INVISIBLE
SQL> drop index SYS_AI_6ztznk7h3fq0b;
SQL> drop index "SYS_AI_6ztznk7h3fq0b";
drop index "SYS_AI_6ztznk7h3fq0b"
*
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes
exec dbms_auto_index.drop_auto_indexes('ASANGA','"SYS_AI_6ztznk7h3fq0b"',TRUE);
select table_name,INDEX_NAME,COMPRESSION,AUTO,CONSTRAINT_INDEX,SECONDARY,VISIBILITY from user_indexes;
TABLE_NAME INDEX_NAME COMPRESSION AUT CON S VISIBILIT
-------------------- -------------------- ------------- --- --- - ---------
X SYS_C0027706 DISABLED NO YES N VISIBLE
Y SYS_AI_614y39b1xnucg ADVANCED LOW YES NO N VISIBLEThe dbms_auto_index package also offeres a method to remove all indexes from a table that isn't used for constraints. This method cannot be used to remove auto indexes.SQL> exec dbms_auto_index.drop_secondary_indexes('ASANGA','Y');
BEGIN dbms_auto_index.drop_secondary_indexes('ASANGA','Y'); END;
*
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes
ORA-06512: at "SYS.DBMS_AUTO_INDEX", line 456
ORA-06512: at "SYS.DBMS_AUTO_INDEX", line 456
ORA-06512: at "SYS.DBMS_AUTO_INDEX", line 452
ORA-06512: at line 1Also any invisible indexes created by the auto index job (say during report only mode) cannot be made visible. Only way to make them visible is to change the auto indexing mode to implement.SQL> select PARAMETER_NAME,PARAMETER_VALUE from DBA_AUTO_INDEX_CONFIG WHERE PARAMETER_NAME='AUTO_INDEX_MODE'; PARAMETER_NAME PARAMETER_VALUE -------------------- -------------------- AUTO_INDEX_MODE REPORT ONLY select table_name,INDEX_NAME,COMPRESSION,AUTO,CONSTRAINT_INDEX,SECONDARY,VISIBILITY from user_indexes; TABLE_NAME INDEX_NAME COMPRESSION AUT CON S VISIBILIT -------------------- -------------------- ------------- --- --- - --------- X SYS_C0027706 DISABLED NO YES N VISIBLE Y SYS_AI_614y39b1xnucg ADVANCED LOW YES NO N INVISIBLE Y SYS_AI_6ztznk7h3fq0b ADVANCED LOW YES NO N INVISIBLE SQL> alter index "SYS_AI_614y39b1xnucg" visible; alter index "SYS_AI_614y39b1xnucg" visible * ERROR at line 1: ORA-65532: cannot alter or drop automatically created indexesFinally as mentioned earlier drop_secondary_indexes method allows dropping all indexes in a table that isn't used for enforcing constraints. This is useful if all indexes (not used for constriant enforcement) are to be dropped and let auto indexing take care of creating indexes. Below show case the use of this method. On the tables below X is the parent table and Y is the child table. The AIDX index is on the foreign key column and AIDX2 is an index on another column not used for enforcing any constraints.
col index_name format a30 col table_name format a30 set line 1000 select table_name,INDEX_NAME,COMPRESSION,AUTO,CONSTRAINT_INDEX,SECONDARY,VISIBILITY from user_indexes; TABLE_NAME INDEX_NAME COMPRESSION AUT CON S VISIBILIT -------------------- -------------------- ------------- --- --- - --------- X SYS_C0027706 DISABLED NO YES N VISIBLE Y AIDX DISABLED NO NO N VISIBLE Y AIDX2 DISABLED NO NO N VISIBLEExecuting drop secondary index meethod on table X will not drop the index created to enforce the primary key (SYS_C0027706).
exec dbms_auto_index.drop_secondary_indexes('ASANGA','X');
PL/SQL procedure successfully completed.
TABLE_NAME INDEX_NAME COLUMN_NAM
---------- --------------- ----------
X SYS_C0027706 A
Y AIDX C
Y AIDX2 DOn the child table the index created on the foreign key column is not dropped. However, other index that is not enforcing any constraints is dropped.exec dbms_auto_index.drop_secondary_indexes('ASANGA','Y');
PL/SQL procedure successfully completed.
TABLE_NAME INDEX_NAME COLUMN_NAM
---------- --------------- ----------
X SYS_C0027706 A
Y AIDX C
