Oracle 19c 新特性 Automatic indexing 自动化索引

2019年2月22日

老张拉呱

2266

Automatic indexing是Oracle Database 19c开始新增加的特性，从字面上很容易理解，就是依据应用负载的变化自动/动态地进行索引的管理任务，比如创建(create index)、重构(rebuild index)和删除(drop index)，从而提高数据库性能，这个特性也是Oracle 自治数据库云服务自我优化的一个基础。

索引从一开始就是数据库性能的一个基本特性，尽管在并行、分区、压缩、物化视图以及内存列等其他性能特性方面有了很大的进步，但索引仍然是OLTP甚至OLAP中都离不开的关键特性。过去的经历大家都有深刻的体会，创建合适的索引其实是蛮有挑战的，它需要我们对数据模型、应用程序以及数据分布有深入的了解，还需要对数据库系统的内部结构有一定的了解（例如查询优化、缓存区管理等），同时辅助各种Advisor(比如Index Advisor, Partition Advisor, In-Memory Advisor等），靠经验丰富的DBA来完成。现实中，无论优化人员多么的技术熟练，但往往当对数据模型、应用程序代码或数据分布进行修改时，他/她却很少修改索引的选择，所以我们经常会看到很多不必要的索引或不合适的索引导致的性能问题。Automatic indexing将这个复杂的处理实现了自动化，基于手动SQL调优的常用方法，通过构建内置的专家系统，自动捕获SQL（Capture）、识别候选索引（Identify Candidates）、验证（Verify）、决策（Decide）、在线验证（Online Validation）、监控（Monitor）等方法将索引的管理实现了自动化，这个过程并不简单。是不是很酷？

注：Automatic indexing特性对于on-prem环境仅支持Oracle Exadata平台，对于Oracle Cloud各个Cloud Edition都支持。

• Automatic indexing 提供如下功能

   1）定期在预定义的时间间隔内在后台运行自动索引过程
   2）分析应用程序工作负载，并根据分析报告相应地创建必要的新的索引，并删除现有性能不佳的索引，以提高数据库性能
   3）重构由于表分区维护操作(比如ALTER table MOVE)而被标记为不可用的索引(unusable index)
   4）提供PL/SQL APIs，用于配置数据库中的自动索引和生成与自动索引操作相关的报告。
说明：1）当前版本Auto indexes 是local B-tree 索引，将来或许也会支持bitmap、FBI、Partial、domain等类型，我们拭目以待吧。
           2）支持分区和非分区表，不支持临时表。

• Automatic indexing 是如何工作的？

自动索引基于手动SQL调优的常用方法，它不断评估执行的SQL和基础表，以确定要创建哪些索引以及可能删除哪些索引。它通过专家系统完成此任务，该系统验证索引可能做出的改进，并在创建之后验证所做的假设。然后它使用强化学习来确保它不再犯同样的错误。最重要的是，随着数据模型和访问路径的变化，Oracle数据库19c能够随着时间的推移进行调整。

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

   1. 识别自动索引候选项
       通过捕获SQL历史到SQL repository（包括SQL，执行计划，绑定变量，统计信息等），识别SQL语句中用到列的使用情况来标识自动索引候选项。
   2. 为自动索引候选项创建invisible的自动索引
       Invisible Indexes是11g中提出的新功能，缺省对优化器是不可见的，也就是说不会影响到用户既有的SQL语句。而且
   3. 根据SQL语句验证不可见的自动索引
       如果通过使用这些自动索引提高了SQL语句的性能，那么会将这些索引配置为可见索引，以便在SQL语句中使用它们。
       如果使用这些索引不能提高SQL语句的性能，则将这些索引置为unusable的索引，并将对应的SQL语句列入“黑名单”。unusable的索引稍后会被自动索引过程删除。被列入“黑名单”的SQL语句将来不允许使用自动索引。
   4. 清除unused auto indexes
       对于长时间不使用的auto indexes会自动进行清除工作。缺省是373天，可以使用DBMS_AUTO_INDEX.CONFIGURE 过程来配置数据库中保留未使用的自动索引的时间段。

• 在数据库中配置Automatic Indexing
   这个动作通过DBMS_AUTO_INDEX.CONFIGURE过程来完成。
   1、启用和禁用Automatic Indexing特性

◊ 启用自动索引，并将任何新的自动索引创建为可见索引(visible indexes)，以便在SQL语句中使用

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

◊ 启用自动索引，但将任何新的自动索引创建为不可见索引(invisible indexes)，所以不能在SQL语句中使用

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

◊ 禁用自动索引，这样就不会创建新的自动索引，并禁用现有的自动索引。这也是缺省模式。

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');

2、指定哪些Schemas可以使用Automatic Indexing

当在上一步启用Automatic Indexing特性后，缺省情况下所有的Schemas都可以使用Automatic Indexing。管理员可以根据需要允许哪些schema使用自动索引。

比如：以下语句将SH和HR用户添加到排除列表中，这样SH和HR用户就不能使用自动索引:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'SH', FALSE);
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'HR', FALSE);

后期如果又想让某个用户使用自动索引，比如HR，可以通过如下语句来设置

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'HR', NULL);

恢复到缺省（也就是所有Schema都可以使用自动索引）

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

3、为unused auto indexes指定保留期

        下面的语句将未使用的自动索引的保留期设置为90天。
        SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', ' 90 ');
        下面的语句将未使用的自动索引的保留期重置为默认值373天。
        SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', NULL);

4、为unused 非自动索引（non-auto indexes）指定保留期

就是为我们手动创建的unused的索引指定一个保留期，缺省情况下，Automatic Indexing Process(自动索引过程)不会删除unused手动创建的索引。

通过如下过程，指定一个unused手动索引保留期，比如60天，那么到期后，将会被Automatic Indexing Process(自动索引过程)清除。

        SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_MANUAL', '60');
        恢复到默认值（也就是不给unused 手动索引指定保留期，Automatic Indexing Process不会删除这些索引）
        SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', NULL);

5、为automatic indexing logs指定保留期

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

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

恢复到缺省

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

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

6、指定一个表空间存放Auto Indexes

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

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

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

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

7、指定一个临时表空间存放临时Auto Indexes结构

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

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_TEMP_TABLESPACE', 'MYAUTOTBS_TEMP');

• 生成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('2019-02-17', 'YYYY-MM-DD'),
                     activity_end => TO_TIMESTAMP('2019-02-17', 'YYYY-MM-DD'),
                               type => 'HTML',          --支持TEXT、HTML、XML 3中格式，缺省是TEXT
                           section => 'SUMMARY',
                               level => 'BASIC');
       dbms_output.put_line(report);
   end;
   /

关于这2个函数的各个参数的详细说明，参看Oracle Database PL/SQL Packages and Types Reference。

生成最近一次的自动索引报告：

   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;
/

• Automatic Indexing相关的数据字典

   DBA_AUTO_INDEX_CONFIG                           --19.1新增视图，描述当前自动索引的配置
   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上执行的验证自动索引的操作

• Automatic Indexing初体验

1、检查缺省Automatic Indexing配置

    CDB$ROOT@SYS>select banner_full from v$version;

BANNER_FULL

----------------------------------------------------------------------------

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.2.0.0.0

    CDB$ROOT@SYS>select * from DBA_AUTO_INDEX_CONFIG;

PARAMETER_NAME PARAMETER_VALUE LAST_MODIFIED MODIFIED_BY

----------------------------------- -------------------- -------------------- --------------------

AUTO_INDEX_DEFAULT_TABLESPACE

AUTO_INDEX_MODE OFF

AUTO_INDEX_REPORT_RETENTION 31

AUTO_INDEX_RETENTION_FOR_AUTO 373

AUTO_INDEX_RETENTION_FOR_MANUAL

AUTO_INDEX_SCHEMA

AUTO_INDEX_SPACE_BUDGET 50

7 rows selected.

    CDB$ROOT@SYS>

2、准备点测试数据

  PDB1@ZRP>create table test as select * from dba_objects;
        Table created.
        PDB1@ZRP>insert into test select * from test;
        72397 rows created.
        PDB1@ZRP>insert into test select * from test;
        144794 rows created.
        ...   ---反复插入
        PDB1@ZRP>update test set object_id=rownum;

     CDB$ROOT@SYS>alter session set container=pdb1;

Session altered.

     CDB$ROOT@SYS>EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

PL/SQL procedure successfully completed.

     CDB$ROOT@SYS>

     CDB$ROOT@SYS>select * from DBA_AUTO_INDEX_CONFIG;

PARAMETER_NAME PARAMETER_VALUE LAST_MODIFIED MODIFIED_BY

----------------------------------- -------------------- ----------------------------- --------------------

AUTO_INDEX_DEFAULT_TABLESPACE

AUTO_INDEX_MODE IMPLEMENT 17-FEB-19 10.03.59.000000 PM SYS

AUTO_INDEX_REPORT_RETENTION 31

AUTO_INDEX_RETENTION_FOR_AUTO 373

AUTO_INDEX_RETENTION_FOR_MANUAL

AUTO_INDEX_SCHEMA

AUTO_INDEX_SPACE_BUDGET 50

7 rows selected.

     CDB$ROOT@SYS>

4、执行测试SQL

       PDB1@ZRP>select object_name from test where object_id=1;
       PDB1@ZRP>select object_type from test where object_id=123;
       PDB1@ZRP>select created from test where object_id=345;
       ...

5、检查Automatic Indexing Process的执行情况（15分钟以后）

查看自动索引任务执行情况

    PDB1@ZRP>select * from DBA_AUTO_INDEX_EXECUTIONS;

EXECUTION_NAME EXECUTION_START EXECUTION_END ERROR_MESSAGE STATUS

---------------------------------------- ------------------- ------------------- -------------------- ---------

SYS_AI_2019-02-17/22:51:00 2019-02-17 22:51:00 2019-02-17 22:53:07 COMPLETED

    PDB1@ZRP>

查看该任务与自动索引相关的统计信息

    PDB1@ZRP>select * from DBA_AUTO_INDEX_STATISTICS where  EXECUTION_NAME='SYS_AI_2019-02-17/22:51:00';

EXECUTION_NAME STAT_NAME VALUE

---------------------------------------- ----------------------------- ----------

SYS_AI_2019-02-17/22:51:00 Index candidates 1

SYS_AI_2019-02-17/22:51:00 Indexes created (visible) 1

SYS_AI_2019-02-17/22:51:00 Indexes created (invisible) 0

SYS_AI_2019-02-17/22:51:00 Indexes dropped 0

SYS_AI_2019-02-17/22:51:00 Space used in bytes 45088768

SYS_AI_2019-02-17/22:51:00 Space reclaimed in bytes 0

SYS_AI_2019-02-17/22:51:00 SQL statements verified 2

SYS_AI_2019-02-17/22:51:00 SQL statements improved 2

SYS_AI_2019-02-17/22:51:00 SQL statements managed by SPM 0

SYS_AI_2019-02-17/22:51:00 SQL plan baselines created 0

SYS_AI_2019-02-17/22:51:00 Improvement percentage 100

11 rows selected.

    PDB1@ZRP>

查看在自动索引上执行的操作

PDB1@ZRP>select index_name,table_name,command,statement

from DBA_AUTO_INDEX_IND_ACTIONS

where execution_name='SYS_AI_2019-02-17/22:51:00'

order by action_id;

INDEX_NAME TABLE_NAME COMMAND STATEMENT

-------------------- ------------ -------------------- ----------------------------------------

SYS_AI_18sc6rdkngxkh TEST CREATE INDEX CREATE INDEX "ZRP"."SYS_AI_18sc6rdkngxkh" ON "ZRP"."TEST"("OBJECT_ID") TABLESP

SYS_AI_18sc6rdkngxkh TEST REBUILD INDEX ALTER INDEX "ZRP"."SYS_AI_18sc6rdkngxkh" REBUILD ONLINE

SYS_AI_18sc6rdkngxkh TEST ALTER INDEX VISIBLE ALTER INDEX "ZRP"."SYS_AI_18sc6rdkngxkh" VISIBLE

    PDB1@ZRP>

查看测试表上是否真的创建索引

PDB1@ZRP>select index_name,index_type,tablespace_name,status,auto

from dba_indexes

             where owner='ZRP' and table_name='TEST';

INDEX_NAME INDEX_TYPE TABLESPACE_NAME STATUS AUT

-------------------- --------------------------- ------------------------------ -------- ---

SYS_AI_18sc6rdkngxkh NORMAL USERS VALID YES

    PDB1@ZRP>

果然自动创建了索引，看下现在的执行计划

    PDB1@ZRP>set autotrace on exp

    PDB1@ZRP>select object_name from test where object_id=1234;

OBJECT_NAME

-------------------------------------------------------------------------------------------------------------

I_OLAP_TAB$

Execution Plan

----------------------------------------------------------

Plan hash value: 159453698

------------------------------------------------------------------------------------------------------------

    | Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------------------------

    |   0 | SELECT STATEMENT                    |                      |     1 |    41 |     4   (0)| 00:00:01 |

    |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST                 |     1 |    41 |     4   (0)| 00:00:01 |

    |*  2 |   INDEX RANGE SCAN                  | SYS_AI_18sc6rdkngxkh |     1 |       |     3   (0)| 00:00:01 |

------------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):

---------------------------------------------------

       2 - access("OBJECT_ID"=1234)

    PDB1@ZRP>

6、检查下Automatic Indexing 报告

我这里以html格式输出

        set serveroutput on
        declare
                report clob := null;
        begin
                report := DBMS_AUTO_INDEX.REPORT_ACTIVITY(
                        activity_start => TO_TIMESTAMP('2019-02-17 22:51:00', 'yyyy-mm-dd hh24:mi:ss'),
                        activity_end => TO_TIMESTAMP('2019-02-17 22:53:07', 'yyyy-mm-dd hh24:mi:ss'),
                                  type => 'HTML',
                              section => 'ALL',
                                 level => 'ALL');
                dbms_output.put_line(report);
        end;
        /

详细的输出报告，点下方链接

http://enmotech.com/web/detail/1/648/1.html

查看。