Oracle 19C新特性之-Automatic indexing 自动化索引

自从我们公司使用了12C列式数据库以后，在一些特定场景下得到了一些特殊的应用。如它的IN-MEM特性。确实是在项目初期给我们生产性能带来了质的飞跃，同时也肩负着吃螃蟹的风险，遇到了几个12C的Bug，19C上市至今我们的生产环境没有真正的去做迁移部署。最近有时间，测试了一下19C的Automatic indexing特性。人狠话不多开整！

Automatic indexing是Oracle Database 19c开始新增加的特性，从字面上很容易理解，就是依据应用负载的变化自动/动态地进行索引的管理任务，比如创建(create index)、重构(rebuild index)和删除(drop index)，从而提高数据库性能，这个特性也是Oracle 自治数据库云服务自我优化的一个基础。
Automatic indexing 主要功能
1）定期在预定义的时间间隔内在后台运行自动索引过程
2）分析应用程序工作负载，并根据分析报告相应地创建必要的新的索引，并删除现有耗费性能的

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上执行的验证自动索引的操作

DBMS_AUTO_INDEX.CONFIGURE包相关参数
AUTO_INDEX_DEFAULT_TABLESPACE –指定自动索引创建所存储的表空间
AUTO_INDEX_MODE –指定自动索引的模式(开关)，当前3个值，默认OFF,表示特性关闭；IMPLEMENT表示自动创建创建、测试、并报告，最终索引是visible状态; REPORT ONLY 会创建索引但是invisible,不会影响SQL，只是意图生成报告。
AUTO_INDEX_REPORT_RETENTION –自动索引报告历史保留的天数 默认31天
AUTO_INDEX_RETENTION_FOR_AUTO — 自动创建的索引从上次使用后多少天不再使用的索引可以删除，默认373天
AUTO_INDEX_RETENTION_FOR_MANUAL — 手动创建的索引从上次使用后多少天不再使用的索引可以删除，默认永远
AUTO_INDEX_SPACE_BUDGET — 自动索引可以使用表空间大小的百分比，默认 50%

Automatic Indexing工作原理
索引管理后台进程TASK调用，可以自动的create, rebuild , drop 索引。后台进程是每15分钟调用一次,(是有j001进程执行_AUTO_INDEX_TASK_INTERVAL参数控制15分钟)。
也是基于传统手动优化SQL的思路，基于SQL中的列使用识别可以创建的索引，然后验证自动索引对性能的影响，然后按预设的值去创建索引，只不过整个过程是自动的，并且整个过程都有审核报告。

Automatic Indexing测试体验
1，查看Oracle数据库版本
[oracle19@source ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 7 10:44:01 2020
Version 19.3.0.0.0

Copyright © 1982, 2019, Oracle. All rights reserved.

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

SQL> select banner_full from v$version;

BANNER_FULL
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> 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

2，开启该特性，可以在CDB也可以在PDB
12:29:43 SYS@yjf19c(source)> show pdbs;

CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---

     2 PDB$SEED                       READ ONLY  NO
     3 YJF19CPDB                      READ WRITE NO

12:29:58 SYS@yjf19c(source)> alter session set container=yjf19cpdb;

Session altered.

12:30:29 SYS@yjf19c(source)> 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 9180
ORA-06512: at “SYS.DBMS_AUTO_INDEX”, line 283
ORA-06512: at line 1
通过MOS查询需要开启一个隐患参数并重启数据库服务
12:33:45 SYS@yjf19c(source)> alter system set “_exadata_feature_on”=true scope=spfile;

System altered.

12:33:48 SYS@yjf19c(source)> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
12:34:22 SYS@yjf19c(source)> startup
ORACLE instance started.

Total System Global Area 2147483552 bytes
Fixed Size 9146272 bytes
Variable Size 1090519040 bytes
Database Buffers 1023410176 bytes
Redo Buffers 24408064 bytes
Database mounted.
Database opened.
12:34:47 SYS@yjf19c(source)> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,‘IMPLEMENT’);

PL/SQL procedure successfully completed. —在PDB中进行完成
12:36:12 SYS@yjf19c(source)> col PARAMETER_VALUE for a20
12:36:19 SYS@yjf19c(source)> /

PARAMETER_NAME PARAMETER_VALUE LAST_MODIFIED

---

AUTO_INDEX_COMPRESSION OFF
AUTO_INDEX_DEFAULT_TABLESPACE
AUTO_INDEX_MODE IMPLEMENT 07-JAN-20 12.35.14.000000 PM
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.
3，创建用户以及对应数据表空间
10:55:52 SYS@yjf19c(source)> alter session set container=yjf19cpdb;

Session altered.

10:55:59 SYS@yjf19c(source)> select file_name from dba_data_files;

/data/u19/app/oracle/oradata/YJF19C/yjf19cpdb/system01.dbf
/data/u19/app/oracle/oradata/YJF19C/yjf19cpdb/sysaux01.dbf
/data/u19/app/oracle/oradata/YJF19C/yjf19cpdb/undotbs01.dbf
/data/u19/app/oracle/oradata/YJF19C/yjf19cpdb/users01.dbf

10:56:16 SYS@yjf19c(source)> CREATE TABLESPACE yjf_data DATAFILE ‘/data/u19/app/oracle/oradata/YJF19C/yjf19cpdb/yjf_data01.dbf’ SIZE 5G AUTOEXTEND Off;
Tablespace created.

10:58:33 SYS@yjf19c(source)> 10:58:33 SYS@yjf19c(source)> CREATE TABLESPACE yjf_idx DATAFILE ‘/data/u19/app/oracle/oradata/YJF19C/yjf19cpdb/yjf_idx01.dbf’ SIZE 2G AUTOEXTEND Off;
Tablespace created.

10:58:45 SYS@yjf19c(source)> 10:58:45 SYS@yjf19c(source)> create user yjf IDENTIFIED BY yjf ACCOUNT UNLOCK DEFAULT TABLESPACE yjf_data TEMPORARY TABLESPACE TEMP;
User created.

10:58:55 SYS@yjf19c(source)> grant connect,resource to yjf;
Grant succeeded.

10:59:00 SYS@yjf19c(source)> grant select any table to yjf;
Grant succeeded.

4，创建数据
11:37:34 YJF@yjf19c(source)> create table index_test as select * from dba_objects;

Table created.

11:37:47 YJF@yjf19c(source)> select count(*) from index_test;

COUNT(*)
72406

11:38:50 YJF@yjf19c(source)> insert into index_test select * from index_test;

72406 rows created.

11:39:03 YJF@yjf19c(source)> insert into index_test select * from index_test;

144812 rows created.

11:39:07 YJF@yjf19c(source)> insert into index_test select * from index_test;

289624 rows created.

11:39:09 YJF@yjf19c(source)> insert into index_test select * from index_test;

579248 rows created.

11:39:13 YJF@yjf19c(source)> insert into index_test select * from index_test;

1158496 rows created.

11:39:16 YJF@yjf19c(source)> insert into index_test select * from index_test;

2316992 rows created.

11:39:22 YJF@yjf19c(source)> insert into index_test select * from index_test;

4633984 rows created.

11:39:33 YJF@yjf19c(source)> select count(*) from index_test;

COUNT(*)
9267968
11:39:45 YJF@yjf19c(source)> insert into index_test select * from index_test;

9267968 rows created.

11:40:13 YJF@yjf19c(source)> 11:40:13 YJF@yjf19c(source)> select count(*) from index_test;

COUNT(*)
18535936

11:41:49 YJF@yjf19c(source)> update index_test set object_id=rownum;

18535936 rows updated.

11:45:06 YJF@yjf19c(source)> commit;

5，模拟日常查询并查看执行计划
13:59:06 YJF@yjf19c(source)> select object_type from index_test where object_id=5555;

OBJECT_TYPE
VIEW

13:59:21 YJF@yjf19c(source)> explain plan for SELECT OBJECT_NAME FROM INDEX_TEST WHERE OBJECT_ID=1;

Explained.

13:59:35 YJF@yjf19c(source)> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

Plan hash value: 356488860

---

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

| 0 | SELECT STATEMENT | | 1 | 40 | 392 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| INDEX_TEST | 1 | 40 | 392 (1)| 00:00:01 |

Predicate Information (identified by operation id):

1 - filter(“OBJECT_ID”=1)

13 rows selected.
该访问路径是全表扫描的方式；我们多次执行以下语句，并静等15分钟查看相关试图是否有结果
14:05:03 YJF@yjf19c(source)> select object_type from index_test where object_id=5559;
14:05:03 YJF@yjf19c(source)> select object_type from index_test where object_id=50;
14:05:03 YJF@yjf19c(source)> select object_type from index_test where object_id=51;
14:05:03 YJF@yjf19c(source)> select object_type from index_test where object_id=52;
14:05:03 YJF@yjf19c(source)> select created from index_test where object_id=345;
15:38:33 YJF@yjf19c(source)> select * from DBA_AUTO_INDEX_EXECUTIONS;

EXECUTION_NAME EXECUTION EXECUTION ERROR_MESSAGE STATUS

---

SYS_AI_2020-01-07/15:10:55 07-JAN-20 07-JAN-20 COMPLETED
SYS_AI_2020-01-07/15:25:56 07-JAN-20 07-JAN-20 COMPLETED
SYS_AI_2020-01-07/14:55:54 07-JAN-20 07-JAN-20 COMPLETED
SYS_AI_2020-01-07/14:10:51 07-JAN-20 07-JAN-20 COMPLETED
SYS_AI_2020-01-07/14:25:52 07-JAN-20 07-JAN-20 COMPLETED
SYS_AI_2020-01-07/14:40:53 07-JAN-20 07-JAN-20 COMPLETED

15:55:34 YJF@yjf19c(source)> select index_name,table_name,command,statement from DBA_AUTO_INDEX_IND_ACTIONS where execution_name=‘SYS_AI_2020-01-07/15:40:57’ order by action_id;

INDEX_NAME TABLE_NAME COMMAND STATEMENT

---

SYS_AI_66825yg9wksv1 INDEX_TEST CREATE INDEX CREATE INDEX “YJF”.“SYS_AI_66825yg9wksv1” ON “YJF”.“INDEX_TEST”(“OBJECT_ID”) T
SYS_AI_66825yg9wksv1 INDEX_TEST REBUILD INDEX ALTER INDEX “YJF”.“SYS_AI_66825yg9wksv1” REBUILD ONLINE
SYS_AI_66825yg9wksv1 INDEX_TEST ALTER INDEX VISIBLE ALTER INDEX “YJF”.“SYS_AI_66825yg9wksv1” VISIBLE
分了三步，第一创建一个索引，第二步设置online属性，第三步设置为可见状态；

6，检查该SYS_AI索引是否真实存在
15:56:43 YJF@yjf19c(source)> explain plan for select object_name from index_test where object_id=1;

Explained.

15:56:59 YJF@yjf19c(source)> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
Plan hash value: 208498442

| 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| INDEX_TEST | 1 | 41 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SYS_AI_66825yg9wksv1 | 1 | | 3 (0)| 00:00:01 |

Predicate Information (identified by operation id):

2 - access(“OBJECT_ID”=1)

15:55:34 YJF@yjf19c(source)> select index_name,index_type,tablespace_name,status,auto from dba_indexes where owner=‘YJF’ and table_name=‘INDEX_TEST’;

INDEX_NAME INDEX_TYPE TABLESPACE_NAME STATUS AUT

---

SYS_AI_66825yg9wksv1 NORMAL YJF_DATA VALID YES

这个是个双刃剑，当然它可以排除掉特定的用户不需要该特性，在数据库自治时代是否可以运用得当还需要我们拭目以待。可以自动Tuning并做出创建动作。把风险点降到最低。