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

查看所有标签

猜你喜欢:

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

内容算法

内容算法

闫泽华 / 中信出版社 / 2018-4-30 / 58.00元

近两年来,伴随着BAT纷纷涌入自媒体平台,自媒体发展可谓迎来爆发。自媒体平台火爆起来是从今日头条异军突起而引发的。它是一款基于数据挖掘的推荐引擎产品,为用户推荐有价值的、个性化的信息,是国内移动互联网领域成长最快的产品服务之一。推荐引擎也将迎来高速发展,针对推荐引擎的优化技术也将会迎来新的机遇。 本书作者从事推荐引擎相关的内容分发相关工作,在书中对内容推荐系统进行了介绍,书的最后,介绍了自媒......一起来看看 《内容算法》 这本书的介绍吧!

在线进制转换器
在线进制转换器

各进制数互转换器

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

在线XML、JSON转换工具