Oracle 19c 新特性自动化索引(Automatic Indexing) 说明 -- cnDBA.cn

1 自动化索引(Automatic Indexing) 概述

Automatic indexing是Oracle 19c引入的新特性，官网对其描述如下：

21.7 Managing Auto Indexes
https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-indexes.html#GUID-D1285CD5-95C0-4E74-8F26-A02018EA7999

自动化索引(Automatic Indexing) 依据应用负载的变化自动/动态地进行索引的管理任务，比如创建(create index)、重构(rebuild index)和删除(drop index)，从而提高数据库性能。

Automatic indexing 提供如下功能

1）以指定的时间间隔定期运行自动化索引特性。
2）分析应用程序工作负载，并根据分析报告相应地创建必要的新的索引，并删除现有性能不佳的索引，以提高数据库性能
3）重构由于表分区维护操作(比如ALTER table MOVE)而被标记为不可用的索引(unusable index)
4）提供PL/SQL API接口，用于配置数据库中的自动索引和生成与自动索引操作相关的报告。

说明：

1)当前版本Auto indexes 是local B-tree 索引。
2)支持分区和非分区表（Only Local Index），不支持临时表。
3)自动创建只针对 = 的查询，不支持：范围、模糊、Min/Max等查询。

目前自动化索引只支持Oracle Exadata平台和Oracle Cloud。在非XD平台，也不建议使用该特性，但如果是模拟测试，可以通过修改”_exadata_feature_on” 参数来测试。

自动化索引(Automatic Indexing) 的执行过程包含五个步骤：自动捕获SQL（Capture）、识别候选索引（Identify Candidates）、验证（Verify）、决策（Decide）、监控（Monitor）。

Capture（获取）：

·定期从ASTS（Automatic SQL Tuning Set）内的应用程序工作负载中获取SQL语句
·SQL、执行计划、绑定变量、执行统计等

ldentify Candidates（选定后补）

·识别并生成抓取的SQL有帮助的候补索引
·创建具有Unusable and Invisible属性的候补索引（metadata only）

Verify（确认）

·确认候补索引是否用于SQL
·物理生成Invisible索引，执行SQL，测试索引是否能够提升性能
·测试在应用程序之外执行

Decide（决定）

·如果所有SQL的性能都提高，索引就会变成Visible
·如果所有SQL的性能都变差，索引就会保持Invisible
·虽然有些SQL会导致性能下降，但如果整体性能提高，索引就会变成Visible

Monitor（监控）

·对使用索引的持续监测。
·删除长期未使用的自动生成的索引。

Automatic Indexing Process(自动索引过程)以后台服务进程每隔15分钟运行一次，并执行如下任务：

1. 识别自动索引候选项

通过捕获SQL历史到SQL repository（包括SQL，执行计划，绑定变量，统计信息等），识别SQL语句中用到列的使用情况来标识自动索引候选项。要确保捕获到的表的统计信息是最新的，在没有启用real-time statistics的情况下，如果表没有统计信息或者统计信息陈旧，则不会进行自动索引。

Oracle 19c 新特性实时统计信息收集(real-time statistics) 说明
https://www.cndba.cn/dave/article/4370

2. 为自动索引候选项创建invisible的自动索引

此步骤创建的自动索引是不可见的，无法在SQL 语句中使用，也就是说不会影响到用户既有的SQL语句。

自动索引可以是单列也可以是多列，主要考虑以下2个因素：

1）表列是否包含虚拟列。
2）是否是分区表。

3. 根据SQL语句验证不可见的自动索引

如果通过使用这些自动索引提高了SQL语句的性能，那么会将这些索引配置为可见索引，以便在SQL语句中使用它们。

如果使用这些索引不能提高SQL语句的性能，则将这些索引置为unusable的索引，并将对应的SQL语句列入“黑名单”。unusable的索引稍后会被自动索引过程删除。被列入“黑名单”的SQL语句将来不允许使用自动索引。

4. 清除unused auto indexes

对于长时间不使用的auto indexes会自动进行清除工作。缺省是373天，可以使用DBMS_AUTO_INDEX.CONFIGURE过程来配置数据库中保留未使用的自动索引的时间段。

Automatic Indexing相关的数据字典如下：

  DBA_AUTO_INDEX_CONFIG：查看当前自动索引的配置
  DBA_INDEXES/ALL_INDEXES/USER_INDEXES：新增加的AUTO列标识是自动索引(YES)还是手动索引(NO)

  DBA_AUTO_INDEX_EXECUTIONS：自动索引任务执行的历史记录
  DBA_AUTO_INDEX_STATISTICS ：与自动索引相关的统计信息

  DBA_AUTO_INDEX_IND_ACTIONS：自动索引上执行的操作
  DBA_AUTO_INDEX_SQL_ACTIONS：在SQL上执行的验证自动索引的操作

自动索引可以通过DBAINDEXES、ALL_INDEXES、USER_INDEXES 视图进行查询，其中自动索引的前缀是SYS_AI,同时在视图的AUTO 列也会显示为YES。而普通索引则显示为NO。

2 操作示例

2.1 启用XD 特性

[dave@www.cndba.cn ~]$ sql dave/dave@dave

SQLcl: Release 19.1 Production on Thu Jan 28 14:15:07 2021

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Thu Jan 28 2021 14:15:07 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0


SQL> alter system set "_exadata_feature_on"=true scope=spfile;
System altered.

SQL> startup force

Total System Global Area   1543500824 bytes
Fixed Size                    9135128 bytes
Variable Size              1308622848 bytes
Database Buffers            218103808 bytes
Redo Buffers                  7639040 bytes
Database mounted.
Database opened.
SQL>

2.2 配置自动化索引存储过程

自动化索引(Automatic Indexing)是动作通过DBMS_AUTO_INDEX.CONFIGURE过程来完成。该函数的的说明可以参考官网：

CONFIGURE Procedure
https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_AUTO_INDEX.html#GUID-93A19936-453A-4C62-8DFB-FB52AC70C838

这里简单列举如下：

1)AUTO_INDEX_MODE: 启用、禁用自动索引
2)AUTO_INDEX_SCHEMA: 指定用户启用自动索引
3)AUTO_INDEX_RETENTION_FOR_AUTO: 指定未使用索引的保留时间
4)AUTO_INDEX_DEFAULT_TABLESPACE：指定索引的表空间
5)REPORT_ACTIVITY/REPORT_LAST_ACTIVITY: 生成自动化索引报告
6)DROP_AUTO_INDEXES/DROP_SECONDARY_INDEXES: 删除自动索引
7)AUTO_INDEX_REPORT_RETENTION: 为自动索引logs 指定保留期
8)AUTO_INDEX_COMPRESSION: 索引压缩

查看自动索引的后台任务，这些任务每15分钟运行一次：

SQL>select task_name,description,advisor_name from dba_advisor_tasks;

2.3 启用和禁用Automatic Indexing特性

启用自动索引：

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

启用自动索引，但只作为不可见自动索引，无法被SQL所使用：

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

禁用自动索引：

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,’OFF’);
http://www.cndba.cn/cndba/dave/article/4372

2.4 指定特定用户启用Automatic Indexing

缺省情况下所有的Schemas都可以使用Automatic Indexing。也可以使用如下参数指定特定用户。

除了DAVE 用户之外，其他用户都可以使用自动索引：

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

恢复DAVE用户使用自动索引：

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

只有DAVE用户可以使用自动索引：

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

所有用户都可以使用自动索引：

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

2.5 指定索引保留期

配置手工创建的索引，未使用60天时删除（未配置时默认永不删除）

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

配置手工创建的索引永不删除（未配置时默认永不删除）

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

配置自动索引，在未使用60天后删除（默认373天）

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

将未使用的自动索引的保留期重置为默认值373天。

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

2.6 指定Auto Indexes的存放表空间

缺省情况下，使用当前数据库的缺省持久表空间。可以通过如果配置指定为其他表空间：

[dave@www.cndba.cn ~]$ sql dave/dave@dave

SQLcl: Release 19.1 Production on Thu Jan 28 14:15:07 2021

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Thu Jan 28 2021 14:15:07 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0


SQL>
SQL> select file_name from dba_data_files;
/u01/app/oracle/oradata/CNDBA/dave/undotbs01.dbf
/u01/app/oracle/oradata/CNDBA/dave/sysaux01.dbf
/u01/app/oracle/oradata/CNDBA/dave/system01.dbf
/u01/app/oracle/oradata/CNDBA/dave/users01.dbf

SQL> create tablespace dave datafile '/u01/app/oracle/oradata/CNDBA/dave/dave01.dbf' size 20m;

Tablespace created.

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE', 'DAVE');

PL/SQL procedure successfully completed.

对于使用缺省持久表空间存放Auto Indexes的情况，可以通过类似如下过程来指定一个空间限额（可以使用到20%），缺省是50%：

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

2.7 指定automatic indexing logs保留期

Automatic indexing reports的生成依赖于automatic indexing logs。所以，当过了automatic indexing logs的保留期后，相应的automatic indexing reports就不能生成了。

缺省是373天，如下示例表示保留60天

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

恢复到缺省373天：

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

2.8 启用自动索引压缩

启用：

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

恢复默认值：

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

2.9 生成Automatic Indexing报告

通过DBMS_AUTO_INDEX包的REPORT_ACTIVITY和REPORT_LAST_ACTIVITY函数可以生成Automatic Indexing Reports。

指定时间间隔、报告类型、级别、内容生成报告：

  set serveroutput on
  declare 
       report clob := null;
   begin
       report := DBMS_AUTO_INDEX.REPORT_ACTIVITY(
                   activity_start => TO_TIMESTAMP('2021-01-27', 'YYYY-MM-DD'),
                   activity_end => TO_TIMESTAMP('2021-01-27', 'YYYY-MM-DD'),
                   type => 'HTML',      
                   section => 'ALL',   
                   level => 'BASIC');

      dbms_output.put_line(report);
   end;
  /

具体参数说明参考官方手册里的说明。

通过sqlplus 简化查看：

SQL> select DBMS_AUTO_INDEX.REPORT_ACTIVITY(sysdate-1/24,NULL,’text’,’all’,’all’) REPORT from dual;

最近一次自动索引操作的摘要、索引详细信息和错误信息，以HTML格式生成：

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

sqlplus 简化查看：

select DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY() from dual;

2.10 查看自动索引配置

查看当前PDB的自动索引配置信息：

SQL> col PARAMETER_NAME for a40
SQL> col PARAMETER_VALUE for a30
SQL> col MODIFIED_BY for a20
SQL> select * from dba_auto_index_config;

PARAMETER_NAME                           PARAMETER_VALUE                LAST_MODIFIED                   MODIFIED_BY
---------------------------------------- ------------------------------ ------------------------------- --------------------
AUTO_INDEX_COMPRESSION                   OFF                            28-JAN-21 05.36.12.000000000 PM DAVE
AUTO_INDEX_DEFAULT_TABLESPACE            DAVE                           28-JAN-21 05.33.19.000000000 PM DAVE
AUTO_INDEX_MODE                          IMPLEMENT                      28-JAN-21 05.12.56.000000000 PM DAVE
AUTO_INDEX_REPORT_RETENTION              373                            28-JAN-21 05.34.03.000000000 PM DAVE
AUTO_INDEX_RETENTION_FOR_AUTO            373                            28-JAN-21 05.31.05.000000000 PM DAVE
AUTO_INDEX_RETENTION_FOR_MANUAL                                         28-JAN-21 05.30.52.000000000 PM DAVE
AUTO_INDEX_SCHEMA                                                       28-JAN-21 05.30.35.000000000 PM DAVE
AUTO_INDEX_SPACE_BUDGET                  20                             28-JAN-21 05.33.24.000000000 PM DAVE

8 rows selected.

查看CDB和所有PDB的配置信息：

SQL> select * from CDB_AUTO_INDEX_CONFIG;

2.11 查看自动索引收集周期

自动索引的执行周期默认为900秒（15分钟），查询如下：

SQL> select * from SYS.SMB$CONFIG where parameter_name like '%AUTO_INDEX_TASK_INTERVAL%';

PARAMETER_NAME                                     PARAMETER_VALUE LAST_UPDATED                    UPDATED_BY      PARAMETER_DATA
--------------------------------------------- -------------------- ------------------------------- --------------- ---------------
_AUTO_INDEX_TASK_INTERVAL                                      900

2.12 删除自动索引

删除单个自动索引，并支持重新创建：

SQL>exec dbms_auto_index.drop_auto_indexes(‘DAVE’,’”SYS_AI_612ud3j5ngf0c”‘,TRUE);

删除所有DAVE的索引，并允许重新创建：

SQL>EXEC DBMS_AUTO_INDEX.DROP_AUTO_INDEXES(‘DAVE’,NULL,TRUE);
http://www.cndba.cn/cndba/dave/article/4372

删除所有索引，除用于约束的索引外的所有索引：

SQL>EXEC DBMS_AUTO_INDEX.DROP_SECONDARY_INDEXES(‘DAVE’,NULL);
SQL>EXEC DBMS_AUTO_INDEX.DROP_SECONDARY_INDEXES(‘DAVE’,’CNDBA’); —删除只针对表CNDBA的index

2.13 通过HINTS 使用自动索引

SQL> create table cndba as select from all_objects;
SQL> select /+ USE_AUTO_INDEXES / OBJECT_ID,OBJECT_NAME FROM cndba WHERE OBJECT_ID <5; SQL> select /+ NO_USE_AUTO_INDEXES */ OBJECT_ID,OBJECT_NAME FROM cndba WHERE OBJECT_ID <5;

签到成功

CNDBA社区