管理自动索引

最新推荐文章于 2021-04-04 08:01:00 发布

地_衣

最新推荐文章于 2021-04-04 08:01:00 发布

阅读量339

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/feixiangtianshi/article/details/102737076

版权

oracle 专栏收录该内容

46 篇文章 0 订阅

订阅专栏

You can use the automatic indexing feature to configure and use auto indexes in an Oracle database to improve database performance.

利用自动索引功能配置及使用来提高数据库性能

About Automatic Indexing
The automatic indexing feature automates the index management tasks in an Oracle database. Automatic indexing automatically creates, rebuilds, 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. 自动索引功能自动执行数据库中索引管理任务。自动索引根据应用程序负载变化自动创建、重建和删除数据库中的索引来提高数据库性能。自动管理的索引称之为自动索引。
How Automatic Indexing Works
This section describes how automatic indexing works.
Configuring Automatic Indexing in an Oracle Database
You can configure automatic indexing in an Oracle database using the DBMS_AUTO_INDEX.CONFIGURE procedure.
Generating Automatic Indexing Reports
You can generate 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.
Views Containing the Automatic Indexing Information
You can query a set of data dictionary views for getting information about the auto indexes in an Oracle database.

Parent topic: Managing Indexes

21.7.1 About Automatic Indexing

The automatic indexing feature automates the index management tasks in an Oracle database. Automatic indexing automatically creates, rebuilds, 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.

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. Indexes are also critical for data warehousing applications, which typically query a relatively small amount of data from very large tables. If you do not update the indexes whenever there are changes in the application workload, the existing indexes can cause the database performance to deteriorate considerably.

索引结构是数据库性能的一个基本特性。索引对OLTP及OLAP应用程序都至关重要。如果在应用程序负载发生变化时索引不做更新，现有索引可能会导致数据库性能显著下降。

Automatic indexing improves database performance by managing indexes automatically and dynamically in an Oracle database based on changes in the application workload.

自动索引根据应用程序负载变化自动动态管理数据库中的索引来提高数据库性能。

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 underperforming indexes to improve database performance.
分析应用程序负载，并相应的创建新索引和删除现有性能不佳的索引来提高数据库性能
Rebuilds the indexes that are marked unusable due to table partitioning maintenance operations, such as ALTER TABLE MOVE.
重建由于分区表维护操作标记的不可用索引。例如：ALTER TABLE MOVE
Provides PL/SQL APIs for configuring automatic indexing in a database and generating reports related to automatic indexing operations.
提供PL/SQL API接口用来配置自动索引和生成与自动索引操作相关的报告。

Note:

Auto indexes are local B-tree indexes.
自动索引是本地B-tree索引
Auto indexes can be created for partitioned as well as non-partitioned tables.
自动索引可为分区和非分区表创建自动索引。
Auto indexes cannot be created for temporary tables.
自动索引无法为临时表创建自动索引

Parent topic: Managing Auto Indexes

21.7.2 How Automatic Indexing Works

This section describes how automatic indexing works.

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

自动索引过程后台每15分钟执行一次和执行如下操作：

Identifies auto index candidates 确定自动索引候选项 Auto index candidates are identified based on the usage of table columns in SQL statements.
Creates invisible auto indexes for the auto index candidates 根据自动索引候选项创建不可见索引
The auto index candidates are created as invisible auto indexes, that is, these auto indexes cannot be used in SQL statements. 自动索引候选项被创建为不可见索引，因此，自动索引不能被SQL使用。
Verifies invisible auto indexes against SQL statements 根据SQL语句验证不可见自动索引
The invisible auto indexes are validated against SQL statements.

If the performance of SQL statements is improved by using these indexes, then the indexes are configured as visible indexes, so that they can be used in SQL statements.如果使用这些索引可用提高SQL性能，这些索引将配置为可见索引，以便SQL使用。

If the performance of SQL statements is not improved by using these indexes, then the indexes are configured as unusable indexes and the SQL statements are blacklisted. The unusable indexes are later deleted by the automatic indexing process. The blacklisted SQL statements are not allowed to use auto indexes in future.如果这些索引不能提高性能，那么这些索引将配置为不可用索引，SQL放入黑名单。不可用索引会被自动索引进程删除。再黑名单中的SQL语句将不允许使用在后面的自动索引中。

Note:

Auto indexes cannot be used by SQL statements that are running for the first time in a database.
Deletes the unused auto indexes 删除不可用索引
The auto indexes that are not used for a long period are deleted. 删除长时间不用的自动索引

Note:

By default, the unused auto indexes are deleted after 373 days. The period for retaining the unused auto indexes in a database can be configured using the DBMS_AUTO_INDEX.CONFIGURE procedure. 默认373天后删除不用的自动索引，可用通过DBMS_AUTO_INDEX.CONFIGURE 配置。

Parent topic: Managing Auto Indexes

21.7.3 Configuring Automatic Indexing in an Oracle Database

自动索引配置

You can configure automatic indexing in an Oracle database using the DBMS_AUTO_INDEX.CONFIGURE procedure.

通过DBMS_AUTO_INDEX.CONFIGURE存储过程来配置自动索引

The following examples describe some of the configuration settings that can be specified using the DBMS_AUTO_INDEX.CONFIGURE procedure:

Enabling and disabling automatic indexing in a database

You can use the AUTO_INDEX_MODE configuration setting to enable or disable automatic indexing in a database.

通过 AUTO_INDEX_MODE配置设置来开启或关闭数据库中的自动索引

The following statement enables automatic indexing in a database and creates any new auto indexes as visible indexes, so that they can be used in SQL statements:

以下语句启用数据库中的自动索引，并将任何新的自动索引创建为可见索引，使用如下语句：

<span style="color:#000000"><code><span style="color:#ff0000">EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');</span></code></span>

The following statement enables automatic indexing in a database, but creates any new auto indexes as invisible indexes, so that they cannot be used in SQL statements:

以下语句启用数据库中的自动索引，并将任何新的自动索引创建为不可见索引，使用如下语句：

<span style="color:#000000"><code><span style="color:#ff0000">EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY')</span>;</code></span>

The following statement disables automatic indexing in a database, so that no new auto indexes are created, and the existing auto indexes are disabled:

关闭数据库中的自动索引：

<span style="color:#000000"><code><span style="color:#ff0000">EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');</span></code></span>

Specifying schemas that can use auto indexes

指定schema使用自动索引

You can use the AUTO_INDEX_SCHEMA configuration setting to specify schemas that can use auto indexes.

通过AUTO_INDEX_SCHEMA 配置来设置指定的schemas使用自动索引功能；

Note:

When automatic indexing is enabled in a database, all the schemas in the database can use auto indexes by default.

当在数据库开启自动索引，默认数据库中所有的schemass都可使用自动索引。

The following statements add the SH and HR schemas to the exclusion list, so that the SH and HR schemas cannot use auto indexes.:

如下语句排除SH和HR schemas不能使用自动索引：

<span style="color:#000000"><code><span style="color:#ff0000">EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'SH', FALSE);
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'HR', FALSE);</span></code></span>

The following statement removes the HR schema from the exclusion list, so that the HR schema can use auto indexes:

如下语句将HR从排除列表删除，可以使用自动索引；

<span style="color:#000000"><code><span style="color:#ff0000">EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'HR', NULL);</span></code></span>

The following statement removes all the schema from the exclusion list, so that all the schemas in the database can use auto indexes:

如下语句删除排除列表中的所有schemas,库中所有schemas都可以使用自动索引功能：

<span style="color:#000000"><code><span style="color:#ff0000">EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', NULL, TRUE);</span></code></span>

Specifying a retention period for unused auto indexes

为未使用的索引设置保留周期

You can use the AUTO_INDEX_RETENTION_FOR_AUTO configuration setting to specify a period for retaining unused auto indexes in a database. The unused auto indexes are deleted after the specified retention period.

通过 AUTO_INDEX_RETENTION_FOR_AUTO配置设置未使用索引保留周期，保留周期后，未使用索引将被删除。

Note:

By default, the unused auto indexes are deleted after 373 days.

The following statement sets the retention period for unused auto indexes to 90 days.

如下语句设置保留周期未90天：

<span style="color:#000000"><code><span style="color:#ff0000">EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', '90');</span></code></span>

The following statement resets the retention period for auto indexes to the default value of 373 days.

如下语句修改为默认保留周期（373天）

<span style="color:#000000"><code><span style="color:#ff0000">EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', NULL);</span></code></span>

Specifying a retention period for unused non-auto indexes

为未使用的非自动索引设置保留周期

You can use the AUTO_INDEX_RETENTION_FOR_MANUAL configuration setting to specify a period for retaining unused non-auto indexes (manually created indexes) in a database. The unused non-auto indexes are deleted after the specified retention period.

通过AUTO_INDEX_RETENTION_FOR_MANUAL为数据库中未使用的非自动索引（手工创建的索引）设置保留周期，保留周期后这些索引将会被删除。

Note:

By default, the unused non-auto indexes are never deleted by the automatic indexing process.

默认未使用的非自动索引将不会被自动索引进程删除。

The following statement sets the retention period for unused non-auto indexes to 60 days.

如下语句为未使用非自动索引设置60天保留周期：

<span style="color:#000000"><code><span style="color:#ff0000">EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_MANUAL', '60');</span></code></span>

The following statement sets the retention period for unused non-auto indexes to NULL so that they are never deleted by the automatic indexing process.

恢复默认设置：

<span style="color:#000000"><code><span style="color:#ff0000">EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', NULL);</span></code></span>

Specifying a retention period for automatic indexing logs

设置自动索引日志保留周期

You can use the AUTO_INDEX_REPORT_RETENTION configuration setting to specify a period for retaining automatic indexing logs in a database. The automatic indexing logs are deleted after the specified retention period.

通过AUTO_INDEX_REPORT_RETENTION 配置自动索引日志保留周期，保留周期后将被删除。

Note:

By default, the automatic indexing logs are deleted after 31 days.

默认，自动索引日志保留31天。

The following statement sets the retention period for automatic indexing logs to 60 days.

将自动索引日志保留周期设置未60天：

<span style="color:#000000"><code><span style="color:#ff0000">EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_REPORT_RETENTION', '60');</span></code></span>

The following statement resets the retention period for automatic indexing logs to the default value 31 days.

恢复默认设置：

<span style="color:#000000"><code><span style="color:#ff0000">EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_REPORT_RETENTION', NULL);</span></code></span>

<span style="color:#000000">
</span>

Note:

Automatic indexing reports are generated based on the automatic indexing logs. Therefore, automatic indexing reports cannot be generated for a period that is more than the retention period of the automatic indexing logs specified using the AUTO_INDEX_REPORT_RETENTION configuration setting.

自动索引报告依赖于自动索引日志，因此，自动索引报告周期不能超过自动索引日志AUTO_INDEX_REPORT_RETENTION 设置的保留周期。

Specifying a tablespace to store auto indexes

为自动索引设置表空间

You can use the AUTO_INDEX_DEFAULT_TABLESPACE configuration setting to specify a tablespace to store auto indexes.

通过AUTO_INDEX_DEFAULT_TABLESPACE 为自动索引设置特定的表空间

Note:

By default, the permanent tablespace specified during the database creation is used for storing auto indexes.

默认，在创建数据库期间指定永久表空间存储自动索引。

The following statement specifies the tablespace of TBS_AUTO to store auto indexes:

制动TBS_AUTO为自动索引表空间：

<span style="color:#000000"><code><span style="color:#ff0000">EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE', 'TBS_AUTO');</span></code></span>

Specifying percentage of tablespace to allocate for auto indexes

指定分配给自动索引表空间的百分比。

You can use the AUTO_INDEX_SPACE_BUDGET configuration setting to specify percentage of tablespace to allocate for auto indexes. You can specify this configuration setting only when the tablespace used for storing auto indexes is the default permanent tablespace specified during the database creation, that is, when no value is specified for the AUTO_INDEX_DEFAULT_TABLESPACE configuration setting.

The following statement allocates 5 percent of the tablespace for auto indexes:

<span style="color:#000000"><code><span style="color:#ff0000">EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SPACE_BUDGET', '5');</span></code></span>

See Also:

Oracle Database PL/SQL Packages and Types Reference for a complete list of configuration settings related to automatic indexing that can be specified using the DBMS_AUTO_INDEX.CONFIGURE procedure

Parent topic: Managing Auto Indexes

21.7.4 Generating Automatic Indexing Reports

生成自动索引报告

You can generate 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.

通过数据库中的REPORT_ACTIVITY 函数和DBMS_AUTO_INDEX 包能生成自动索引操作相关的报告

See Also:

Oracle Database PL/SQL Packages and Types Reference for the syntax of the REPORT_ACTIVITY and REPORT_LAST_ACTIVITY functions of the DBMS_AUTO_INDEX package.

Generating a report of automatic indexing operations for a specific period

The following example generates a report containing typical information about the automatic indexing operations for the last 24 hours. The report is generated in the plain text format by default.

生成最近24小时的报告：

<span style="color:#000000"><code><span style="color:#ff0000">declare
  report clob := null;
begin
  report := DBMS_AUTO_INDEX.REPORT_ACTIVITY();
end;</span></code></span>

The following example generates a report containing basic information about the automatic indexing operations for the month of November 2018. The report is generated in the HTML format and includes only the summary of automatic indexing operations.

指定时间段及生成格式：

<span style="color:#000000"><code><span style="color:#ff0000">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;</span></code></span>

Generating a report of the last automatic indexing operation

生成最近的自动索引操作相关的报告：

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

<span style="color:#000000"><code><span style="color:#ff0000">declare
  report clob := null;
begin
  report := DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY();
end;</span></code></span>

The following example 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.

<span style="color:#000000"><code><span style="color:#ff0000">declare
  report clob := null;
begin
  report := DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY(
              type    => 'HTML',
              section => 'SUMMARY +INDEX_DETAILS +ERRORS',
              level   => 'BASIC');
end;</span></code></span>

Parent topic: Managing Auto Indexes

21.7.5 Views Containing the Automatic Indexing Information

包含自动索引信息的视图：

You can query a set of data dictionary views for getting information about the auto indexes in an Oracle database.

The following views show information about the automatic indexing configuration settings and the auto indexes created in an Oracle database:

View Description

View	Description
`<span style="color:#000000"><code><span style="color:#ff0000">DBA_AUTO_INDEX_CONFIG</span></code></span>`	Shows the current configuration settings for automatic indexing.
`<span style="color:#000000"><code><span style="color:#ff0000">DBA_INDEXES ALL_INDEXES USER_INDEXES</span> </code></span>`	The `AUTO` column in these views indicates whether an index is an auto index (`YES`) or not (`NO`).

<span style="color:#000000"><code><span style="color:#ff0000">DBA_AUTO_INDEX_CONFIG</span></code></span>

Shows the current configuration settings for automatic indexing.

<span style="color:#000000"><code><span style="color:#ff0000">DBA_INDEXES
ALL_INDEXES
USER_INDEXES</span>
</code></span>

The AUTO column in these views indicates whether an index is an auto index (YES) or not (NO).

See Also:

Oracle Database Reference for complete descriptions of these views

来源： Managing Indexes

地_衣

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
管理自动索引

You can use the automatic indexing feature to configure and use auto indexes in an Oracle database to improve database performance. 利用自动索引功能配置及使用来提高数据库性能About Automatic Indexing The aut...
复制链接

扫一扫