oracle 19新特性的自动索引

1.关于测试

最近oracle举办了一系列线上分享，其中一个19c的新特性是自动索引。自己在docker中测试了一下，失败。

[oracle@8aa96a41b58b ~]$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 20 09:44:52 2020Version 19.3.0.0.0SQL> 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 supportedORA-06512: at "SYS.DBMS_SYS_ERROR", line 79ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 9180ORA-06512: at "SYS.DBMS_AUTO_INDEX", line 283ORA-06512: at line 1自动索引只能在exadata机器上配置。Auto indexing will be allowed only in EXADATA systems, if not it will raise ORA-40216: feature not supported when we try it turn it on in non exadata systems.ORA-40216 When Using Auto Index Feature (Doc ID 2570076.1)

2.检查系统隐含参数

SELECT i.ksppinm name,       i.ksppdesc description,       CV.ksppstvl VALUE,       CV.ksppstdf isdefault,       DECODE(BITAND(CV.ksppstvf, 7),              1,              'MODIFIED',              4,              'SYSTEM_MOD',              'FALSE') ismodified,       DECODE(BITAND(CV.ksppstvf, 2), 2, 'TRUE', 'FALSE') isadjusted  FROM sys.x$ksppi i, sys.x$ksppcv CV WHERE i.inst_id = USERENV('Instance')   AND CV.inst_id = USERENV('Instance')   AND i.indx = CV.indx   AND i.ksppinm LIKE '%exadata%'/' ORDER BY REPLACE(i.ksppinm, '_', '');其中有一项为：NAME                     DESCRIPTION           VALUE      ISDEFAULT ISMODIFIED ISADJ------------------------ --------------------- ---------- --------- ---------- -----_exadata_feature_on      Exadata Feature On    FALSE      TRUE      FALSE      FALSE

3.打开隐含参数

SQL> alter system set "_exadata_feature_on"=true scope=spfile;SQL> shut immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area 1610609888 bytesFixed Size                  9135328 bytesVariable Size             385875968 bytesDatabase Buffers         1207959552 bytesRedo Buffers                7639040 bytesDatabase mounted.Database opened.

4.重新测试
注：以下测试脚本来自oracle在线分享。

重新打开自动索引配置，成功。SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');PL/SQL procedure successfully completed.启用自动索引有三个参数：EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT|REPORT ONLY|OFF');IMPLEMENT：启用自动索引并将创建后的索引设置为可见状态，优化器可使用该索引。REPORT ONLY：启用，索引是不可见自动索引OFF:关闭自动索引cdb自动索引已启用：SQL> COLUMN parameter_name FORMAT A40SQL> COLUMN parameter_value FORMAT A20SQL> SELECT con_id, parameter_name, parameter_value FROM cdb_auto_index_config;    CON_ID PARAMETER_NAME                           PARAMETER_VALUE---------- ---------------------------------------- --------------------         1 AUTO_INDEX_COMPRESSION                   OFF         1 AUTO_INDEX_DEFAULT_TABLESPACE**         1 AUTO_INDEX_MODE                          IMPLEMENT**         1 AUTO_INDEX_REPORT_RETENTION              31         1 AUTO_INDEX_RETENTION_FOR_AUTO            373         1 AUTO_INDEX_RETENTION_FOR_MANUAL         1 AUTO_INDEX_SCHEMA         1 AUTO_INDEX_SPACE_BUDGET                  50         3 AUTO_INDEX_COMPRESSION                   OFF         3 AUTO_INDEX_DEFAULT_TABLESPACE         3 AUTO_INDEX_MODE                          OFF    CON_ID PARAMETER_NAME                           PARAMETER_VALUE---------- ---------------------------------------- --------------------         3 AUTO_INDEX_REPORT_RETENTION              31         3 AUTO_INDEX_RETENTION_FOR_AUTO            373         3 AUTO_INDEX_RETENTION_FOR_MANUAL         3 AUTO_INDEX_SCHEMA         3 AUTO_INDEX_SPACE_BUDGET                  50

启用pdb自动索引：

SQL> alter session set container=orclpdb;Session altered.SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');PL/SQL procedure successfully completed.SQL> sELECT con_id, parameter_name, parameter_value FROM cdb_auto_index_config;    CON_ID PARAMETER_NAME                           PARAMETER_VALUE---------- ---------------------------------------- --------------------         3 AUTO_INDEX_COMPRESSION                   OFF         3 AUTO_INDEX_DEFAULT_TABLESPACE   **      3 AUTO_INDEX_MODE                          IMPLEMENT**         3 AUTO_INDEX_REPORT_RETENTION              31         3 AUTO_INDEX_RETENTION_FOR_AUTO            373         3 AUTO_INDEX_RETENTION_FOR_MANUAL         3 AUTO_INDEX_SCHEMA         3 AUTO_INDEX_SPACE_BUDGET                  508 rows selected.

创建自动索引的存储空间

默认情况下，在数据库创建期间指定的永久表空间用于存储自动索引。是否配置可检查AUTO_INDEX_DEFAULT_TABLESPACE参数。SQL> create tablespace tbs_auto datafile '/opt/oracle/oradata/ORCL/ORCLPDB/tbs_auto_ind01.dbf' size 100M;Tablespace created.

设置自动索引使用的默认表空间SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE','tbs_auto');           PL/SQL procedure successfully completed.SQL> SELECT con_id, parameter_name, parameter_value FROM cdb_auto_index_config;    CON_ID PARAMETER_NAME                           PARAMETER_VALUE---------- ---------------------------------------- --------------------         3 AUTO_INDEX_COMPRESSION                   OFF         3 AUTO_INDEX_DEFAULT_TABLESPACE            TBS_AUTO         3 AUTO_INDEX_MODE                          IMPLEMENT         3 AUTO_INDEX_REPORT_RETENTION              31         3 AUTO_INDEX_RETENTION_FOR_AUTO            373         3 AUTO_INDEX_RETENTION_FOR_MANUAL         3 AUTO_INDEX_SCHEMA         3 AUTO_INDEX_SPACE_BUDGET                  50保留系统默认使用以下存储过程EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE',NULL);

指定用户启用自动索引

在数据库中启用自动索引后，默认情况下，数据库中的所有用户都可以使用自动索引。可以查看cdb_auto_index_config.AUTO_INDEX_SCHEMA字段查看不同的配置。SH不能使用自动索引(AUTO_INDEX_SCHEMA=schema NOT IN (SH))：EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'SH', FALSE);仅SH可以使用自动索引(AUTO_INDEX_SCHEMA=schema IN (SH))：EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'SH', TRUE);数据库中的所有用户都可以使用自动索引：EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', NULL, TRUE);

模拟自动索引创建：

SQL> create table hr.tab_auto as select rownum id,t.* from dba_objects t;Table created.SQL> declarea varchar2(2000) := '';  2    3  begin  4  for x in 1.. 10000 loop  5  select object_name into a from tab_auto where id=x;  6  end loop;  7  end;  8  /PL/SQL procedure successfully completed.SQL> COLUMN task_name FORMAT A30SQL> COLUMN advisor_name FORMAT A30SQL> select task_name,advisor_name from dba_advisor_tasks where owner='SYS' ORDER BY TASK_ID;TASK_NAME                      ADVISOR_NAME------------------------------ ------------------------------SYS_AUTO_SPM_EVOLVE_TASK       SPM Evolve AdvisorSYS_AI_SPM_EVOLVE_TASK         SPM Evolve Advisor       <<

过15分钟后查看索引

COLUMN OWNER FORMAT a10col INDEX_TYPE format a10col INDEX_NAME format a20col TABLE_NAME format a20col TABLE_OWNER format a10SELECT OWNER,INDEX_TYPE,INDEX_NAME,TABLE_NAME,TABLE_OWNER FROM DBA_INDEXES WHERE AUTO='YES' ORDER BY OWNER,INDEX_NAME;OWNER      INDEX_TYPE INDEX_NAME           TABLE_NAME           TABLE_OWNE---------- ---------- -------------------- -------------------- ----------HR         NORMAL     SYS_AI_8abjpspc3b08n TAB_AUTO             HR

查看执行计划

SQL> select count(*) from tab_auto where id=100;    COUNT(*)----------         1SQL> select * from table(dbms_xplan.display_cursor(format=>'TYPICAL'));PLAN_TABLE_OUTPUT-----------------------------------------------------------------------------------------SQL_ID  8fp2w8rwapnbz, child number 0-------------------------------------select count(*) from tab_auto where id=100Plan hash value: 896819007------------------------------------------------------------------------------------------| Id  | Operation         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT  |                      |       |       |     1 (100)|          ||   1 |  SORT AGGREGATE   |                      |     1 |     5 |            |          ||*  2 |   INDEX RANGE SCAN| SYS_AI_8abjpspc3b08n |     1 |     5 |     1   (0)| 00:00:01 |------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("ID"=100)使用了新创建的自动索引。

指定未使用的自动索引的保留期限

使用AUTO_INDEX_RETENTION_FOR_AUTO配置未使用的自动索引保留在数据库中的期限。在指定的保留期限后，将删除未使用的自动索引。注意：默认情况下，未使用的自动索引将在373天后删除。以下语句将未使用的自动索引的保留期限设置为90天。EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', '90');以下语句将自动索引的保留期重置为默认值373天。EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', NULL);

生成自动索引报告

您可以使用软件包的REPORT_ACTIVITY和REPORT_LAST_ACTIVITY功能在Oracle数据库中生成与自动索引操作有关的报告DBMS_AUTO_INDEX。-- 过去24小时内自动索引操作的典型信息，文本。SELECT DBMS_AUTO_INDEX.report_activity()  FROM dual;-- 最新活动的默认TEXT报告。SELECT DBMS_AUTO_INDEX.report_last_activity() FROM dual;-- 指定时间段的HTML报告。SELECT DBMS_AUTO_INDEX.report_activity(activity_start =>TO_TIMESTAMP('2018-11-01', 'YYYY-MM-DD'),activity_end => TO_TIMESTAMP('2018-11-05', 'YYYY-MM-DD'),,type => 'HTML') FROM   dual;也可以使用activity_start =>TIMESTAMP - N-- 最新活动的HTML报告。SELECT DBMS_AUTO_INDEX.report_last_activity( type => 'HTML') FROM   dual;---最新活动的HTML报告。包括最新活动自动索引操作的摘要，索引详细信息和错误信息DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY(type    => 'HTML',section => 'SUMMARY +INDEX_DETAILS +ERRORS',level   => 'BASIC');