Dynamic Parameters造成的linked server效能問題

曾今遇到這樣一個linked server的效能問題。

客戶這邊的linked server是從一臺SQL Server 2000的伺服器連線到一臺後端的Oracle的資料庫。客戶遇到的問題是，他們將linked server執行在out of process模式下時，執行一句類似”select * from Oracleserver.databasename.schema.T where…”的查詢來獲取Oracle上的資料，但該查詢花了很長時間都沒執行結束。然而，再另一臺SQL Server上建立linked server （執行在in process模式下）連線到同樣的Oracle資料庫，執行同樣的語句，結果就能很快返回。

是什麼原因造成了這樣的效能差異呢？

客戶通過跟蹤Oracle的查詢執行狀況，發覺在有問題的Linked Server上，執行查詢時會在Oracle那邊對錶T做一個全表掃描，然後把表T上所有的資料返回給SQL Server。然後在SQL Server那段對這些資料做過濾（也就是所有的where條件都是在SQL Server中被執行的）。不過在那套好的linked server上，所有的過濾操作是在Oracle端就執行完成的，然後Oracle端直接把過濾完的資料返回給SQL Server。在表T的資料量非常大的情況下，這兩種不同的行為會造成返回到SQL Server端得資料量有巨大的差異。考慮到網路傳輸大資料的延遲和SQL Server需要呼叫大量IO來快取接受到的資料，這種行為的差異會最終導致查詢效能上的巨大區別。

客戶一開始以為是out of process模式的問題，但是即使我們把有問題的linked server設定為in process模式下，問題依舊存在。

我們這裡搭建了一個測試環境來模擬客戶那裡遇到的問題，並且查閱了相關的資料。我們發覺，當linked server使用四段式方式（Oracleserver.databasename.schema.T這樣的形式）來訪問後端Oracle資料庫的時候，預設行為就是把所有的資料傳送給SQL Server然後在SQL Server端做過濾。也就是說，這事實上是MSDAROA（微軟的Oracle驅動）的一個標準行為。

那麼是什麼原因使得另一套Linked Server會直接在Oracle端做過濾呢？

最終的研究表明這其實是受到一個叫”Dynamic Parameters”的MSDAROA設定的影響。當”Dynamic Parameters”被設定為1的時候，就會出現這種狀況。

你可以到以下注冊表項下面找到Dynamic Parameters的設定。

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\MSDAORA]

如果你找不到Dynamic Parameters這個值或者這個值為1，則資料就是在Oracle端先過濾然後返回。反之，如果Dynamic Parameters值為0（即disabled），則會把所有資料都返回到SQL Server端。