技術專欄
Automatic Indexing 顧名思義，即 Oracle 資料庫自動管理索引的機制。 它通過持續監控應用程式的 SQL 工作負載，識別高頻訪問且缺乏有效索引的模式，並自動創建、驗證和維護索引，從而提升查詢性能。 其主要工作流程可以概括為：
捕捉：資料庫系統每隔 15分鐘會捕捉時間內的 SQL 工作負載資訊 (STS)，包括 SQL 語句、執行計劃和統計訊息。
識別：基於捕捉到的資訊，Automatic Indexing 會自動識別出潛在的索引創建候選對象，即那些可以通過添加索引來提升性能的 SQL 語句。
創建：系統會自動創建候選索引，但這些索引最初會被標記為 INVISIBLE，不會被優化器使用。
驗證：系統會持續監控並驗證這些 INVISIBLE 索引的性能影響。 如果驗證結果表明索引能夠提升性能，則會將其標記為 VISIBLE，供優化器使用。 反之，則會將其標記為 UNUSABLE 或直接刪除。
維護：系統會持續監控已有索引的使用情況和性能表現，並自動刪除不再需要或性能低下的索引。
自動化索引管理：大大降低了 DBA 手動管理索引的工作量，讓 DBA 可以更專注於其他重要的任務。
性能提升：通過自動創建和維護索引，可以顯著提升應用程式的查詢性能，尤其對於那些缺乏經驗的 DBA 或開發人員來說，效果更為顯著。
自適應工作負載變化：Automatic Indexing 可以動態適應應用程式工作負載的變化，自動調整索引策略，從而保持最佳性能。
降低人為錯誤風險：自動化機制可以減少人為錯誤的可能性，例如創建錯誤的索引或忘記刪除不再需要的索引。
Automatic Indexing 是 Oracle 19c 版本引入的一項重要特性，目前 Automatic Indexing 可以應用於以下平台[1]：
Oracle Database Enterprise Edition on Engineered Systems
Oracle Exadata Database Service on Dedicated Infrastructure
Oracle Exadata Database Service on Cloud@Customer
Automatic Indexing 的配置和管理主要通過 DBMS_AUTO_INDEX 程序包來實現。 以下是一些常用的 SQL 語法：
|
-- 啟用 Automatic Indexing EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
-- 禁用 Automatic Indexing EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF'); |
|
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 |
參數說明:
| 參數名稱 | 說明 |
| AUTO_INDEX_COMPRESSION | 控制自動創建的索引是否使用壓縮。OFF 表示不使用壓縮，ON 表示使用壓縮。 |
| AUTO_INDEX_DEFAULT_TABLESPACE | 指定自動創建索引的預設表格空間。如果未設定，則使用索引所在表格的表格空間。 |
| AUTO_INDEX_MODE | 控制自動索引功能的模式。IMPLEMENT 表示啟用自動索引功能，自動創建和刪除索引；REPORT ONLY 表示僅生成索引建議報告，不實際創建或刪除索引。 |
| AUTO_INDEX_REPORT_RETENTION | 指定自動索引建議報告的保留天數。 |
| AUTO_INDEX_RETENTION_FOR_AUTO | 指定自動創建的索引的保留天數，如果在此期間內索引未使用，則會被自動刪除。 |
| AUTO_INDEX_RETENTION_FOR_MANUAL | 指定手動創建的索引的保留天數，如果在此期間內索引未使用，則會被自動刪除。 |
| AUTO_INDEX_SCHEMA | 指定自動索引功能要監視的 Schema 名稱。 |
| AUTO_INDEX_SPACE_BUDGET | 控制自動索引功能可以使用的最大儲存空間（以 MB 為單位）。如果設定為 0 或未設定，則表示沒有空間限制。 |
|
select task_name, status, enabled, interval, last_schedule_time, systimestamp-last_schedule_time ago from dba_autotask_schedule_control where dbid = sys_context('userenv','con_dbid') and task_name = 'Auto Index Task';
-- 自動索引任務默認每 900 秒執行一次 -- 若 enabled 欄位的值為 false，則代表 automatic indexing 沒有啟用 |
|
set serveroutput on declare report clob := null; begin report := dbms_auto_index.report_last_activity(); dbms_output.put_line(report); end; /
|
|
begin dbms_auto_index.drop_auto_indexes( owner => 'MY_SCHEMA', index_name => '"SYS_AI_512bd3h5nif1a"', allow_recreate => false); end; / -- owner：索引擁有者的 Schema 名稱。如果設定為 NULL，則表示刪除當前用户的 Schema 中的索引。 -- index_name：要刪除的索引名稱。如果設定為 NULL，則表示刪除所有符合條件的自動索引。 -- allow_recreate：是否允許自動索引功能重新創建被刪除的索引。預設值為 FALSE，表示不允許重新創建。 |
|
select owner, index_name, index_type, table_owner, table_name table_type from dba_indexes where auto = 'YES' order by owner, index_name;
--如果表的統計信息過時或缺失，自動索引功能將不會考慮為這些表創建索引，直到收集到最新的統計信息 |
索引在 DML 操作期間會產生維護成本，這可能會抵消其對數據訪問性能的提升。Oracle 23ai 對自動索引功能進行了增強，使其在決定哪些索引有利於整體工作負載時，能夠更全面地考慮索引維護成本。
主要增強功能包括：
更全面的索引評估： 除了數據訪問性能之外，自動索引功能現在還會考慮索引維護成本，例如 DML 操作對索引的影響。
支持範圍謂詞： 使用範圍謂詞篩選的欄位現在可以被考慮用於創建索引，例如 WHERE age BETWEEN 20 AND 30。
支持函數索引： 現在支持基於函數的索引，例如 CREATE INDEX idx_name ON table_name (UPPER(column_name))。
這些增強功能進一步擴展了自動索引的適用範圍，使其能夠更有效地提升資料庫性能。
Automatic Indexing 是一項具有革命性意義的技術，它將索引管理的智慧化和自動化提升到了新的高度。 而 23ai 版本的增強功能，使其更加易用和普及。 通過自動化的索引創建、驗證和維護，它可以顯著提升應用程式性能，降低 DBA 的工作負擔，並減少人為錯誤風險。 在生產環境中建議謹慎使用 Automatic Indexing，並結合實際情況進行充分的測試和驗證，相信隨著這項技術的不斷成熟和完善，它將為 Oracle 資料庫的性能優化帶來更大的貢獻。
希望本文能為您理解和應用 Automatic Indexing，如果您有任何疑問或建議，請洽邁達特 Oracle 團隊。
