Oracle 19c中的自動索引（DBMS_AUTO_INDEX）

lhrbest發表於2020-11-04
Oracle索引Index

Oracle 19c中的自動索引（DBMS_AUTO_INDEX）

它能做什麼

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

  • 根據表列使用情況確定潛在的自動索引。文件稱這些為“候選索引(candidate indexes)”。

  • 將自動索引建立為不可見索引，因此不會在執行計劃中使用它們。索引名稱包括“SYS_AI”字首。

  • 根據SQL語句測試不可見的自動索引，以確保它們能提高效能。如果它們導致效能提高，則可以它們可見。如果效能未得到改善，則相關的自動索引將標記為不可用，稍後將被刪除。針對失敗的自動索引測試的SQL語句被列入黑名單，因此將來不會考慮將它們用於自動索引。第一次對資料庫執行SQL時，最佳化程式不會考慮自動索引。

  • 刪除未使用的索引。

先決條件

Oracle 19c，此功能僅限於企業版。透過設定初始化引數“_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

這不受支援，不應在實際系統上使用。

配置

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

顯示配置

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>

啟用/禁用自動索引

使用  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');

自動索引的表空間

預設情況下，自動索引是在預設的永久表空間中建立的。如果這是不可接受的，您可以使用  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);

模式級（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>

其它配置

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

  • 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 引數指定自定義表空間時，將忽略此引數。

刪除二級索引

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

如果您感覺特別勇敢， 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;

檢視

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

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;

活動報告

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       : 0
 Executions with fatal error  : 0
-------------------------------------------------------------------------------
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
 Index candidates            : 0
 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
---------------------------------------------------------------------------------------------
No errors found.
---------------------------------------------------------------------------------------------
SQL>



About Me

........................................................................................................................

● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除

● 本文在個人微 信公眾號（ DB寶）上有同步更新

● QQ群號： 230161599 、618766405，微信群私聊

● 個人QQ號（646634621），微 訊號（db_bao），註明新增緣由

● 於 2020年11月完成

● 最新修改時間：2020年11月

● 版權所有，歡迎分享本文，轉載請保留出處

........................................................................................................................

● 小麥苗的微店：

● 小麥苗出版的資料庫類叢書： http://blog.itpub.net/26736162/viewspace-2142121/

● 小麥苗OCP、OCM、高可用、DBA學習班： http://blog.itpub.net/26736162/viewspace-2148098/

● 資料庫筆試面試題庫及解答： http://blog.itpub.net/26736162/viewspace-2134706/

........................................................................................................................

請掃描下面的二維碼來關注小麥苗的微 信公眾號（ DB寶）及QQ群（230161599、618766405）、新增小麥苗微 信（db_bao）， 學習最實用的資料庫技術。

........................................................................................................................

 

 



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2732221/，如需轉載，請註明出處，否則將追究法律責任。

相關文章