一区二区三区在线-一区二区三区亚洲视频-一区二区三区亚洲-一区二区三区午夜-一区二区三区四区在线视频-一区二区三区四区在线免费观看

服務(wù)器之家：專注于服務(wù)器技術(shù)及軟件下載分享
分類導(dǎo)航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|數(shù)據(jù)庫(kù)技術(shù)|

四步搞定異常SQL

2021-07-26 23:48數(shù)倉(cāng)寶貝庫(kù)葉樺等 數(shù)據(jù)庫(kù)技術(shù)

本文介紹如何查找需要進(jìn)行優(yōu)化的異常SQL并進(jìn)行處理。

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

四步搞定異常SQL

1)統(tǒng)計(jì)信息，具體如下

  • 陳舊的統(tǒng)計(jì)信息
  • 錯(cuò)誤的優(yōu)化器參數(shù)配置
  • 數(shù)據(jù)庫(kù)遷移前后優(yōu)化器的改變
  • 頻繁的數(shù)據(jù)量變化

2)SQL語(yǔ)句編寫問題

3)游標(biāo)共享問題，具體如下

  • 變量窺探
  • 使用常量

4)資源爭(zhēng)用問題，具體如下

  • 硬件資源不足
  • 鎖或latch等的爭(zhēng)用問題

以上幾種原因最終都會(huì)導(dǎo)致主機(jī)CPU的使用率增加、主機(jī)I/O異常繁忙、語(yǔ)句執(zhí)行時(shí)間異常增加、數(shù)據(jù)庫(kù)整體性能下降、應(yīng)用超時(shí)等問題。

01定位問題SQL

進(jìn)行SQL優(yōu)化的第一步是定位問題SQL。Oracle會(huì)在內(nèi)存中記錄每條SQL語(yǔ)句執(zhí)行所消耗的資源，再由專門的進(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可以通過指定的兩個(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)行的排名。

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

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

 

  1. <!--替換對(duì)應(yīng)的SPID即可 --> 
  1.  
  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è)務(wù)超時(shí)反饋，檢查對(duì)應(yīng)的應(yīng)用模塊正在執(zhí)行的SQL語(yǔ)句。
  • 數(shù)據(jù)庫(kù)性能整體下降，檢查異常等待事件，查詢語(yǔ)句如下：

 

  1. <!--替換等待事件名稱即可--> 
  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健康檢查

接下來(lái)介紹SQL調(diào)優(yōu)健康檢查(SQL Tuning Health Check，SQLHC)腳本的基礎(chǔ)知識(shí)，以及如何使用它來(lái)收集性能較差SQL的關(guān)鍵信息。SQLHC可以幫助我們專注于特定的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問題，我們通過執(zhí)行計(jì)劃就能判斷其優(yōu)化方向;而對(duì)于復(fù)雜SQL問題的診斷，則需要借助于更多信息，比如，數(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或訪問數(shù)據(jù)字典視圖的權(quán)限。操作也非常簡(jiǎn)單，只需要上傳sqlhc.sql到數(shù)據(jù)庫(kù)服務(wù)器指定的目錄下執(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í)需要輸入以下兩個(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所示。

四步搞定異常SQL

圖1 SQLHC.SQL執(zhí)行后生成的文件

圖1中的部分內(nèi)容說明如下。

  • 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是查詢中的輔助信息的集合，包括查詢中引用的所有表和列。SQL profile存儲(chǔ)在數(shù)據(jù)字典中，優(yōu)化器在優(yōu)化過程中使用這些信息來(lái)確定最優(yōu)的計(jì)劃。

1. 使用coe_xfr_sql_profile.sql

1)運(yùn)行分析腳本，命令如下：

  1. shell> sqlplus "/ as sysdba" 
  2. SQL>@coe_xfr_sql_profile.sql cdwjdd67x27mh  
  3. <!--后跟問題SQL對(duì)應(yīng)的ID--> 

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

  1. SQL> SQL_ID (required) 
  2. PLAN_HASH_VALUE AVG_ET_SECS 
  3. --------------- ----------- 
  4.     2979024279        .011 
  5.      647855111       5.164 
  6. 從以上輸出結(jié)果來(lái)看，2979024279對(duì)應(yīng)的執(zhí)行計(jì)劃響應(yīng)時(shí)間更快。 
  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中對(duì)應(yīng)的profile名進(jìn)行刪除，命令如下：

  1. 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)。帶入問題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)容，查詢語(yǔ)句如下：

  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ù)庫(kù)同時(shí)給出了創(chuàng)建索引和SQL profile兩個(gè)建議，那么在某些情況下，只要接受SQL profile即可，某些情況下則需要同時(shí)采納創(chuàng)建索引和接受SQL porfile兩個(gè)建議。因?yàn)閯?chuàng)建索引之后，數(shù)據(jù)庫(kù)可能需要通過SQL profile的幫助才能選擇新的索引。

3)查看SQL profile。數(shù)據(jù)字典視圖DBA_SQL_PROFILES可用于展示數(shù)據(jù)庫(kù)中存儲(chǔ)的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計(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)過驗(yàn)證的最優(yōu)計(jì)劃。當(dāng)系統(tǒng)開啟自動(dòng)SQL Plan Baseline(SQL計(jì)劃基線)捕獲時(shí)，CBO會(huì)記錄會(huì)話內(nèi)執(zhí)行的任意SQL，并把SQL的相關(guān)信息存儲(chǔ)為SQL計(jì)劃基線。第一次執(zhí)行的語(yǔ)句，由于沒有基礎(chǔ)數(shù)據(jù)，因此會(huì)被當(dāng)成最優(yōu)的執(zhí)行計(jì)劃。第二次執(zhí)行時(shí)，CBO會(huì)與存儲(chǔ)在SQL計(jì)劃基線中的計(jì)劃進(jìn)行比較，如果新執(zhí)行計(jì)劃的性能有所改善，那么SPM會(huì)把新的執(zhí)行計(jì)劃標(biāo)記為該語(yǔ)句最優(yōu)的執(zhí)行計(jì)劃。默認(rèn)情況下，CBO會(huì)使用SQL計(jì)劃基線中最優(yōu)的執(zhí)行計(jì)劃。而對(duì)于異常SQL自動(dòng)捕獲，則需要設(shè)置參數(shù)optimizer_capture_sql_plan_baselines的值為true，默認(rèn)是false，命令如下：

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

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

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

  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 | 

查找對(duì)應(yīng)的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語(yǔ)句加載至SQL計(jì)劃基線中，命令如下：

  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視圖以確認(rèn)情況，命令如下：

  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語(yǔ)句中加入HINT改變執(zhí)行計(jì)劃，強(qiáng)制全表掃描，命令如下：

  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)查找對(duì)應(yīng)的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í)行計(jì)劃加載到原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視圖以確認(rèn)情況，命令如下：

  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所示。

四步搞定異常SQL

圖2　DBA_SQL_PLAN_BASELINES視圖信息

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

  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語(yǔ)句，命令如下：

  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í)行計(jì)劃中，SQL計(jì)劃基線表明以上SQL已經(jīng)開始按照我們想要的方式在執(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ì)劃。

原文鏈接：https://mp.weixin.qq.com/s?__biz=Mzg4OTU0NzY1NA==&mid=2247484285&idx=1&sn=1c95835ba848cbdfca93957c825c0704&chksm=cfeb7d94f89cf482def1f5b3770cf774def2c4ee2270aa5f8bb6a2d0da1bcf1b1750a3f343b7&mpshare=1&

延伸 · 閱讀

精彩推薦
主站蜘蛛池模板： 国产嘿咻 | 美女沟厕撒尿全过程高清图片 | 视频一区二区 村上凉子 | 69japanese日本100 6969精品视频在线观看 | 深夜影院深a久久 | 久久国产精品高清一区二区三区 | 日日爽| 99国产成人精品2021 | 青青五月天 | 亚洲无限| 91国内精品久久久久怡红院 | a天堂中文在线 | 精品国产欧美一区二区三区成人 | 91天堂视频 | 欧美久久一区二区三区 | 色视频综合 | 日产国产精品亚洲系列 | 91亚洲在线 | 操黄| 亚洲免费黄色网 | 精品一区二区三区免费毛片 | 办公室出轨秘书高h | 成人免费体验区福利云点播 | 九九在线免费视频 | 国产caoni555在线观看 | 乳环贵妇堕落开发调教番号 | 色哟哟观看 | 久久精品黄AA片一区二区三区 | 青青草在线观看 | 天堂资源在线8 | 亚洲 日本 天堂 国产 在线 | 亚洲国产精品一在线观看 | 国产二区三区 | 99久久国产综合精品女小说 | 国产福利在线观看91精品 | 国产资源一区 | a一级一级| 18亚洲chinese男男1069 | 日本私人影院 | 精品亚洲欧美中文字幕在线看 | 亚洲精品免费观看 |