歡迎光臨
每天分享高質量文章

Oracle 19c 新特性:自動化索引 Automatic indexing 實踐

 

 

老張拉呱:thomas zhang,甲骨文雲平臺事業部資深技術顧問,2008年加入甲骨文公司資料庫咨詢部門,10+年甲骨文解決方案咨詢支持經驗,資深系統工程師、Oracle OCM認證專家,具有豐富的Cloud /IT專案經驗。目前主要負責甲骨文中國北方區(醫院/衛生、交通、製造、教育、政府、證券、媒體、金融、零售等行業)客戶的資料庫、中間件、IaaS/PaaS、集成系統等相關技術解決方案咨詢工作。

 

簽名:我為人人,人人為我,三人行,必有我師

新浪微博: http://weibo.com/tomszrp

Automatic indexingOracle Database 19c開始新增加的特性,從字面上很容易理解,就是依據應用負載的變化自動/動態地進行索引的管理任務,比如創建(create index)、重構(rebuild index)和刪除(drop index),從而提高資料庫性能,這個特性也是Oracle自治資料庫雲服務自我優化的一個基礎。

 

索引從一開始就是資料庫性能的一個基本特性,儘管在並行、分割槽、壓縮、物化視圖以及記憶體列等其他性能特性方面有了很大的進步,但索引仍然是OLTP甚至OLAP中都離不開的關鍵特性。過去的經歷大家都有深刻的體會,創建合適的索引其實是蠻有挑戰的,它需要我們對資料模型、應用程式以及資料分佈有深入的瞭解,還需要對資料庫系統的內部結構有一定的瞭解(例如查詢優化、快取區管理等),同時輔助各種Advisor(比如Index Advisor, Partition Advisor, In-Memory Advisor等),靠經驗豐富的DBA來完成。

 

現實中,無論優化人員多麼的技術熟練,但往往當對資料模型、應用程式代碼或資料分佈進行修改時,他/她卻很少修改索引的選擇,所以我們經常會看到很多不必要的索引或不合適的索引導致的性能問題。Automatic indexing將這個複雜的處理實現了自動化,基於手動SQL調優的常用方法,通過構建內置的專家系統,自動捕獲SQLCapture)、識別候選索引(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 索引,將來或許也會支持bitmapFBIPartialdomain等型別,我們拭目以待吧。

2)支持分割槽和非分割槽表,不支持臨時表。

 

•Automatic indexing 是如何工作的?

自動索引基於手動SQL調優的常用方法,它不斷評估執行的SQL和基礎表,以確定要創建哪些索引以及可能刪除哪些索引。它通過專家系統完成此任務,該系統驗證索引可能做出的改進,併在創建之後驗證所做的假設。然後它使用強化學習來確保它不再犯同樣的錯誤。最重要的是,隨著資料模型和訪問路徑的變化,Oracle資料庫19c能夠隨著時間的推移進行調整。

 

Automatic Indexing Process(自動索引過程)以後臺服務行程每隔15分鐘運行一次,並執行如下任務:

  1. 識別自動索引候選項

      通過捕獲SQL歷史到SQL repository(包括SQL,執行計劃,系結變數,統計信息等),識別SQL陳述句中用到列的使用情況來標識自動索引候選項。

  2. 為自動索引候選項創建invisible的自動索引

      Invisible Indexes11g中提出的新功能,預設對優化器是不可見的,也就是說不會影響到用戶既有的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使用自動索引。

       比如:以下陳述句將SHHR用戶添加到排除串列中,這樣SHHR用戶就不能使用自動索引:

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_ACTIVITYREPORT_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’,          —
支持TEXTHTMLXML 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、準備點測試資料

       [email protected]>create table test as select * from dba_objects;
Table created.
[email protected]>insert into test select* from test;
72397 rows created.
[email protected]>insert into test select* from test;
144794 rows created.
…   —
反覆插入

       [email protected]>update test set object_id=rownum;
2316704rows updated.
[email protected]>commit;
Commit complete.
[email protected]>

  

3、啟用Automatic Indexing

 

4、執行測試SQL

 

      [email protected]>select object_name from test where object_id=1;
[email protected]>select object_type from testwhere object_id=123;
[email protected]>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;
/

詳細的輸出報告,點“閱讀原文”查看。

 

原創:老張拉呱


資源下載

關註公眾號:資料和雲(OraNews)回覆關鍵字獲取

2018DTCC , 資料庫大會PPT

2018DTC,2018 DTC 大會 PPT

DBALIFE ,“DBA 的一天”海報

DBA04 ,DBA 手記4 電子書

122ARCH ,Oracle 12.2體系結構圖

2018OOW ,Oracle OpenWorld 資料

產品推薦

 

雲和恩墨Bethune Pro企業版,集監控,巡檢,安全於一身,你的專屬資料庫實時監控和智慧巡檢平臺,漂亮的不像實力派,你值得擁有!

雲和恩墨zData一體機現已發佈超融合版本和精簡版,支持各種簡化場景部署,零資料丟失備份一體機ZDBM也已發佈,歡迎關註。

    閱讀原文

    赞(0)

    分享創造快樂