Automatic Indexing for Database Nodes

The automatic indexing feature automates the index management tasks in database nodes with Oracle Database 19.3 or later. Automatic indexing automatically creates, rebuilds, and drops indexes in a database based on the changes in application workload, thus improving database performance.

This feature includes the following options:

• Settings: On the Automatic Indexing Configuration Settings page, you can enable and disable automatic indexing, specify the retention period for unused indexes and automatic indexing logs, and specify the schemas to be included or excluded from using automatic indexing.

• Activity Report: On the Automatic Indexing Activity Report page, you can enter the following details and click Generate Report to view the details of the auto indexes generated in the database:

  • Report Format: Select the format in which you want the report to be generated.
  • Sections: Select the sections that you want displayed in the report. The Summary, Index Details, Verification Details, and Errors options are selected by default in the Sections field, and you can opt to remove any of these sections.
  • Time Period: Select the monitoring time period for which you want the report to be generated.
  If you select the default options, namely the HTML report format and all the options in the Sections field for a specific time period, then the following sections are displayed in the Report Summary:

  • Overview of Executions: This section displays the overall performance improvement factor as a result of the auto indexes, the number of times the auto index operations were completed, the number of times the auto index operations were interrupted, and fatal errors, if any. Note that the Overview of Executions section is displayed irrespective of which other section is selected in the Sections field.
  • For the Summary section option, the following bar graphs are displayed:
    • Summary of Auto Indexes Actions
    • Summary of Auto Indexes SQL Actions
    • Summary of Manual Indexes
  • For the Errors section option, the Error Summary pie chart is displayed.
  • For the Index Details section option, the following sections are displayed:
    • Index: Created
    • Index: Dropped

    You can click a row in the Index: Created and Index: Dropped sections to view more details such as the ID of the index that was created or dropped, the Key, and Type. You can also use the Download All Index Created Details and Download All Index Dropped Details options given in these sections to download the index details in a .csv format.

  • For the Verification Details section option, the Verification Details section is displayed, which includes the details of the SQLs for which auto indexes were generated and used. You can click a row in this section to view more details such as SQL ID, SQL Text, and Improvement Factor.

  If you select the TEXT report format in the Report Format field, then the same information is displayed in a plain text format and can be downloaded by clicking theText Download option.

For information on managing auto indexes, see Managing Auto Indexes in Oracle Database Administrator’s Guide.