知識星球
老張拉呱：thomas zhang，甲骨文雲平臺事業部資深技術顧問，2008年加入甲骨文公司資料庫諮詢部門，10+年甲骨文解決方案諮詢支援經驗，資深系統工程師、Oracle OCM認證專家，具有豐富的Cloud /IT專案經驗。目前主要負責甲骨文中國北方區（醫院/衛生、交通、製造、教育、政府、證券、媒體、金融、零售等行業）客戶的資料庫、中介軟體、IaaS/PaaS、整合系統等相關技術解決方案諮詢工作。
簽名：我為人人，人人為我，三人行，必有我師。
新浪微博： http://weibo.com/tomszrp
Automatic indexing是Oracle Database 19c開始新增加的特性，從字面上很容易理解，就是依據應用負載的變化自動/動態地進行索引的管理任務，比如建立(create index)、重構(rebuild index)和刪除(drop index)，從而提高資料庫效能，這個特性也是Oracle自治資料庫雲服務自我最佳化的一個基礎。
索引從一開始就是資料庫效能的一個基本特性，儘管在並行、分割槽、壓縮、物化檢視以及記憶體列等其他效能特性方面有了很大的進步，但索引仍然是OLTP甚至OLAP中都離不開的關鍵特性。過去的經歷大家都有深刻的體會，建立合適的索引其實是蠻有挑戰的，它需要我們對資料模型、應用程式以及資料分佈有深入的瞭解，還需要對資料庫系統的內部結構有一定的瞭解（例如查詢最佳化、快取區管理等），同時輔助各種Advisor(比如Index Advisor, Partition Advisor, In-Memory Advisor等），靠經驗豐富的DBA來完成。
現實中，無論最佳化人員多麼的技術熟練，但往往當對資料模型、應用程式程式碼或資料分佈進行修改時，他/她卻很少修改索引的選擇，所以我們經常會看到很多不必要的索引或不合適的索引導致的效能問題。Automatic indexing將這個複雜的處理實現了自動化，基於手動SQL調優的常用方法，透過構建內建的專家系統，自動捕獲SQL（Capture）、識別候選索引（Identify Candidates）、驗證（Verify）、決策（Decide）、線上驗證（Online Validation）、監控（Monitor）等方法將索引的管理實現了自動化，這個過程並不簡單。是不是很酷？
註：Automatic indexing特性對於on-prem環境僅支援Oracle Exadata平臺，對於Oracle Cloud各個Cloud Edition都支援。
•Automatic indexing 提供如下功能
1）定期在預定義的時間間隔內在後臺執行自動索引過程
2）分析應用程式工作負載，並根據分析報告相應地建立必要的新的索引，並刪除現有效能不佳的索引，以提高資料庫效能
3）重構由於表分割槽維護操作(比如ALTER table MOVE)而被標記為不可用的索引(unusable index)
4）提供PL/SQLAPIs，用於配置資料庫中的自動索引和生成與自動索引操作相關的報告。
說明：
1）當前版本Auto indexes 是local B-tree 索引，將來或許也會支援bitmap、FBI、Partial、domain等型別，我們拭目以待吧。
2）支援分割槽和非分割槽表，不支援臨時表。
•Automatic indexing 是如何工作的？
自動索引基於手動SQL調優的常用方法，它不斷評估執行的SQL和基礎表，以確定要建立哪些索引以及可能刪除哪些索引。它透過專家系統完成此任務，該系統驗證索引可能做出的改進，併在建立之後驗證所做的假設。然後它使用強化學習來確保它不再犯同樣的錯誤。最重要的是，隨著資料模型和訪問路徑的變化，Oracle資料庫19c能夠隨著時間的推移進行調整。
Automatic Indexing Process(自動索引過程)以後臺服務行程每隔15分鐘執行一次，並執行如下任務：
1. 識別自動索引候選項
透過捕獲SQL歷史到SQL repository（包括SQL，執行計劃，系結變數，統計資訊等），識別SQL陳述句中用到列的使用情況來標識自動索引候選項。
2. 為自動索引候選項建立invisible的自動索引
Invisible Indexes是11g中提出的新功能，預設對最佳化器是不可見的，也就是說不會影響到使用者既有的SQL陳述句。而且
3. 根據SQL陳述句驗證不可見的自動索引
如果透過使用這些自動索引提高了SQL陳述句的效能，那麼會將這些索引配置為可見索引，以便在SQL陳述句中使用它們。
如果使用這些索引不能提高SQL陳述句的效能，則將這些索引置為unusable的索引，並將對應的SQL陳述句列入“黑名單”。unusable的索引稍後會被自動索引過程刪除。被列入“黑名單”的SQL陳述句將來不允許使用自動索引。
4. 清除unused auto indexes
對於長時間不使用的auto indexes會自動進行清除工作。預設是373天，可以使用DBMS_AUTO_INDEX.CONFIGURE過程來配置資料庫中保留未使用的自動索引的時間段。
• 在資料庫中配置AutomaticIndexing
這個動作透過DBMS_AUTO_INDEX.CONFIGURE過程來完成。
1、啟用和禁用Automatic Indexing特性
◊啟用自動索引，並將任何新的自動索引建立為可見索引(visible indexes)，以便在SQL陳述句中使用
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,’IMPLEMENT’);
◊啟用自動索引，但將任何新的自動索引建立為不可見索引(invisible indexes)，所以不能在SQL陳述句中使用
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,’REPORT ONLY’);
◊禁用自動索引，這樣就不會建立新的自動索引，並禁用現有的自動索引。這也是預設樣式。
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,’OFF’);
2、指定哪些Schemas可以使用Automatic Indexing
當在上一步啟用Automatic Indexing特性後，預設情況下所有的Schemas都可以使用Automatic Indexing。管理員可以根據需要允許哪些schema使用自動索引。
比如：以下陳述句將SH和HR使用者新增到排除串列中，這樣SH和HR使用者就不能使用自動索引:
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_SCHEMA’, ‘SH’, FALSE);
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_SCHEMA’, ‘HR’, FALSE);
後期如果又想讓某個使用者使用自動索引，比如HR，可以透過如下陳述句來設定
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_SCHEMA’, ‘HR’, NULL);
恢復到預設（也就是所有Schema都可以使用自動索引）
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_SCHEMA’, NULL, TRUE);
3、為unused auto indexes指定保留期
下麵的陳述句將未使用的自動索引的保留期設定為90天。
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_RETENTION_FOR_AUTO‘, ‘ 90‘);
下麵的陳述句將未使用的自動索引的保留期重置為預設值373天。
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_RETENTION_FOR_AUTO‘, NULL);
4、為unused 非自動索引（non-auto indexes）指定保留期
就是為我們手動建立的unused的索引指定一個保留期，預設情況下，Automatic Indexing Process(自動索引過程)不會刪除unused手動建立的索引。
透過如下過程，指定一個unused手動索引保留期，比如60天，那麼到期後，將會被Automatic Indexing Process(自動索引過程)清除。
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_RETENTION_FOR_MANUAL’, ’60’);
恢復到預設值（也就是不給unused 手動索引指定保留期，Automatic Indexing Process不會刪除這些索引）
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_RETENTION_FOR_AUTO’,NULL);
5、為automatic indexing logs指定保留期
預設是31天，如下示例表示保留60天
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_REPORT_RETENTION’, ’60’);
恢復到預設
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_REPORT_RETENTION’, NULL);
註意：Automatic indexing reports的生成依賴於automatic indexing logs。所以，當過了automatic indexing logs的保留期後，相應的automatic indexing reports就不能生成了。
6、指定一個表空間存放Auto Indexes
預設情況下，使用當前資料庫的預設持久表空間。可以透過如果配置指定為其他表空間：
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_DEFAULT_TABLESPACE’, ‘MYAUTOTBS’);
說明：對於使用預設持久表空間存放Auto Indexes的情況，可以透過類似如下過程來指定一個空間限額（可以使用到20%），預設是50%：
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_SPACE_BUDGET’, ’20’);
7、指定一個臨時表空間存放臨時Auto Indexes結構
預設情況下，使用當前資料庫的預設TEMP表空間。可以透過如果配置指定為其他表空間：
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_TEMP_TABLESPACE’, ‘MYAUTOTBS_TEMP’);
•生成Automatic Indexing報告
透過DBMS_AUTO_INDEX包的REPORT_ACTIVITY和REPORT_LAST_ACTIVITY函式可以生成AutomaticIndexing Reports。
比如生成指定時間範圍的自動索引報告：
set serveroutput on
declare
report clob := null;
begin
report := DBMS_AUTO_INDEX.REPORT_ACTIVITY(
activity_start => TO_TIMESTAMP(‘2019-02-17’, ‘YYYY-MM-DD’),
activity_end => TO_TIMESTAMP(‘2019-02-17’, ‘YYYY-MM-DD’),
type => ‘HTML’, —支援TEXT、HTML、XML 3中格式，預設是TEXT
section => ‘SUMMARY’,
level => ‘BASIC’);dbms_output.put_line(report);
end;/
關於這2個函式的各個引數的詳細說明，參看Oracle Database PL/SQL Packages and Types Reference。
生成最近一次的自動索引報告：
set serveroutput on
declare
report clob := null;
begin
report := DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY(
type => ‘HTML’,
section => ‘SUMMARY +INDEX_DETAILS +ERRORS’,
level => ‘BASIC’);dbms_output.put_line(report);
end;/
•Automatic Indexing相關的資料字典
DBA_AUTO_INDEX_CONFIG –19.1新增檢視，描述當前自動索引的配置
DBA_INDEXES/ALL_INDEXES/USER_INDEXES —新增加的AUTO列標識是自動索引(YES)還是手動索引(NO)DBA_AUTO_INDEX_EXECUTIONS —顯示歷史自動索引任務執行
DBA_AUTO_INDEX_STATISTICS —顯示與自動索引相關的統計資訊DBA_AUTO_INDEX_IND_ACTIONS —顯示在自動索引上執行的操作
DBA_AUTO_INDEX_SQL_ACTIONS —顯示在SQL上執行的驗證自動索引的操作
•Automatic Indexing初體驗
1、檢查預設Automatic Indexing配置
2、準備點測試資料
PDB1@ZRP>create table test as select * from dba_objects;
Table created.
PDB1@ZRP>insert into test select* from test;
72397 rows created.
PDB1@ZRP>insert into test select* from test;
144794 rows created.
… —反覆插入PDB1@ZRP>update test set object_id=rownum;
2316704rows updated.
PDB1@ZRP>commit;
Commit complete.
PDB1@ZRP>
3、啟用Automatic Indexing
4、執行測試SQL
PDB1@ZRP>select object_name from test where object_id=1;
PDB1@ZRP>select object_type from testwhere object_id=123;
PDB1@ZRP>select created from test whereobject_id=345;
…
5、檢查Automatic Indexing Process的執行情況（15分鐘以後）
6、檢查下Automatic Indexing 報告
這裡以html格式輸出
set serveroutput on
declare
report clob := null;
begin
report := DBMS_AUTO_INDEX.REPORT_ACTIVITY(
activity_start => TO_TIMESTAMP(‘2019-02-17 22:51:00’, ‘yyyy-mm-ddhh24:mi:ss’),
activity_end => TO_TIMESTAMP(‘2019-02-17 22:53:07’, ‘yyyy-mm-dd hh24:mi:ss’),
type => ‘HTML’,
section => ‘ALL’,
level => ‘ALL’);
dbms_output.put_line(report);
end;
/
詳細的輸出報告，點“閱讀原文”檢視。
