Oracle 19C新特性-Automatic Indexing

Oracle Database 19c最令人印象深刻的新功能之一是Automatic Indexing。可以说,这是一个非常伟大的创新功能。
Automatic Indexing索引的概述
在我们日常的工作中,我们经常要做优化方面的工作,Automatic Indexing提供自动连续的优化数据库工作负载,稳定可靠的性能以及几乎无需人工干预。它具备以下功能

  • 根据表列的使用情况,识别出潜在的自动索引。文档中称作“candidate indexes”。
  • 将自动索引创建为不可见索引,因此它们不会在执行计划中使用。索引名称包括“ SYS_AI”前缀。
  • 针对SQL语句测试不可见的自动索引,以确保它们可以提高性能。如果它们改善了性能,则将它们变为可见。如果性能没有提高,则将相关的自动索引标记为不可用,然后将其删除。针对失败的自动索引测试的SQL语句被列入黑名单,因此将来将不再考虑将它们用于自动索引。对于首次对数据库运行SQL的优化程序不会考虑自动索引。
  • 删除未使用的索引。

Automatic Indexing索引的如何工作
这里我参考了Automatic Indexing in Oracle Database 19c

自动索引过程每15分钟在后台运行一次,并执行以下操作:
1.识别自动索引候选者
根据SQL语句中表列的用法来标识自动索引候选者。确保表统计信息是最新的。具有过时统计信息的表不被视为自动索引。
2.为自动索引候选项创建不可见的自动索引
自动索引候被创建为不可见的自动索引,也就是说,这些自动索引不能在SQL语句中使用。
3.根据SQL语句验证不可见的自动索引
不可见的自动索引针对SQL语句进行了验证。如果通过使用这些索引提高了SQL语句的性能,则将这些索引配置为可见索引(visible indexes),以便可以在SQL语句中使用它们。如果使用这些索引不能提高SQL语句的性能,则将这些索引配置为不可用的索引(unusable indexes),并将SQL语句列入黑名单。稍后,自动索引过程将删除不可用的索引。列入黑名单的SQL语句将来不允许使用自动索引。
4.删除未使用的自动索引
长时间不使用的自动索引将被删除。

让我们在环境中测试一下Automatic Indexing,我使用的是Redhat 7.8操作系统和Oracle Database 19.3.0.0.0版本
我们可以从视图中CDB_AUTO_INDEX_CONFIG查看Automatic Indexing的设置通过查看视图可以发现默认AUTO_INDEX_MODE为OFF,默认是关闭的。

我们可以通过DBMS_AUTO_INDEX包来控制Automatic Indexing的开关。

  • IMPLEMENT:打开自动索引。使改进性能的新索引可见,并且可供优化程序使用。
  • REPORT ONLY :打开自动索引,但是新索引仍然不可见。
  • OFF :关闭自动索引。

打开Automatic Indexing,我们发现运行直接报错ORA-40216: feature not supported,这是什么情况?不支持这个功能?

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
BEGIN DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT'); END;
*
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

通过查询发现不支持这个功能?只有云数据库和Exadata才支持这个功能。通过网上搜索一番,发现可以设置参数“_exadata_feature_on”=true来开启进行测试,通过设置参数我们将”Automatic Indexing”功能打开了。

SQL> alter system set "_exadata_feature_on"=true scope=spfile;
System altered.
SQL> shutdown immediate;
SQL> startup;
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
PL/SQL procedure successfully completed.

默认情况之下,将在默认的永久表空间中创建自动索引,如果想指定一个表空间来保存它们,可以使用AUTO_INDEX_DEFAULT_TABLESPACE方法。下面,我们创建一个表空间来保存自动索引,并相应地设置属性。

SQL> alter session set container=ORCLPDB1; 
Session altered.

SQL> CREATE TABLESPACE AUTOINDEX_T1 DATAFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/autoindex_t1.dbf' SIZE 500M AUTOEXTEND OFF;
Tablespace created.

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE','AUTOINDEX_T1');
PL/SQL procedure successfully completed.

如果想用回默认表空间,把第二个参数设置成NULL就可以了。

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE',NULL);

接下来我们可以使用AUTO_INDEX_SCHEMA包在SCHEMA级别进行控制。该属性指定包含或者排除的SCHEMA。

SQL> create user test identified by test;
User created.

SQL> grant dba to test;
Grant succeeded.
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'TEST', allow => TRUE);
PL/SQL procedure successfully completed.
如果要取消可以设置成NULL
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', NULL, allow => FALSE);

DBMS_AUTO_INDEX包中还提供了一些方法。
AUTO_INDEX_RETENTION_FOR_AUTO:在数据库中保留未使用的自动索引的天数,之后将其删除。默认值为373天。
AUTO_INDEX_RETENTION_FOR_MANUAL:在数据库中保留未使用的手动创建的索引(非自动索引)的天数,之后将其删除。设置NULL为时,不会删除手动创建的索引。默认值为NULL。
AUTO_INDEX_REPORT_RETENTION:删除自动索引日志之前在数据库中保留的天数。由于自动索引报告是根据这些日志生成的,因此,在超过为所指定的值的时间内,无法生成自动索引报告
AUTO_INDEX_REPORT_RETENTION。自动索引日志的保留期。报告基于这些日志。默认31天。
AUTO_INDEX_SPACE_BUDGET:用于自动索引的表空间大小的百分比。仅当在数据库创建期间指定的默认表空间用于存储自动索引时,才可以使用此配置设置。
AUTO_INDEX_COMPRESSION 启用和禁用自动索引的高级索引压缩的值。支持的值为ON,为自动索引启用高级压缩。OFF:禁用自动索引的高级压缩。

DROP_SECONDARY_INDEXES方法可以帮助删除索引(用于约束的索引除外)。这样一来,我们就可以拥有一个干净的系统,自动索引就可以做出所有索引决策。

表级别的删除
EXEC DBMS_AUTO_INDEX.drop_secondary_indexes('SCHEMA','TABLE'); 
SCHEMA级别的删除
dbms_auto_index.drop_secondary_indexes('SCHEMA');
数据库级别的删除
dbms_auto_index.drop_secondary_indexes;

我们可以使用REPORT_ACTIVITY或者是REPORT_LAST_ACTIVITY来输出报告。REPORT_ACTIVITY功能允许您显示指定时间段内的数据,默认为最后一天。而REPORT_LAST_ACTIVITY功能报告上一次自动索引操作。两者都允许使用以下参数定制输出。
TYPE :允许的值(TEXT,HTML,XML)。
SECTION:允许的值(SUMMARY,INDEX_DETAILS,VERIFICATION_DETAILS,ERRORS,ALL)。您还可以使用带有“ +”和“-”字符的组合来指示是否应包含或排除某些内容。例如“ SUMMARY + ERRORS”或“ ALL -ERRORS”。
LEVEL :允许的值(BASIC,TYPICAL,ALL)。

下面显示了使用SQL中的这些功能的一些示例。

SET LONG 1000000 PAGESIZE 0
最近24小时的默认TEXT报告。
SELECT DBMS_AUTO_INDEX.report_activity()FROM dual; 
一天之前的HTML报告。
SELECT DBMS_AUTO_INDEX.report_activity(
         activity_start => SYSTIMESTAMP-2,
         activity_end => SYSTIMESTAMP-1,
         type =>'HTML')
FROM dual; 
最新活动的HTML报告。
SELECT DBMS_AUTO_INDEX.report_last_activity(
         type =>'HTML')
FROM dual;

最后我们来做个案例。先创建一张表。

create table test (id1 number not null,id2 number not null,id3 number not null);

往表中插入数据。并且收集表的统计信息。

insert into test
select id1.n, id2.x, ceil(dbms_random.value(0, 100))
from (select level - 1 n from dual connect by level < 300) id1 , 
(select level x from dual connect by level < 50000) id2 
where id2.x <= id1.n * 3000; exec dbms_stats.gather_table_stats(OWNNAME=>'TEST',TABNAME=>'TEST' , cascade => true);

SQL> select table_name,num_rows,last_analyzed from dba_tables where table_name='TEST';

TABLE_NAME					     NUM_ROWS LAST_ANALYZED
-------------------------------------------------- ---------- -------------------
TEST						     14507718 2020-02-28 01:07:18

在其他节点开启sqlplus窗口,批量执行下列语句

select avg(id1) from test where id1 between 1 and 100;
select avg(id1) from test where id1 between 1 and 200;
select avg(id1) from test where id1 between 1 and 300;
select sum(id1) from test where id1 between 1 and 100;
select sum(id1) from test where id1 between 100 and 200;
select sum(id1) from test where id1 between 200 and 300;
select sum(id1) from test where id1=1;
select sum(id1) from test where id1=2;
select sum(id1) from test where id1=3;
select count(id1) from test where id1 between 1 and 100;
select distinct(id1) from test where id1 between 1 and 100;
select distinct id1 from test where id1 is not null;

然后执行报告查看。我这里其实已经有一个候选的索引,但是因为空间我弄的很小,所以创建的时候失败了。

SQL> SELECT DBMS_AUTO_INDEX.report_activity() FROM dual;

DBMS_AUTO_INDEX.REPORT_ACTIVITY()
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
 Activity start 	      : 27-FEB-2020 01:13:43
 Activity end		      : 28-FEB-2020 01:13:43
 Executions completed	      : 8
 Executions interrupted       : 0
 Executions with fatal error  : 0
-------------------------------------------------------------------------------

SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
 Index candidates	     : 1
 Indexes created	     : 0
 Space used		     : 0 B
 Indexes dropped	     : 0
 SQL statements verified     : 0
 SQL statements improved     : 0
 SQL plan baselines created  : 0
 Overall improvement factor  : 0x
-------------------------------------------------------------------------------

SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
 Unused indexes    : 0
 Space used	   : 0 B
 Unusable indexes  : 0
-------------------------------------------------------------------------------

ERRORS
--------------------------------------------------------------------------------
-------------
 - ORA-01652: unable to extend temp segment by 1024 in tablespace AUTOINDEX_T1
--------------------------------------------------------------------------------

我们可以查看DBA_AUTO_INDEX_IND_ACTIONS视图,来查看系统对自动索引执行的操作。可以看到最开始创建的索引是UNUSABLE和INVISIBLE的。这符合预期。后面发生了rebuild因为空间不足所以报了ORA-1652的错误。

 ACTION_ID INDEX_NAME		     INDEX_OWNER	  COMMAND	  STATEMENT								                  ERROR#
---------- ------------------------- -------------------- --------------- ---------------------------------------------------------------------- ----------
	 1 SYS_AI_6nfam1248fft7      TEST		  CREATE INDEX	  CREATE INDEX "TEST"."SYS_AI_6nfam1248fft7"  ON "TEST"."TEST"("ID1")         0
                                                                          TABLESPACE "AUTOINDEX_T1" UNUSABLE INVISIBLE AUTO  ONLINE
	 2 SYS_AI_6nfam1248fft7      TEST		  REBUILD INDEX   ALTER INDEX "TEST"."SYS_AI_6nfam1248fft7"   REBUILD  ONLINE		       -1652
	 3 SYS_AI_6nfam1248fft7      TEST		  REBUILD INDEX   ALTER INDEX "TEST"."SYS_AI_6nfam1248fft7"   REBUILD			       -1652

参考文档:https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-indexes.html#GUID-082972AD-1866-411A-8250-9B23D4088582

分享到: 更多

Post a Comment

Your email is never published nor shared. Required fields are marked *