四步搞定異常SQL
影響SQL執(zhí)行效率的因素主要包括以下幾點(diǎn)。
1)統(tǒng)計(jì)信息，具體如下
- 陳舊的統(tǒng)計(jì)信息
- 錯(cuò)誤的優(yōu)化器參數(shù)配置
- 數(shù)據(jù)庫(kù)遷移前后優(yōu)化器的改變
- 頻繁的數(shù)據(jù)量變化
2)SQL語(yǔ)句編寫(xiě)問(wèn)題
3)游標(biāo)共享問(wèn)題，具體如下
- 變量窺探
- 使用常量
4)資源爭(zhēng)用問(wèn)題，具體如下
- 硬件資源不足
- 鎖或latch等的爭(zhēng)用問(wèn)題
以上幾種原因最終都會(huì)導(dǎo)致主機(jī)CPU的使用率增加、主機(jī)I/O異常繁忙、語(yǔ)句執(zhí)行時(shí)間異常增加、數(shù)據(jù)庫(kù)整體性能下降、應(yīng)用超時(shí)等問(wèn)題。
01定位問(wèn)題SQL
進(jìn)行SQL優(yōu)化的第一步是定位問(wèn)題SQL。Oracle會(huì)在內(nèi)存中記錄每條SQL語(yǔ)句執(zhí)行所消耗的資源，再由專(zhuān)門(mén)的進(jìn)程(MMON)將這些統(tǒng)計(jì)指標(biāo)定期保存為AWR快照，筆者認(rèn)為，AWR的快照采集，是Oracle優(yōu)于其他數(shù)據(jù)庫(kù)的一大利器。
兩次快照內(nèi)統(tǒng)計(jì)值的差異可以反映快照時(shí)間段內(nèi)資源的消耗情況。Oracle可以通過(guò)指定的兩個(gè)快照來(lái)生成性能報(bào)告。執(zhí)行Oracle內(nèi)置的awrrpt腳本，按照提示逐步進(jìn)行即可生成AWR報(bào)告。AWR報(bào)告中有關(guān)于SQL的各項(xiàng)排名，如按SQL耗時(shí)、CPU消耗、I/O消耗、邏輯讀消耗、物理讀消耗、執(zhí)行次數(shù)、解析次數(shù)、共享內(nèi)存使用大小、子游標(biāo)使用量、Cluster等待等進(jìn)行的排名。
除了通過(guò)AWR查找異常SQL之外，我們還可以通過(guò)以下方式定位異常SQL。
- 主機(jī)資源異常，找出排行靠前的進(jìn)程(top process)，檢查執(zhí)行的SQL語(yǔ)句，查詢(xún)命令如下：
- <!--替換對(duì)應(yīng)的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è)務(wù)超時(shí)反饋，檢查對(duì)應(yīng)的應(yīng)用模塊正在執(zhí)行的SQL語(yǔ)句。
- 數(shù)據(jù)庫(kù)性能整體下降，檢查異常等待事件，查詢(xún)語(yǔ)句如下：
- <!--替換等待事件名稱(chēng)即可-->
- 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健康檢查
接下來(lái)介紹SQL調(diào)優(yōu)健康檢查(SQL Tuning Health Check，SQLHC)腳本的基礎(chǔ)知識(shí)，以及如何使用它來(lái)收集性能較差SQL的關(guān)鍵信息。SQLHC可以幫助我們專(zhuān)注于特定的SQL，并檢查基于成本的優(yōu)化器統(tǒng)計(jì)信息、對(duì)象元數(shù)據(jù)、配置參數(shù)和其他可能會(huì)影響性能的因素。與SQLT(SQLTXPLAIN)相比，SQLHC不需要在數(shù)據(jù)庫(kù)中提前配置腳本，只需要利用已執(zhí)行語(yǔ)句的SQL_ID生成報(bào)告即可。SQLHC適用于Oracle 10g及以上版本，同樣也支持RAC。
對(duì)于簡(jiǎn)單的SQL問(wèn)題，我們通過(guò)執(zhí)行計(jì)劃就能判斷其優(yōu)化方向;而對(duì)于復(fù)雜SQL問(wèn)題的診斷，則需要借助于更多信息，比如，數(shù)據(jù)庫(kù)版本信息、參數(shù)設(shè)置、表/索引/字段統(tǒng)計(jì)信息、統(tǒng)計(jì)信息變化情況、當(dāng)前和歷史執(zhí)行計(jì)劃、sql path/sql profile/sql plan baseline等。
好消息是SQLHC不必運(yùn)行多個(gè)腳本來(lái)收集數(shù)據(jù)，單個(gè)SQLHC就能收集所有的數(shù)據(jù)，并以HTML這種易于閱讀的格式顯示。
SQLHC的官方下載地址為：SQL Tuning Health-Check Script (SQLHC) (Doc ID：1366133.1)。
首先從MOS中下載腳本代碼并將其上傳到服務(wù)器，然后找到需要評(píng)估的SQL_ID，其可以來(lái)自于AWR、ASH報(bào)告或V$SQL視圖。
接下來(lái)，我們重點(diǎn)介紹SQLHC的使用方法，SQLHC主要包含如下三個(gè)文件。
- sqldx.sql：僅收集信息而不進(jìn)行任何修改操作，較為常用。
- sqlhc.sql：收集SQL相關(guān)數(shù)據(jù)字典信息。
- sqlhcxec.sql：執(zhí)行SQL，再收集信息，會(huì)產(chǎn)生變更操作。
要想執(zhí)行以上腳本，我們需要擁有DBA或訪問(wèn)數(shù)據(jù)字典視圖的權(quán)限。操作也非常簡(jiǎn)單，只需要上傳sqlhc.sql到數(shù)據(jù)庫(kù)服務(wù)器指定的目錄下執(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í)需要輸入以下兩個(gè)參數(shù)。
- Oracle Pack License (Tuning, Diagnostics or None) [T|D|N] (required)。
- T：調(diào)優(yōu)，較為常用。
- D：診斷。
- N：無(wú)。
- SQL_ID：需要診斷的SQL語(yǔ)句。
下面以SQL d18wwg2f3txc0為例，執(zhí)行完之后自動(dòng)打包生成一個(gè)壓縮文件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)容說(shuō)明如下。
- health_check：用于診斷，以及統(tǒng)計(jì)信息異常、Index異常、特殊參數(shù)設(shè)置等。
- diagnostics：內(nèi)容包括SQL文本、執(zhí)行計(jì)劃綁定情況、當(dāng)前和歷史SQL的相關(guān)信息、表格/列/索引/列柱狀圖詳細(xì)信息、系統(tǒng)參數(shù)、表格創(chuàng)建語(yǔ)句、SQL相關(guān)等待事件，等等。
- execution plan：SQL文本、當(dāng)前和歷史執(zhí)行計(jì)劃。
- sqldx：SQL綁定、直方圖、各對(duì)象的數(shù)據(jù)字典信息等。
- 10053跟蹤：SQL的執(zhí)行方式。
03SQL PROFILE
SQL profile是查詢(xún)中的輔助信息的集合，包括查詢(xún)中引用的所有表和列。SQL profile存儲(chǔ)在數(shù)據(jù)字典中，優(yōu)化器在優(yōu)化過(guò)程中使用這些信息來(lái)確定最優(yōu)的計(jì)劃。
1. 使用coe_xfr_sql_profile.sql
1)運(yùn)行分析腳本，命令如下：
- shell> sqlplus "/ as sysdba"
- SQL>@coe_xfr_sql_profile.sql cdwjdd67x27mh
- <!--后跟問(wèn)題SQL對(duì)應(yīng)的ID-->
2)輸入所希望的執(zhí)行計(jì)劃哈希值，命令如下：
- SQL> SQL_ID (required)
- PLAN_HASH_VALUE AVG_ET_SECS
- --------------- -----------
- 2979024279 .011
- 647855111 5.164
- 從以上輸出結(jié)果來(lái)看，2979024279對(duì)應(yīng)的執(zhí)行計(jì)劃響應(yīng)時(shí)間更快。
- 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)輸出結(jié)果如下：
- SQL>@coe_xfr_sql_profile_cdwjdd67x27mh_2979024279.sql
4)檢查profile情況。通過(guò)查詢(xún)dba_sql_profiles視圖，查看具體的固化情況，命令如下：
- SQL> select name,SQL_TEXT,status from dba_sql_profiles;
5)刪除profile。帶入dba_sql_profiles中對(duì)應(yīng)的profile名進(jìn)行刪除，命令如下：
- SQL> exec dbms_sqltune.drop_sql_profile('coe_f4sgavkagjb1q_2593387201');
2. 使用SQL調(diào)優(yōu)建議工具
1)運(yùn)行SQL調(diào)優(yōu)建議工具(SQL Tuning Advisor)。帶入問(wèn)題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)容，查詢(xún)語(yǔ)句如下：
- 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ù)庫(kù)同時(shí)給出了創(chuàng)建索引和SQL profile兩個(gè)建議，那么在某些情況下，只要接受SQL profile即可，某些情況下則需要同時(shí)采納創(chuàng)建索引和接受SQL porfile兩個(gè)建議。因?yàn)閯?chuàng)建索引之后，數(shù)據(jù)庫(kù)可能需要通過(guò)SQL profile的幫助才能選擇新的索引。
3)查看SQL profile。數(shù)據(jù)字典視圖DBA_SQL_PROFILES可用于展示數(shù)據(jù)庫(kù)中存儲(chǔ)的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計(jì)劃管理
Oracle 11g R1引進(jìn)了SQL Plan Management(SPM，SQL計(jì)劃管理)，SPM是一種預(yù)防性機(jī)制，使優(yōu)化程序可以自動(dòng)管理執(zhí)行計(jì)劃，從而確保數(shù)據(jù)庫(kù)使用的是已知的或經(jīng)過(guò)驗(yàn)證的最優(yōu)計(jì)劃。當(dāng)系統(tǒng)開(kāi)啟自動(dòng)SQL Plan Baseline(SQL計(jì)劃基線(xiàn))捕獲時(shí)，CBO會(huì)記錄會(huì)話(huà)內(nèi)執(zhí)行的任意SQL，并把SQL的相關(guān)信息存儲(chǔ)為SQL計(jì)劃基線(xiàn)。第一次執(zhí)行的語(yǔ)句，由于沒(méi)有基礎(chǔ)數(shù)據(jù)，因此會(huì)被當(dāng)成最優(yōu)的執(zhí)行計(jì)劃。第二次執(zhí)行時(shí)，CBO會(huì)與存儲(chǔ)在SQL計(jì)劃基線(xiàn)中的計(jì)劃進(jìn)行比較，如果新執(zhí)行計(jì)劃的性能有所改善，那么SPM會(huì)把新的執(zhí)行計(jì)劃標(biāo)記為該語(yǔ)句最優(yōu)的執(zhí)行計(jì)劃。默認(rèn)情況下，CBO會(huì)使用SQL計(jì)劃基線(xiàn)中最優(yōu)的執(zhí)行計(jì)劃。而對(duì)于異常SQL自動(dòng)捕獲，則需要設(shè)置參數(shù)optimizer_capture_sql_plan_baselines的值為true，默認(rèn)是false，命令如下：
- SQL> alter system set optimizer_capture_sql_plan_baselines=true scope=spfile;
接下來(lái)將為大家演示將SQL執(zhí)行計(jì)劃手動(dòng)加載到SQL計(jì)劃基線(xiàn)中的優(yōu)化案例。
1)執(zhí)行SQL語(yǔ)句，命令如下：
- 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 |
查找對(duì)應(yīng)的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語(yǔ)句加載至SQL計(jì)劃基線(xiàn)中，命令如下：
- 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視圖以確認(rèn)情況，命令如下：
- 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語(yǔ)句中加入HINT改變執(zhí)行計(jì)劃，強(qiáng)制全表掃描，命令如下：
- 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)查找對(duì)應(yīng)的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í)行計(jì)劃加載到原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視圖以確認(rèn)情況，命令如下：
- 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)刪除第一個(gè)SQL 執(zhí)行計(jì)劃，命令如下：
- 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語(yǔ)句，命令如下：
- 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í)行計(jì)劃中，SQL計(jì)劃基線(xiàn)表明以上SQL已經(jīng)開(kāi)始按照我們想要的方式在執(zhí)行。
持續(xù)運(yùn)行的系統(tǒng)和數(shù)據(jù)的不斷變化可能會(huì)影響某些SQL的執(zhí)行計(jì)劃，從而導(dǎo)致整體性能的下降，使用SQL計(jì)劃管理有助于最大程度地防止由于執(zhí)行計(jì)劃更改而導(dǎo)致的性能下降。對(duì)于一些特殊的SQL，我們也可以使用SQL計(jì)劃管理的特性，隨時(shí)調(diào)整執(zhí)行計(jì)劃。
