签到成功

知道了

CNDBA社区CNDBA社区

Oracle 19c新特性 -- 自动化索引

2019-06-20 17:12 3966 0 原创 Oracle 19c
作者： Expect-乐
本文链接：http://www.cndba.cn/Expect-le/article/3429

说明

oracle 19c中引入了自动化索引：Oracle会根据应用程序负载自动完成索引的创建、重建和删除。

某种程度上可大大提高数据库性能，而无需人工干预。

自动索引提供以下功能：

  • 以指定时间间隔定期运行自动化索引特性。
  • 分析应用程序工作负载，并相应地创建新索引并删除现有性能不佳的索引以提高数据库性能。
  • 重建由于表分区维护操作而标记为不可用的索引，例如ALTER TABLE MOVE。
  • 提供了PL/SQL API接口，用于在数据库中配置自动索引并生成与自动索引操作相关的报告。

注意：

  • 自动化索引时B-tree索引
  • 可以为分区表和非分区表创建自动索引。
  • 无法为临时表创建自动索引

自动化索引工作原理

自动索引过程每15分钟在后台运行，并执行以下操作：
1) 识别自动索引候选列
根据SQL语句中表列的用法来标识自动索引候选列。
2) 为自动索引候选列创建不可见自动索引
不可见自动索引无法被SQL语句使用
3) 验证不可见自动索引对于SQL语句的影响
如果通过使用这些索引能够提高SQL语句的性能，则将索引设置为可见索引。
如果使用这些索引没有能够提高SQL语句的性能，则将索引配置为不可用索引，并将SQL语句列入黑名单。 稍后将通过自动索引过程删除不可用的索引。 以后，黑名单中的SQL语句不允许使用自动索引。http://www.cndba.cn/Expect-le/article/3429

注：第一次执行的SQL语句无法使用自动索引，原因是Oracle无法对该SQL语句做出有效的判断。http://www.cndba.cn/Expect-le/article/3429

4) 删除长时间没有使用的自动索引
长时间没有使用的自动索引将被删除。
默认情况下，超过373天没有使用的自动索引将被删除。可通过DBMS_AUTO_INDEX.CONFIGURE存储过程来配置该时间。http://www.cndba.cn/Expect-le/article/3429http://www.cndba.cn/Expect-le/article/3429

配置自动索引

通过DBMS_AUTO_INDEX.CONFIGURE存储过程来启用、禁用自动索引。

启用、禁用自动索引

启用：

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

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

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

禁用：http://www.cndba.cn/Expect-le/article/3429

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

指定用户启用自动索引

默认情况下，如果启用了自动索引，那么数据库中的所有用户都可以使用自动索引。

除了用户LEI之外，其他用户使用自动索引

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'LEI', FALSE);

只有LEI用户可使用自动索引

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'LEI', NULL);

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

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

指定未使用的非自动索引的保留时间

通过AUTO_INDEX_RETENTION_FOR_MANUAL配置来指定非自动索引（手动创建的索引）的保留时间。如果超过该时间还没被使用的非自动索引也会被删除。

默认情况下，非自动索引是永远不会被删除的。http://www.cndba.cn/Expect-le/article/3429

配置保留时间60天

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_MANUAL', '60');

配置永远不删除

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', NULL);

指定自动化索引日志的保留时间

通过AUTO_INDEX_REPORT_RETENTION配置指定自动化索引日志的保留时间。超过该时间的自动索引日志会被删除。

http://www.cndba.cn/Expect-le/article/3429

默认情况，自动索引日志会在31天后被删除。

指定60天后删除

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

重置保留时间，恢复到31天后删除

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

由于自动化索引报告是根据自动索引日志生成的，所以如果日志被删除了那么是无法生成报告的。http://www.cndba.cn/Expect-le/article/3429

指定存储自动索引的表空间

通过AUTO_INDEX_DEFAULT_TABLESPACE配置指定一个表空间用于存储自动索引。

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

指定表空间TBS_AUTO

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE', 'TBS_AUTO');

指定表空间的百分比用于存储自动索引
指定表空间总大小的百分比用于存储自动索引相关数据。
如：指定5%用于存储自动索引

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SPACE_BUDGET', '5');

生成自动化索引报告

通过DBMS_AUTO_INDEX包中的REPORT_ACTIVITY和REPORT_LAST_ACTIVITY两个存储过程来生成报告。http://www.cndba.cn/Expect-le/article/3429

使用默认配置生成报告

declare
  report clob := null;
begin
  report := DBMS_AUTO_INDEX.REPORT_ACTIVITY();
end;

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

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;

生成最后一次自动化索引操作的报告

declare
  report clob := null;
begin
  report := DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY();
end;

以下示例生成一个报告，其中包含有关上次自动索引操作的基本信息。 该报告包括上次自动索引操作的摘要，索引详细信息和错误信息。 报告以HTML格式生成。

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

DBMS_AUTO_INDEX.REPORT_ACTIVITY和REPORT_LAST_ACTIVITY说明

DBMS_AUTO_INDEX.REPORT_ACTIVITY (
   activity_start  IN  TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP - 1,
   activity_end    IN  TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP,
   type            IN  VARCHAR2 DEFAULT 'TEXT',
   section         IN  VARCHAR2 DEFAULT 'ALL',
   level           IN  VARCHAR2 DEFAULT 'TYPICAL')
RETURN CLOB;

DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY (
   type            IN  VARCHAR2 DEFAULT 'TEXT',
   section         IN  VARCHAR2 DEFAULT 'ALL',
   level           IN  VARCHAR2 DEFAULT 'TYPICAL')
RETURN CLOB;

解释：

  • TYPE : 可选值：TEXT, HTML, XML
  • SECTION : 可选值：SUMMARY, INDEX_DETAILS, VERIFICATION_DETAILS, ERRORS, ALL。可通过+号选择多个值，-号去掉某个值。
  • LEVEL : 可选值：BASIC, TYPICAL, ALL。

自动化索引相关视图

SQL> SELECT view_name
FROM   dba_views
WHERE  view_name LIKE 'DBA_AUTO_INDEX%'
ORDER BY 1;  2    3    4

VIEW_NAME
---------------------------
DBA_AUTO_INDEX_CONFIG
DBA_AUTO_INDEX_EXECUTIONS
DBA_AUTO_INDEX_IND_ACTIONS
DBA_AUTO_INDEX_SQL_ACTIONS
DBA_AUTO_INDEX_STATISTICS
DBA_AUTO_INDEX_VERIFICATIONS

6 rows selected.
视图 说明
DBA_AUTO_INDEX_CONFIG 显示自动化索引当前的配置
DBA_INDEXES
ALL_INDEXES
USER_INDEXES		 AUTO列显示该索引是否是自动化索引

其他索引相关视图

具体例子

查看当前自动化索引的配置

以下是查看CDB、PDB的所有配置。

SQL> col NAME for a10
SQL> col PARAMETER_NAME for a40
SQL> col PARAMETER_VALUE for a15
SQL> col LAST_MODIFIED for a15
SQL> col MODIFIED_BY for a20
SQL> select s.NAME,
       t.PARAMETER_NAME,
       t.PARAMETER_VALUE,
       t.LAST_MODIFIED,
       t.MODIFIED_BY
  from cdb_auto_index_config t, v$containers s
 where t.CON_ID = s.CON_ID

NAME       PARAMETER_NAME                           PARAMETER_VALUE LAST_MODIFIED   MODIFIED_BY
---------- ---------------------------------------- --------------- --------------- --------------------
PDB        AUTO_INDEX_DEFAULT_TABLESPACE
PDB        AUTO_INDEX_MODE                          OFF
PDB        AUTO_INDEX_REPORT_RETENTION              31
PDB        AUTO_INDEX_RETENTION_FOR_AUTO            373
PDB        AUTO_INDEX_RETENTION_FOR_MANUAL
PDB        AUTO_INDEX_SCHEMA
PDB        AUTO_INDEX_SPACE_BUDGET                  50
CDB$ROOT   AUTO_INDEX_DEFAULT_TABLESPACE
CDB$ROOT   AUTO_INDEX_MODE                          OFF
CDB$ROOT   AUTO_INDEX_REPORT_RETENTION              31
CDB$ROOT   AUTO_INDEX_RETENTION_FOR_AUTO            373

NAME       PARAMETER_NAME                           PARAMETER_VALUE LAST_MODIFIED   MODIFIED_BY
---------- ---------------------------------------- --------------- --------------- --------------------
CDB$ROOT   AUTO_INDEX_RETENTION_FOR_MANUAL
CDB$ROOT   AUTO_INDEX_SCHEMA
CDB$ROOT   AUTO_INDEX_SPACE_BUDGET                  50

14 rows selected.

修改配置
启用PDB的自动索引功能

SQL> alter session set container=pdb;
Session altered.

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
PL/SQL procedure successfully completed.

指定表空间存储自动索引数据http://www.cndba.cn/Expect-le/article/3429

SQL> CREATE TABLESPACE AUTO_INDEXES_TS DATAFILE SIZE 100M AUTOEXTEND ON NEXT 100M;
Tablespace created.

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE','AUTO_INDEXES_TS');
PL/SQL procedure successfully completed.

用户LEI启用自动索引

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

PL/SQL procedure successfully completed.

再次查看配置

SQL> select s.NAME,
       t.PARAMETER_NAME,
       t.PARAMETER_VALUE,
       t.LAST_MODIFIED,
       t.MODIFIED_BY
  from cdb_auto_index_config t, v$containers s
 where t.CON_ID = s.CON_ID

NAME       PARAMETER_NAME                           PARAMETER_VALUE LAST_MODIFIED             MODIFIED_BY
---------- ---------------------------------------- --------------- ------------------------- --------------------
PDB        AUTO_INDEX_DEFAULT_TABLESPACE            AUTO_INDEXES_TS 21-JUN-19 12.19.35.000000 SYS
                                                                     AM

PDB        AUTO_INDEX_MODE                          IMPLEMENT       21-JUN-19 12.16.47.000000 SYS
                                                                     AM

PDB        AUTO_INDEX_REPORT_RETENTION              31
PDB        AUTO_INDEX_RETENTION_FOR_AUTO            373
PDB        AUTO_INDEX_RETENTION_FOR_MANUAL
PDB        AUTO_INDEX_SCHEMA                        schema IN (LEI) 21-JUN-19 12.21.02.000000 SYS
                                                                     AM

NAME       PARAMETER_NAME                           PARAMETER_VALUE LAST_MODIFIED             MODIFIED_BY
---------- ---------------------------------------- --------------- ------------------------- --------------------

PDB        AUTO_INDEX_SPACE_BUDGET                  50
CDB$ROOT   AUTO_INDEX_DEFAULT_TABLESPACE
CDB$ROOT   AUTO_INDEX_MODE                          OFF
CDB$ROOT   AUTO_INDEX_REPORT_RETENTION              31
CDB$ROOT   AUTO_INDEX_RETENTION_FOR_AUTO            373
CDB$ROOT   AUTO_INDEX_RETENTION_FOR_MANUAL
CDB$ROOT   AUTO_INDEX_SCHEMA
CDB$ROOT   AUTO_INDEX_SPACE_BUDGET                  50

14 rows selected.

查看报告方式

SET LONG 1000000 PAGESIZE 0

-- 查看默认24小时文本格式的报告.
SELECT DBMS_AUTO_INDEX.report_activity() FROM dual;

-- 查看最后一个自动索引的TEXT格式报告
SELECT DBMS_AUTO_INDEX.report_last_activity() FROM dual;

-- 查看前一天的THML格式的报告.
SELECT DBMS_AUTO_INDEX.report_activity(
         activity_start => SYSTIMESTAMP-2,
         activity_end   => SYSTIMESTAMP-1,
         type           => 'HTML')
FROM   dual;

-- 查看最后一次自动索引操作的HTML报告
SELECT DBMS_AUTO_INDEX.report_last_activity(
         type => 'HTML')
FROM   dual;

-- 查看前一天的XML格式的报告
SELECT DBMS_AUTO_INDEX.report_activity(
         activity_start => SYSTIMESTAMP-2,
         activity_end   => SYSTIMESTAMP-1,
         type           => 'XML',
         section        => 'ALL',
         "LEVEL"        => 'ALL')
FROM   dual;

-- 查看最后一次自动索引的XML报告
SELECT DBMS_AUTO_INDEX.report_last_activity(
         type     => 'HTML',
         section  => 'ALL',
         "LEVEL"  => 'ALL')
FROM   dual;

SET PAGESIZE 14

报告样例：

SQL> SELECT DBMS_AUTO_INDEX.report_last_activity( type => 'HTML', section => 'ALL', "LEVEL" => 'ALL') FROM dual;
Executions completed    : 0
Executions interrupted    : 0
Executions with fatal error    : 0

SUMMARY (AUTO INDEXES)
Index candidates    : 0
Indexes created    : 0
Space used    : 0_B
Indexes dropped    : 0
SQL statements verified    : 0
SQL statements improved    : 0
SQL plan baselines created    : 0
Overall improvement factor    : 0x

SUMMARY (MANUAL INDEXES)
Unused indexes    : 0
Space used    : 0_B
Unusable indexes    : 0

ERRORS
No errors found.

版权声明：本文为博主原创文章，未经博主允许不得转载。

自动化索引

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
Expect-乐

Expect-乐

关注

Without the continuous bitter cold, there can be no fragrant plum blossom

  • 336
    原创
  • 6
    翻译
  • 100
    转载
  • 41
    评论
  • 访问：1571163次
  • 积分：1957
  • 等级：核心会员
  • 排名：第4名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ