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