四步搞定異常SQL
影響SQL執(zhí)行效率的因素主要包括以下幾點。
1)統(tǒng)計信息，具體如下
- 陳舊的統(tǒng)計信息
- 錯誤的優(yōu)化器參數(shù)配置
- 數(shù)據(jù)庫遷移前后優(yōu)化器的改變
- 頻繁的數(shù)據(jù)量變化
2)SQL語句編寫問題
3)游標共享問題，具體如下
- 變量窺探
- 使用常量
4)資源爭用問題，具體如下
- 硬件資源不足
- 鎖或latch等的爭用問題
以上幾種原因最終都會導致主機CPU的使用率增加、主機I/O異常繁忙、語句執(zhí)行時間異常增加、數(shù)據(jù)庫整體性能下降、應用超時等問題。
01定位問題SQL
進行SQL優(yōu)化的第一步是定位問題SQL。Oracle會在內(nèi)存中記錄每條SQL語句執(zhí)行所消耗的資源，再由專門的進程(MMON)將這些統(tǒng)計指標定期保存為AWR快照，筆者認為，AWR的快照采集，是Oracle優(yōu)于其他數(shù)據(jù)庫的一大利器。
兩次快照內(nèi)統(tǒng)計值的差異可以反映快照時間段內(nèi)資源的消耗情況。Oracle可以通過指定的兩個快照來生成性能報告。執(zhí)行Oracle內(nèi)置的awrrpt腳本，按照提示逐步進行即可生成AWR報告。AWR報告中有關于SQL的各項排名，如按SQL耗時、CPU消耗、I/O消耗、邏輯讀消耗、物理讀消耗、執(zhí)行次數(shù)、解析次數(shù)、共享內(nèi)存使用大小、子游標使用量、Cluster等待等進行的排名。
除了通過AWR查找異常SQL之外，我們還可以通過以下方式定位異常SQL。
- 主機資源異常，找出排行靠前的進程(top process)，檢查執(zhí)行的SQL語句，查詢命令如下：
- <!--替換對應的SPID即可 -->
- SQL> select p.inst_id,
- p.spid,
- a.sid,
- a.serial#,
- a.sql_id,
- a.event,
- a.status,
- a.program,
- a.machine
- from gv$session a, gv$process p
- where a.inst_id = p.inst_id
- and a.paddr = p.addr
- and p.spid =15883;
- 業(yè)務超時反饋，檢查對應的應用模塊正在執(zhí)行的SQL語句。
- 數(shù)據(jù)庫性能整體下降，檢查異常等待事件，查詢語句如下：
- <!--替換等待事件名稱即可-->
- SQL> select a.inst_id,
- a.process,
- a.sid,
- a.serial#,
- a.sql_id,
- a.event,
- a.status,
- a.program,
- a.machine
- from gv$session a where event = 'db file scattered read';
02SQL健康檢查
接下來介紹SQL調(diào)優(yōu)健康檢查(SQL Tuning Health Check，SQLHC)腳本的基礎知識，以及如何使用它來收集性能較差SQL的關鍵信息。SQLHC可以幫助我們專注于特定的SQL，并檢查基于成本的優(yōu)化器統(tǒng)計信息、對象元數(shù)據(jù)、配置參數(shù)和其他可能會影響性能的因素。與SQLT(SQLTXPLAIN)相比，SQLHC不需要在數(shù)據(jù)庫中提前配置腳本，只需要利用已執(zhí)行語句的SQL_ID生成報告即可。SQLHC適用于Oracle 10g及以上版本，同樣也支持RAC。
對于簡單的SQL問題，我們通過執(zhí)行計劃就能判斷其優(yōu)化方向;而對于復雜SQL問題的診斷，則需要借助于更多信息，比如，數(shù)據(jù)庫版本信息、參數(shù)設置、表/索引/字段統(tǒng)計信息、統(tǒng)計信息變化情況、當前和歷史執(zhí)行計劃、sql path/sql profile/sql plan baseline等。
好消息是SQLHC不必運行多個腳本來收集數(shù)據(jù)，單個SQLHC就能收集所有的數(shù)據(jù)，并以HTML這種易于閱讀的格式顯示。
SQLHC的官方下載地址為：SQL Tuning Health-Check Script (SQLHC) (Doc ID：1366133.1)。
首先從MOS中下載腳本代碼并將其上傳到服務器，然后找到需要評估的SQL_ID，其可以來自于AWR、ASH報告或V$SQL視圖。
接下來，我們重點介紹SQLHC的使用方法，SQLHC主要包含如下三個文件。
- sqldx.sql：僅收集信息而不進行任何修改操作，較為常用。
- sqlhc.sql：收集SQL相關數(shù)據(jù)字典信息。
- sqlhcxec.sql：執(zhí)行SQL，再收集信息，會產(chǎn)生變更操作。
要想執(zhí)行以上腳本，我們需要擁有DBA或訪問數(shù)據(jù)字典視圖的權限。操作也非常簡單，只需要上傳sqlhc.sql到數(shù)據(jù)庫服務器指定的目錄下執(zhí)行即可，具體方法如下：
- shell> unzip sqlhc.zip
- Archive: sqlhc.zip
- creating: sqlhc/
- inflating: sqlhc/sqlhc.sql
- inflating: sqlhc/sqldx.sql
- inflating: sqlhc/sqlhcxec.sql
- shell> cd sqlhc
- shell> ls -tlr
- total 2
- -rw-r--r-- 1 mfggprd dba 48747 Nov 11 2013 sqldx.sql
- -rw-r--r-- 1 mfggprd dba 288298 Apr 16 2014 sqlhc.sql
- -rw-r--r-- 1 mfggprd dba 292838 Apr 16 2014 sqlhcxec.sql
- shell> sqlplus / as sysdba
- SQL> @sqlhc.sql T d18wwg2f3txc0
執(zhí)行時需要輸入以下兩個參數(shù)。
- Oracle Pack License (Tuning, Diagnostics or None) [T|D|N] (required)。
- T：調(diào)優(yōu)，較為常用。
- D：診斷。
- N：無。
- SQL_ID：需要診斷的SQL語句。
下面以SQL d18wwg2f3txc0為例，執(zhí)行完之后自動打包生成一個壓縮文件sqlhc_ 20200303_1555_d18wwg2f3txc0.zip，生成的內(nèi)容包括health_check、diagnostics、execution plan、sql_detail、10053 trace、sqldx、SQL monitor(可選)，如圖1所示。
圖1 SQLHC.SQL執(zhí)行后生成的文件
圖1中的部分內(nèi)容說明如下。
- health_check：用于診斷，以及統(tǒng)計信息異常、Index異常、特殊參數(shù)設置等。
- diagnostics：內(nèi)容包括SQL文本、執(zhí)行計劃綁定情況、當前和歷史SQL的相關信息、表格/列/索引/列柱狀圖詳細信息、系統(tǒng)參數(shù)、表格創(chuàng)建語句、SQL相關等待事件，等等。
- execution plan：SQL文本、當前和歷史執(zhí)行計劃。
- sqldx：SQL綁定、直方圖、各對象的數(shù)據(jù)字典信息等。
- 10053跟蹤：SQL的執(zhí)行方式。
03SQL PROFILE
SQL profile是查詢中的輔助信息的集合，包括查詢中引用的所有表和列。SQL profile存儲在數(shù)據(jù)字典中，優(yōu)化器在優(yōu)化過程中使用這些信息來確定最優(yōu)的計劃。
1. 使用coe_xfr_sql_profile.sql
1)運行分析腳本，命令如下：
- shell> sqlplus "/ as sysdba"
- SQL>@coe_xfr_sql_profile.sql cdwjdd67x27mh
- <!--后跟問題SQL對應的ID-->
2)輸入所希望的執(zhí)行計劃哈希值，命令如下：
- SQL> SQL_ID (required)
- PLAN_HASH_VALUE AVG_ET_SECS
- --------------- -----------
- 2979024279 .011
- 647855111 5.164
- 從以上輸出結果來看，2979024279對應的執(zhí)行計劃響應時間更快。
- Parameter 2:
- PLAN_HASH_VALUE (required)
- Enter value for 2: 2979024279
- Values passed:
- ~~~~~~~~~~~~~
- SQL_ID : "cdwjdd67x27mh"
- PLAN_HASH_VALUE: "2979024279"
- Execute coe_xfr_sql_profile_cdwjdd67x27mh_2979024279.sql
3)輸出結果如下：
- SQL>@coe_xfr_sql_profile_cdwjdd67x27mh_2979024279.sql
4)檢查profile情況。通過查詢dba_sql_profiles視圖，查看具體的固化情況，命令如下：
- SQL> select name,SQL_TEXT,status from dba_sql_profiles;
5)刪除profile。帶入dba_sql_profiles中對應的profile名進行刪除，命令如下：
- SQL> exec dbms_sqltune.drop_sql_profile('coe_f4sgavkagjb1q_2593387201');
2. 使用SQL調(diào)優(yōu)建議工具
1)運行SQL調(diào)優(yōu)建議工具(SQL Tuning Advisor)。帶入問題SQL_ID，命令如下：
- SQL> var tuning_task varchar2(100);
- SQL> DECLARE
- 2 l_sql_id v$session.prev_sql_id%TYPE;
- 3 l_tuning_task VARCHAR2(30);
- 4 BEGIN
- 5 l_sql_id:='gg859wbj3hkfq';
- 6 l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);
- 7 :tuning_task:=l_tuning_task;
- 8 dbms_sqltune.execute_tuning_task(l_tuning_task);
- 9 dbms_output.put_line(l_tuning_task);
- 10 END;
- 11 /
- PL/SQL procedure successfully completed.
- SQL> SQL> print tuning_task;
- TUNING_TASK
- --------------
- TASK_8233
查看建議內(nèi)容，查詢語句如下：
- SQL> SELECT dbms_sqltune.report_tuning_task('TASK_8233') FROM dual;
2)接受SQL profile。根據(jù)SQL調(diào)優(yōu)建議工具提供的建議，接受SQL profile，命令如下：
- SQL> exec dbms_sqltune.accept_sql_profile(task_name => 'TASK_8193',replace => TRUE,
- force_match=>true);
如果數(shù)據(jù)庫同時給出了創(chuàng)建索引和SQL profile兩個建議，那么在某些情況下，只要接受SQL profile即可，某些情況下則需要同時采納創(chuàng)建索引和接受SQL porfile兩個建議。因為創(chuàng)建索引之后，數(shù)據(jù)庫可能需要通過SQL profile的幫助才能選擇新的索引。
3)查看SQL profile。數(shù)據(jù)字典視圖DBA_SQL_PROFILES可用于展示數(shù)據(jù)庫中存儲的SQL profile，命令如下：
- SQL> SELECT NAME, SQL_TEXT, CATEGORY, STATUS FROM DBA_SQL_PROFILES;
4)刪除SQL profile，命令如下：
- SQL> BEGIN
- DBMS_SQLTUNE.DROP_SQL_PROFILE (
- name => 'sql_profile'
- );
- END;
- /
04SQL計劃管理
Oracle 11g R1引進了SQL Plan Management(SPM，SQL計劃管理)，SPM是一種預防性機制，使優(yōu)化程序可以自動管理執(zhí)行計劃，從而確保數(shù)據(jù)庫使用的是已知的或經(jīng)過驗證的最優(yōu)計劃。當系統(tǒng)開啟自動SQL Plan Baseline(SQL計劃基線)捕獲時，CBO會記錄會話內(nèi)執(zhí)行的任意SQL，并把SQL的相關信息存儲為SQL計劃基線。第一次執(zhí)行的語句，由于沒有基礎數(shù)據(jù)，因此會被當成最優(yōu)的執(zhí)行計劃。第二次執(zhí)行時，CBO會與存儲在SQL計劃基線中的計劃進行比較，如果新執(zhí)行計劃的性能有所改善，那么SPM會把新的執(zhí)行計劃標記為該語句最優(yōu)的執(zhí)行計劃。默認情況下，CBO會使用SQL計劃基線中最優(yōu)的執(zhí)行計劃。而對于異常SQL自動捕獲，則需要設置參數(shù)optimizer_capture_sql_plan_baselines的值為true，默認是false，命令如下：
- SQL> alter system set optimizer_capture_sql_plan_baselines=true scope=spfile;
接下來將為大家演示將SQL執(zhí)行計劃手動加載到SQL計劃基線中的優(yōu)化案例。
1)執(zhí)行SQL語句，命令如下：
- SQL> set autotrace on
- SQL> set line 150 pagesize 0
- SQL> select * from scott.dept where deptno=10;
- 10 YDFD NEW YORK
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 2852011669
- ---------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 19 | 1 (0)| 00:00:01 |
- | 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 19 | 1 (0)| 00:00:01 |
- |* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
查找對應的SQL_ID和PLAN HASH VALUE，命令如下：
- SQL> select sql_id,plan_hash_value from v$sql where sql_text like '%scott.dept where
- deptno = 10%';
- SQL_ID PLAN_HASH_VALUE
- ------------- ---------------
- fxchug4tpuqcj 2852011669
2)將以上SQL語句加載至SQL計劃基線中，命令如下：
- SQL> var n number
- SQL> begin
- 2 :n:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'fxchug4tpuqcj', plan_hash_
- value=>2852011669, fixed =>'NO', enabled=>'YES');
- 3 end;
- 4 /
- PL/SQL procedure successfully completed.
3)查看DBA_SQL_PLAN_BASELINES視圖以確認情況，命令如下：
- SQL> select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, SQL_TEXT
- from dba_SQL_PLAN_BASELINES
- where ACCEPTED = 'YES'
- order by LAST_MODIFIED;
- SQL_HANDLE PLAN_NAME ENABLED ACCEPTED SQL_TEXT
- -------------------- ------------------------------ ------- --------- --------
- SQL_59f9d6822a74ea01 SQL_PLAN_5myfqh8p79uh10348d329 YES YES <CLOB>
4)在以上的SELECT語句中加入HINT改變執(zhí)行計劃，強制全表掃描，命令如下：
- SQL> select /*+ full(dept) */ *
- from scott.dept
- where deptno = 10;
- 10 YDFD NEW YORK
- Execution Plan
- --------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 19 | 3 (0)| 00:00:01 |
- |* 1 | TABLE ACCESS FULL| DEPT | 1 | 19 | 3 (0)| 00:00:01 |
- --------------------------------------------------------------------------
5)查找對應的SQL_ID和PLAN HASH VALUE，命令如下：
- SQL> select sql_id, plan_hash_value
- from v$sql
- where sql_text like '%select /*+ full(dept) */%';
- SQL_ID PLAN_HASH_VALUE
- ------------- ---------------
- fgb5t3n5w5btx 3383998547
6)將加有HINT的執(zhí)行計劃加載到原SQL中的SPM中，命令如下：
- SQL> var n number
- SQL> begin
- 2 :n:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'fgb5t3n5w5btx', plan_hash_
- value=>3383998547, fixed =>'NO', enabled=>'YES');
- 3 end;
- 4 /
7)查看DBA_SQL_PLAN_BASELINES視圖以確認情況，命令如下：
- SQL> select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, SQL_TEXT
- from dba_SQL_PLAN_BASELINES
- where ACCEPTED = 'YES'
- order by LAST_MODIFIED;
DBA_SQL_PLAN_BASELINES的視圖信息如圖2所示。
圖2 DBA_SQL_PLAN_BASELINES視圖信息
8)刪除第一個SQL 執(zhí)行計劃，命令如下：
- SQL> variable n number ;
- SQL> exec :n:=dbms_spm.DROP_SQL_PLAN_BASELINE(SQL_HANDLE=> 'SQL_59f9d6822a74ea01',
- PLAN_NAME=> 'SQL_PLAN_5myfqh8p79uh10348d329');
- PL/SQL procedure successfully completed.
9)重新執(zhí)行SQL語句，命令如下：
- SQL> set autotrace on
- SQL> set line 150 pagesize 0
- SQL> select * from scott.dept where deptno=10;
- 10 YDFD NEW YORK
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 3383998547
- --------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 19 | 3 (0)| 00:00:01 |
- |* 1 | TABLE ACCESS FULL| DEPT | 1 | 19 | 3 (0)| 00:00:01 |
- --------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter("DEPTNO"=10)
- Note
- -----
- - SQL plan baseline "SQL_PLAN_5myfqh8p79uh10e23be79" used for this statement
執(zhí)行計劃中，SQL計劃基線表明以上SQL已經(jīng)開始按照我們想要的方式在執(zhí)行。
持續(xù)運行的系統(tǒng)和數(shù)據(jù)的不斷變化可能會影響某些SQL的執(zhí)行計劃，從而導致整體性能的下降，使用SQL計劃管理有助于最大程度地防止由于執(zhí)行計劃更改而導致的性能下降。對于一些特殊的SQL，我們也可以使用SQL計劃管理的特性，隨時調(diào)整執(zhí)行計劃。
