签到成功

知道了

CNDBA社区CNDBA社区

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

2021-01-28 18:21 551 0 原创 Oracle 19c
作者: Dave

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

https://www.cndba.cn/dave/article/4372

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相关的数据字典如下:https://www.cndba.cn/dave/article/4372

  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’);

禁用自动索引:https://www.cndba.cn/dave/article/4372

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

2.4 指定特定用户启用Automatic Indexing

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

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

https://www.cndba.cn/dave/article/4372

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

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

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

只有DAVE用户可以使用自动索引:https://www.cndba.cn/dave/article/4372

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

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

https://www.cndba.cn/dave/article/4372

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天)https://www.cndba.cn/dave/article/4372

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就不能生成了。

https://www.cndba.cn/dave/article/4372

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

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_REPORT_RETENTION’, ‘60’);https://www.cndba.cn/dave/article/4372

恢复到缺省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;https://www.cndba.cn/dave/article/4372

最近一次自动索引操作的摘要、索引详细信息和错误信息,以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);

删除所有索引,除用于约束的索引外的所有索引:

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社区的观点或立场
Dave

Dave

关注

人的一生应该是这样度过的:当他回首往事的时候,他不会因为虚度年华而悔恨,也不会因为碌碌无为而羞耻;这样,在临死的时候,他就能够说:“我的整个生命和全部精力,都已经献给世界上最壮丽的事业....."

  • 1902
    原创
  • 2
    翻译
  • 456
    转载
  • 131
    评论
  • 访问:3641974次
  • 积分:3137
  • 等级:核心会员
  • 排名:第1名
精华文章
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        Copyright © 2016 All Rights Reserved. Powered by ZhiXinTech · 皖ICP备19020168号·

        QQ交流群