首页 » ORACLE [C]系列, ORACLE 9i-23c » Oracle19c新特性: 自动索引(Automatic indexing)

Oracle19c新特性: 自动索引(Automatic indexing)

在上一篇浅谈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;

# 自动任务执行情况

SQL> select * from DBA_AUTO_INDEX_EXECUTIONS order by 1 desc;

EXECUTION_NAME                           EXECUTION_START     EXECUTION_END       ERROR_MESSAGE                  STATUS
---------------------------------------- ------------------- ------------------- ------------------------------ -----------
SYS_AI_2019-02-24/02:43:32               2019-02-24 02:43:32 2019-02-24 02:43:40                                COMPLETED
SYS_AI_2019-02-24/02:28:30               2019-02-24 02:28:30 2019-02-24 02:28:36                                COMPLETED
SYS_AI_2019-02-24/02:13:28               2019-02-24 02:13:28 2019-02-24 02:13:43                                COMPLETED
SYS_AI_2019-02-24/01:58:26               2019-02-24 01:58:26 2019-02-24 01:58:33                                COMPLETED


select * from DBA_AUTO_INDEX_STATISTICS;

select * from DBA_AUTO_INDEX_IND_ACTIONS;
COMMAND
----------------------------------------------------------------
STATEMENT
------------------------------------------------------------------------
START_TIME END_TIME ERROR#
------------------- ------------------- ----------
SYS_AI_2019-02-24/02:13:28 1
SYS_AI_672r7cg8xggpp
ANBOB
TOBJ
ANBOB
CREATE INDEX
CREATE INDEX "ANBOB"."SYS_AI_672r7cg8xggpp" ON "ANBOB"."TOBJ"("OBJECT_NAME") TABLESPACE "AUTO_INDEX_TBS" UNUSABLE INVISIBLE AUTO COMPRESS ADVANCED LOW ONLINE
2019-02-24 02:13:35 2019-02-24 02:13:36 0

SYS_AI_2019-02-24/02:13:28 2
SYS_AI_gg8213wrt5npc
ANBOB
TOBJ
ANBOB
CREATE INDEX
CREATE INDEX "ANBOB"."SYS_AI_gg8213wrt5npc" ON "ANBOB"."TOBJ"("OBJECT_ID") TABLESPACE "AUTO_INDEX_TBS" UNUSABLE INVISIBLE AUTO COMPRESS ADVANCED LOW ONLINE
2019-02-24 02:13:36 2019-02-24 02:13:36 0

# 验证新索引

SQL> @ind anbob.tobj
Display indexes where table or index name matches %anbob.tobj%...

TABLE_OWNER          TABLE_NAME                     INDEX_NAME                     POS# COLUMN_NAME                    DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
ANBOB                TOBJ                           SYS_AI_672r7cg8xggpp              1 OBJECT_NAME
                                                    SYS_AI_gg8213wrt5npc              1 OBJECT_ID


INDEX_OWNER          TABLE_NAME                     INDEX_NAME                     IDXTYPE    UNIQ STATUS   PART TEMP  H     LFBLKS           NDK   NUM_ROWS       CLUF LAST_ANALYZED       DEGREE VISIBILIT
-------------------- ------------------------------ ------------------------------ ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ ---------
ANBOB                TOBJ                           SYS_AI_672r7cg8xggpp           NORMAL     NO   UNUSABLE NO   N     3       1817         59708     284952     120792 2019-02-24 02:13:36 1      INVISIBLE
                     TOBJ                           SYS_AI_gg8213wrt5npc           NORMAL     NO   UNUSABLE NO   N     3        658         72248     284952     120792 2019-02-24 02:13:36 1      INVISIBLE

# 验证执行计划

select * from anbob.tobj where object_id=11;

@xi

# 每个auto index task会生成一个报告，可以以text,xml,html格式查看，如下

-- text

SQL> select DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY('TEXT','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;
 /

Note:
上面的例子可以看出自动索引已经创建到了invisible unusble阶段，还没有验证完该AUTO INDEX是否可以提升SQL的性能而物化该索引并visible。

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，目前同样也是禁用。

 

Related Posts:

打赏

, ,

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：