自拍偷在线精品自拍偷,亚洲欧美中文日韩v在线观看不卡

四步搞定異常SQL

作者：葉樺等
數(shù)據(jù)庫 SQL Server
本文介紹如何查找需要進行優(yōu)化的異常SQL并進行處理。

 影響SQL執(zhí)行效率的因素主要包括以下幾點。

[[413035]]

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報告中有關(guān)于SQL的各項排名，如按SQL耗時、CPU消耗、I/O消耗、邏輯讀消耗、物理讀消耗、執(zhí)行次數(shù)、解析次數(shù)、共享內(nèi)存使用大小、子游標使用量、Cluster等待等進行的排名。

除了通過AWR查找異常SQL之外，我們還可以通過以下方式定位異常SQL。

  • 主機資源異常，找出排行靠前的進程(top process)，檢查執(zhí)行的SQL語句，查詢命令如下：

  1. <!--替換對應的SPID即可 --> 
  2. SQL> select p.inst_id, 
  3.        p.spid, 
  4.        a.sid, 
  5.        a.serial#, 
  6.        a.sql_id, 
  7.        a.event, 
  8.        a.status, 
  9.        a.program, 
  10.        a.machine 
  11.   from gv$session a, gv$process p 
  12.  where a.inst_id = p.inst_id 
  13.    and a.paddr = p.addr 
  14.    and p.spid =15883; 
  • 業(yè)務超時反饋，檢查對應的應用模塊正在執(zhí)行的SQL語句。
  • 數(shù)據(jù)庫性能整體下降，檢查異常等待事件，查詢語句如下：

  1. <!--替換等待事件名稱即可--> 
  2. SQL> select a.inst_id, 
  3.        a.process, 
  4.        a.sid, 
  5.        a.serial#, 
  6.        a.sql_id, 
  7.        a.event, 
  8.        a.status, 
  9.        a.program, 
  10.        a.machine 
  11.   from gv$session a where event = 'db file scattered read'; 

02SQL健康檢查

接下來介紹SQL調(diào)優(yōu)健康檢查(SQL Tuning Health Check，SQLHC)腳本的基礎(chǔ)知識，以及如何使用它來收集性能較差SQL的關(guān)鍵信息。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相關(guān)數(shù)據(jù)字典信息。
  • sqlhcxec.sql：執(zhí)行SQL，再收集信息，會產(chǎn)生變更操作。

要想執(zhí)行以上腳本，我們需要擁有DBA或訪問數(shù)據(jù)字典視圖的權(quán)限。操作也非常簡單，只需要上傳sqlhc.sql到數(shù)據(jù)庫服務器指定的目錄下執(zhí)行即可，具體方法如下：

 

  1. shell> unzip sqlhc.zip 
  2. Archive: sqlhc.zip 
  3.  creating: sqlhc/ 
  4.  inflating: sqlhc/sqlhc.sql 
  5.  inflating: sqlhc/sqldx.sql 
  6.  inflating: sqlhc/sqlhcxec.sql 
  7. shell> cd sqlhc 
  8. shell> ls -tlr 
  9. total 2 
  10. -rw-r--r-- 1 mfggprd dba 48747 Nov 11 2013 sqldx.sql 
  11. -rw-r--r-- 1 mfggprd dba 288298 Apr 16 2014 sqlhc.sql 
  12. -rw-r--r-- 1 mfggprd dba 292838 Apr 16 2014 sqlhcxec.sql 
  13. shell> sqlplus / as sysdba 
  14. 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的相關(guān)信息、表格/列/索引/列柱狀圖詳細信息、系統(tǒng)參數(shù)、表格創(chuàng)建語句、SQL相關(guān)等待事件，等等。
  • 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)運行分析腳本，命令如下：

 

  1. shell> sqlplus "/ as sysdba" 
  2. SQL>@coe_xfr_sql_profile.sql cdwjdd67x27mh  
  3. <!--后跟問題SQL對應的ID--> 

2)輸入所希望的執(zhí)行計劃哈希值，命令如下：

 

  1. SQL> SQL_ID (required) 
  2. PLAN_HASH_VALUE AVG_ET_SECS 
  3. --------------- ----------- 
  4.     2979024279        .011 
  5.      647855111       5.164 
  6. 從以上輸出結(jié)果來看，2979024279對應的執(zhí)行計劃響應時間更快。 
  7. Parameter 2: 
  8. PLAN_HASH_VALUE (required) 
  9. Enter value for 2: 2979024279  
  10. Values passed: 
  11. ~~~~~~~~~~~~~ 
  12. SQL_ID         : "cdwjdd67x27mh" 
  13. PLAN_HASH_VALUE: "2979024279" 
  14. Execute coe_xfr_sql_profile_cdwjdd67x27mh_2979024279.sql 

3)輸出結(jié)果如下：

 

  1. SQL>@coe_xfr_sql_profile_cdwjdd67x27mh_2979024279.sql 

4)檢查profile情況。通過查詢dba_sql_profiles視圖，查看具體的固化情況，命令如下：

 

  1. SQL>  select name,SQL_TEXT,status from dba_sql_profiles; 

5)刪除profile。帶入dba_sql_profiles中對應的profile名進行刪除，命令如下：

 

  1. 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，命令如下：

 

  1. SQL> var tuning_task varchar2(100);   
  2. SQL> DECLARE   
  3.   2    l_sql_id v$session.prev_sql_id%TYPE;   
  4.   3    l_tuning_task VARCHAR2(30);   
  5.   4  BEGIN   
  6.   5    l_sql_id:='gg859wbj3hkfq';   
  7.   6    l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);   
  8.   7    :tuning_task:=l_tuning_task;   
  9.   8    dbms_sqltune.execute_tuning_task(l_tuning_task);   
  10.   9    dbms_output.put_line(l_tuning_task);   
  11.  10  END;   
  12.  11  /  
  13. PL/SQL procedure successfully completed. 
  14. SQL> SQL> print tuning_task; 
  15. TUNING_TASK 
  16. -------------- 
  17. TASK_8233 

查看建議內(nèi)容，查詢語句如下：

 

  1. SQL> SELECT dbms_sqltune.report_tuning_task('TASK_8233') FROM dual; 

2)接受SQL profile。根據(jù)SQL調(diào)優(yōu)建議工具提供的建議，接受SQL profile，命令如下：

 

  1. SQL> exec dbms_sqltune.accept_sql_profile(task_name => 'TASK_8193',replace => TRUE, 
  2.     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，命令如下：

 

  1. SQL> SELECT NAME, SQL_TEXT, CATEGORY, STATUS FROM   DBA_SQL_PROFILES; 

4)刪除SQL profile，命令如下：

 

  1. SQL> BEGIN 
  2.   DBMS_SQLTUNE.DROP_SQL_PROFILE (  
  3.     name => 'sql_profile'  
  4. ); 
  5. END; 
  6. / 

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的相關(guān)信息存儲為SQL計劃基線。第一次執(zhí)行的語句，由于沒有基礎(chǔ)數(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，命令如下：

 

  1. SQL> alter system set optimizer_capture_sql_plan_baselines=true scope=spfile; 

接下來將為大家演示將SQL執(zhí)行計劃手動加載到SQL計劃基線中的優(yōu)化案例。

1)執(zhí)行SQL語句，命令如下：

 

  1. SQL> set autotrace on 
  2. SQL> set line 150 pagesize 0 
  3. SQL>  select * from scott.dept where  deptno=10; 
  4.         10 YDFD           NEW YORK 
  5.  
  6.  
  7. Execution Plan 
  8. ---------------------------------------------------------- 
  9. Plan hash value: 2852011669 
  10. --------------------------------------------------------------------------------- 
  11. | Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     | 
  12. --------------------------------------------------------------------------------- 
  13. |   0 | SELECT STATEMENT      |         |     1 |    19 |     1   (0)| 00:00:01 | 
  14. |   1 |  TABLE ACCESS BY INDEX ROWID| DEPT |  1 |    19 |     1   (0)| 00:00:01 | 
  15. |*  2 |   INDEX UNIQUE SCAN   | PK_DEPT |     1 |       |     0   (0)| 00:00:01 | 

查找對應的SQL_ID和PLAN HASH VALUE，命令如下：

 

  1. SQL> select sql_id,plan_hash_value from v$sql where sql_text like '%scott.dept where  
  2.     deptno = 10%'; 
  3. SQL_ID        PLAN_HASH_VALUE 
  4. ------------- --------------- 
  5. fxchug4tpuqcj      2852011669 

2)將以上SQL語句加載至SQL計劃基線中，命令如下：

 

  1. SQL> var n number 
  2. SQL> begin 
  3.   2  :n:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'fxchug4tpuqcj', plan_hash_ 
  4.       value=>2852011669, fixed =>'NO', enabled=>'YES'); 
  5.   3  end; 
  6.   4  / 
  7. PL/SQL procedure successfully completed. 

3)查看DBA_SQL_PLAN_BASELINES視圖以確認情況，命令如下：

 

  1. SQL> select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, SQL_TEXT 
  2.   from dba_SQL_PLAN_BASELINES 
  3.  where ACCEPTED = 'YES' 
  4. order by LAST_MODIFIED; 
  5. SQL_HANDLE            PLAN_NAME                       ENABLED ACCEPTED  SQL_TEXT 
  6. --------------------  ------------------------------  ------- --------- -------- 
  7. SQL_59f9d6822a74ea01  SQL_PLAN_5myfqh8p79uh10348d329  YES     YES       <CLOB> 

4)在以上的SELECT語句中加入HINT改變執(zhí)行計劃，強制全表掃描，命令如下：

  1. SQL> select /*+ full(dept) */ * 
  2.   from scott.dept 
  3.  where deptno = 10; 
  4.    10 YDFD           NEW YORK 
  5.  
  6.  
  7. Execution Plan 
  8. -------------------------------------------------------------------------- 
  9. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
  10. -------------------------------------------------------------------------- 
  11. |   0 | SELECT STATEMENT  |      |     1 |    19 |     3   (0)| 00:00:01 | 
  12. |*  1 |  TABLE ACCESS FULL| DEPT |     1 |    19 |     3   (0)| 00:00:01 | 
  13. -------------------------------------------------------------------------- 

5)查找對應的SQL_ID和PLAN HASH VALUE，命令如下：

 

  1. SQL> select sql_id, plan_hash_value 
  2. from v$sql 
  3.  where sql_text like '%select /*+ full(dept) */%'; 
  4. SQL_ID        PLAN_HASH_VALUE 
  5. ------------- --------------- 
  6. fgb5t3n5w5btx      3383998547 

6)將加有HINT的執(zhí)行計劃加載到原SQL中的SPM中，命令如下：

 

  1. SQL> var n number 
  2. SQL> begin 
  3.   2  :n:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'fgb5t3n5w5btx', plan_hash_ 
  4.       value=>3383998547, fixed =>'NO', enabled=>'YES'); 
  5.   3  end; 
  6.   4  / 

7)查看DBA_SQL_PLAN_BASELINES視圖以確認情況，命令如下：

 

  1. SQL> select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, SQL_TEXT 
  2.   from dba_SQL_PLAN_BASELINES 
  3.  where ACCEPTED = 'YES' 
  4. order by LAST_MODIFIED; 

DBA_SQL_PLAN_BASELINES的視圖信息如圖2所示。

 

 

 

 

圖2　DBA_SQL_PLAN_BASELINES視圖信息

8)刪除第一個SQL 執(zhí)行計劃，命令如下：

 

  1. SQL> variable n number ; 
  2. SQL> exec :n:=dbms_spm.DROP_SQL_PLAN_BASELINE(SQL_HANDLE=> 'SQL_59f9d6822a74ea01',  
  3.     PLAN_NAME=> 'SQL_PLAN_5myfqh8p79uh10348d329'); 
  4. PL/SQL procedure successfully completed. 

9)重新執(zhí)行SQL語句，命令如下：

 

  1. SQL> set autotrace on 
  2. SQL> set line 150 pagesize 0 
  3. SQL> select * from scott.dept where  deptno=10; 
  4.         10 YDFD           NEW YORK 
  5.  
  6.  
  7. Execution Plan 
  8. ---------------------------------------------------------- 
  9. Plan hash value: 3383998547 
  10. -------------------------------------------------------------------------- 
  11. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
  12. -------------------------------------------------------------------------- 
  13. |   0 | SELECT STATEMENT  |      |     1 |    19 |     3   (0)| 00:00:01 | 
  14. |*  1 |  TABLE ACCESS FULL| DEPT |     1 |    19 |     3   (0)| 00:00:01 | 
  15. -------------------------------------------------------------------------- 
  16. Predicate Information (identified by operation id): 
  17. --------------------------------------------------- 
  18.    1 - filter("DEPTNO"=10) 
  19. Note 
  20. ----- 
  21.    - 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í)行計劃。

責任編輯：華軒 來源： 數(shù)倉寶貝庫
SQL數(shù)據(jù)庫優(yōu)化
相關(guān)推薦

2009-02-25 16:09:17

Windows優(yōu)化XPy

2010-04-23 14:33:34

郵件服務器加密

2009-09-24 11:10:56

打印服務器

2015-03-11 09:55:15

運維管理

2010-04-28 12:02:37

Forefront網(wǎng)絡優(yōu)化

2021-11-23 23:43:16

MySQL數(shù)據(jù)庫Docker

2010-06-12 13:49:16

學習UML

2010-06-13 14:19:40

學習UML

2010-09-06 11:58:39

ppp撥號Linux

2010-09-14 17:35:52

2017-04-17 12:31:45

SDN網(wǎng)絡虛擬化

2010-04-20 10:12:05

2010-06-02 17:29:02

svnserve服務

2010-11-19 15:44:04

IT跳槽

2011-07-07 13:09:04

編程

2013-07-15 14:30:44

產(chǎn)品經(jīng)理

2020-02-05 08:47:31

數(shù)據(jù)科學編程數(shù)據(jù)庫

2010-05-28 09:47:43

SVN項目創(chuàng)建

2013-03-18 11:03:48

云計算部署云計算CIO

2010-06-02 18:22:43

SVN本地設置
點贊
收藏

51CTO技術(shù)棧公眾號