前言
前幾天負責維護的一套核心系統的主機CPU，出現了一次小的CPU波動，（正常5%左右，問題時段最高到15%）因為是最核心的系統，所以要追查根本原因；跟著博主一起來看，是什么造成的這次CPU波動。
現象
出現問題時間并未引起關注，因為主機的性能非常強悍，這次微小的CPU波動也只是最高到15%的使用率，遠遠沒有到達預警值；但是因為是最為核心的MES系統，在日常點檢中巡查到這次波動，所以要排查根因；
1.首先要找到問題的表象，利用OEM查看問題時段的CPU走勢，確實有個微小的波峰，同時拉長時段看，同周同月同時段無相同波峰（排除schedule job）
PS：OEM真的是排查異常的好幫手！
2.查看OEM數據庫的性能主頁，可以看到明細的波峰，等待事件為direct path read,該等待事件最常見的是大報表SQL的；
3.為了排除是否定時任務等的干擾，需要查詢相應JOB和歸檔量規律
select * from dba_jobs;
select * from dba_schedule_jobs;
#同時查詢各個時段的歸檔量，看問題時段是否有規律性增長
---- Show the Number of Redo Log Switches Per Hour
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SELECT to_char(first_time, 'yyyy - mm - dd') aday,
to_char(first_time, 'hh24') hour,
count(*) total
FROM v$log_history
WHERE thread#=&EnterThreadId
GROUP BY to_char(first_time, 'yyyy - mm - dd'),
to_char(first_time, 'hh24')
ORDER BY to_char(first_time, 'yyyy - mm - dd'),
to_char(first_time, 'hh24') asc
/
4.以上都排除后，加上問題指向異常sql，拉取問題時段的AWR報表 看看能不能找到什么端倪
通過AWR輕松找到一個問題sql，該sql執行了10次 每次居然需要2400多秒，明顯不太正常。
5.定位到這個異常sql，進一步來追查，這個sql 是不是發生了執行計劃偏移
這里就想到了使用sqlhc來查一下這個sql id具體狀況，使用辦法很簡單 執行sqlhc.sql 參數1 D，參數2：sql id
就可以得到一個壓縮包，大概有如下的報告，關鍵為第一個main.html 這里有關于這個sql的執行的詳細信息
更多 可以參考官方文檔SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1)
從這里看到有兩個執行計劃，其中一個不正常有2000多秒，而且可以看到first snapshot時間和CPU的異常波動時間 完全吻合。
這里就可以基本斷定，這次異常的CPU波動的直接原因是這個sql id執行計劃偏移造成的。
根因
直接原因找到了，但是根本原因是什么？也就是說是什么造成的這個sql的執行計劃變了。只有找到了根本原因才能從根本上解決這個問題。
觀察這個sql的執行計劃 可以在執行計劃的最后清楚的看到cardinality feedback used for this statement，一下就讓我想起了之前曾經遇到過的一個案例，幾乎一模一樣。
前文：好好的數據庫怎么突然跑不動了？
http://m.sunline.cc/db/1769620497277456384
何為基數反饋？
Cardinality Feedback是11gR2出現的新特性(ps:12C后改名為Statistics Feedback，統計反饋，本文還是以個人習慣稱為基數反饋 ），基數反饋是優化器自動改進對基數估計錯誤的重復查詢的計劃的能力。由于多種原因，優化器可能不正確地估算基數，例如缺少統計信息、不準確的統計信息或復雜的謂詞。基數反饋幫助優化器從錯誤計算中學習，以便使用更準確的基數估計生成更好的計劃。
基數反饋是如何工作
即使統計數據被盡可能準確地計算，估計的基數可能也是不準確的。在第一次執行 SQL 語句時，會生成一個執行計劃。在計劃優化期間，會注意到某些類型的估算，并監視生成的游標。執行完成后，計劃中的一些基數估算會與執行期間實際觀察到的基數進行比較。如果發現這些估算與實際基數存在顯著差異，則會存儲更正后的基數以供以后使用。下次執行查詢時，將再次對其進行優化（硬解析），而這次優化器將使用這些更正后的估算值來替代之前使用的原始估算值。基于更準確的統計數據可能會創建不同的計劃。
Oracle 能夠使用統計反饋重復地重新優化語句。這可能是必要的，因為基數差異可能取決于計劃的結構和形狀。因此，在第二次執行查詢時，使用統計反饋生成新計劃后，仍可能發現更多的基數估算與實際基數存在顯著偏差。在這種情況下，Oracle 可以在下次執行時再次重新優化。
但是，有一些保障措施可確保在少數執行后這種情況將穩定下來，因此您可能會在最初的幾次執行中看到計劃的變化，但最終將選擇出一個計劃，并用于所有后續的執行。
流程圖如下
官方建議什么情況下適合啟用基數反饋
- 沒有統計信息且未使用動態采樣表
- 表上有多個連接或分離的過濾謂詞，且沒有擴展統計信息
- 包含復雜運算符的謂詞，優化器無法準確計算選擇性估算值
在某些情況下，還有其他可用的技術來改善估算；例如，動態采樣或多列統計允許優化器更準確地估算連接謂詞的選擇性。在這些技術適用的情況下，Statistics Feedback將不被啟用。
然而，如果對于相關列的組合不存在多列統計信息，則優化器可以回退到使用Statistics Feedback。
如何關閉基數反饋
基數反饋有隱含參數_OPTIMIZER_USE_FEEDBACK控制，默認是開啟的 ，可以在session和system級別關閉
1.會話級別或者系統級別關閉基數反饋
alter session set "_OPTIMIZER_USE_FEEDBACK" = FALSE;
alter system set "_OPTIMIZER_USE_FEEDBACK" = FALSE;
2.sql級別加hint
select /*+ opt_param('_optimizer_use_feedback' 'false') */ ...為什么基數反饋后執行計劃反而變壞
參考Bug 16837274 - Cardinality feedback produces poor subsequent plan (Doc ID 16837274.8)
Description A suboptimal execution plan may be produced due to cardinality feedback for the object on the right side of NLJ .（nested loops join） Rediscovery Notes Bad plan due to cardinality feedback for the object on the right side of NLJ. Workaround Set "_optimizer_use_feedback"=false Note: This fix effectively fixes all of the cases fixed by Bug 13454409 and should be used instead of that fix.
處理辦法
如果sqlid被刷出內存，在次被加載后就有可能觸發基數反饋。 本次直接使用嘗試coe_xfr_sql_profile.sql ，但是只能帶出異常的sqlplan，沒有帶出正常的sql plan；這時候該如何處理？
如果一個sql的執行計劃有問題，但是不能動到原sql，可以利用生成一個加了hint 的執行計劃綁定到原有的sql中，這樣不會影響到原來的sql，具體要求和步驟如下：
coe_load_sql_baseline.sql 和 coe_load_sql_profile.sql 有很多應用場景。下面以一個典型例子說明如何強制優化器使用只能通過 Hint 才能得到的執行計劃。操作步驟
- 確保原始 SQL（不帶 Hint）和加了 Hint 的 SQL 都在共享池中（可以通過先執行一遍來實現）。
- 分別找出兩條 SQL 的 sql_id 和 plan_hash_value。
選擇使用 Baseline 還是 Profile：
- 想要完全保證計劃穩定性 → 使用 SQL Plan Baseline（推薦）
- 只想“引導”優化器但保留一定靈活性 → 使用 SQL Profile
參考文檔：
- Document 1524658.1 FAQ: SQL Plan Management (SPM) Frequently Asked Questions
3. 進入 sqlt/utl 目錄。
目錄下有兩個腳本可實現本文目標：
- coe_load_sql_baseline.sql （11g 及以上）
把加了 Hint 的 SQL 的執行計劃加載為原始 SQL 的自定義 SQL Plan Baseline。 - coe_load_sql_profile.sql （10g 及以上）
把加了 Hint 的 SQL 的執行計劃加載為原始 SQL 的自定義 SQL Profile。
4.運行腳本，提供原始 SQL 的 sql_id 以及加了 Hint 的 SQL 的 sql_id 和 plan_hash_value。
示例環境準備：
SQL> -- 創建索引用于演示
SQL> create index i_emp_ename on emp(ename);
Index created.
SQL> -- 收集統計信息
SQL> exec dbms_stats.gather_table_stats(ownname=>'USER_ID',tabname=>'EMP')
PL/SQL procedure successfully completed.步驟 1：執行原始 SQL（不帶 Hint）
SQL> select ename from emp where ename='name';
Plan hash value: 3045807146
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 1 (0)|
|* 1 | INDEX RANGE SCAN| I_EMP_ENAME | 1 | 6 | 1 (0)|
---------------------------------------------------------------------這就是我們要改變執行計劃的原始語句。
步驟 2：執行加了 Hint 的 SQL
SQL> select /*+ FULL (EMP) */ ename from emp where ename='name';
Plan hash value: 2872589290
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)|
|* 1 | TABLE ACCESS FULL| EMP | 1 | 6 | 3 (0)|
---------------------------------------------------------------這就是我們想要的“完美計劃”。
步驟 3：查找兩條 SQL 的 sql_id 和 plan_hash_value
SQL> select sql_id, plan_hash_value, sql_text
from v$sql
where sql_text like '%emp%';
SQL_ID PLAN_HASH_VALUE SQL_TEXT
------------- ----------------- -------------------------------------------------
0vdqhcj6gaqnt 3924418374 select sql_id ,plan_hash_value, sql_text from v$sql ...
4f74t4ab7rd5y 2872589290 select /*+ FULL (EMP) */ ename from emp where ename='name'
329d885bxvrcr 3045807146 select ename from emp where ename='name'- 原始 SQL_ID：329d885bxvrcr
- 加 Hint SQL_ID：4f74t4ab7rd5y
- 目標計劃的 Plan Hash Value：2872589290
步驟 4：選擇方案固定計劃方案 A：使用 coe_load_sql_baseline.sql（推薦，強制使用指定計劃）
要求：
- 原始 SQL 必須在共享池或 AWR 中
- 加 Hint 的 SQL 必須在共享池中
以 DBA 權限用戶（如 SYSTEM）連接，不要用 SYS（SYS 模式下無法創建 staging 表，會報 ORA-19381）
SQL> @coe_load_sql_baseline.sql
Parameter 1:
ORIGINAL_SQL_ID (required)
Enter value for 1: 329d885bxvrcr
Parameter 2:
MODIFIED_SQL_ID (required)
Enter value for 2: 4f74t4ab7rd5y
PLAN_HASH_VALUE AVG_ET_SECS
-------------------- --------------------
2872589290 .003
Parameter 3:
PLAN_HASH_VALUE (required)
Enter value for 3: 2872589290
...
****************************************************************************
* Enter <User_Name> password to export staging table STGTAB_BASELINE_329d885bxvrcr
****************************************************************************
...
coe_load_sql_baseline completed.再次執行原始 SQL：
SQL> select ename from emp where ename='name';
Plan hash value: 2872589290
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 6 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- SQL plan baseline "329D885BXVRCR_4F74T4AB7RD5Y" used for this statement原始 SQL 已經成功使用我們指定的執行計劃，且創建了 SQL Plan Baseline。
方案 B：使用 coe_load_sql_profile.sql要求同上
SQL> @coe_load_sql_profile.sql
Parameter 1: ORIGINAL_SQL_ID → 329d885bxvrcr
Parameter 2: MODIFIED_SQL_ID → 4f74t4ab7rd5y
Parameter 3: PLAN_HASH_VALUE → 2872589290
...
coe_load_sql_profile completed.再次執行原始 SQL：
SQL> select ename from emp where ename='Name';
Plan hash value: 2872589290
...
Note
-----
- SQL profile "329D885BXVRCR_2872589290" used for this statement
如何將sql plan 刷出share pool步驟如下
1.Find ADDRESS and HASH_VALUE using SQL_ID
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '<SQL_ID>';
Example:
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID='XXXXXXXXXXX';
ADDRESS HASH_VALUE
---------------- ----------
000000085FD77CF0 808321886
2) Now purge the plan from Shared pool using DBMS_SHARED_POOL procedure
SQL> exec DBMS_SHARED_POOL.PURGE ('000000085FD77CF0, 808321886', 'C');
PL/SQL procedure successfully completed.
NOTE:
‘C’ (for cursor) or ‘S’ (for SQL)
3) Check the shared pool again after the purge successfully completes which should show no rows.
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID='XXXXXXXXXXX';
no rows selected參考文檔
Directing Plans with Baselines/Profiles Using coe_load_sql_baseline.sql / coe_load_sql_profile.sql (shipped with SQLT) (Doc ID 1400903.1)
Document 1524658.1 FAQ: SQL Plan Management (SPM) Frequently Asked Questions
Bug 16837274 - Cardinality feedback produces poor subsequent plan (Doc ID 16837274.8)
SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1)
Document 1359841.1 Plan Stability Features (Including SPM) Start Point
Document 271196.1 Automatic SQL Tuning - SQL Profiles
