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