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

栏目: 数据库 · 发布时间: 5年前

内容简介:在上一篇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)》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

从零开始学微信公众号运营推广

从零开始学微信公众号运营推广

叶龙 / 清华大学出版社 / 2017-6-1 / 39.80

本书是丛书的第2本,具体内容如下。 第1章 运营者入门——选择、注册和认证 第2章 变现和赚钱——如何从0到100万 第3章 决定打开率——标题的取名和优化 第4章 决定美观度——图片的选取和优化 第5章 决定停留率——正文的编辑和优化 第6章 决定欣赏率——版式的编辑和优化 第7章 数据的分析——用户内容的精准营销 书中从微信运营入门开始,以商业变......一起来看看 《从零开始学微信公众号运营推广》 这本书的介绍吧!

XML、JSON 在线转换
XML、JSON 在线转换

在线XML、JSON转换工具

html转js在线工具
html转js在线工具

html转js在线工具

RGB HSV 转换
RGB HSV 转换

RGB HSV 互转工具