知識星球
老張拉呱：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（點選“閱讀原文”）
原創：老張拉呱
