jump to navigation

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.
2 comments

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

Oracle 19c Automatic Indexing: Configuration (All I Need) July 29, 2019

Posted by Richard Foote in Automatic Indexing, AUTO_INDEX_COMPRESSION, AUTO_INDEX_DEFAULT_TABLESPACE, AUTO_INDEX_MODE, AUTO_INDEX_REPORT_RETENTION, AUTO_INDEX_RETENTION_FOR_AUTO, AUTO_INDEX_RETENTION_FOR_MANUAL, AUTO_INDEX_SCHEMA, AUTO_INDEX_SPACE_BUDGET, DBA_AUTO_INDEX_CONFIG, DBMS_AUTO_INDEX.CONFIGURE, Oracle Indexes, Oracle19c, SMB$CONFIG.
2 comments

In Rainbows

In this post, I’ll discuss how to configure the new Automatic Indexing capability introduced in Oracle Database 19c.

The intent of Oracle here is to make the configuration of Automatic Indexing as simplistic as possible, with as few levers for DBAs to potentially stuff up as possible. The ultimate goal would be to have a switch that just turns the feature on and that all necessary indexes then simply be created/modified/dropped as required. It’s not quite there yet, but it’ll no doubt get closer with each new release.

By default, Automatic Indexing is turned OFF. To turn on these capabilities, you simply run the following using the DBMS_AUTO_INDEX.CONFIGURE procedure:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE‘,’IMPLEMENT‘);

PL/SQL procedure successfully completed.

That’s it. Automatic Indexing is now enabled and as discussed in the previous blog post on the Automatic Indexing Methodology, every 15 minutes, the SYS_AUTO_INDEX_TASK background task will kickoff and automatically create/replace/drop any database indexes as necessary.

Another option, is to enable Automatic Indexing in ‘REPORT ONLY‘ mode:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,’REPORT ONLY‘);

PL/SQL procedure successfully completed.

This will only create new indexes as INVISIBLE indexes, which are not considered by default by the CBO. The intent here is that the DBA can investigate the newly created Automatic Indexes and decide whether turning on this feature for real would be a good idea. Need to exercise some caution with this option though, as the limited options regarding how to subsequently administer the created Invisible Automatic Indexing can be problematic. I’ll discuss all this is more detail in a future post.

To turn off Automatic Indexing, simply set the AUTO_INDEX_MODE to ‘OFF’:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,’OFF‘);

PL/SQL procedure successfully completed.

Note here the documentation states “the existing auto indexes are disabled” which is incorrect. New Automatic Indexes will no longer be created, but existing Automatic Indexes will still be both Visible and Valid and available to the CBO for continued use.

By default, Automatic Indexing considers all tables in all “user created” schemas. However, this can be controlled with the AUTO_INDEX_SCHEMA option within the DBMS_AUTO_INDEX.CONFIGURE procedure. You can control which schemas to either explicitly include or exclude from Automatic Indexing considerations.

To add the BOWIE schema to an “Inclusion” list of schemas, you run the following:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_SCHEMA‘,’BOWIE’, TRUE);

PL/SQL procedure successfully completed.

 

To add the BOWIE schema to an “Exclusion” list of schemas, you run the following:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_SCHEMA‘,’BOWIE’, FALSE);

PL/SQL procedure successfully completed.

 

To remove the BOWIE schema from whichever list it belongs, you run the following:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_SCHEMA‘, ‘BOWIE’, NULL);

PL/SQL procedure successfully completed.

 

To remove all schemas from the “Inclusion” list, you run the following:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_SCHEMA‘, NULL, TRUE);

PL/SQL procedure successfully completed.

 

You can configure a tablespace to be the tablespace in which all Automatic Indexes are to now be created by running the following:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_DEFAULT_TABLESPACE‘,’INDEX_TS’);

PL/SQL procedure successfully completed

The INDEX_TS tablespace is now the location of all newly created Automatic Indexes.

You can also control how much of the configured Automatic Indexing tablespace is to be reserved for use by Automatic Indexes (default 50%) by running the following:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_SPACE_BUDGET‘, ’42’);

PL/SQL procedure successfully completed.

Now, only 42% of the INDEX_TS tablespace can be used by Automatic Indexes.

 

You can control the number of days (the default is 373 days) in which if an Automatic Index has been deemed NOT to have been used, it will be automatically dropped. The following command:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_RETENTION_FOR_AUTO‘, ’42’);

PL/SQL procedure successfully completed.

will automatically drop any Automatic Index that is deemed not to have been used in the last 42 days.

Note: Oracle uses the new Indexing Tracking feature introduced in Oracle 12.2 to determine if an index has/has not been used, which has limitations that could potentially result in an Automatic Index that has been “lightly” used during the retention period being dropped. This will be discussed in more detail in a future post.

A similar retention configuration can be implemented for manually created indexes. The following command:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_RETENTION_FOR_MANUAL’,’42’);

PL/SQL procedure successfully completed.

will automatically drop any manually created index that is deemed not to have been used in the last 42 days.

Note: the same Indexing Tracking limitations means that manually created indexes lightly used during the last 42 days could also be automatically dropped.

I will discuss various undocumented implications of automatically dropping both Automatic and Manual Indexes in future posts.

 

Very importantly,  the Automatic Indexing logs on which Automatic Indexing Reports are based are only retained by default for just 31 days. To change the time before the Automatic Indexing logs are deleted, you run the following:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_REPORT_RETENTION‘,’342’);

PL/SQL procedure successfully completed.

The Automatic Indexing logs are now retained for 342 days. These Automatic Indexing reports (and hence logs) are critical for understanding what the Automatic Indexing featuring is doing within the database and will be discussed extensively in future posts. I would recommend increasing the retention period from the 31 days default.

 

The final configuration option is not officially documented (yet) and controls whether or not Advanced Compression is used for Automatic Indexes. The default is that Advanced Compression is disabled, but this can be changed as follows:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_COMPRESSION‘,’ON’);

PL/SQL procedure successfully completed.

All Automatic Indexes are now automatically compressed using Advanced Low Compression.

I believe this was at some stage going to be the default behaviour, but due to licencing considerations and that many sites don’t have the Advanced Compression Option, this was subsequently changed. If you do have Advanced Compression, I would strongly recommend turning this ON, as implementing Advanced Low Compression is a relatively no-brainer beneficial decision.

I will however discuss the various undocumented implications of Advanced Compression in relation to Automatic Indexing in future posts.

The documented DBA_AUTO_INDEX_CONFIG view can be used to view the current setting for all of these configuration options:

SQL> select * from dba_auto_index_config;

PARAMETER_NAME                             PARAMETER_VALUE      LAST_MODIFIED                              MODIFIED_BY
---------------------------------------- -------------------- ---------------------------------------- --------------------
AUTO_INDEX_COMPRESSION                   OFF
AUTO_INDEX_DEFAULT_TABLESPACE
AUTO_INDEX_MODE                          REPORT ONLY           03-JUL-19 05.43.28.000000 AM            BOWIE
AUTO_INDEX_REPORT_RETENTION              31
AUTO_INDEX_RETENTION_FOR_AUTO            42                    20-JUN-19 06.32.06.000000 AM            BOWIE
AUTO_INDEX_RETENTION_FOR_MANUAL                                02-JUL-19 12.12.21.000000 AM            BOWIE
AUTO_INDEX_SCHEMA  schema IN (BOWIE)                           20-JUN-19 06.27.26.000000 AM            BOWIE
AUTO_INDEX_SPACE_BUDGET                  50

 

Additionally, you can view both the documented and undocumented settings regarding Automatic Indexing by looking at the SMB$CONFIG table:

 

SQL> select parameter_name, parameter_value
from sys.SMB$CONFIG;

PARAMETER_NAME                           PARAMETER_VALUE
---------------------------------------- ---------------
SPACE_BUDGET_PERCENT                                  10
PLAN_RETENTION_WEEKS                                  53
SPM_TRACING                                            0
AUTO_CAPTURE_PARSING_SCHEMA_NAME                       0
AUTO_CAPTURE_MODULE                                    0
AUTO_CAPTURE_ACTION                                    0
AUTO_CAPTURE_SQL_TEXT                                  0
AUTO_INDEX_SCHEMA                                      0
AUTO_INDEX_DEFAULT_TABLESPACE                          0
AUTO_INDEX_SPACE_BUDGET                               50
AUTO_INDEX_REPORT_RETENTION                           31
AUTO_INDEX_RETENTION_FOR_AUTO                          0
AUTO_INDEX_RETENTION_FOR_MANUAL                        0
AUTO_INDEX_MODE                                        0
_AUTO_INDEX_TRACE                                      0
_AUTO_INDEX_TASK_INTERVAL                            900
_AUTO_INDEX_TASK_MAX_RUNTIME                        3600
_AUTO_INDEX_IMPROVEMENT_THRESHOLD                     20
_AUTO_INDEX_REGRESSION_THRESHOLD                      10
_AUTO_INDEX_ABSDIFF_THRESHOLD                        100
_AUTO_INDEX_STS_CAPTURE_TASK                           0
_AUTO_INDEX_CONTROL                                    0
_AUTO_INDEX_DERIVE_STATISTICS                          0
_AUTO_INDEX_CONCURRENCY                                1
_AUTO_INDEX_SPA_CONCURRENCY                            1
_AUTO_INDEX_REBUILD_TIME_LIMIT                        30
_AUTO_INDEX_REBUILD_COUNT_LIMIT                        5
_AUTO_INDEX_REVERIFY_TIME                             30
AUTO_INDEX_COMPRESSION                                 0
AUTO_SPM_EVOLVE_TASK                                   0
AUTO_SPM_EVOLVE_TASK_INTERVAL                       3600
AUTO_SPM_EVOLVE_TASK_MAX_RUNTIME                    1800

 

I’ll discuss a number of these undocumented configuration options in future posts.

In my next post however, we’ll look at the Automatic Indexing feature in action with a very simple example to start with…