SQL Server 語句在 Oracle 中即時執行時需要很長時間

請幫助我解釋此聲明和計劃：

https://www.brentozar.com/pastetheplan/?id=Bysy6YtEV

我們從 Oracle 遷移到 SQL Server，我們有一些非常奇怪的行為。這可能與遷移過程中的問題有關。

我發現很難解釋執行計劃。兩種環境都應具有相同的結構和索引。統計數據應該是最新的。SQL Server 中的設置：

• 啟用創建自動統計
• 針對 Ad Hoc Queries 進行優化 = true
• 啟用快照隔離
• 最大並行 = 4
• 門檻值 50

DB 大小為 600 Gb，16 核，160 Gb 記憶體。

查詢：

SELECT
   COUNT ( t_01.rsecondary_objectu ) AS selectExpr
FROM
   PIMANRELATION t_01 ,
   PITEM t_02 ,
   PITEMREVISION t_03
WHERE
   (   (   (   ( t_01.rprimary_objectu = t_02.puid )
           OR  ( t_01.rprimary_objectu = t_03.puid )
           )
      AND
           ( t_01.rrelation_typeu = 'w8INy241VJFL2B' )
       )
  AND  t_01.rsecondary_objectu = '2yLJkWqiVJFL2B'
   )

甲骨文

我們發現問題是相關的並且取決於數據。如果我在 GUI 中選擇要複製的其他項目（它實際上是複制的東西，以及應用程序如何執行這些語句），它會立即工作。然後，一旦正常工作，查詢看起來有點不同：https ://www.brentozar.com/pastetheplan/?id=SJo-h2c44

SELECT  COUNT ( t_01.rsecondary_objectu ) AS selectExpr FROM PIMANRELATION t_01 , PITEM t_02 , PITEMREVISION t_03 WHERE ( ( ( ( t_01.rprimary_objectu = t_02.puid ) OR ( t_01.rprimary_objectu = t_03.puid ) )  AND ( t_01.rrelation_typeu = 'w8INy241VJFL2B'  ) ) AND t_01.rsecondary_objectu IN  ('wBHZpD0uVJFL2B' , 'V2PlBGlAVJFL2B' , 'lnHlBGlAVJFL2B'  ) )

SQl Server 似乎與我之前提到的那個（無限執行）完全掙扎，同時幾乎立即提供了第二個。這真是太瘋狂了。就像一個有缺陷的產品。

我對這些聲明沒有影響，因為它們是由應用程序生成的。

CE 110 / 70 產生相同的計劃。即使禁用所有自定義索引並僅保留應用程序建議的索引，它的行為也相同。所有主鍵都是聚集索引。但也許在我們的遷移過程中出現了一些問題。但這很奇怪。大多數東西執行良好，但主題的查詢是極端的。我讓它在 SQL Server 中執行 45 分鐘。它根本沒有完成。

---

另一個例子。一旦創建了最高評級的索引，我們的 Prod 環境就會變得無法使用：

索引：CREATE INDEX EN_PIPRELEASESTATUS_1 ON

$$ TCEUP01 $$.$$ dbo $$.$$ PRELEASESTATUS $$($$ pname $$,$$ pdate_released $$） 包括 （$$ puid $$) 此查詢的結果： https : //pastebin.com/Ax3qTUjd ===>花了 137.142 秒

我們的測試環境應該相同，但行為不同： https : //pastebin.com/0PTZTJpr ===>花了 3.884 秒

有問題的計劃如下所示： https ://www.brentozar.com/pastetheplan/?id=Skvy0qRNE

它似乎可以無限執行。

Oracle 優化器使用OR 擴展來提高查詢效率。從文件中引用：

在 OR 擴展中，優化器將帶有包含 OR 運算符的 WHERE 子句的查詢轉換為使用 UNION ALL 運算符的查詢。

數據庫可以出於各種原因執行 OR 擴展。例如，它可以啟用更有效的訪問路徑或避免笛卡爾積的替代連接方法。

您可以認為新查詢是這樣編寫的：

SELECT
(
   SELECT
       COUNT ( t_01.rsecondary_objectu ) AS selectExpr
   FROM
       PIMANRELATION t_01
       INNER JOIN PITEMREVISION t_03 ON t_01.rprimary_objectu = t_03.puid
       CROSS JOIN PITEM t_02    
   WHERE
   t_01.rrelation_typeu = 'w8INy241VJFL2B'
   AND t_01.rsecondary_objectu = '2yLJkWqiVJFL2B'
) 
+ 
(  
   SELECT
       COUNT ( t_01.rsecondary_objectu ) AS selectExpr
   FROM
       PIMANRELATION t_01
       INNER JOIN PITEM t_02 ON t_01.rprimary_objectu = t_02.puid
       CROSS JOIN PITEMREVISION t_03
   WHERE
   t_01.rrelation_typeu = 'w8INy241VJFL2B'
   AND t_01.rsecondary_objectu = '2yLJkWqiVJFL2B'
   AND LNNVL(t_01.rprimary_objectu = t_03.puid)
)
from dual;

現在查詢的兩個部分都有一個相等條件，因此 Oracle 可以使用索引對兩者執行有效的嵌套循環連接。它仍然需要對查詢的兩個部分進行交叉連接，但與在同一查詢中進行兩次交叉連接相比，中間結果集的大小顯著減小。例如，如果PIMANRELATION有 1 個相關行並且PITEMREVISION兩者PITEM都有一百萬行，那麼如果將它們交叉連接在一起，您將獲得一萬億行。但是，如果您拆分查詢，那麼您最終只會得到一百萬行。

SQL Server 查詢優化器有一個規則可以轉換OR為UNION ALL: SelToIdxStrategy。沒有這方面的文件，我能找到的唯一參考是這個答案。但是，該規則將不適用於這種情況。相反，您會得到兩個只能通過嵌套循環連接實現的交叉連接。對於 中的每個相關行PIMANRELATION，SQL Server 將交叉連接到 中的所有行PITEM，然後交叉連接到 中的所有行PITEMREVISION，最後過濾掉之後的行。您可以輕鬆地最終過濾掉數万億行。

我有壞消息要告訴你。如果您確實無法更改查詢文本的任何部分，並且您需要該查詢能夠很好地執行，那麼 SQL Server 可能不是您的應用程序的正確平台。數據庫有不同的優勢和劣勢，您可能需要更改查詢以適應這些差異。

這是僅基於查詢結構的建議：

在開始對執行計劃、表統計資訊和直方圖、基數估計、隔離級別、記憶體設置和許多其他可能的問題原因和解決方法進行追逐之前，請考慮它**可能是應用程序中的一個錯誤，**它產生了詢問。

我的推理很簡單：如果它看起來像垃圾並且由 ORM 生成，那麼它很可能是垃圾。

我建議你檢查：

• 如果查詢在原始 Oracle 數據庫中執行完全相同，或者在目標是 SQL Server 數據庫時產生它的查詢不同（和 ORM/應用程序）會稍微或更多地改變它。
• 有哪些測試表明查詢與要應用的業務邏輯/要求一致。應用程序是否有這樣的測試，它們是否在（Oracle 和 SQL Server）環境中都成功通過了？

我的觀點是，在確定查詢沒有正確性問題之前追查性能問題是沒有意義的。

---

詳細地說，查詢沒有什麼意義。該OR條件 - 這是輔助 (PITEM和PITEMREVISION) 表的唯一連接過濾器 - 本質上引入了交叉連接（幸運的是，您已經在 Oracle 中啟用了一些優化功能，但在 SQL Server 中沒有這樣做。請參閱Joe Obbish 的答案詳細解釋發生了什麼）。

為了更清楚，請考慮以下與您的查詢等效的查詢：

WITH
 t_01 AS
 ( SELECT rprimary_objectu
   FROM PIMANRELATION 
   WHERE rrelation_typeu = 'w8INy241VJFL2B'
     AND rsecondary_objectu = '2yLJkWqiVJFL2B'
 ),
 count_items AS
 ( SELECT COUNT(*) AS a
   FROM t_01
        JOIN PITEM t_02
        ON t_01.rprimary_objectu = t_02.puid
 ),
 count_revisions AS 
 ( SELECT COUNT(*) AS b
   FROM t_01
        JOIN PITEMREVISION t_03
        ON t_01.rprimary_objectu = t_03.puid
 ),
 count_all_items AS
 ( SELECT COUNT(*) AS aa
   FROM PITEM
 ),
 count_all_revisions AS
 ( SELECT COUNT(*) AS bb
   FROM PITEMREVISION
 )
SELECT
   (a * bb) + (b * aa) - (a * b) AS selectExpr
FROM
   count_items, count_all_items,
   count_revisions, count_all_revisions ;

你明白為什麼上面說的意義不大嗎？請注意count_all_items和count_all_revisions計算如何計算這兩個表中的所有行。我看不到這背後的業務邏輯。

（上面當然會更有效，因為它不做任何交叉連接，而是分離表掃描並將計算轉移到主查詢中的簡單乘法。無論優化器變得多麼聰明，總是有限制他們可以提供的可能的轉換和優化。）

唯一可能使用這種奇怪計數的情況是，如果在查詢後立即檢查計數是否為0或>= 1- 某些 ORM 似乎更喜歡這種更有效的EXISTS方法。在這種情況下，計數是 5 還是 500 萬並不重要，因為目標是找出兩個表中的任何一個中是否存在相關行。

引用自：https://dba.stackexchange.com/questions/229124