一 問題描述

有次生產環境cpu使用率增高，ADDM報告提示某條sql比較耗費cpu：

提示：

在分析期間, 此 SQL 語句至少利用了 6 個不同的執行計劃

#查看該sql都有哪些執行計劃

SELECT?*?

FROM?table(DBMS_XPLAN.DISPLAY_AWR('sqlid值'));

我手動執行這個sql需要5秒。但是我用sql monitor查看發現它執行得超慢，5個多小時了，還沒執行完：

發現99%慢在一個表的全表掃描上，但是這個條件字段上是有索引的，它沒走索引，走的全表掃描：

我手動執行這個sql（5秒），看它的執行計劃這個表是走了索引的。

說明數據庫有時選擇了錯誤的執行計劃。

二 解決辦法

2.1 收集下這個表的統計信息

BEGINDBMS_STATS.GATHER_TABLE_STATS(ownname ????=> 'schema名稱', ????-- 表所屬的用戶（如 HR）tabname ????=> '表名', ?????-- 表名（如 EMPLOYEES）estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -- 自動估算采樣比例method_opt ?=> 'FOR ALL COLUMNS SIZE AUTO', ?????-- 自動選擇直方圖列cascade ????=> TRUE, ????????????-- 同時收集索引的統計信息（默認為 FALSE）degree ?????=> 8,no_invalidate => FALSE ??????????-- 立即使依賴的游標失效（可選）);END;/

#查看統計信息

select table_name,num_rows,blocks,last_analyzed from dba_tables where table_name in ('表名') and owner='schema名稱';

2.2 固化執行計劃

如果收集完表的統計信息，sql還是慢，則固化下執行計劃

2.2.1 上傳文件coe_xfr_sql_profile.sql

點擊這里下載該文件，然后用oracle用戶將其上傳至/home/oracle目錄下

2.2.2 執行coe_xfr_sql_profile.sql，并手動執行其生成的固化sql

# su - oracle

$ sqlplus / as sysdba @/home/oracle/coe_xfr_sql_profile.sql

Parameter 1:
SQL_ID (required)

Enter value for 1: 7arsymf6aatr3? ? ? >>>入參1：此處輸入需要固化的SQL_ID

PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
? ? ?2931187647?? ? ???.036 ? ? ?>>> 一般選擇AVG_ET_SECS最小版本,并記住PLAN_HASH_VALUE值：2931187647
? ? ?3394618928 ? ? ?42.585
? ? ? 894327090 ? ? 164.624

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 2931187647? ? >>>入參2：此處輸入上面AVG_ET_SECS最小耗費資源的PLAN_HASH_VALUE 值

Values passed:
~~~~~~~~~~~~~
SQL_ID ? ? ? ? : "7arsymf6aatr3"
PLAN_HASH_VALUE: "2931187647"

Execute coe_xfr_sql_profile_7arsymf6aatr3_2931187647.sql??? ? >>>>此處為oracle自動生成的SQL固化腳本
on TARGET system in order to create a custom SQL Profile
with plan 2931187647 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.
SQL> @coe_xfr_sql_profile_7arsymf6aatr3_2931187647? ? ? ? ? ? >>>>手動執行上面生成的sql腳本。
... ...
PL/SQL procedure successfully completed.

... ...
COE_XFR_SQL_PROFILE_7arsymf6aatr3_2931187647 completed? ? ?>>>>固化完成。

/*

#

select?*?from?dba_sql_profiles;

*/

2.3 查看下當前會話

看是否還有相關慢sql。收集該表的統計信息和固化執行計劃不會影響已經在執行的sql。

這些查詢sql如果執行了好幾個小時的話，問下業務能否kill。運行那么久沒有結果感覺查詢下去也沒啥意義了，但是還是要謹慎kill，需要問下業務能否kill。