oracle 19新特性的自动索引

1.关于测试

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

[oracle@8aa96a41b58b ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 20 09:44:52 2020
Version 19.3.0.0.0

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机器上配置。
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> startup
ORACLE instance started.

Total System Global Area 1610609888 bytes
Fixed Size                  9135328 bytes
Variable Size             385875968 bytes
Database Buffers         1207959552 bytes
Redo Buffers                7639040 bytes
Database 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 A40
SQL> COLUMN parameter_value FORMAT A20
SQL> 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                  50

8 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> declare
a 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 A30
SQL> COLUMN advisor_name FORMAT A30
SQL> 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 Advisor
SYS_AI_SPM_EVOLVE_TASK         SPM Evolve Advisor       <<<
SYS_AI_VERIFY_TASK             SQL Performance Analyzer <<<与自动索引相关
SYS_AUTO_INDEX_TASK            SQL Access Advisor       <<<
AUTO_STATS_ADVISOR_TASK        Statistics Advisor
INDIVIDUAL_STATS_ADVISOR_TASK  Statistics Advisor

过15分钟后查看索引

COLUMN OWNER FORMAT a10
col INDEX_TYPE format a10
col INDEX_NAME format a20
col TABLE_NAME format a20
col TABLE_OWNER format a10
SELECT 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(*)
----------
         1

SQL> 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=100

Plan 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');