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

19c 新特性: Hint Usage Reports詳解

 

 

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

 

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

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

Oracle Hints 是 SQL陳述句中的註釋機制,用來傳遞指令給 Oracle 資料庫最佳化器,告訴最佳化器按照我們的告訴它的方式 選擇執行計劃,除非存在某種條件阻止最佳化器這樣做,典型的比如設定了 OPTIMIZER_IGNORE_HINTS、OPTIMIZER_IGNORE_PARALLEL_HINTS 引數或發生了查詢轉換甚至 Hint 衝突,那麼可能會導致 Hint 失效。

 

最早在 Oracle Database 7 中就引入了 Hint,那時候當最佳化器生成執行計劃時,使用者幾乎沒有什麼可以求助的資源。不像現在 Oracle 資料庫中提供了豐富的最佳化工具,典型的比如SQL Tuning Advisor、SQL plan management以及SQL Performance Analyzer 等等,從而可以幫助我們解決最佳化器無法解決的效能問題。

 

Oracle 建議儘量使用這些輔助的調優工具而不是 Hint,一是這些調優工具比 Hint 更加系統、全面、有效,二是當用了很多 Hint 後,在資料和資料庫環境發生變化時,可能帶來新的問題,也就是說使用 Hint 可能能帶來短期好處,但不會長期持續提高效能。

 

所以,常規的使用 Hint 的場景應該是:當收集了相關的統計資訊後,在不用 hints 使用 explain plan 評估了執行計劃以後才應該謹慎使用。對於一些不得不用的在實際環境中,也可能經常面臨這樣的問題:比如語法不正確或 Hint 使用不當,系統不會給我們任何錯誤提示。所以很多時候可能也就“稀里糊塗”的、一頓“猛藥”下去,”病”好了,哈哈。

 

隨著 Oracle Database 19c 的釋出,最佳化器有一個新的重要的增強:Hint Usage report,它可以報告 hint 的使用情況,該報告包括所有最佳化器提示的狀態,包括 PARALLEL 和 INMEMORY。

 

Hint Usage report 會顯示使用和忽略了哪些提示,並通常解釋為什麼忽略提示。忽略提示最常見的原因有語法錯誤(Syntax errors)、不正確的Hint(Unresolved hints)、衝突(Conflicting hints)、受影響的hint(Hints affected by transformations)等,我這裡透過dbms_xplan來快速體驗一下。

 

DBMS_XPLAN.DISPLAY_CURSOR(

   sql_id            IN  VARCHAR2  DEFAULT  NULL,

   cursor_child_no   IN  NUMBER    DEFAULT  0, 

   format            IN  VARCHAR2  DEFAULT  ‘TYPICAL’);

dbms_xplain.display_cursor大家應該經常用,不再多少。19c中新增加的flag:

       +HINT_REPORT_USED             顯示使用的hints

       +HINT_REPORT_UNUSED         顯示未使用和語法錯誤的hint, 預設format=TYPICAL包含該flag,也就是會報告無效的Hint — Invalid Hint

       +HINT_REPORT                      包含上面2個flag的內容,當format=ALL時自動包含該flag.

 

下麵簡單看個示例:

 

PDB1@ZRP>set feedback on sql_id      

PDB1@ZRP>select /*+ full(test) index(nonexists) nonfunc(test) */ object_name from test where object_id=123;

OBJECT_NAME

———————————————————————————————————–

OPQTYPE$

 

1 row selected.

 

SQL_ID: aqh25km72pgj3

PDB1@ZRP>PDB1@ZRP>select * from dbms_xplan.display_cursor(sql_id=>’aqh25km72pgj3′);

PLAN_TABLE_OUTPUT

————————————————————————–

SQL_ID  aqh25km72pgj3, child number 0

————————————-

select /*+ full(test) index(nonexists) nonfunc(test) */ object_name

from test where object_id=123

 

Plan hash value: 1357081020

Hint Report (identified by operation id / Query Block Name / Object Alias):

Total hints for statement: 2 (N – Unresolved (1), E – Syntax error (1))

—————————————————————————

 

   1 –  SEL$1

         N –  index(nonexists)

         E –  nonfunc

 

PDB1@ZRP>

 

說明:最後一部分是Hint Report,它告訴我們對於執行計劃Id=1這行,查詢塊SEL$1有2個未使用的提示:1個是語法錯誤(E),因為nonfunc不是一個提示。另一個是index(),它雖然是一種有效的語法,但是它提到了一個不在查詢中的別名(nonexists),然後錯誤被解析(N)。

 

下麵再看一個忽略正常提示的Hint Report示例:

 

PDB1@ZRP>alter session set optimizer_ignore_hints=true;

Session altered.

PDB1@ZRP>select /*+ full(test) */ object_name from test where object_id=123;

OBJECT_NAME

—————————————————————————-

OPQTYPE$

 

1 row selected.

SQL_ID: 1azqdh1xrf33w

PDB1@ZRP>select * from dbms_xplan.display_cursor(sql_id=>’1azqdh1xrf33w’);

 

PLAN_TABLE_OUTPUT

————————————————————————————————————

SQL_ID  1azqdh1xrf33w, child number 0

————————————-

select /*+ full(test) */ object_name from test where object_id=123

 

Plan hash value: 159453698

Hint Report (identified by operation id / Query Block Name / Object Alias):

Total hints for statement: 1 (U – Unused (1))

—————————————————————————

   1 –  SEL$1 / TEST@SEL$1

         U –  full(test) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS

PDB1@ZRP>

 

說明:最後一部分是Hint Report告訴我們有1個未使用的提示,是因為IGNORE_OPTIM_EMBEDDED_HINTS設定被拒絕了

 

更多詳細介紹,請大家參考SQL Tuning Guide(點選“閱讀原文”)

 

原創:老張拉呱

 

贊(0)

分享創造快樂