内容简介：在上一篇Automatic index是有索引管理后台进程TASK调用， 可以自动的create, rebuild , drop 索引。后台进程是每15分钟调用一次,(是有j001进程执行_AUTO_INDEX_TASK_INTERVAL参数控制15分钟)。也是基于传统手动优化SQL的思路，基于SQL中的列使用识别可以创建的索引，然后验证自动索引对性能的影响，然后按预设的值去创建索引，只不是整个过程是自动的，并且整个过程都有审核报告。1. 捕捉Capture
在上一篇 浅谈Oracle Database 19c 中记录了Oracle Database 19c中引入了自动索引，它不断评估执行的 SQL 和基础表，以确定要创建哪些索引以及可能删除哪些索引。 它通过专家系统完成此任务，而且是一位7*24小时一直在工作的“专家”。
如何工作
Automatic index是有索引管理后台进程TASK调用， 可以自动的create, rebuild , drop 索引。后台进程是每15分钟调用一次,(是有j001进程执行_AUTO_INDEX_TASK_INTERVAL参数控制15分钟)。也是基于传统手动优化SQL的思路，基于SQL中的列使用识别可以创建的索引，然后验证自动索引对性能的影响，然后按预设的值去创建索引，只不是整个过程是自动的，并且整个过程都有审核报告。
1. 捕捉Capture
定期的捕获应用程序SQL历史进SQL仓库，包括SQL的文本、执行计划、绑定变量，执行统计信息等。
2. 视别后选索引Identify Candidates
识别有益于新SQL的后选索引，创建这个只有元数据的后选索引unusable\invisible index, 删除新创建的obsoloted索引。
3. 验证Verify
验证优化器对于后期捕捉的SQL是否会使用新创建的索引, 如果这个索引可以提升SQL的性能，就会物化该索引，所有的验证都是在应用程序工作流的外部完成。
4，决策Decide
如果该索引可以提升所有SQL的性能，会把该自动索引更改为visible, 如果该索引所有SQL性能更差，该索引会保持invisible, 如果该索引只部分SQL性能更差，该索引会更改为visible, 但是对于性能变差的SQL还是不可用。
5. 在线确认Online Validation
为其它SQL在线确认新索引的使用情况，开始是只允许一个会话使用一个SQL命令使用该索引，这样出问题也不会是大面积。
6. 监控Monitor
对于自动索引提供连续不断的监控，对于自动创建的索引而长时间不使用的会自动删除。多久后删除都可以配置。
自动索引适用于开发、测试、生产的所有阶段， 据去年的OOW上介绍自动索引会支持：单列索引，多列索引，函数索引，压缩索引（Compression Advanced Low），但目前19C 在线官方文档上只有local bTree index, 支持分区表和非分区表，临时表除外，也许后面的版本会跟上。 自动索引会消耗一定的CPU、内存和存储， RM限制了该任务只能使用一个CPU,可以指定自动索引存放的表空间和使用表空间容量的比例，有些宣传资料上自动索引也是可以指定独立的TEMP表空间（AUTO_INDEX_TEMP_TABLESPACE），但目前的版本还无法修改。
其实如果看自动索引的创建流程，正式之前ORACLE不同版本逐渐引入的关于索引的特性的集合。如下:
create index ANBOB.SYS_AI_XXXXXX ON ANBOB.T1(ID) TABLESPACE USERS UNUSABLE INVISIBLE AUTO COMPRESS ADVANCED LOW ONLINE;
注：自动创建的索引是以 SYS_AI 开头，之前有整理过一结SYS开头样式的列笔记，look here
相关视图
下面是19c中引入的关于自动化索引相关的视图或列。
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上执行的验证自动索引的操作
DBA_AUTO_INDEX_VERIFICATIONS — 列出自动索引的PLAN_HASH_VALUE,和BUFFER_GETS对比等
相关配置
配置可以在CDB级修改，也可以在PDB级修改。
SQL> @i
USERNAME INST_NAME HOST_NAME SID SERIAL# VERSION STARTED SPID OPID CPID SADDR PADDR
-------------------- -------------------- ------------------------- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
SYS anbob19c localhost.localdomain 390 56967 19.0.0.0.0 20190220 2529 28 2453 0000000067081028 0000000067CFFEC8
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE
SQL> alter session set container=pdb1;
Session altered.
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
7 rows selected.
-- check explan
Execution Plan
----------------------------------------------------------
Plan hash value: 4100957058
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 714 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| SMB$CONFIG | 14 | 714 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_SMB$CONFIG_PKEY | 14 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PARAMETER_NAME" LIKE 'AUTO_INDEX%')
filter("PARAMETER_NAME" LIKE 'AUTO_INDEX%')
Note
-----
- Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)
Note:
查询dba_auto_index_config和cdb_auto_index_config可以查看当前automatic index特性的配置，该视图数据是源于SMB$CONFIG基表，可以使用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_SCHEMA
AUTO_INDEX_SPACE_BUDGET — 自动索引可以使用表空间大小的百分比，默认 50%
如何trace Automatic Index
从上面查询自动索引配置的视图可以看到是基于 SMB$CONFIG 表的，其它该表中还有很多参数是隐藏的，其中就包括了自动索引调度的间隔时间和trace和一些资源限制参数。
SQL> select * from SMB$CONFIG; PARAMETER_NAME PARAMETER_VALUE LAST_UPDATED UPDATED_BY PARAMETER_DATA ---------------------------------------- --------------- ----------------------------------- ---------- ---------------------------------------- SPACE_BUDGET_PERCENT 10 PLAN_RETENTION_WEEKS 53 SPM_TRACING 0 AUTO_CAPTURE_PARSING_SCHEMA_NAME 0 AUTO_CAPTURE_MODULE 0 AUTO_CAPTURE_ACTION 0 AUTO_CAPTURE_SQL_TEXT 0 AUTO_INDEX_SCHEMA 0 AUTO_INDEX_DEFAULT_TABLESPACE 0 AUTO_INDEX_SPACE_BUDGET 50 AUTO_INDEX_REPORT_RETENTION 31 AUTO_INDEX_RETENTION_FOR_AUTO 0 373 AUTO_INDEX_RETENTION_FOR_MANUAL 0 AUTO_INDEX_MODE 0 24-FEB-19 12.24.02.000000 AM SYS IMPLEMENT _AUTO_INDEX_TRACE 0 _AUTO_INDEX_TASK_INTERVAL 900 _AUTO_INDEX_TASK_MAX_RUNTIME 3600 _AUTO_INDEX_IMPROVEMENT_THRESHOLD 20 _AUTO_INDEX_REGRESSION_THRESHOLD 10 _AUTO_INDEX_ABSDIFF_THRESHOLD 100 _AUTO_INDEX_STS_CAPTURE_TASK 0 24-FEB-19 12.24.02.000000 AM SYS ON _AUTO_INDEX_CONTROL 0 _AUTO_INDEX_DERIVE_STATISTICS 0 ON _AUTO_INDEX_CONCURRENCY 1 _AUTO_INDEX_SPA_CONCURRENCY 1 _AUTO_INDEX_REBUILD_TIME_LIMIT 30 _AUTO_INDEX_REBUILD_COUNT_LIMIT 5 AUTO_SPM_EVOLVE_TASK 0 OFF AUTO_SPM_EVOLVE_TASK_INTERVAL 3600 AUTO_SPM_EVOLVE_TASK_MAX_RUNTIME 1800
跟踪的方法
update SMB$CONFIG set _AUTO_INDEX_TRACE=2
Or
call dbms_auto_index_internal.configure(‘_AUTO_INDEX_TRACE’,2,true,true)
关闭TRACE，替换2为0
然后去查找日志文件即可
grep ^AI $ORACLE_BASE/diag/rdbms/*/$ORACLE_SID/trace/$ORACLE_SID_*trc
当然也可以从DBA_AUTO_INDEX_IND_ACTIONS 查看索引操作过程。
功能演示
测试这个功能1种是等15分钟，后台进程捕捉后创建；另一种可以使用hint /*+ USE_AUTO_INDEXES */, 这是在2018OOW上有介绍，但这种方法目前发布的版本里也是不可用，等待后续版本增强。
1， 自动
alter session set container=pdb1;
create tablespace auto_index_tbs datafile '/u01/app/oracle/oradata/ANBOB19C/pdb1/auto_index_tbs01.dbf' size 5g;
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE','AUTO_INDEX_TBS');
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
create user anbob identified by anbob;
grant connect,resource to anbob;
alter user anbob quota 5g on users;
alter user anbob quota 1g on auto_index_tbs;
create table anbob.tobj as select * from all_objects;
insert into anbob.tobj select * from anbob.tobj;
insert into anbob.tobj select * from anbob.tobj;
select object_name from anbob.tobj where object_id=7;
select object_name from anbob.tobj where object_id=8;
select object_name from anbob.tobj where object_id=9;
select object_name from anbob.tobj where object_id=10;
# 验证是否有创建自动索引
select * from DBA_AUTO_INDEX_EXECUTIONS;
select * from DBA_AUTO_INDEX_STATISTICS;
select * from DBA_AUTO_INDEX_IND_ACTIONS;
# 验证新索引
@ind anbob.tobj
# 验证执行计划
select * from anbob.tobj where object_id=11;
@xi
# 每个auto index task会生成一个报告，可以以text,xml,html格式查看，如下
-- text
select DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY('TEXT','ALL','ALL') from dual;
select DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY(SYSDATE-1,SYSDATE,'HTML','ALL','ALL') from dual;
-- html
set serveroutput on
declare
report clob := null;
begin
report := DBMS_AUTO_INDEX.REPORT_ACTIVITY(
activity_start => sysdate-1,
activity_end => sysdate,
type => 'HTML',
section => 'ALL',
level => 'ALL');
dbms_output.put_line(report);
end;
/
2, hint USE_AUTO_INDEXES
select /*+ USE_AUTO_INDEXES */ object_id from anbob.tobj where object_name='OBJ$';
SQL> select /*+USE_AUTO_INDEXES */ object_id from anbob.tobj where object_name='OBJ$';
OBJECT_ID
----------
18
18
18
18
4 rows selected.
SQL> @x2
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 1825173622
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 200 | 1512 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TOBJ | 5 | 200 | 1512 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='OBJ$')
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
E - USE_AUTO_INDEXES
Note:
显示目前的版本这个hint还不能使用，从hint report中可以看出hint不能使用的原因是sql语法错误。 后续版本再关注， 文档中有说禁用自动索引hint为NO_USE_AUTO_INDEXES，目前同样也是禁用。
以上所述就是小编给大家介绍的《Oracle19c新特性: 自动索引(Automatic indexing)》，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持！
猜你喜欢:
- 深入理解mysql 索引特性(面试高频,屡试不爽的mysql索引总结)
- InnoDB引擎B+树索引使用和新特性
- MySQL 8.0.13(GA) 发布，新增函数索引、强制主键等新特性
- MySQL 8.0.13(GA) 发布，新增函数索引、强制主键等新特性
- MySQL索引使用说明(单列索引和多列索引)
- Elasticsearch索引的基本操作(3)-索引的滚动索引
本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。
