Automatic indexing - AUTO_INDEX_TABLE

 
One of the more common questions regarding the Automatic Indexing (AI) are the areas of concern around having large and expensive automatic index build operations suddenly Occurring in one database and the impact this may have on overall performance.
 
Additionally, the questions around scenarios where very large automatic indexes are suddenly being build, but then get cancelled because they couldn’t get completed in the default (3600 second) allocated time, only for them to be attempted to be built again and for this cycle to be forever ongoing.
 
Sometimes we may not necessarily want to have indexes built on specific tables, perhaps they are massive, and we want to control when and how indexes on such tables are built. Perhaps because we are satisfied such table are already indexed very well. 
 
The impact of overall database performance of the AI Task creating large indexes is reduced, by Oracle only allowing one index to be created serially at any given time.
 
To address all these concerns Oracle has now introduced a new configuration (available in Oracle 21c and above) option within the DBMS_AUTO_INDEX.CONFIGURE procedure, AUTO_INDEX_TABLE. This now allow us to explicitly state which table to either included / exclude from the AI process. Where as in the prior version of Oracle we had the ability to state the included / exclude at schema level and not at table level.
 
demo@ATP21C> select *
  2  from dba_auto_index_config
  3  where parameter_name ='AUTO_INDEX_TABLE';
 
PARAMETER_NAME       PARAMETER_VALUE    LAST_MODIFIED   MODIF
-------------------- ------------------ --------------- -----
AUTO_INDEX_TABLE                       
 
To add the EMP table to an exclusion list of tables then
 
demo@ATP21C> exec dbms_auto_index.configure('AUTO_INDEX_TABLE','ai_demo.EMP',false);
 
PL/SQL procedure successfully completed.
 
To add the DEPT table to an inclusion list of tables then
 
demo@ATP21C> exec dbms_auto_index.configure('AUTO_INDEX_TABLE','ai_demo.DEPT',true);
 
PL/SQL procedure successfully completed.
 
To view the current AI settings
 
demo@ATP21C> select *
  2  from dba_auto_index_config
  3  where parameter_name ='AUTO_INDEX_TABLE';
 
PARAMETER_NAME       PARAMETER_VALUE                                                   LAST_MODIFIED                  MODIF
-------------------- ----------------------------------------------------------------- ------------------------------ -----
AUTO_INDEX_TABLE     table IN ("AI_DEMO"."DEPT") AND table NOT IN ("AI_DEMO"."EMP")    18-AUG-22 02.01.16.000000 PM   DEMO
 
Finally, to remove all tables from both exclusion or inclusion table list, then
 
demo@ATP21C> exec dbms_auto_index.configure('AUTO_INDEX_TABLE',null);
 
PL/SQL procedure successfully completed.
 
demo@ATP21C> select *
  2  from dba_auto_index_config
  3  where parameter_name ='AUTO_INDEX_TABLE';
 
PARAMETER_NAME       PARAMETER_VALUE    LAST_MODIFIED                  MODIF
-------------------- ------------------ ------------------------------ -----
AUTO_INDEX_TABLE                        18-AUG-22 02.01.34.000000 PM   DEMO
 
 
This means we can now more safely deploy AI, by determining explicitly which tables to include or exclude.
 
To support large table that can take potentially longer to build than the default 3600 seconds for the AI task to complete, we can also change the MAX_RUN_TIME of the AI tasks as follows.
 
demo@ATP21C> col dbid noprint
demo@ATP21C> select *
  2  from dba_autotask_settings
  3  where task_name ='Auto Index Task';
 
   TASK_ID TASK_NAME          INTERVAL MAX_RUN_TIME ENABL
---------- ----------------- --------- ------------ -----
         3 Auto Index Task         900         3600 TRUE
 
demo@ATP21C> exec dbms_auto_task_admin.modify_autotask_setting('Auto Index Task','MAX RUN TIME',7200);
 
PL/SQL procedure successfully completed.
 
demo@ATP21C> select *
  2  from dba_autotask_settings
  3  where task_name ='Auto Index Task';
                                             
   TASK_ID TASK_NAME          INTERVAL MAX_RUN_TIME ENABL
---------- ----------------- --------- ------------ -----
         3 Auto Index Task         900         7200 TRUE
                                             
demo@ATP21C>