oracle 19新特性的自動(dòng)索引

1.關(guān)于測(cè)試

最近oracle舉辦了一系列線上分享，其中一個(gè)19c的新特性是自動(dòng)索引。自己在docker中測(cè)試了一下，失敗。

[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

自動(dòng)索引只能在exadata機(jī)器上配置。
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.檢查系統(tǒng)隱含參數(shù)

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, '_', '');

其中有一項(xiàng)為：
NAME                     DESCRIPTION           VALUE      ISDEFAULT ISMODIFIED ISADJ
------------------------ --------------------- ---------- --------- ---------- -----
_exadata_feature_on      Exadata Feature On    FALSE      TRUE      FALSE      FALSE

3.打開(kāi)隱含參數(shù)

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.重新測(cè)試
注：以下測(cè)試腳本來(lái)自oracle在線分享。

重新打開(kāi)自動(dòng)索引配置，成功。
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

PL/SQL procedure successfully completed.

啟用自動(dòng)索引有三個(gè)參數(shù)：
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT|REPORT ONLY|OFF');
IMPLEMENT：?jiǎn)⒂米詣?dòng)索引并將創(chuàng)建后的索引設(shè)置為可見(jiàn)狀態(tài)，優(yōu)化器可使用該索引。
REPORT ONLY：?jiǎn)⒂?，索引是不可?jiàn)自動(dòng)索引
OFF:關(guān)閉自動(dòng)索引

cdb自動(dòng)索引已啟用：
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自動(dòng)索引：

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.

創(chuàng)建自動(dòng)索引的存儲(chǔ)空間

默認(rèn)情況下，在數(shù)據(jù)庫(kù)創(chuàng)建期間指定的永久表空間用于存儲(chǔ)自動(dòng)索引。是否配置可檢查AUTO_INDEX_DEFAULT_TABLESPACE參數(shù)。
SQL> create tablespace tbs_auto datafile '/opt/oracle/oradata/ORCL/ORCLPDB/tbs_auto_ind01.dbf' size 100M;
Tablespace created.

設(shè)置自動(dòng)索引使用的默認(rèn)表空間
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

保留系統(tǒng)默認(rèn)使用以下存儲(chǔ)過(guò)程
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE',NULL);

指定用戶啟用自動(dòng)索引

在數(shù)據(jù)庫(kù)中啟用自動(dòng)索引后，默認(rèn)情況下，數(shù)據(jù)庫(kù)中的所有用戶都可以使用自動(dòng)索引。
可以查看cdb_auto_index_config.AUTO_INDEX_SCHEMA字段查看不同的配置。

SH不能使用自動(dòng)索引(AUTO_INDEX_SCHEMA=schema NOT IN (SH))：
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'SH', FALSE);

僅SH可以使用自動(dòng)索引(AUTO_INDEX_SCHEMA=schema IN (SH))：
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'SH', TRUE);

數(shù)據(jù)庫(kù)中的所有用戶都可以使用自動(dòng)索引：
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', NULL, TRUE);

模擬自動(dòng)索引創(chuàng)建：

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 <<<與自動(dòng)索引相關(guān)
SYS_AUTO_INDEX_TASK            SQL Access Advisor       <<<
AUTO_STATS_ADVISOR_TASK        Statistics Advisor
INDIVIDUAL_STATS_ADVISOR_TASK  Statistics Advisor

過(guò)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

查看執(zhí)行計(jì)劃

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)

使用了新創(chuàng)建的自動(dòng)索引。

指定未使用的自動(dòng)索引的保留期限

使用AUTO_INDEX_RETENTION_FOR_AUTO配置未使用的自動(dòng)索引保留在數(shù)據(jù)庫(kù)中的期限。在指定的保留期限后，將刪除未使用的自動(dòng)索引。

注意：默認(rèn)情況下，未使用的自動(dòng)索引將在373天后刪除。
以下語(yǔ)句將未使用的自動(dòng)索引的保留期限設(shè)置為90天。
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', '90');

以下語(yǔ)句將自動(dòng)索引的保留期重置為默認(rèn)值373天。
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', NULL);

生成自動(dòng)索引報(bào)告

您可以使用軟件包的REPORT_ACTIVITY和REPORT_LAST_ACTIVITY功能在Oracle數(shù)據(jù)庫(kù)中生成與自動(dòng)索引操作有關(guān)的報(bào)告DBMS_AUTO_INDEX。

-- 過(guò)去24小時(shí)內(nèi)自動(dòng)索引操作的典型信息，文本。
SELECT DBMS_AUTO_INDEX.report_activity()  FROM dual;

-- 最新活動(dòng)的默認(rèn)TEXT報(bào)告。
SELECT DBMS_AUTO_INDEX.report_last_activity() FROM dual;

-- 指定時(shí)間段的HTML報(bào)告。
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

-- 最新活動(dòng)的HTML報(bào)告。
SELECT DBMS_AUTO_INDEX.report_last_activity( type => 'HTML') FROM   dual;

---最新活動(dòng)的HTML報(bào)告。包括最新活動(dòng)自動(dòng)索引操作的摘要，索引詳細(xì)信息和錯(cuò)誤信息
DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY(type    => 'HTML',section => 'SUMMARY +INDEX_DETAILS +ERRORS',level   => 'BASIC');