7

Automatic Indexes: AUTO_INDEX_TABLE Configuration (“Without You”)

 2 years ago
source link: https://richardfoote.wordpress.com/2022/05/03/automatic-indexes-auto_index_table-configuration-without-you/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

Automatic Indexes: AUTO_INDEX_TABLE Configuration (“Without You”) May 3, 2022

Posted by Richard Foote in 21c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, AUTO_INDEX_TABLE, DBMS_AUTO_INDEX.CONFIGURE, Exadata, Oracle, Oracle 21c, Oracle Cloud, Oracle General, Oracle Indexes.
trackback

One of the more common questions I get regarding Automatic Indexing (AI) are areas of concern around having large and expensive automatic index build operations suddenly occurring in one’s database and the impact this may have on overall performance.

Additionally, I’ve had questions around scenarios where very large automatic indexes are suddenly being built, but then get canceled because they couldn’t complete in the default (3600 seconds, 1 hour) allocated time, only for them to be attempted to be built again and for this cycle to be forever ongoing.

And this is fair enough. You may not necessarily want to have indexes built on specific tables, perhaps because they’re massive and you want to control when and how indexes on such tables are built, perhaps because you’re satisfied that such tables are already indexed satisfactorily, etc. etc.

Note: the impact on 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.

However, to help address these concerns, Oracle has now (from Oracle Database 21c) introduced a new configuration option within the DBMS_AUTO_INDEX.CONFIGURE procedure, AUTO_INDEX_TABLE. This now allows us to explicitly state which tables we may wish to either include or exclude from the AI process. Previously, we only had the ability to state which schemas we wanted to in/exclude from the AI process.

To add the BOWIE.SALES table to an exclusion list:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_TABLE', ‘BOWIE.SALES’, FALSE);
PL/SQL procedure successfully completed.

To add the BOWIE.PRODUCTS table to an inclusion list:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_TABLE', ‘BOWIE.PRODUCTS', TRUE);
PL/SQL procedure successfully completed.

To view current AI settings:

SQL> select parameter_name, parameter_value from dba_auto_index_config;
PARAMETER_NAME                      PARAMETER_VALUE
----------------------------------- -----------------------------------------------------------------
AUTO_INDEX_COMPRESSION              ON
AUTO_INDEX_DEFAULT_TABLESPACE       USERDATA2
AUTO_INDEX_MODE                     IMPLEMENT
AUTO_INDEX_REPORT_RETENTION         100
AUTO_INDEX_RETENTION_FOR_AUTO       373
AUTO_INDEX_RETENTION_FOR_MANUAL
AUTO_INDEX_SCHEMA                   schema IN (BOWIE)
AUTO_INDEX_SPACE_BUDGET             100
AUTO_INDEX_TABLE                    table IN ("BOWIE"."PRODUCTS") AND table NOT IN ("BOWIE"."SALES")

To remove all tables from both inclusion/exclusion table lists:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_TABLE', NULL);
PL/SQL procedure successfully completed.

This means you can now more safely deploy AI, by determining explicitly which tables you wish to in/exclude.

Note if you wish to include large tables that can potentially take longer to build than the default 3600 seconds allowed for the AI task to complete, you can change the MAX_RUN_TIME of the AI task as follows (e.g. increase the max run time to 18000 seconds, 5 hours):

SQL> select task_id, task_name, enabled, interval, max_run_time, enabled from dba_autotask_settings
where task_name = 'Auto Index Task';
TASK_ID TASK_NAME            ENABL   INTERVAL MAX_RUN_TIME ENABL
---------- -------------------- ----- ---------- ------------ -----
3 Auto Index Task      TRUE         900         3600 TRUE
SQL> exec dbms_auto_task_admin.modify_autotask_setting('Auto Index Task', 'MAX RUN TIME', 18000);
PL/SQL procedure successfully completed.
SQL> select task_id, task_name, enabled, interval, max_run_time, enabled from dba_autotask_settings
where task_name = 'Auto Index Task';
TASK_ID TASK_NAME            ENABL   INTERVAL MAX_RUN_TIME ENABL
---------- -------------------- ----- ---------- ------------ -----
3 Auto Index Task      TRUE         900        18000 TRUE

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK