案例：DRIVING_SITE 提示（HINT）設定無效的調查

• 2020 年 3 月 26 日
• 筆記

Keyword:

FGA DRIVING_SITE HINT NOT WORK DBLINK 10053 SYS_AUDIT

客戶的問題

用戶發現對某使用的DB Link的視圖（View）設定細粒度審計(Fine Grained Auditing 簡稱FGA)後， 該視圖中包含的DRIVING_SITE提示（HINT）變的無效了。

用戶的資料庫環境資訊如下：

澄清和核實問題

根據用戶描述，可以判定問題的焦點是SQL提示（HINT）DRIVING_SITE的無效和FGA設定的關係。

以下是用戶提供的執行計劃狀況：

通過上面的輸出，我們看到設定FGA後執行計劃確實發生了變化，和沒有追加DRIVING_SITE 提示的執行計劃輸出相同，即該提示沒有起到期待的作用。

從何著手？

即使是再優秀的工程師，也不是所有的問題和所有的知識都了解的。 所以針對用戶的問題，現在我們需要一步一步地明確和解決以下的一些問題：

理清問題後的調查（Research ）

關於DRIVING_SITE提示

我們首先了解一下DRIVING_SITE提示：

官方在線文檔【Database Reference】手冊會提供關於一般SQL語法、表達式 、 使用規則等相關的知識內容，所以我們先從這個文檔開始入手查看DRIVING_SITE提示的含義：

根據上面的描述，我們知道DRIVING_SITE 提示的主要作用是在分散式處理（即使用DB Link）時， 指示優化器在哪個資料庫上執行。

根據用戶提供的SQL文:

即，如果沒有指定DRIVING_SITE的情況下，dept_view（DEPT@TEST）的數據應該通過DB Link發送到本地（即EMP所在的資料庫）後，在本地進行表的連接。

如果指定DRIVING_SITE的情況下，emp的數據會被送到遠程（即dept_view所在的資料庫）， 然後在遠程資料庫上進行表的連接後生成結果，再返回結果給本地。 （※這樣在本地的表很小，遠程表很大的情況下，可能會減少數據的傳輸，得到更好的效率）

DRIVING_SITE的使用限制

了解了DRIVING_SITE的含義，我們再看看是不是DRIVING_SITE的使用限制導致該問題發生？ 通過對MOS和相關資源如在線文檔，白皮書，網上資源等的檢索， 我們知道對於DRIVING_SITE提示的使用，有如下的限制：

雖然對於driving_site提示有上面的限制，但是我們並沒有發現任何和FGA有關的內容。

所以我們還要進一步地調查driving_site提示無效的原因。

一般都有哪些情況會導致提示無效？

既然我們現在沒有找到最終合理的解釋，下面嘗試從一般經驗和方法入手調查。 根據經驗，一般都有哪些情況會導致提示無效？

好吧，越扯越遠了~~，但確實沒有本次問題（DRIVING_SITE）的經驗。。。

提示無效時，應該如何入手調查？

下面我們通過一般的診斷方法進行入手。 其實目前為止，並沒有包治百病的提示無效的解決方法，但是通過10053 Trace(Optimizer Trace),通常可以排除一些提示無效的原因和SQL解析執行情況， 並且在 10gR2以後的版本，10053中增加了Dumping Hints的功能，會表示出Hint相關的錯誤(err)和是否使用等資訊。

（當然有時候Dumping Hints並不能輸出很有用的資訊，甚至輸出的是無法理解的內容，所以僅能做參考）

不管怎樣，我們需要先取得10053 trace看一看是有查詢變換或者其他有用的資訊輸出。

現象能否能夠重現？取得10053 Trace資訊

根據用戶的描述，在用戶環境中通過TEST CASE可以重現問題， 所以我們也嘗試在測試環境中重新問題，看看是不是跟環境有關，是不是在所有的資料庫上都能夠重現？

重現測試

測試1. 沒有設定FGA，追加了DRIVING_SITE 提示時的情況

我們根據執行計劃可以看到，DRIVING_SITE提示起到了作用：

ID 3中emp的數據被送到遠程（即dept_view所在的資料庫），然後在遠程資料庫上進行表的連接後生成結果，再返回結果給本地； Note資訊中包括了「fully remote statement」資訊。

測試2. 設定FGA，追加了DRIVING_SITE 提示時的情況

我們根據執行計劃可以看到，DRIVING_SITE提示並沒有起到作用：

ID=4 dept_view（DEPT@TEST）的數據通過DB Link發送到本地（即EMP所在的資料庫）後，在本地進行表的連接； 並且Note資訊中沒有遠程執行的資訊。

我們還在11.2.0.4 、12.1.0.2和12.2.0.1版本上做了測試，如用戶所說會有同樣的問題。

取得10053 Trace

雖然在我們的測試環境中也重現了現象，但是我們還是無法確定是期待動作還是bug導致的問題，於是嘗試取得10053 Trace來看看是否有有效的輸出。

在【沒有設定FGA追加了DRIVING_SITE 提示時】和【設定FGA追加了DRIVING_SITE 提示時】的情況，分別通過下面的方法，取得10053 trace。

10053 Trace結果

然後我們查看輸出內容，看看有什麼有用的資訊。

在沒有設定FGA追加了DRIVING_SITE 提示時本地的10053中並沒有完整的輸出， 猜測是在遠程資料庫中執行的，所以應該在遠程資料庫中設定10053，查看才有效。 （本次暫時忽略）

查看設定FGA追加了DRIVING_SITE 提示時時本地的10053的資訊

根據Dumping Hints的資訊err=0，used=1，應該是使用了提示， 但事實上提示（HINT）確實無效的，所以通過這個資訊無法繼續調查。

我們通過【Final query after transformations】，即查詢轉換後的資訊， 可以看到在對於DEPT_VIEW視圖，系統自動追加了SYS_AUDIT函數。

這個問題到底是期待動作還是bug導致的？

至此，我們可以猜測問題很有可能和追加了SYS_AUDIT函數有關。

SYS_AUDIT函數有什麼特點？ 關於這個函數的資訊很少，但是我們知道在實現FGA時，內部會使用這個函數。 而且它和SYS_CONTEXT等函數一樣都是只能在本地資料庫執行的函數。 通過檢索相關的資訊時，我們發現類似於Bug 12810600（Not a Bug）的報告， （關於只能在本地資料庫執行的函數SYS_CONTEXT導致DRIVING_SITE無效的報告） 即為了避免由於本地資料庫執行的函數在遠程資料庫執行發生結果不正確， 這些函數只能夠在本地執行，不能夠在遠程執行。

參考： Bug 12810600 - DRIVING_SITE HINT DOES NOT WORK

Product Version 10.2.0.4 Status 92 - Closed, Not a Bug

原因和解決方案

根據上面的調查結果，我們可以判斷本次現象的原因是由於Oracle產品規格的限制， 當設定FGA審計時，DRIVING_SITE 提示無效屬於期待動作， 對於這一點沒有更好的解決辦法，需要客戶在應用程式和應用方法上做調整。

如果為了更好的分散式查詢性能可考慮其他的查詢優化方法。

參考：  Database Administrator's Guide  http://docs.oracle.com/cd/E11882_01/server.112  /e25494/ds_appdev.htm#ADMIN12196  >Tuning Distributed Queries  

知識點總結（KM）

通過本次案例， 我們詳細描述了解決DRIVING_SITE 提示設定無效問題的思路和過程， 並涵蓋了以下的知識點。