Oracle19C 自动索引初探

Automatic indexing 提供如下功能

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

说明：

• 当前版本Auto indexes 是local B-tree 索引
• 支持分区和非分区表，不支持临时表。

Automatic indexing 是如何工作的

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

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

• 识别自动索引候选项

通过捕获SQL历史到SQL repository（包括SQL，执行计划，绑定变量，统计信息等），识别SQL语句中用到列的使用情况来标识自动索引候选项。

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

Invisible Indexes是11g中提出的新功能，缺省对优化器是不可见的，也就是说不会影响到用户既有的SQL语句。

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

如果通过使用这些自动索引提高了SQL语句的性能，那么会将这些索引配置为可见索引，以便在SQL语句中使用它们。
如果使用这些索引不能提高SQL语句的性能，则将这些索引置为unusable的索引，并将对应的SQL语句列入“黑名单”。unusable的索引稍后会被自动索引过程删除。被列入“黑名单”的SQL语句将来不允许使用自动索引。

• 清除unused auto indexes

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

在数据库中配置AutomaticIndexing

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

需要设置隐含参数，否则会报错

--报错如下：
SQL>  EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
BEGIN DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT'); END;
*
ERROR at line 1:
ORA-40216: feature not supported
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 9450
ORA-06512: at "SYS.DBMS_AUTO_INDEX", line 295
ORA-06512: at line 1
--设置隐含参数，并重启数据库
alter system set "_exadata_feature_on"=true scope=spfile;
shutdown immediate
startup

启用和禁用Automatic Indexing特性

--启用自动索引，并将任何新的自动索引创建为可见索引(visible indexes)，以便在SQL语句中使用
 EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
--启用自动索引，但将任何新的自动索引创建为不可见索引(invisible indexes)，所以不能在SQL语句中使用
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');
--禁用自动索引，这样就不会创建新的自动索引，并禁用现有的自动索引。这也是缺省模式。
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');

指定哪些Schemas可以使用Automatic Indexing

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

--eg: SH
 EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'SH', FALSE);
--后期如果又想让某个用户使用自动索引，比如HR，可以通过如下语句来设置
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'HR', NULL);
--恢复到缺省（也就是所有Schema都可以使用自动索引）
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', NULL, TRUE);

为unused auto indexes指定保留期

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

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

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

--指定一个unused手动索引保留期，比如60天，那么到期后，将会被Automatic Indexing Process(自动索引过程)清除。
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_MANUAL', '60');
--恢复到默认值（也就是不给unused 手动索引指定保留期，Automatic Indexing Process不会删除这些索引）
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO',NULL);

为automatic indexing logs指定保留期

--缺省是31天，如下示例表示保留60天
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_REPORT_RETENTION', '60');
--恢复到缺省
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_REPORT_RETENTION', NULL);

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

指定一个表空间存放Auto Indexes

-- 缺省情况下，使用当前数据库的缺省持久表空间。可以通过如果配置指定为其他表空间：
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE', 'MYAUTOTBS');
--对于使用缺省持久表空间存放Auto Indexes的情况，可以通过类似如下过程来指定一个空间限额（可以使用到20%），缺省是50%：
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SPACE_BUDGET', '20');

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

--缺省情况下，使用当前数据库的缺省TEMP表空间。可以通过如果配置指定为其他表空间：
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_TEMP_TABLESPACE', 'MYAUTOTBS_TEMP');

生成Automatic Indexing报告

通过DBMS_AUTO_INDEX包的REPORT_ACTIVITY和REPORT_LAST_ACTIVITY函数可以生成AutomaticIndexing 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;
  /

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

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初体验

检查自动索引缺省配置

SQL> /
PARAMETER_NAME                 PARAMETER_VALUE        LAST_MODIFIED                       MODIFIED_BY
---------------------------------------- ------------------------------ -------------------------------------------------- --------------------------------------------------
AUTO_INDEX_COMPRESSION             OFF
AUTO_INDEX_DEFAULT_TABLESPACE
AUTO_INDEX_MODE              IMPLEMENT            08-JUN-20 03.08.17.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
8 rows selected.
SQL> 1
  1* select * from dba_auto_index_config
SQL> select banner_full from v$version;
BANNER_FULL
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

准备测试数据

alias mytest='sqlplus mytest/test@mypdb'
[oracle@rac19c ~]$ mytest
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 8 15:29:20 2020
Version 19.6.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Last Successful login time: Wed Apr 15 2020 16:17:43 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
SQL> create table test as select * from dba_objects;
Table created.
SQL> insert into test select *from test;
72545 rows created.
SQL> /
145090 rows created.
SQL> /
290180 rows created.
SQL> /
580360 rows created.
SQL> /
1160720 rows created.
SQL> commit;
Commit complete.
SQL> update test set object_id=rownum;
2321440 rows updated.
SQL> commit;
Commit complete.

启用Automatic Indexing

SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE','IMPLEMENT');
PL/SQL procedure successfully completed.
SQL> set lines 200
set pages 999
col parameter_name for a40
col parameter_value for a15
col last_modified for a40
col modified_by for a20
select * from dba_auto_index_config;SQL> SQL> SQL> SQL> SQL> SQL> 
PARAMETER_NAME                 PARAMETER_VALUE LAST_MODIFIED                  MODIFIED_BY
---------------------------------------- --------------- ---------------------------------------- --------------------
AUTO_INDEX_COMPRESSION             OFF
AUTO_INDEX_DEFAULT_TABLESPACE
AUTO_INDEX_MODE              IMPLEMENT     08-JUN-20 04.11.50.000000 PM          MYTEST
AUTO_INDEX_REPORT_RETENTION         31
AUTO_INDEX_RETENTION_FOR_AUTO         373
AUTO_INDEX_RETENTION_FOR_MANUAL
AUTO_INDEX_SCHEMA
AUTO_INDEX_SPACE_BUDGET          50
8 rows selected.

执行测试SQL

select object_name from test where object_id=1;
select object_type from test where object_id=123;
select created from test where object_id=345;

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

--查看自动索引任务执行情况
col EXECUTION_NAME for a40
col ERROR_MESSAGE for a20
alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
select * from dba_auto_index_executions;
--查看该任务与自动索引相关的统计信息
select * from dba_auto_index_statistics where execution_name='SYS_AI_2020-06-08/16:42:12';
EXECUTION_NAME                 STAT_NAME                VALUE
---------------------------------------- ----------------------------- ----------
SYS_AI_2020-06-08/16:42:12         Index candidates            3
SYS_AI_2020-06-08/16:42:12         Indexes created (visible)        1
SYS_AI_2020-06-08/16:42:12         Indexes created (invisible)        0
SYS_AI_2020-06-08/16:42:12         Indexes dropped            0
SYS_AI_2020-06-08/16:42:12         Space used in bytes         45088768
SYS_AI_2020-06-08/16:42:12         Space reclaimed in bytes        0
SYS_AI_2020-06-08/16:42:12         SQL statements verified        3
SYS_AI_2020-06-08/16:42:12         SQL statements improved        3
SYS_AI_2020-06-08/16:42:12         SQL statements managed by SPM        0
SYS_AI_2020-06-08/16:42:12         SQL plan baselines created        0
SYS_AI_2020-06-08/16:42:12         Improvement percentage           100
--查看在自动索引上执行的操作
col index_name for a20
col table_name for a20
col command for a20
col statement for a80
set pages 999
select index_name,table_name,command,statement from dba_auto_index_ind_actions where execution_name='SYS_AI_2020-06-08/16:42:12' order by action_id;
--eg:
INDEX_NAME         TABLE_NAME       COMMAND           STATEMENT
-------------------- -------------------- -------------------- --------------------------------------------------------------------------------
SYS_AI_6q50typ711dvz TESTA_DEP1       CREATE INDEX           CREATE INDEX "DEP1"."SYS_AI_6q50typ711dvz"   ON "DEP1"."TESTA_DEP1"("C1") TABLES
SYS_AI_7371mg6j5bawt TESTB_DEP1       CREATE INDEX           CREATE INDEX "DEP1"."SYS_AI_7371mg6j5bawt"   ON "DEP1"."TESTB_DEP1"("C1") TABLES
SYS_AI_ap6rzrnxmm8k5 TEST          CREATE INDEX           CREATE INDEX "MYTEST"."SYS_AI_ap6rzrnxmm8k5"   ON "MYTEST"."TEST"("OBJECT_ID") T
SYS_AI_ap6rzrnxmm8k5 TEST          REBUILD INDEX        ALTER INDEX "MYTEST"."SYS_AI_ap6rzrnxmm8k5"   REBUILD  ONLINE
SYS_AI_ap6rzrnxmm8k5 TEST          ALTER INDEX VISIBLE  ALTER INDEX "MYTEST"."SYS_AI_ap6rzrnxmm8k5"   VISIBLE
--查看测试表上是否创建了索引
select index_name,index_type,tablespace_name,status,auto from dba_indexes where owner='MYTEST' and table_name='TEST';
--eg:
INDEX_NAME         INDEX_TYPE          TABLESPACE_NAME        STATUS     AUT
-------------------- --------------------------- ------------------------------ -------- ---
SYS_AI_ap6rzrnxmm8k5 NORMAL             USERS                VALID     YES
--查看执行计划
set autotrace on exp
select object_name from test where object_id=1234;

检查下Automatic Indexing 报告

--这里以html格式输出,详细见附件
 set serveroutput on
        declare 
               report clob := null;
        begin
               report := DBMS_AUTO_INDEX.REPORT_ACTIVITY(
                       activity_start => TO_TIMESTAMP('2020-06-08 16:42:12', 'yyyy-mm-ddhh24:mi:ss'),
                       activity_end => TO_TIMESTAMP('2020-06-08 16:43:10', 'yyyy-mm-dd hh24:mi:ss'),
                                 type => 'HTML', 
                             section => 'ALL', 
                                level => 'ALL');
               dbms_output.put_line(report);
end;
/

参考

https://blog.csdn.net/Hehuyi_In/article/details/90481080
https://blog.csdn.net/enmotech/article/details/87870944

附：html代码输出界面。

GENERAL INFORMATION

---

Activity start	: 08-JUN-2020 16:42:12
Activity end	: 08-JUN-2020 16:43:10
Executions completed	: 1
Executions interrupted	: 0
Executions with fatal error	: 0

---

SUMMARY (AUTO INDEXES)

---

Index candidates	: 3
Indexes created (visible / invisible)	: 1 (1 / 0)
Space used (visible / invisible)	: 45.09 MB (45.09_MB / 0_B)
Indexes dropped	“> : 0
SQL statements verified	: 3
SQL statements improved (improvement factor)	: 3 (59085.3x)
SQL plan baselines created	: 0
Overall improvement factor	: 59085.3x

---

SUMMARY (MANUAL
INDEXES)

---

Unused indexes	: 0
Space used	: 0 B
Unusable indexes	“> : 0

---

INDEX
DETAILS

---

1. The following indexes were created:



: invisible

---

Owner	Table	Index	Key	Type	Properties
MYTEST	TEST	SYS_AI_ap6rzrnxmm8k5	OBJECT_ID	B-TREE	NONE

---

VERIFICATION
DETAILS

---

1. The performance of the following statements
improved:

---

Parsing Schema Name	: MYTEST
SQL ID	: 9nd4vtk5wbjbv
SQL Text	: select created from test where object_id=345
Improvement Factor	: 44916x

Execution Statistics:

---

	Original Plan	Auto Index Plan
Elapsed Time (s):	225322	28504
CPU Time (s):	219338	2013
Buffer Gets:	44916	4
Optimizer Cost:	10360	4
Disk Reads:	0	2
Direct Writes:	0	0
Rows Processed:	1	1
Executions:	1	1

PLANS SECTION

---

-
Original

---

Plan Hash Value	: 1357081020

Id	Operation	Name	Rows	Bytes	Cost	Time
0	SELECT STATEMENT				10360
1	. TABLE ACCESS FULL	TEST	16	208	10360	00:00:01

Notes

---

-
optimizer_use_stats_on_conventional_dml =
yes








-
With Auto Indexes

---

Plan Hash Value	: 3677153392

Id	Operation	Name	Rows	Bytes	Cost	Time
0	SELECT STATEMENT		1	13	4	00:00:01
1	. TABLE ACCESS BY INDEX ROWID BATCHED	TEST	1	13	4	00:00:01
2	.. INDEX RANGE SCAN	SYS_AI_ap6rzrnxmm8k5	1		3	00:00:01

Predicate Information (identified by operation id):

---

• 2 -
  
  access(“OBJECT_ID”=345)
  

Notes

---

-

optimizer_use_stats_on_conventional_dml =
yes




- Dynamic sampling used for this statement


( level = 11 )

---

Parsing Schema Name	: MYTEST
SQL ID	: ax5r8mcd7u1w7
SQL Text	: select object_type from test where object_id=123
Improvement Factor	: 44916x

Execution Statistics:

---

	Original Plan	Auto Index Plan
Elapsed Time (s):	293346	232
CPU Time (s):	291256	233
Buffer Gets:	44916	4
Optimizer Cost:	10360	4
Disk Reads:	0	0
Direct Writes:	0	0
Rows Processed:	1	1
Executions:	1	1

PLANS SECTION

---

- Original

---

Plan Hash Value	: 1357081020

Id	Operation	Name	Rows	Bytes	Cost	Time
0	SELECT STATEMENT				10360
1	. TABLE ACCESS FULL	TEST	16	240	10360	00:00:01

Notes

---

-
optimizer_use_stats_on_conventional_dml =
yes








-
With Auto Indexes

---

Plan Hash Value	: 3677153392

Id	Operation	Name	Rows	Bytes	Cost	Time
0	SELECT STATEMENT		2	30	4	00:00:01
1	. TABLE ACCESS BY INDEX ROWID BATCHED	TEST	2	30	4	00:00:01
2	.. INDEX RANGE SCAN	SYS_AI_ap6rzrnxmm8k5	1		3	00:00:01

Predicate Information (identified by operation id):

---

• 2 -
  
  access(“OBJECT_ID”=123)
  

Notes

---

-

optimizer_use_stats_on_conventional_dml =
yes




- Dynamic sampling used for this statement


( level = 11 )

---

Parsing Schema Name	: MYTEST
SQL ID	: csv9p5vfq6kt1
SQL Text	: select object_name from test where object_id=1
Improvement Factor	: 87424x

Execution Statistics:

---

	Original Plan	Auto Index Plan
Elapsed Time (s):	3981732	377
CPU Time (s):	984250	376
Buffer Gets:	87424	4
Optimizer Cost:	10360	4
Disk Reads:	9066	0
Direct Writes:	0	0
Rows Processed:	1	1
Executions:	1	1

PLANS SECTION

---

-
Original

---

Plan Hash Value	: 1357081020

Id	Operation	Name	Rows	Bytes	Cost	Time
0	SELECT STATEMENT				10360
1	. TABLE ACCESS FULL	TEST	16	640	10360	00:00:01

Notes

---

-
optimizer_use_stats_on_conventional_dml =
yes








-
With Auto Indexes

---

Plan Hash Value	: 3677153392

Id	Operation	Name	Rows	Bytes	Cost	Time
0	SELECT STATEMENT		2	80	4	00:00:01
1	. TABLE ACCESS BY INDEX ROWID BATCHED	TEST	2	80	4	00:00:01
2	.. INDEX RANGE SCAN	SYS_AI_ap6rzrnxmm8k5	1		3	00:00:01

Predicate Information (identified by operation id):

---

• 2 -
  
  access(“OBJECT_ID”=1)
  

Notes

---

-

optimizer_use_stats_on_conventional_dml =
yes




- Dynamic sampling used for this statement


( level = 11 )

---

---

ERRORS

---

No errors
found.

---