Automatic Indexing in Oracle 19C

  • The automatic indexing feature automates the index management tasks in an Oracle database. 
  • Automatic indexing automatically creates and drops indexes in a database based on the changes in application workload, thus improving database performance. 
  • The automatically managed indexes are known as auto indexes. Auto Index names include the "SYS_AI" prefix.
  • Index structures are an essential feature to database performance. 
  • Indexes are critical for OLTP applications, which use large data sets and run millions of SQL statements a day.

Automatic indexing provides the following functionality:

  • Runs the automatic indexing process in the background periodically at a predefined time interval.
  • Analyzes application workload, and accordingly creates new indexes and drops the existing under performing indexes to improve database performance.
  • Rebuilds the indexes that are marked unusable due to table partitioning maintenance operations, such as ALTER TABLE MOVE.
  • Provides PL/SQL APIs for configuring automatic indexing in a database and generating reports related to automatic indexing operations.

Note:

  • Auto indexes are local B-tree indexes.
  • Auto indexes can be created for partitioned as well as non-partitioned tables.
  • Auto indexes cannot be created for temporary tables.
  • Automatic indexing uses the SQL performance analyzer framework internally to measure SQL statement performance.

How Automatic Indexing Works:

The automatic indexing process runs in the background every 15 minutes and performs the following operations:

  1. Automatic index candidates are identified based on the usage of table columns in SQL statements.
  2. Index candidates are initially created invisible and unusable. They are not visible to the application workload. Invisible automatic indexes cannot be used by SQL statements in the application workload.
  3. During this verification phase, some or all candidate indexes will be built and made valid so that the performance effect on SQL statements can be measured. All candidate indexes remain invisible during the verification step. If the performance of SQL statements is not improved by using the candidate indexes, they remain invisible.
  4. Candidate valid indexes found to improve SQL performance will be made visible and available to the application workload. Candidate indexes that do not improve SQL performance will revert to invisible and be unusable after a short delay.
  5. Unusable and unused valid indexes are deleted by the automatic indexing process.


How to Configure the Automatic index?

Configuration View: DBA_AUTO_INDEX_CONFIG

Syntax:

DBMS_AUTO_INDEX.CONFIGURE (
parameter_name         IN VARCHAR2,
parameter_value        IN VARCHAR2,
allow                  IN BOOLEAN  DEFAULT TRUE);

Parameters Name:

AUTO_INDEX_MODE - Sets the overall mode of Automatic Indexing. Values: IMPLEMENT (enables automatic indexing), REPORT ONLY (generates recommendations without implementing them), OFF (disables automatic indexing).
AUTO_INDEX_RETENTION_FOR_AUTO - Number of days to retain unused automatic indexes.
AUTO_INDEX_RETENTION_FOR_MANUAL - Number of days to retain unused manual indexes.
AUTO_INDEX_SCHEMA - List of schemas for which Automatic Indexing is enabled. You can specify individual schemas or set it to ALL for the entire database.
AUTO_INDEX_SPACE_BUDGET - Specifies the percentage of tablespace that can be used by automatically created indexes. The default is 50%.
AUTO_INDEX_COMPRESSION - Enables or disables compression for automatically created indexes. Values: ON, OFF.
AUTO_INDEX_DEFAULT_TABLESPACE - Specifies the default tablespace where automatic indexes will be created.
AUTO_INDEX_REPORT_RETENTION - Number of days for which automatic indexing logs are retained in the database before they are deleted. Default value is 373 days.

parameter_value :

Value for the configuration setting specified in parameter_name.
When it is set to NULL, the configuration setting is assigned the default value.

Allow:

This parameter is applicable only for the AUTO_INDEX_SCHEMA configuration setting and it can have one of the following values:

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.

Query to View Current Automatic Indexing Configuration:

SELECT PARAMETER_NAME, PARAMETER_VALUE, LAST_MODIFIED, MODIFIED_BY FROM DBA_AUTO_INDEX_CONFIG;

Examples:

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE', 'IMPLEMENT');
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE', 'REPORT ONLY');
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', 30);  -- Retain unused indexes for 30 days.
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_MANUAL', 60);  -- Retain unused manual indexes for 60 days.
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'HR,SCOTT');    --To enable Specific Schemas.
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'HR', FALSE);    --HR schemas to the exclusion list.
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'HR', NULL);    --removes the HR schema from the exclusion list.
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'ALL');    -- To enable it for all schemas.
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SPACE_BUDGET', 60);  -- Limit space usage to 60% of the tablespace.
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_COMPRESSION', 'ON');  -- Enable compression.
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_COMPRESSION', 'OFF'); -- Disable compression.
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE', 'USERS');  -- Use the USERS tablespace.
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_REPORT_RETENTION', '60');  --sets the retention period logs to 60 days.
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_REPORT_RETENTION', NULL); --resets the retention period, The default value 373 days.

Drop Auto Index:

Procedure: DROP_AUTO_INDEXES

  • This procedure can be used to manually drop the automatically created indexes that overrides the retention parameter setting.

Syntax:

DBMS_AUTO_INDEX.DROP_AUTO_INDEXES (
owner           IN  VARCHAR2 DEFAULT NULL,
index_name      IN  VARCHAR2 DEFAULT NULL,
allow_recreate  IN  BOOLEAN DEFAULT FALSE);

Options:

owner - The name of the index owner.
index_name - The name of the index.
allow_recreate - Set this parameter to allow (TRUE)or disallow (FALSE) automatic creation of the dropped index again.

Examples:
exec dbms_auto_index.drop_auto_indexes('SH','"SYS_AI_612ud3j5ngf0c"',TRUE); --Drop a single index and allow recreate.

exec dbms_auto_index.drop_auto_indexes('SH',NULL,TRUE); --Drop all indexes owned by SH and allow recreate.

Drop the Secondary Indexes:

Procedure: DROP_SECONDARY_INDEXES

  • This procedure deletes all the indexes, except the ones used for constraints, from a schema or a table.

Syntax:
DBMS_AUTO_INDEX.DROP_SECONDARY_INDEXES (
ownname    IN  VARCHAR2 DEFAULT NULL,
tabname    IN  VARCHAR2 DEFAULT NULL);

Parameters and Description:

ownname - (Optional) Name of the schema from which all the indexes need to be deleted. Note: The indexes used for constraints are not deleted.

Tabname - (Optional) Name of the table from which all the indexes need to be deleted. Note: The indexes used for constraints are not deleted.

Deletes all the indexes, except the ones used for constraints, from the SH schema:

begin
dbms_auto_index.drop_secondary_indexes('SH');
end;

Deletes all the indexes, except the ones used for constraints, from the EMP table in the HR schema:

begin
dbms_auto_index.drop_secondary_indexes('HR', 'EMP');
end;

deletes all the indexes, except the ones used for constraints, for which the user has the delete privileges from all the schemas in a database:

begin
dbms_auto_index.drop_secondary_indexes;
end;

Generating Automatic Indexing Reports:

  • Generate the  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.

Syntax:

DBMS_AUTO_INDEX.REPORT_ACTIVITY (
activity_start  IN  TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP - 1,
activity_end    IN  TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP,
type            IN  VARCHAR2 DEFAULT 'TEXT',
section         IN  VARCHAR2 DEFAULT 'ALL',
level           IN  VARCHAR2 DEFAULT 'TYPICAL')
RETURN CLOB;

Generating a report of the last automatic indexing operation:

generates a report containing typical information about the last automatic indexing operation. The report is generated in the plain text format by default.

Syntax:

DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY (
type            IN  VARCHAR2 DEFAULT 'TEXT',
section         IN  VARCHAR2 DEFAULT 'ALL',
level           IN  VARCHAR2 DEFAULT 'TYPICAL')
RETURN CLOB;

Parameters and Descriptions:

activity_start - Time starting from which the executed automatic indexing operations are considered for the report. If NULL is specified, the last executed automatic indexing operation is considered for the report. If no value is specified for this parameter, then the current time minus one day (24 hours) is considered at the start time.

activity_end - Time till which the executed automatic indexing operations are considered for the report. If no value is specified, then the current time is considered as the end time.

Type - Format of the report. It can have one of the following values: (TEXT, HTML, XML) The default value is TEXT.

Section - Sections to include in the report. It can have a combination of the following values:

SUMMARY: Include only the summary details section in the report.
INDEX_DETAILS: Include only the auto index details section in the report.
VERIFICATION_DETAILS: Include only the auto index verification details section in the report.
ERRORS: Include only the error details section in the report.
ALL: Include all the sections (summary details, auto index details, auto index verification details, and error details) in the report. This is the default value.

A combination of these values can be specified using the + or - operators as shown in the following examples:

  • SUMMARY +INDEX_DETAILS +ERRORS: Include summary details, auto index details, and error details sections in the report.
  • ALL -ERRORS: Include all the sections in the report, except the error details section.


Level - Level of automatic indexing information to include in the report. It can have one of the following values:

BASIC: Include basic automatic indexing information in the report.
TYPICAL: Include typical automatic indexing information in the report. This is the default value.
ALL: Include all the automatic indexing information in the report.

Examples: Default TEXT report for the last 24 hours.

declare
report clob := null;
begin
report := DBMS_AUTO_INDEX.REPORT_ACTIVITY();
end;

(OR)

select dbms_auto_index.report_activity() from dual;

Generate the report containing basic information about the automatic indexing operations for the month of November 2018.

declare
report clob := null;
begin
report := DBMS_AUTO_INDEX.REPORT_ACTIVITY(
activity_start => TO_TIMESTAMP('2018-11-01', 'YYYY-MM-DD'),
activity_end   => TO_TIMESTAMP('2018-12-01', 'YYYY-MM-DD'),
type           => 'HTML',
section        => 'SUMMARY',
level          => 'BASIC');
end;

HTML Report for the day before yesterday:

select dbms_auto_index.report_activity(
activity_start => systimestamp-2,
activity_end   => systimestamp-1,
type           => 'HTML')
from   dual;

Generate the report containing typical information about the last automatic indexing operation:

declare
report clob := null;
begin
report := DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY();
end;

  • Generates a report containing basic information about the last automatic indexing operation. 
  • The report includes the summary, index details, and error information of the last automatic indexing operation. 
  • The report is generated in the HTML format.

declare
report clob := null;
begin
report := DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY(
type    => 'HTML',
section => 'SUMMARY +INDEX_DETAILS +ERRORS',
level   => 'BASIC');
end;

Comments

Post a Comment