在eygle的文章《Oracle 19c 十大新特性一览》中,曾经提到 Oracle 19c的一个重要新特性,就是:自动化索引创建和实施。
对于关系型数据库来说,索引是使得查询加速的重要手段,而如何设计和创建有效的索引,长期以来是一项复杂的任务。
来自原文的引用
在 Oracle 19c 中,自动化索引创建和实施技术被引入进来,Oracle 通过模拟人工索引的思路,建立了内置的专家系统。
数据库内置的算法将会通过捕获、识别、验证、决策、在线验证、监控的全流程管控索引自动化的过程。
这一特性将会自动帮助用户创建有效的索引,并通过提前验证确保其性能和有效性,并且在实施之后进行监控,这一特效将极大缓解数据库索引维护工作。
自动化还将删除由新创建的索引(逻辑合并)废弃的索引,并删除自动创建但长时间未使用的索引。

在多处有文提到,Automatic indexing特性对于on-prem环境仅支持Oracle Exadata平台,对于Oracle Cloud各个Cloud Edition都支持。所以on-prem环境的用户,是不能使用这个特性的,仅仅是使用了exadata或者O云的环境才可以。为了测试用途,我们打开on-prem的exadata特性之后开始测试。否则将会遇到下面的报错。
1 2 3 4 5 6 |
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 |
此仅为测试用途,测试完了我就删了,不然晚上害怕。
首先,打开自动索引这个特性,并且设置自动索引的表空间。


测试用户为HR,基于DBA_OBJECTS创建一个大表,并且将OBJECT_ID列更新为ROWNUM。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
SQL> create table AUTOIDX as select * from dba_objects; Table created. SQL> insert into AUTOIDX select* from AUTOIDX; 72446 rows created. SQL> / 144892 rows created. SQL> / 289784 rows created. SQL> / 579568 rows created. SQL> update AUTOIDX set object_id=rownum; 1159136 rows updated. SQL> commit; Commit complete. |
这个表是有统计信息的,统计信息是CTAS时候的行数,后面几次insert的没有收集。统计信息是不准确的,不过此时表上没有索引,但是这个数量级也足够CBO跟AUTO INDEXING特性在几次全扫之后来给OBJECT_ID加索引了,因为。。。如果这个都实现不了那还玩儿啥。
1 2 3 4 5 |
SQL> select NUM_ROWS,LAST_ANALYZED from user_tables where table_name='AUTOIDX'; NUM_ROWS LAST_ANALYZED ---------- --------------- 72446 14-MAY-19 |
这个执行计划里提示到的dynamic statistics used好像也是新特性?下次研究下。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
SQL> select object_name from AUTOIDX where object_id=7; OBJECT_NAME -------------------------------------------------------------------------------- C_TS# Execution Plan ---------------------------------------------------------- Plan hash value: 532725924 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 16 | 1264 | 6352 (1)| 00:00:01 | |* 1 | TABLE ACCESS FULL| AUTOIDX | 16 | 1264 | 6352 (1)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=7) Note ----- - dynamic statistics used: statistics for conventional DML Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 22612 consistent gets 0 physical reads 0 redo size 556 bytes sent via SQL*Net to client 411 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed |
这时候是没有索引的,默认需要等待15分钟才能看到下次任务启动,为了快速,我把间隔时间修改成3分钟。

查看任务,这个自动索引任务是每三分钟启动一次。(默认15分钟)

Oracle自信的给这个任务名字加上了AI。
看一下这个任务的详情:

从任务相信可以看到,有一个候选INDEX,创建了一个visible的索引,使用了45088768个字节的空间,以及其他等等。
查看表上的索引,也加上了,还是AI。

查看trace,能在trace中发现完整的Automatic Indexing过程:这里摘录几段说明下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 |
*** ACTION NAME:(CLEANUP) 2019-05-14T13:06:37.093991+08:00 任务开始前有一次CLEANUP操作,这应是AutoIndexing清理长期未使用INDEX的任务项。 *** ACTION NAME:(STS_CHECK) 2019-05-14T13:06:37.107740+08:00 检查STS,SQL Tuning Set,从中尝试从中获取SQL,能在trace看到,也从内存中获取了 *** ACTION NAME:(GET_STATEMENTS_FOR_AI) 2019-05-14T13:06:37.108166+08:00 获得SQL,这一部分我理解就是文档上说的捕获。 *** ACTION NAME:(COMPILATION_VERIFICATION) 2019-05-14T13:06:38.498241+08:00 这一步在文档上未提及,字面意思是编译验证,应该是验证CREATE INDEX语法的合法性。 AI: get_finding_info(): finding_name: Statements using auto index in compilation verification(final) attr_2: attr_8: AI: Merging keys for object 73238 index_keys: AI: Processing index key col: 4 cols_upd: 4 keysize: 1 key_id: 1 key_id_max: 1 status: 2 flags: 1 AI: Creating advisor object for index "HR"."SYS_AI_6ua5p8jqsj6ts" on "HR"."AUTOIDX"("OBJECT_ID") AI: log_finding(): p_obj_id: 280 finding_code: 4 finding_name: New index candidate vc_arg1: vc_arg2: vc_arg3: n_arg1: n_arg2: obj_id: AI: log_finding(): p_obj_id: finding_code: 5 finding_name: Candidate indexes vc_arg1: vc_arg2: vc_arg3: n_arg1: 1 n_arg2: 0 obj_id: AI: log_action(): p_obj_id: 280 p_command: 2 p_stmt: CREATE INDEX "HR"."SYS_AI_6ua5p8jqsj6ts" ON "HR"."AUTOIDX"("OBJECT_ID") TABLESPACE "AUTOINDEXING" UNUSABLE INVISIBLE AUTO ONLINE 注意这里是用UNUSABLE和INVISIBLE来创建这个索引的 。 time: 2019-05-14/13:06:38 AI: log_action_end(): action_id: 7 error_code: 0 time: 2019-05-14/13:06:38 AI: DDL on index completed, space used: , flag 21, ind_obj 280, obj_num 73253, rebuild_count 0, misestimate_count 0 AI: log_finding(): p_obj_id: 280 finding_code: 30 finding_name: Index created vc_arg1: vc_arg2: vc_arg3: n_arg1: n_arg2: obj_id: AI: log_finding(): p_obj_id: finding_code: 9 finding_name: Redundant indexes vc_arg1: vc_arg2: vc_arg3: n_arg1: 0 n_arg2: obj_id: AI: get_finding_info(): finding_name: Auto index compilation verification done attr_2: attr_8: AI: Starting verification at 14-MAY-19 01.06.38.820503000 PM +08:00, exec_name: SYS_AI_2019-05-14/13:06:35_C, basic_filter:: (sql_id, plan_hash_value) in (select /*+ unnest */ sql_id_vc, attr2 from sys.wri$_adv_objects os where task_id = 5 and type = 7 and exec_name = 'SYS_AI_2019-05-14/13:06:35' and bitand(attr7, 535) = 0 and bitand(attr7, 64) > 0) *** ACTION NAME:(GATHER_STATS) 2019-05-14T13:06:38.820619+08:00 AI: log_action(): p_obj_id: 280 p_command: 45 p_stmt: ALTER INDEX "HR"."SYS_AI_6ua5p8jqsj6ts" REBUILD ONLINE time: 2019-05-14/13:06:44 优化器评估介入,计算新索引是否会提升SQL的性能,如果可以提升,就会rebuild这个索引,英文文档里似乎把这个过程叫做实体化这个索引。 *** 2019-05-14T13:06:46.929599+08:00 (ORA19PDB1(3)) AI: log_action_end(): action_id: 8 error_code: 0 time: 2019-05-14/13:06:46 AI: DDL on index completed, space used: 45088768, flag 89, ind_obj 280, obj_num , rebuild_count 1, misestimate_count 0 AI: log_finding(): p_obj_id: 280 finding_code: 32 finding_name: Index rebuilt vc_arg1: vc_arg2: vc_arg3: n_arg1: 45088768 n_arg2: obj_id: AI: get_finding_info(): finding_name: Statements using auto index in compilation verification attr_2: attr_8: DDL完成,空间占用等等信息。 *** ACTION NAME:(EXECUTION_VERIFICATION1) 2019-05-14T13:06:47.255905+08:00 这是第一次验证VERIFICATION1,注意,下面有6条SQL,是之前自动索引捕获到的6条SQL,是基于这6条SQL来创建的自动索引,所以优化器需要用INVISIABLE的索引(我猜是HINT方式),实体化的索引来验证捕获到的6条SQL是否有性能提升。 AI: Starting analyze performance at 14-MAY-19 01.06.49.429568000 PM +08:00 AI: sql_id 9d5h4kdnfm50n improved, spa_plan_hash_value: 1022989665, spa_buffer_gets: 4, sts_buffer_gets: 45054, sts_executions: 1, improvement_factor: 45054 AI: log_finding(): p_obj_id: 277 finding_code: 28 finding_name: Statement improved vc_arg1: vc_arg2: vc_arg3: n_arg1: 45054 n_arg2: 1 obj_id: AI: sql_id an7jjdqv13y7z improved, spa_plan_hash_value: 1022989665, spa_buffer_gets: 4, sts_buffer_gets: 45054, sts_executions: 1, improvement_factor: 45054 AI: log_finding(): p_obj_id: 276 finding_code: 28 finding_name: Statement improved vc_arg1: vc_arg2: vc_arg3: n_arg1: 45054 n_arg2: 1 obj_id: AI: sql_id 1w6n2rapsu52p improved, spa_plan_hash_value: 1022989665, spa_buffer_gets: 4, sts_buffer_gets: 45054, sts_executions: 1, improvement_factor: 45054 AI: log_finding(): p_obj_id: 278 finding_code: 28 finding_name: Statement improved vc_arg1: vc_arg2: vc_arg3: n_arg1: 45054 n_arg2: 1 obj_id: AI: sql_id 00tt2zgsykkpq improved, spa_plan_hash_value: 1022989665, spa_buffer_gets: 4, sts_buffer_gets: 90108, sts_executions: 2, improvement_factor: 45054 AI: log_finding(): p_obj_id: 275 finding_code: 28 finding_name: Statement improved vc_arg1: vc_arg2: vc_arg3: n_arg1: 45054 n_arg2: 1 obj_id: AI: sql_id d1br4bhvatbha improved, spa_plan_hash_value: 1022989665, spa_buffer_gets: 4, sts_buffer_gets: 90165, sts_executions: 2, improvement_factor: 45079.5 AI: log_finding(): p_obj_id: 274 finding_code: 28 finding_name: Statement improved vc_arg1: vc_arg2: vc_arg3: n_arg1: 45079.5 n_arg2: 1 obj_id: AI: sql_id gdncrrawz473k improved, spa_plan_hash_value: 1022989665, spa_buffer_gets: 4, sts_buffer_gets: 540648, sts_executions: 12, improvement_factor: 45054 AI: log_finding(): p_obj_id: 273 finding_code: 28 finding_name: Statement improved vc_arg1: vc_arg2: vc_arg3: n_arg1: 45054 n_arg2: 1 obj_id: AI: Ending analyze performance at 14-MAY-19 01.06.50.083400000 PM +08:00 这里结束性能分析。 AI: log_finding(): p_obj_id: finding_code: 3 finding_name: Statements discarded (misestimate or high selectivity of indexes) vc_arg1: vc_arg2: vc_arg3: n_arg1: 0 n_arg2: obj_id: *** MODULE NAME:(SYS_AI_MODULE) 2019-05-14T13:06:50.083900+08:00 *** ACTION NAME:(EXECUTION_VERIFICATION2) 2019-05- 这里是第二次验证,VERIFICATION2,这部分Auto Indexing将索引设置为VISIABLE继续验证。 14T13:06:50.083921+08:00 AI: log_action(): p_obj_id: 280 p_command: 60 p_stmt: ALTER INDEX "HR"."SYS_AI_6ua5p8jqsj6ts" VISIBLE time: 2019-05-14/13:06:50 AI: log_finding(): p_obj_id: finding_code: 39 finding_name: Auto index action based on performance validation done vc_arg1: vc_arg2: vc_arg3: n_arg1: 0 n_arg2: obj_id: log_action_end(): action_id: 9 error_code: 0 time: 2019-05-14/13:06:50 AI: DDL on index completed, space used: , flag 169, ind_obj 280, obj_num , rebuild_count 1, misestimate_count 0 AI: log_finding(): p_obj_id: 280 finding_code: 34 finding_name: Index marked visible vc_arg1: vc_arg2: vc_arg3: n_arg1: n_arg2: obj_id: AI: log_finding(): p_obj_id: finding_code: 19 finding_name: Effective indexes vc_arg1: vc_arg2: vc_arg3: n_arg1: 1 n_arg2: obj_id: AI: log_finding(): p_obj_id: finding_code: 24 finding_name: Regressed statements vc_arg1: vc_arg2: vc_arg3: n_arg1: 0 n_arg2: obj_id: AI: log_finding(): p_obj_id: finding_code: 27 finding_name: Unchanged statements vc_arg1: vc_arg2: vc_arg3: n_arg1: 0 n_arg2: obj_id: AI: log_finding(): p_obj_id: finding_code: 29 finding_name: Improved statements vc_arg1: vc_arg2: vc_arg3: n_arg1: 6 n_arg2: obj_id: AI: log_finding(): p_obj_id: finding_code: 13 finding_name: Statements considered by SPM vc_arg1: vc_arg2: vc_arg3: n_arg1: 0 n_arg2: obj_id: AI: Ending ai action at 14-MAY-19 01.06.50.125999000 PM +08:00 |
随后也能验证,CBO开始使用这个INDEX了。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
SQL> set autot traceonly SQL> select object_name from AUTOIDX where object_id=772617; Execution Plan ---------------------------------------------------------- Plan hash value: 1022989665 ------------------------------------------------------------------------------------------------------------ | 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| AUTOIDX | 1 | 41 | 4 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | SYS_AI_6ua5p8jqsj6ts | 1 | | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=772617) Statistics ---------------------------------------------------------- 151 recursive calls 0 db block gets 131 consistent gets 0 physical reads 0 redo size 575 bytes sent via SQL*Net to client 641 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 9 sorts (memory) 0 sorts (disk) 1 rows processed |
我这个库有BUG,不能使用如下方式出报告:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
set serveroutput on declare report clob := null; begin report := DBMS_AUTO_INDEX.REPORT_ACTIVITY( activity_start => TO_TIMESTAMP('2019-05-14 13:06:00', 'yyyy-mm-dd hh24:mi:ss'), activity_end => TO_TIMESTAMP('2019-05-14 14:00:00', 'yyyy-mm-dd hh24:mi:ss'), type => 'HTML', section => 'ALL', level => 'ALL'); dbms_output.put_line(report); end; / * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 10 换成TEXT就可以,但是阅读性很差。 |
好不容易把报告搞出来,简单截图几张。


我当时测试的时候,表上是没有正确的统计信息的,假设表上没有统计信息,或者统计信息跟实际差别巨大,那么Automatic Indexing会不会误判?
当我尝试清掉环境,删除这个自动索引的时候:
1 2 3 4 5 6 7 8 9 10 11 12 |
SQL> drop index "SYS_AI_6ua5p8jqsj6ts"; drop index "SYS_AI_6ua5p8jqsj6ts" * ERROR at line 1: ORA-65532: cannot alter or drop automatically created indexes SQL> alter index "SYS_AI_6ua5p8jqsj6ts" unusable; alter index "SYS_AI_6ua5p8jqsj6ts" unusable * ERROR at line 1: ORA-65532: cannot alter or drop automatically created indexes |
我即使关闭了这个特性,这个索引同样不能被alter或者drop,假如因这个索引导致性能问题,DBA将束手无策?在没找到删除办法之前,这意味着Oracle的AI系统足够相信自己创建的索引,它来创建,测试和决策,并且绝对不让人的DBA来操作它的索引。但是它却可以删人工创建的未使用的索引。
接着说统计信息的事情,表上无统计信息或者表上统计信息跟实际差别巨大的情况下Automatic Indexing会如何工作:(为了测试,我只好drop purge了表,索引当然跟着没有了,这里可以10046跟一下索引是怎么没的,update ind$的auto标志位,然后delete这一条?)
重建环境:
清空表的统计信息:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SQL> alter session set nls_date_format="YYYY-MM-DD HH24:MI:SS"; Session altered. SQL> select NUM_ROWS,LAST_ANALYZED from user_tables where table_name='AUTOIDX'; NUM_ROWS LAST_ANALYZED ---------- ------------------- 72446 2019-05-14 15:07:11 SQL> EXEC DBMS_STATS.DELETE_TABLE_STATS('HR','AUTOIDX'); PL/SQL procedure successfully completed. SQL> select NUM_ROWS,LAST_ANALYZED from user_tables where table_name='AUTOIDX'; NUM_ROWS LAST_ANALYZED ---------- ------------------- SQL> |
打开自动索引特性。重复执行SQL。观察是否新生成索引,是否重新收集了统计信息。
根据测试,统计信息缺失的情况下,Automatic Indexing不会工作。在重新收集统计信息之后,Automatic Indexing开始工作。此外,如果统计信息不准确也不见得肯定可以触发AutoMatic Indexing。所以,表统计信息在Automatic indexing中作用也是非常被依赖的指标。

最后,我找到一个办法,可以一次把AI做的一切事情全部清空,dbms_auto_index_internal.AI_CLEAR,但是这个影响范围太大了。还是没能找到怎么删掉某一个索引的办法。
最后我发现是可以删除的。哭了。
SQL> drop index HR.”SYS_AI_6ua5p8jqsj6ts”;
drop index HR.”SYS_AI_6ua5p8jqsj6ts”
*
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes
SQL> update ind$ set PROPERTY=0 where obj#=73205;
1 row updated.
SQL> drop index HR.”SYS_AI_6ua5p8jqsj6ts”;
Index dropped.