1、它能做什麼

自動索引功能執行以下操作。

根據表列使用情況確定潛在的自動索引。文件稱這些為 “候選索引 (candidate indexes)”。
將自動索引建立為不可見索引，因此不會在執行計劃中使用它們。索引名稱包括 “SYS_AI”字首。
根據 SQL語句測試不可見的自動索引，以確保它們能提高效能。如果它們導致效能提高，則可以它們可見。如果效能未得到改善，則相關的自動索引將標記為不可用，稍後將被刪除。針對失敗的自動索引測試的 SQL語句被列入黑名單，因此將來不會考慮將它們用於自動索引。第一次對資料庫執行 SQL時，最佳化程式不會考慮自動索引。
刪除未使用的索引。

2、先決條件

透過設定初始化引數 “_exadata_feature_on=true” 進行測試。注：請不要在生產系統中測試。

export ORACLE_SID=cdb1
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES
sqlplus / as sysdba <<EOF
alter system 
set "_exadata_feature_on"=true scope=spfile;
shutdown immediate;
startup;
exit;
EOF

3、配置

使用 DBMS_AUTO_INDEX 包來管理自動索引特性。下面描述了基本管理。

3.1 顯示配置

CDB_AUTO_INDEX_CONFIG檢視顯示當前的自動索引配置。
COLUMN parameter_name FORMAT A40
COLUMN parameter_value FORMAT A15
SELECT con_id, parameter_name, parameter_value 
FROM   
cdb_auto_index_config
ORDER BY 1, 2;
    CON_ID PARAMETER_NAME                           PARAMETER_VALUE
---------- ---------------------------------------- ---------------
         
1 AUTO_INDEX_COMPRESSION                   OFF
1 AUTO_INDEX_DEFAULT_TABLESPACE
1 AUTO_INDEX_MODE                          OFF
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
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
SQL>

如果我們切換到使用者定義的可插拔資料庫，我們只獲取該容器的值。

ALTER SESSION SET CONTAINER = pdb1;
COLUMN parameter_name FORMAT A40
COLUMN parameter_value FORMAT A15
SELECT con_id, parameter_name, parameter_value 
FROM   
cdb_auto_index_config
ORDER BY 1, 2;
    CON_ID PARAMETER_NAME                           PARAMETER_VALUE
---------- ---------------------------------------- ---------------
         
3 AUTO_INDEX_COMPRESSION                   OFF
         
3 AUTO_INDEX_DEFAULT_TABLESPACE
         
3 AUTO_INDEX_MODE                          OFF
         
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
SQL>

3.2 啟用 / 禁用自動索引

使用 DBMS_AUTO_INDEX 包的 CONFIGURE 儲存過程配置自動索引。

使用 AUTO_INDEX_MODE 屬性控制用於自動索引的開關，該屬性具有以下允許值：

IMPLEMENT：開啟自動索引。提高效能的新索引可見並可供最佳化程式使用。
REPORT ONLY：開啟自動索引，但新索引仍然不可見。
OFF ：關閉自動索引。

模式之間切換的命令示例如下：

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');

3.3 自動索引的表空間

預設情況下，自動索引是在預設的永久表空間中建立的。如果這是不可接受的，您可以使用 AUTO_INDEX_DEFAULT_TABLESPACE屬性指定一個表空間來儲存它們。下面我們建立一個表空間來儲存自動索引，並相應地設定屬性。

ALTER SESSION SET CONTAINER = pdb1;
CREATE TABLESPACE AUTO_INDEXES_TS 
DATAFILE SIZE 100M 
AUTOEXTEND ON NEXT 100M;
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE','AUTO_INDEXES_TS');

如果要設定使用預設永久表空間，可以設定為 NULL ，如下命令所示：

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE',NULL);

3.4 模式級（ Schema-Level ）控制

一旦啟用了自動索引，在嘗試識別候選索引時會考慮所有模式。您可以使用 AUTO_INDEX_SCHEMA 屬性更改預設行為，該屬性允許您維護包含 /排除列表。

如果 ALLOW引數設定為 true，則指定的模式 (schema)將新增到包含列表中。注意 :它構建了一個包含模式的謂詞。

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'TEST', allow => TRUE);
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'TEST2', allow => TRUE);
COLUMN parameter_name FORMAT A40
COLUMN parameter_value FORMAT A15
SELECT con_id, parameter_name, parameter_value 
FROM   
cdb_auto_index_config
ORDER BY 1, 2;
    CON_ID PARAMETER_NAME                           PARAMETER_VALUE
---------- ---------------------------------------- ----------------------------------------
         
3 AUTO_INDEX_COMPRESSION                   OFF
         
3 AUTO_INDEX_DEFAULT_TABLESPACE            AUTO_INDEXES_TS
         
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                        schema IN (TEST, TEST2)
         
3 AUTO_INDEX_SPACE_BUDGET                  50
SQL>

可以使用 NULL 引數值消除包含列表，如下所示：

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', NULL, allow => TRUE);
COLUMN parameter_name FORMAT A40
COLUMN parameter_value FORMAT A15
SELECT con_id, parameter_name, parameter_value 
FROM   
cdb_auto_index_config
ORDER BY 1, 2;
    CON_ID PARAMETER_NAME                           PARAMETER_VALUE
---------- ---------------------------------------- ----------------------------------------
         
3 AUTO_INDEX_COMPRESSION                   OFF
         
3 AUTO_INDEX_DEFAULT_TABLESPACE            AUTO_INDEXES_TS
         
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
SQL>

如果 ALLOW引數設定為 FALSE，則指定的模式將新增到排除列表中。

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'TEST', allow => FALSE);
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'TEST2', allow => FALSE);
COLUMN parameter_name FORMAT A40
COLUMN parameter_value FORMAT A15
SELECT con_id, parameter_name, parameter_value 
FROM   
cdb_auto_index_config
ORDER BY 1, 2;
    CON_ID PARAMETER_NAME                           PARAMETER_VALUE
---------- ---------------------------------------- ----------------------------------------
         
3 AUTO_INDEX_COMPRESSION                   OFF
         
3 AUTO_INDEX_DEFAULT_TABLESPACE            AUTO_INDEXES_TS
         
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                        schema NOT IN (TEST, TEST2)
         3 AUTO_INDEX_SPACE_BUDGET                  50
SQL>

可以使用 NULL引數值清除排除列表。

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', NULL, allow => FALSE);
COLUMN parameter_name FORMAT A40
COLUMN parameter_value FORMAT A15
SELECT con_id, parameter_name, parameter_value 
FROM   cdb_auto_index_config
ORDER BY 1, 2;
    CON_ID PARAMETER_NAME                           PARAMETER_VALUE
---------- ---------------------------------------- ----------------------------------------
         3 AUTO_INDEX_COMPRESSION                   OFF
         3 AUTO_INDEX_DEFAULT_TABLESPACE            AUTO_INDEXES_TS
         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
SQL>

4、 其它配置

您可能希望考慮其他引數，這些都在此詳細說明。

AUTO_INDEX_COMPRESSION ：據推測用於控制壓縮程度。預設為 “OFF” 。
AUTO_INDEX_REPORT_RETENTION ：自動索引日誌的保留期。預設 31 天。
AUTO_INDEX_RETENTION_FOR_AUTO ：未使用的自動索引的保留期。預設 373 天。
AUTO_INDEX_RETENTION_FOR_MANUAL ：未使用的手動建立索引的保留期。設定為NULL 時，不考慮手動建立的索引。預設為NULL 。
AUTO_INDEX_SPACE_BUDGET ：用於自動索引儲存的預設永久表空間的百分比。使用 AUTO_INDEX_DEFAULT_TABLESPACE 引數指定自定義表空間時，將忽略此引數。

5、 刪除二級索引

在做這個之前，請仔細考慮，測試，測試，測試！

如果您感覺特別勇敢，DROP_SECONDARY_INDEXES過程將刪除除用於約束的索引之外的所有索引。這可以在表、模式（Schema）、資料庫級別完成。
-- 表級別
EXEC DBMS_AUTO_INDEX.drop_secondary_indexes('MY_SCHEMA', 
'MY_TABLE');
-- 模式（Schema）級別
EXEC DBMS_AUTO_INDEX.drop_secondary_indexes('MY_SCHEMA');
-- 資料庫級別
EXEC DBMS_AUTO_INDEX.drop_secondary_indexes;

6、檢視

有幾個與自動索引功能相關的檢視，如下所示：

SELECT view_name
FROM   
dba_views
WHERE  view_name LIKE 'DBA_AUTO_INDEX%'
ORDER BY 1;
VIEW_NAME
--------------------------------------------------------------------------------
DBA_AUTO_INDEX_CONFIG
DBA_AUTO_INDEX_EXECUTIONS
DBA_AUTO_INDEX_IND_ACTIONS
DBA_AUTO_INDEX_SQL_ACTIONS
DBA_AUTO_INDEX_STATISTICS
DBA_AUTO_INDEX_VERIFICATIONS
SQL>

此外，{CDB|DBA|ALL|USER}_INDEXES 檢視包含AUTO列，該列指示索引是否由自動索引功能建立。

COLUMN owner FORMAT A30
COLUMN index_name FORMAT A30
COLUMN table_owner FORMAT A30
COLUMN table_name FORMAT A30
SELECT owner,
       
index_name,
       
index_type,
       
table_owner,
       
table_name
       
table_type
FROM   
dba_indexes
WHERE  auto = 'YES'
ORDER BY owner, index_name;

7、 活動報告

DBMS_AUTO_INDEX 包中包含兩個報告功能。

DBMS_AUTO_INDEX.REPORT_ACTIVITY (
   activity_start  IN  TIMESTAMP 
WITH TIME ZONE 
DEFAULT SYSTIMESTAMP - 1,
   activity_end    IN  TIMESTAMP WITH TIME ZONE 
DEFAULT SYSTIMESTAMP,
   
type            IN  VARCHAR2 
DEFAULT 'TEXT',
   
section         
IN  VARCHAR2 
DEFAULT 'ALL',
   
level           
IN  VARCHAR2 
DEFAULT 'TYPICAL')
RETURN CLOB;
DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY (
   type            IN  VARCHAR2 DEFAULT 'TEXT',
   section         IN  VARCHAR2 DEFAULT 'ALL',
   level           IN  VARCHAR2 DEFAULT 'TYPICAL')
RETURN CLOB;

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中使用這些函式的一些示例如下所示。注意引用 LEVEL引數。在 SQL呼叫中使用它時，這是必要的，因此這不是對 LEVEL偽列的引用。

SET LONG 1000000 PAGESIZE 0
-- 過去24小時的預設TEXT報告。
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 => SYSTIMESTAMP-2,
         activity_end   => SYSTIMESTAMP-1,
type           => 'HTML')
FROM   dual;
-- 最新活動的HTML報告。
SELECT DBMS_AUTO_INDEX.report_last_activity( 
type => 'HTML')
FROM   dual;
-- 前天的XML報告包含所有資訊。
SELECT DBMS_AUTO_INDEX.report_activity(
         activity_start => SYSTIMESTAMP-2,
         activity_end   => SYSTIMESTAMP-1,  
type           => 'XML',
section        => 'ALL',
"LEVEL"        => 'ALL')
FROM   dual;
-- 包含所有資訊的最新活動的XML報告。
SELECT DBMS_AUTO_INDEX.report_last_activity(
type     => 'HTML',    
section  => 'ALL',     
"LEVEL"  => 'ALL')
FROM   dual;
SET PAGESIZE 14

以下是在建立任何索引之前預設活動報告的輸出示例。

SELECT DBMS_AUTO_INDEX.report_activity() 
FROM dual;
GENERAL INFORMATION
-------------------------------------------------------------------------------
 Activity 
start               : 
03-JUN-2019 21:59:21
 Activity 
end                 : 
04-JUN-2019 21:59:21
 Executions completed         : 
2
 Executions interrupted       : 
 Executions 
with fatal 
error  : 
-------------------------------------------------------------------------------
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
 Index candidates            : 
 Indexes created             : 
 Space used                  : 
0 B
 Indexes dropped             : 
 SQL statements verified     : 
 SQL statements improved     : 
 SQL plan baselines created  : 
 Overall improvement factor  : 
0x
-------------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
 Unused indexes    : 
 Space used        : 
0 B
 Unusable indexes  : 
-------------------------------------------------------------------------------
ERRORS
---------------------------------------------------------------------------------------------
No errors found.
---------------------------------------------------------------------------------------------
SQL>

Oracle Database 19c中的自動索引

相關文章