Remark:
Oracle’s automatic indexing can only be implemented on an all-in-one machine. It cannot be used if it is not an all-in-one machine and an error will be reported.
SYS@test>EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_COMPRESSION', 'ON');
BEGIN DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_COMPRESSION', 'ON'); END;
*
ERROR at line 1:
ORA-40216: feature not supported
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 10888
ORA-06512: at "SYS.DBMS_AUTO_INDEX", line 301
ORA-06512: at line 1
SYS@test>
Reference documentation:
Database Administrator’s Guide
21 Managing Indexes
21.7 Managing Auto Indexes
PL/SQL Packages and Types Reference
31 DBMS_AUTO_INDEX
19c automatically creates index function (Doc ID 2533822.1)
[Automatic indexing] How To Enable AUTO_INDEX_COMPRESSION on Exadata (Doc ID 2610685.1)
ORA-40216 When Using Auto Index Feature (Doc ID 2570076.1)
Automatic Indexing – Autonomous Database (Shared) (Doc ID 2764331.1)
How automatic indexing works:
This section describes how automatic indexing works.
The automatic indexing process runs in the background every 15 minutes and performs the following operations:
-
Identify auto index candidates
Auto index candidates are identified based on the usage of table columns in SQL statements.
Ensure that table statistics are up to date. Tables without statistics are not considered for auto indexing. Tables with stale statistics are not considered for auto indexing, if real-time statistics are not available.
-
Creates invisible auto indexes for the auto index candidates
The auto index candidates are created as invisible auto indexes, that is, these auto indexes cannot be used in SQL statements.
Automatic indexes can be single-column or multi-column. They are considered for the following:
- Table columns (including virtual columns)
- Partitioned and non-partitioned tables
-
Verifies invisible auto indexes against SQL statements
The invisible auto indexes are validated against SQL statements.
If the performance of SQL statements is improved by using these indexes, then the indexes are configured as visible indexes, so that they can be used in SQL statements.
If the performance of SQL statements is not improved by using these indexes, then the indexes are configured as unusable indexes and the SQL statements are blacklisted. The unusable indexes are later deleted by the automatic indexing process. The blacklisted SQL statements are not allowed to use auto indexes in future.
Note:
Auto indexes cannot be used by SQL statements that are running for the first time in a database.
-
Delete the unused auto indexes
The auto indexes that are not used for a long period are deleted.
Note:
By default, the unused auto indexes are deleted after 373 days. The period for retaining the unused auto indexes in a database can be configured using the
DBMS_AUTO_INDEX.CONFIGUREprocedure.
Configure automatic indexing
You can configure automatic indexing in an Oracle database using the DBMS_AUTO_INDEX.CONFIGURE procedure.
exec dbms_auto_index.configure(‘AUTO_INDEX_MODE’,’
AUTO_INDEX_MODE: Modes of operation of auto indexes. It can have one of the following values:
IMPLEMENT: In this mode, new auto indexes are created as visible indexes and any existing invisible auto indexes are also set to visible indexes. In this mode, auto indexes are available to be used in SQL statements.
REPORT ONLY: In this mode, new auto indexes are created as invisible indexes and are not available to be used in SQL statements.
OFF: Setting the mode to OFF prevents new auto indexes from being considered and created. However, it does not disable existing auto indexes.
DBA_AUTO_INDEX_CONFIG displays the current configuration parameter settings for automatic indexing
SQL> select parameter_name,parameter_value from dba_auto_index_config where parameter_name=’AUTO_INDEX_MODE’;
PARAMETER_NAME PARAMETER_VALUE
———————————- —————— ———-
AUTO_INDEX_MODE OFF
To enable automatic indexing in a database and creating any new auto indexes as visible indexes:
SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE','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 IMPLEMENT
To enable automatic indexing in a database and create any new auto indexes as invisible indexes:
SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE','REPORT ONLY');
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
To disable automatic indexing in a database so that no new auto indexes are created (existing auto indexes remain enabled):
SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE','OFF')
SQL> select parameter_name,parameter_value from dba_auto_index_config where parameter_name='AUTO_INDEX_MODE';
PARAMETER_NAME PARAMETER_VALUE
----------------------------------
AUTO_INDEX_MODE OFF
Start automatic indexing at the schema level
You can use the AUTO_INDEX_SCHEMA configuration setting to specify schemas that can use auto indexes.
When automatic indexing is enabled in a database, all the schemas in the database can use auto indexes by default.
The automatic indexing process manages two schema lists – the inclusion list and the exclusion list. The inclusion list contains the schemas that can use auto indexes. The exclusion list contains the schemas that cannot use auto indexes. Initially, both these lists are empty and all the schemas in the database can use auto indexes when automatic indexing is enabled for a database.
execute dbms_auto_index.configure(‘AUTO_INDEX_SCHEMA’,’
TRUE: Add the specified schema to the inclusion list.
FALSE: Add the specified schema to the exclusion list.
NULL: Remove the specified schema from the list to which it is currently added
If the inclusion list contains at least one schema, then only the schemas listed in the inclusion list can use auto indexes:
SQL> select parameter_name,parameter_value from dba_auto_index_config where parameter_name='AUTO_INDEX_SCHEMA';
PARAMETER_NAME PARAMETER_VALUE
----------------------------------
AUTO_INDEX_SCHEMA
SQL> execute dbms_auto_index.configure('AUTO_INDEX_SCHEMA','ADMIN',TRUE);
SQL> select parameter_name,parameter_value from dba_auto_index_config where parameter_name='AUTO_INDEX_SCHEMA';
PARAMETER_NAME PARAMETER_VALUE
----------------------------------
AUTO_INDEX_SCHEMA schema IN (ADMIN)
In this case ADMIN schema only enabled with auto indexes and all other schemas excluded.
If the inclusion list is empty and the exclusion list contains at least one schema, then all the schemas can use auto indexes, except the schemas listed in the exclusion list:
SQL> execute dbms_auto_index.configure('AUTO_INDEX_SCHEMA','ADMIN',FALSE);
SQL> select parameter_name,parameter_value from dba_auto_index_config where parameter_name='AUTO_INDEX_SCHEMA';
PARAMETER_NAME PARAMETER_VALUE
----------------------------------
AUTO_INDEX_SCHEMA schema NOT IN (ADMIN)
In this case ADMIN schema excluded from auto indexes feature and all other schemas included.
If both the lists (the inclusion list and the exclusion list) contain at least one schema, then all the schemas can use auto indexes, except the schemas listed in the exclusion list:
SQL> execute dbms_auto_index.configure('AUTO_INDEX_SCHEMA','ADMIN',TRUE);
SQL> execute dbms_auto_index.configure('AUTO_INDEX_SCHEMA','TCUSER',FALSE);
SQL> select parameter_name,parameter_value from dba_auto_index_config where parameter_name='AUTO_INDEX_SCHEMA';
PARAMETER_NAME PARAMETER_VALUE
---------------------------------- ------------------ ----------------------------------------
AUTO_INDEX_SCHEMA schema IN (ADMIN) AND schema NOT IN (TCUSER)
In this case, TCUSER is excluded from auto indexes feature and all other schemas included.
Generate automatic index report
You can generate reports related to automatic indexing operations in an Oracle database using the REPORT_ACTIVITY and REPORT_LAST_ACTIVITY functions of the DBMS_AUTO_INDEX package.
Generating a report of automatic indexing operations for last 24 hours
SQL> set long 1000000 SQL> select dbms_auto_index.report_activity from dual; REPORT_ACTIVITY -------------------------------------------------- ---------------------------------- GENERAL INFORMATION -------------------------------------------------- -------------------------- Activity start: 10-APR-2021 20:14:05 Activity end: 11-APR-2021 20:14:05 Executions completed: 95 Executions interrupted : 0 Executions with fatal error: 0 -------------------------------------------------- -------------------------- SUMMARY (AUTO INDEXES) -------------------------------------------------- -------------------------- REPORT_ACTIVITY -------------------------------------------------- ---------------------------------- Index candidates : 0 Indexes created: 0 Space used: 0 B Indexes dropped : 0 SQL statements verified : 0 SQL statements improved : 0 SQL plan baselines created : 0 Overall improvement factor: 0x -------------------------------------------------- -------------------------- SUMMARY (MANUAL INDEXES) REPORT_ACTIVITY -------------------------------------------------- ---------------------------------- -------------------------------------------------- -------------------------- Unused indexes: 0 Space used: 0 B Unusable indexes : 0 -------------------------------------------------- -------------------------- ERRORS -------------------------------------------------- ---------------------------------- No errors found. -------------------------------------------------- ----------------------------------
Generating a report of automatic indexing operations for a specific period
SQL> set long 1000000
SQL> select dbms_auto_index.report_activity(activity_start => TO_TIMESTAMP('2021-04-01', 'YYYY-MM-DD'), activity_end => TO_TIMESTAMP('2021-04-11', 'YYYY-MM-DD') ) from dual;
DBMS_AUTO_INDEX.REPORT_ACTIVITY(ACTIVITY_START=>TO_TIMESTAMP('2021-04-01','YYYY-
-------------------------------------------------- ----------------------------------
GENERAL INFORMATION
-------------------------------------------------- --------------------------
Activity start : 01-APR-2021 00:00:00
Activity end: 11-APR-2021 00:00:00
Executions completed: 951
Executions interrupted : 0
Executions with fatal error: 0
-------------------------------------------------- --------------------------
SUMMARY (AUTO INDEXES)
-------------------------------------------------- --------------------------
DBMS_AUTO_INDEX.REPORT_ACTIVITY(ACTIVITY_START=>TO_TIMESTAMP('2021-04-01','YYYY-
-------------------------------------------------- ----------------------------------
Index candidates : 0
Indexes created: 0
Space used: 0 B
Indexes dropped : 0
SQL statements verified : 0
SQL statements improved : 0
SQL plan baselines created : 0
Overall improvement factor: 0x
-------------------------------------------------- --------------------------
SUMMARY (MANUAL INDEXES)
DBMS_AUTO_INDEX.REPORT_ACTIVITY(ACTIVITY_START=>TO_TIMESTAMP('2021-04-01','YYYY-
-------------------------------------------------- ----------------------------------
-------------------------------------------------- --------------------------
Unused indexes: 0
Space used: 0 B
Unusable indexes : 0
-------------------------------------------------- --------------------------
ERRORS
-------------------------------------------------- ----------------------------------
-------------
No errors found.
-------------------------------------------------- ----------------------------------
END