Oracle 查詢執行第一次完成，但從第二次執行掛斷

我們有一個非常複雜的視圖，它只在第一次完成執行，但在第二次從同一個會話執行時掛起。

**複雜視圖 -**具有嵌套視圖、大量內連接和外連接、多個聯合等的視圖……但沒有鎖定語句，只有純讀取。

$ sqlplus sys/system as sysdba;
SQL> SELECT * FROM v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0  Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> alter session set current_schema = DB_2017_2018;

SQL> alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD';

SQL> select 1 from complex_view where company_id = 1 and invoice_month = 1 and invoice_year = 2018;
-- runs in 33 sec

SQL> select 1 from complex_view where company_id = 1 and invoice_month = 1 and invoice_year = 2018;
-- hangs up.. Ctrl + C after 3 minutes

SQL> select * from complex_view where company_id = 1 and invoice_month = 1 and invoice_year = 2018;
-- runs in 1m 30 sec

SQL> select * from complex_view where company_id = 1 and invoice_month = 1 and invoice_year = 2018;
-- hangs up.. Ctrl + C after 5 - 10 minutes

-- ALL THESE COMMANDS / QUERIES RUN IN SAME SESSION

我認為第二次執行完全相同的查詢是問題發生的時候。如果我只是更改任何選擇列或 where 條件，那麼它會第一次執行並從第二次掛斷（參見上面的命令）。

嫌疑人1

我懷疑的一件事可能是它與oracle的記憶體有關。因此，當我在每次查詢執行之前執行以下命令時，我沒有任何查詢掛起。

SQL> alter system flush shared_pool;
SQL> alter system flush buffer_cache;

---

嫌疑人 2（根本原因可能是嫌疑人 1）

一旦任何查詢掛起，即使我在另一個會話中執行相同的查詢，它仍然會掛起。因此，如果我終止執行掛起查詢的活動會話，那麼我只能第一次成功執行查詢，但在第二次執行時遇到同樣的問題。

SQL> ALTER SYSTEM KILL SESSION '12,256' IMMEDIATE;

---

AFAIK，我唯一可以確定的是，oraclebuffer_cache和shared_pool我們的版本存在嚴重問題。

表或簡單視圖不會出現此問題，僅針對此復雜視圖（未測試其他復雜視圖）。此外，我們僅在過去 2 - 3 週內遇到過這種奇怪的問題。它託管在雲（AWS EC2）中，因此它可能與亞馬遜的幽靈/崩潰更新檔有關，因為這是過去 2-3 週發生的變化（不相關？！）。或者可能是由於在該期間達到了某些數據庫數據門檻值水平。

準備提供任何其他分析或指標

這在 11.2 上非常典型，因為有一個稱為“基數回饋”的功能。檢查慢執行的執行計劃，如果在執行計劃的註釋部分看到“基數回饋”，請嘗試設置以下參數：alter session set "_optimizer_use_feedback"=false;，然後再次執行測試。

與此相關的一些錯誤：

錯誤 12557401 - 基數回饋導致第二次執行的次優計劃（文件 ID 12557401.8）

錯誤 16837274 - 基數回饋產生糟糕的後續計劃（文件 ID 16837274.8）

錯誤 8608703 - 基數回饋創建的次優執行計劃（文件 ID 8608703.8）

錯誤 8521689 - 第二次執行 GROUP BY 查詢時的次優執行計劃（文件 ID 8521689.8）

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