[译] Oracle Database 19c 中的自动索引 (DBMS_AUTO_INDEX)

原文地址：https://oracle-base.com/articles/19c/automatic-indexing-19c#drop-an-automatic-index 原文作者：Tim Hall

Oracle 数据库 19c 引入了自动索引功能，它可以让您将一些有关索引管理的决策交给数据库。

一、介绍

本章目标：掌握本章核心知识点 前置要求：完成前序章节学习 预计时长：60 分钟

自动索引功能包括以下几个特性：

可以根据数据表中列使用情况识别潜在的自动索引。我们可以称之为 "候选索引"。
将自动索引创建为不可见索引，因此它们不会在执行计划中使用。索引名称包括“SYS_AI”前缀。
针对 SQL 语句测试不可见的自动索引以确保它们能够提升性能。如果它们确实可以提高性能，就会变成可见索引。反之，如果性能没有得到改善，相关的自动索引会被标记为不可用并随后被删除。针对失败的自动索引测试的 SQL 语句被列入阻止列表，因此将来不会考虑将它们用于自动索引。优化器不会在第一次对数据库运行 SQL 时考虑自动索引。
删除未使用的索引。

由于从未在 Exadata 上使用过此功能，因此无法评论其有效性。

二、先决条件

此功能目前仅限于工程系统上的企业版，如此处所述。有一种通过启用 _exadata_feature_on 初始化参数进行测试的解决方案。

Bash

1export ORACLE_SID=cdb1
2export ORAENV_ASK=NO
3. oraenv
4export ORAENV_ASK=YES
5
6sqlplus / as sysdba <<EOF
7
8alter system set "_exadata_feature_on"=true scope=spfile;
9shutdown immediate;
10startup;
11
12exit;
13EOF

此方式不建议在生产系统进行使用，仅用于测试所用。

三、配置方式

DBMS_AUTO_INDEX 包用于管理自动索引功能，基本管理如下所述。

1、检查配置

CDB_AUTO_INDEX_CONFIG 视图显示当前的自动索引配置，以下查询使用 auto_index_config.sql 脚本。

SQL

1column parameter_name format a40
2column parameter_value format a15
3
4select con_id, parameter_name, parameter_value 
5from   cdb_auto_index_config
6order by 1, 2;
7
8    CON_ID PARAMETER_NAME                           PARAMETER_VALUE
9---------- ---------------------------------------- ---------------
10         1 AUTO_INDEX_COMPRESSION                   OFF
11         1 AUTO_INDEX_DEFAULT_TABLESPACE
12         1 AUTO_INDEX_MODE                          OFF
13         1 AUTO_INDEX_REPORT_RETENTION              31
14         1 AUTO_INDEX_RETENTION_FOR_AUTO            373
15         1 AUTO_INDEX_RETENTION_FOR_MANUAL
16         1 AUTO_INDEX_SCHEMA
17         1 AUTO_INDEX_SPACE_BUDGET                  50
18         3 AUTO_INDEX_COMPRESSION                   OFF
19         3 AUTO_INDEX_DEFAULT_TABLESPACE
20         3 AUTO_INDEX_MODE                          OFF
21         3 AUTO_INDEX_REPORT_RETENTION              31
22         3 AUTO_INDEX_RETENTION_FOR_AUTO            373
23         3 AUTO_INDEX_RETENTION_FOR_MANUAL
24         3 AUTO_INDEX_SCHEMA
25         3 AUTO_INDEX_SPACE_BUDGET                  50
26
27SQL>

如果我们切换到用户定义的可插拔数据库，我们就只能查看该容器的值。

SQL

1alter session set container = pdb1;
2
3SQL> @auto_index_config.sql
4
5    CON_ID PARAMETER_NAME                           PARAMETER_VALUE
6---------- ---------------------------------------- ---------------
7         3 AUTO_INDEX_COMPRESSION                   OFF
8         3 AUTO_INDEX_DEFAULT_TABLESPACE
9         3 AUTO_INDEX_MODE                          OFF
10         3 AUTO_INDEX_REPORT_RETENTION              31
11         3 AUTO_INDEX_RETENTION_FOR_AUTO            373
12         3 AUTO_INDEX_RETENTION_FOR_MANUAL
13         3 AUTO_INDEX_SCHEMA
14         3 AUTO_INDEX_SPACE_BUDGET                  50
15
16SQL>

关于该参数的详细描述可参考官方文档：DBMS_AUTO_INDEX！

2、启用/禁用自动索引

我们可以使用 DBMS_AUTO_INDEX 包的 CONFIGURE 过程配置来自动索引。

自动索引 的开关是使用 AUTO_INDEX_MODE 属性控制的，该属性具有以下几种模式：

IMPLEMENT：打开自动索引，提高性能的新索引变得可见并可供优化器使用。
REPORT ONLY：打开自动索引，但新索引仍然不可见。
OFF: 关闭自动索引。

可以使用以下命令进行模式间的切换：

SQL

1exec dbms_auto_index.configure('AUTO_INDEX_MODE','IMPLEMENT');
2exec dbms_auto_index.configure('AUTO_INDEX_MODE','REPORT ONLY');
3exec dbms_auto_index.configure('AUTO_INDEX_MODE','OFF');

3、自动索引的表空间

默认情况下，在默认永久表空间中创建自动索引。如果想使用新的表空间来创建，可以使用 AUTO_INDEX_DEFAULT_TABLESPACE 属性指定一个表空间来保存它们。下面我们创建一个表空间来保存自动索引，并相应地设置属性。

Bash

1alter session set container = pdb1;
2
3create tablespace auto_indexes_ts datafile size 100m autoextend on next 100m;
4
5exec dbms_auto_index.configure('AUTO_INDEX_DEFAULT_TABLESPACE','AUTO_INDEXES_TS');

设置为 NULL 则代表使用默认的永久表空间：

SQL

1Exec dbms_auto_index.configure('AUTO_INDEX_DEFAULT_TABLESPACE',NULL);

4、用户级控制

启用自动索引后，在尝试识别候选索引时会考虑所有用户。您可以使用 AUTO_INDEX_SCHEMA 属性更改默认行为，这允许您维护包含/排除列表。

如果 ALLOW 参数设置为 TRUE，则将指定的用户添加到包含列表中。请注意，它构建了一个包含用户的谓词。

SQL

1exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'TEST', allow => TRUE);
2exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'TEST2', allow => TRUE);
3
4SQL> @auto_index_config.sql
5
6    CON_ID PARAMETER_NAME                           PARAMETER_VALUE
7---------- ---------------------------------------- ----------------------------------------
8         3 AUTO_INDEX_COMPRESSION                   OFF
9         3 AUTO_INDEX_DEFAULT_TABLESPACE            AUTO_INDEXES_TS
10         3 AUTO_INDEX_MODE                          IMPLEMENT
11         3 AUTO_INDEX_REPORT_RETENTION              31
12         3 AUTO_INDEX_RETENTION_FOR_AUTO            373
13         3 AUTO_INDEX_RETENTION_FOR_MANUAL
14         3 AUTO_INDEX_SCHEMA                        schema IN (TEST, TEST2)
15         3 AUTO_INDEX_SPACE_BUDGET                  50
16
17SQL>

可以使用 NULL 参数值将包含列表清空：

SQL

1exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', NULL, allow => TRUE);
2
3SQL> @auto_index_config.sql
4
5    CON_ID PARAMETER_NAME                           PARAMETER_VALUE
6---------- ---------------------------------------- ----------------------------------------
7         3 AUTO_INDEX_COMPRESSION                   OFF
8         3 AUTO_INDEX_DEFAULT_TABLESPACE            AUTO_INDEXES_TS
9         3 AUTO_INDEX_MODE                          IMPLEMENT
10         3 AUTO_INDEX_REPORT_RETENTION              31
11         3 AUTO_INDEX_RETENTION_FOR_AUTO            373
12         3 AUTO_INDEX_RETENTION_FOR_MANUAL
13         3 AUTO_INDEX_SCHEMA
14         3 AUTO_INDEX_SPACE_BUDGET                  50
15
16SQL>

如果 ALLOW 参数设置为 FALSE，则将指定的用户添加到排除列表中：

SQL

1exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'TEST', allow => FALSE);
2exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'TEST2', allow => FALSE);
3
4SQL> @auto_index_config.sql
5
6    CON_ID PARAMETER_NAME                           PARAMETER_VALUE
7---------- ---------------------------------------- ----------------------------------------
8         3 AUTO_INDEX_COMPRESSION                   OFF
9         3 AUTO_INDEX_DEFAULT_TABLESPACE            AUTO_INDEXES_TS
10         3 AUTO_INDEX_MODE                          IMPLEMENT
11         3 AUTO_INDEX_REPORT_RETENTION              31
12         3 AUTO_INDEX_RETENTION_FOR_AUTO            373
13         3 AUTO_INDEX_RETENTION_FOR_MANUAL
14         3 AUTO_INDEX_SCHEMA                        schema NOT IN (TEST, TEST2)
15         3 AUTO_INDEX_SPACE_BUDGET                  50
16
17SQL>

同样的，可以使用 NULL 参数值将排除列表清空：

SQL

1exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', NULL, allow => FALSE);
2
3SQL> @auto_index_config.sql
4
5    CON_ID PARAMETER_NAME                           PARAMETER_VALUE
6---------- ---------------------------------------- ----------------------------------------
7         3 AUTO_INDEX_COMPRESSION                   OFF
8         3 AUTO_INDEX_DEFAULT_TABLESPACE            AUTO_INDEXES_TS
9         3 AUTO_INDEX_MODE                          IMPLEMENT
10         3 AUTO_INDEX_REPORT_RETENTION              31
11         3 AUTO_INDEX_RETENTION_FOR_AUTO            373
12         3 AUTO_INDEX_RETENTION_FOR_MANUAL
13         3 AUTO_INDEX_SCHEMA
14         3 AUTO_INDEX_SPACE_BUDGET                  50
15
16SQL>

5、其他配置

如果需要了解其他参数，下面详细说明了这些参数：

AUTO_INDEX_COMPRESSION ：文档中未说明，大概用于控制压缩级别，默认 OFF
AUTO_INDEX_REPORT_RETENTION ：自动索引日志的保留期。报告基于这些日志，默认 31 天。
AUTO_INDEX_RETENTION_FOR_AUTO ：未使用的自动索引的保留期，默认 373 天。
AUTO_INDEX_RETENTION_FOR_MANUAL ：未使用的手动创建索引的保留期，当设置为 NULL 时，不考虑删除手动创建的索引，默认为空。
AUTO_INDEX_SPACE_BUDGET ：用于自动索引存储的默认永久表空间的百分比，使用 AUTO_INDEX_DEFAULT_TABLESPACE 参数指定自定义表空间时，将忽略此参数。

四、删除二级索引

‼️ 做这个之前要仔细考虑，测试，测试，测试！

如果你真的勇气非凡，DROP_SECONDARY_INDEXES 过程将删除除用于约束的索引之外的所有索引。这可以在表、模式或数据库级别完成。

SQL

1-- Table-level
2exec dbms_auto_index.drop_secondary_indexes('MY_SCHEMA', 'MY_TABLE');
3
4-- Schema-level
5exec dbms_auto_index.drop_secondary_indexes('MY_SCHEMA');
6
7-- Database-level
8exec dbms_auto_index.drop_secondary_indexes;

这让您一清二楚，因此自动索引可以为您做出所有索引决策。

五、删除自动索引

DROP_AUTO_INDEXES 过程允许我们删除自动创建的索引。根据参数值，我们可以删除指定的自动索引，也可以是用户的所有自动索引。

删除指定的自动索引，并确保它不会被重新创建。请注意，索引名称是 双引号 的！

SQL

1begin
2  dbms_auto_index.drop_auto_indexes(
3    owner          => 'MY_SCHEMA',
4    index_name     => '"SYS_AI_512bd3h5nif1a"',
5    allow_recreate => false);
6end;
7/

删除指定用户下的所有自动索引，但允许重新创建它们：

SQL

1begin
2  dbms_auto_index.drop_auto_indexes(
3    owner          => 'MY_SCHEMA',
4    index_name     => null,
5    allow_recreate => true);
6end;
7/

删除当前用户的所有自动索引，但允许重新创建它们：

SQL

1begin
2  dbms_auto_index.drop_auto_indexes(
3    owner          => null,
4    index_name     => null,
5    allow_recreate => true);
6end;
7/

在此功能的初始版本中，没有一种机制可以删除由自动索引功能创建的特定索引，或者首先阻止创建特定索引。 Franck Pachot 写了一些可以让你做到这一点的黑客。

How to drop an index created by Oracle 19c Auto Indexing?

六、相关视图

有几个与自动索引功能相关的视图，如下所示：

SQL

1select view_name
2from   dba_views
3where  view_name like 'DBA_AUTO_INDEX%'
4order by 1;
5
6VIEW_NAME
7--------------------------------------------------------------------------------
8DBA_AUTO_INDEX_CONFIG
9DBA_AUTO_INDEX_EXECUTIONS
10DBA_AUTO_INDEX_IND_ACTIONS
11DBA_AUTO_INDEX_SQL_ACTIONS
12DBA_AUTO_INDEX_STATISTICS
13DBA_AUTO_INDEX_VERIFICATIONS
14
15SQL>

此外，{CDB|DBA|ALL|USER}_INDEXES 视图包括 AUTO 列，该列指示索引是否由自动索引功能创建。

以下查询可以使用 auto_indexes.sql 脚本：

SQL

1
2column owner format a30
3column index_name format a30
4column table_owner format a30
5column table_name format a30
6
7select owner,
8       index_name,
9       index_type,
10       table_owner,
11       table_name
12       table_type
13from   dba_indexes
14where  auto = 'YES'
15order by owner, index_name;

DBMS_AUTO_INDEX 包包含两个报告函数：

SQL

1DBMS_AUTO_INDEX.REPORT_ACTIVITY (
2   activity_start  IN  TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP - 1,
3   activity_end    IN  TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP,
4   type            IN  VARCHAR2 DEFAULT 'TEXT',
5   section         IN  VARCHAR2 DEFAULT 'ALL',
6   level           IN  VARCHAR2 DEFAULT 'TYPICAL')
7RETURN CLOB;
8
9DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY (
10   type            IN  VARCHAR2 DEFAULT 'TEXT',
11   section         IN  VARCHAR2 DEFAULT 'ALL',
12   level           IN  VARCHAR2 DEFAULT 'TYPICAL')
13RETURN CLOB;

REPORT_ACTIVITY 函数允许您显示指定时间段内的活动，默认为最后一天。 REPORT_LAST_ACTIVITY 函数报告最后一次自动索引操作。两者都允许您使用以下参数定制输出。

TYPE ：允许的值（文本、HTML、XML）。
SECTION：允许值（SUMMARY、INDEX_DETAILS、VERIFICATION_DETAILS、ERRORS、ALL）。您还可以使用“+”和“-”字符的组合来指示是否应该包括或排除某些内容。例如“SUMMARY +ERRORS”或“ALL -ERRORS”。
LEVEL ：允许值（基本、典型、全部）。

下面显示了从 SQL 中使用这些函数的一些示例。注意 LEVEL 参数的引用。在 SQL 调用中使用 this 时这是必需的，因此它理解这不是对 LEVEL 伪列的引用。

SQL

1set long 1000000 pagesize 0
2
3-- Default TEXT report for the last 24 hours.
4select dbms_auto_index.report_activity() from dual;
5
6-- Default TEXT report for the latest activity.
7select dbms_auto_index.report_last_activity() from dual;
8
9-- HTML Report for the day before yesterday.
10select dbms_auto_index.report_activity(
11         activity_start => systimestamp-2,
12         activity_end   => systimestamp-1,
13         type           => 'HTML')
14from   dual;
15
16-- HTML report for the latest activity.
17select dbms_auto_index.report_last_activity(
18         type => 'HTML')
19from   dual;
20
21-- XML Report for the day before yesterday with all information.
22select dbms_auto_index.report_activity(
23         activity_start => systimestamp-2,
24         activity_end   => systimestamp-1,
25         type           => 'XML',
26         section        => 'ALL',
27         "LEVEL"        => 'ALL')
28from   dual;
29
30-- XML report for the latest activity with all information.
31select dbms_auto_index.report_last_activity(
32         type     => 'HTML',
33         section  => 'ALL',
34         "LEVEL"  => 'ALL')
35from   dual;
36
37set pagesize 14

以下是在创建任何索引之前默认活动报告的输出示例：

SQL

1select dbms_auto_index.report_activity() from dual;
2
3GENERAL INFORMATION
4-------------------------------------------------------------------------------
5 Activity start               : 03-JUN-2019 21:59:21
6 Activity end                 : 04-JUN-2019 21:59:21
7 Executions completed         : 2
8 Executions interrupted       : 0
9 Executions with fatal error  : 0
10-------------------------------------------------------------------------------
11
12SUMMARY (AUTO INDEXES)
13-------------------------------------------------------------------------------
14 Index candidates            : 0
15 Indexes created             : 0
16 Space used                  : 0 B
17 Indexes dropped             : 0
18 SQL statements verified     : 0
19 SQL statements improved     : 0
20 SQL plan baselines created  : 0
21 Overall improvement factor  : 0x
22-------------------------------------------------------------------------------
23
24SUMMARY (MANUAL INDEXES)
25-------------------------------------------------------------------------------
26 Unused indexes    : 0
27 Space used        : 0 B
28 Unusable indexes  : 0
29-------------------------------------------------------------------------------
30
31ERRORS
32---------------------------------------------------------------------------------------------
33No errors found.
34---------------------------------------------------------------------------------------------
35
36SQL>

有关更多信息，请参阅：