Oracle 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
…
Version 19.3.0.0.0

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@ludadbhost(source)> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
1
2 PDB$SEED READ ONLY NO
3 ludadbhostPDB READ WRITE NO
1
2
12:29:58 SYS@ludadbhost(source)> alter session set container=ludadbhostpdb;

Session altered.

12:30:29 SYS@ludadbhost(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@ludadbhost(source)> alter system set “_exadata_feature_on”=true scope=spfile;

System altered.

12:33:48 SYS@ludadbhost(source)> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
12:34:22 SYS@ludadbhost(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@ludadbhost(source)> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,‘IMPLEMENT’);

PL/SQL procedure successfully completed. —在PDB中进行完成
12:36:12 SYS@ludadbhost(source)> col PARAMETER_VALUE for a20
12:36:19 SYS@ludadbhost(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@ludadbhost(source)> alter session set container=ludadbhostpdb;

Session altered.

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

/data/u19/app/oracle/oradata/ludadbhost/ludadbhostpdb/system01.dbf
/data/u19/app/oracle/oradata/ludadbhost/ludadbhostpdb/sysaux01.dbf
/data/u19/app/oracle/oradata/ludadbhost/ludadbhostpdb/undotbs01.dbf
/data/u19/app/oracle/oradata/ludadbhost/ludadbhostpdb/users01.dbf

10:56:16 SYS@ludadbhost(source)> CREATE TABLESPACEluda_data DATAFILE ‘/data/u19/app/oracle/oradata/ludadbhost/ludadbhostpdb/LUDA_data01.dbf’ SIZE 5G AUTOEXTEND Off;
Tablespace created.

10:58:33 SYS@ludadbhost(source)> 10:58:33 SYS@ludadbhost(source)> CREATE TABLESPACEluda_idx DATAFILE ‘/data/u19/app/oracle/oradata/ludadbhost/ludadbhostpdb/LUDA_idx01.dbf’ SIZE 2G AUTOEXTEND Off;
Tablespace created.

10:58:45 SYS@ludadbhost(source)> 10:58:45 SYS@ludadbhost(source)> create userluda IDENTIFIED BYluda ACCOUNT UNLOCK DEFAULT TABLESPACEluda_data TEMPORARY TABLESPACE TEMP;
User created.

10:58:55 SYS@ludadbhost(source)> grant connect,resource toluda;
Grant succeeded.

10:59:00 SYS@ludadbhost(source)> grant select any table toluda;
Grant succeeded.

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

Table created.

11:37:47luda@ludadbhost(source)> select count(*) from index_test;

COUNT(*)
72406

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

72406 rows created.

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

144812 rows created.

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

289624 rows created.

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

579248 rows created.

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

1158496 rows created.

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

2316992 rows created.

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

4633984 rows created.

11:39:33luda@ludadbhost(source)> select count(*) from index_test;

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

9267968 rows created.

11:40:13luda@ludadbhost(source)> 11:40:13luda@ludadbhost(source)> select count(*) from index_test;

COUNT(*)
18535936

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

18535936 rows updated.

11:45:06luda@ludadbhost(source)> commit;

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

OBJECT_TYPE
VIEW

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

Explained.

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

PLAN_TABLE_OUTPUT

Plan hash value: 356488860

| 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:03luda@ludadbhost(source)> select object_type from index_test where object_id=5559;
14:05:03luda@ludadbhost(source)> select object_type from index_test where object_id=50;
14:05:03luda@ludadbhost(source)> select object_type from index_test where object_id=51;
14:05:03luda@ludadbhost(source)> select object_type from index_test where object_id=52;
14:05:03luda@ludadbhost(source)> select created from index_test where object_id=345;
15:38:33luda@ludadbhost(source)> select * from DBA_AUTO_INDEX_EXECUTIONS;

15:55:34luda@ludadbhost(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 “LUDA”.“SYS_AI_66825yg9wksv1” ON “LUDA”.“INDEX_TEST”(“OBJECT_ID”) T
SYS_AI_66825yg9wksv1 INDEX_TEST REBUILD INDEX ALTER INDEX “LUDA”.“SYS_AI_66825yg9wksv1” REBUILD ONLINE
SYS_AI_66825yg9wksv1 INDEX_TEST ALTER INDEX VISIBLE ALTER INDEX “LUDA”.“SYS_AI_66825yg9wksv1” VISIBLE
分了三步，第一创建一个索引，第二步设置online属性，第三步设置为可见状态；

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

案例参考csdn

Oracle 恢复工具 Mdata 5.0.1 版本发布

近期文章

分类目录

扫码关注微信公众号:Oracle运维那些事获取定期发布的数据库运维的有趣事情!

近期活动

Oracle 恢复工具 Mdata 5.0.1 版本发布

近期文章

分类目录

扫码关注微信公众号:Oracle运维那些事 获取定期发布的数据库运维的有趣事情!

近期活动

扫码关注微信公众号:Oracle运维那些事获取定期发布的数据库运维的有趣事情!