快捷導(dǎo)航

oracle數(shù)據(jù)庫(kù)慢查詢(xún)SQL實(shí)例詳解

更新時(shí)間：2024年06月03日 08:33:05 作者：喜羊羊love紅太狼

一般的業(yè)務(wù)系統(tǒng)如果遇到性能問(wèn)題,絕大部分都是來(lái)自數(shù)據(jù)庫(kù)的,有的業(yè)務(wù)一個(gè)查詢(xún)執(zhí)行時(shí)間好幾秒,這就是我們說(shuō)說(shuō)的SQL慢查詢(xún),這篇文章主要給大家介紹了關(guān)于oracle數(shù)據(jù)庫(kù)慢查詢(xún)SQL的相關(guān)資料,需要的朋友可以參考下

場(chǎng)景：

線(xiàn)上環(huán)境出現(xiàn)辦件列表查詢(xún)非常慢大概要1分鐘才刷出來(lái)，及很多功能都出現(xiàn)系統(tǒng)性卡頓。

環(huán)境：

oracle數(shù)據(jù)庫(kù)，工作表歷史表act_hi_proinst單表數(shù)據(jù)量一百多萬(wàn)

慢SQL查詢(xún)一：

select *
from (select v.sql_id,
v.sql_text,
v.sql_fulltext,
v.FIRST_LOAD_TIME,
v.last_load_time,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
v.EXECUTIONS,
v.LOADS,
v.cpu_time / v.executions / 1000 / 1000 ave_cpu_time,
v.ELAPSED_TIME / v.executions / 1000 / 1000 ave_time
from v$sql v) a
where a.last_LOAD_TIME > '2024-01-01/00:00:00'  and ave_time > 5 and a.executions > 0 order by ave_time desc;

其中各字段含義如下：

v.sql_text: 包含SQL語(yǔ)句的文本內(nèi)容
v.sql_fulltext: 包含完整的SQL語(yǔ)句文本內(nèi)容
v.FIRST_LOAD_TIME: SQL語(yǔ)句第一次加載到共享池中的時(shí)間
v.last_load_time: SQL語(yǔ)句最后一次加載到共享池中的時(shí)間
v.elapsed_time: SQL語(yǔ)句的總執(zhí)行時(shí)間（以微秒為單位）
v.cpu_time: SQL語(yǔ)句的總CPU執(zhí)行時(shí)間（以微秒為單位）
v.disk_reads: SQL語(yǔ)句的總磁盤(pán)讀取次數(shù)
v.EXECUTIONS: SQL語(yǔ)句的總執(zhí)行次數(shù)
v.LOADS: SQL語(yǔ)句的總加載次數(shù)
ave_cpu_time: 每次執(zhí)行的平均CPU執(zhí)行時(shí)間（以秒為單位）
ave_time: 每次執(zhí)行的平均總執(zhí)行時(shí)間（以秒為單位）

問(wèn)題一：辦件列表查詢(xún)慢

辦件查詢(xún)列表主要涉及到如下兩個(gè)SQL語(yǔ)句

    select * from (
     select a.*,rownum as num from (
       select RES.* ,H.NAME_ as bizName, H.XZQ_ as bizXzq, H.DUE_DATE_ as bizDueDate, H.PROC_DEF_KEY_ as bizProcDefKey, 
      H.CATEGORY_ as bizCategory, H.DATUM_TYPE_ as bizDatumType, H.START_USER_ID_ as bizStartUserId, H.DEPT_CODE_ as bizDeptCode,
      H.F1_ as bizF1, H.F2_ as bizF2, H.F3_ as bizF3, H.F4_ as bizF4, H.F5_ as bizF5, H.F6_ as bizF6, H.F7_ as bizF7, 
      H.F8_ as bizF8, H.F9_ as bizF9, H.F10_ as bizF10, H.F11_ as bizF11, H.F12_ as bizF12, H.F13_ as bizF13, H.F14_ as bizF14,
      H.F15_ as bizF15, H.F16_ as bizF16, H.F17_ as bizF17, H.F18_ as bizF18, H.F19_ as bizF19, H.F20_ as bizF20
	  from gisqbpm.ACT_HI_PROCINST RES
	  left join gisqbpm.ACT_HI_BIZ_PROCINST H on  H.PROC_INST_ID_ = RES.PROC_INST_ID_)a where rownum<15 )b 
    where b.num>0

線(xiàn)上測(cè)試1.58秒

select count(RES.ID_) 
      from gisqbpm.ACT_HI_PROCINST RES, gisqbpm.ACT_HI_BIZ_PROCINST H 
      where  H.PROC_INST_ID_ = RES.PROC_INST_ID_;

但是分頁(yè)查詢(xún)總數(shù)的sql語(yǔ)句執(zhí)行五次，5.932s，3.78s，2.89s, 2.5s，1.9s

分析：

原因是前端剛打開(kāi)辦件查詢(xún)列表時(shí)，由于查詢(xún)總數(shù)的sql語(yǔ)句，沒(méi)有任何過(guò)濾條件導(dǎo)致兩種表只有關(guān)聯(lián)查詢(xún)并沒(méi)有過(guò)濾故全表掃描耗時(shí)較長(zhǎng)。

解決方法：

由于兩張關(guān)聯(lián)表中數(shù)據(jù)是一對(duì)一的，因此如果僅僅考慮第一次查詢(xún)慢的問(wèn)題，直接可以去掉關(guān)聯(lián)，單表查詢(xún)的總數(shù)就可以了。

但是事與愿違，這只能解決辦件查詢(xún)第一進(jìn)入的問(wèn)題，如果有條件參數(shù)過(guò)濾的話(huà)（關(guān)聯(lián)表的參數(shù)）還要加上這個(gè)關(guān)聯(lián)表，后端改動(dòng)有點(diǎn)大。

因此建議線(xiàn)上前端處理辦件查詢(xún)第一次進(jìn)入時(shí)帶上時(shí)間范圍。

問(wèn)題二：系統(tǒng)性卡頓

描述也不算是系統(tǒng)系卡頓吧，有寫(xiě)接口還是比較快的，只能說(shuō)有很多重要的操作反應(yīng)都很慢，下面是獲取的當(dāng)天的慢SQL。

這里挑選了幾個(gè)耗時(shí)較長(zhǎng)的簡(jiǎn)單的分析（這里面的sql是另外一個(gè)部門(mén)的）

DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  broken BOOLEAN := FALSE;
BEGIN pro_inert_rybjlcx_sed; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

1.該SQL執(zhí)行（Execution）一次，加載（LOADS）一次平均耗時(shí)將近一個(gè)小時(shí)。執(zhí)行 pro_inert_rybjlcx_sed慢

SELECT COUNT(0) FROM (SELECT * FROM (select * from v_fwdyaq where 1=1) WHERE 1=1 )

2..該SQL執(zhí)行（Execution）11次，加載（LOADS）216次平均每次執(zhí)行耗時(shí)接近半個(gè)小時(shí)。需要對(duì)該語(yǔ)句重點(diǎn)優(yōu)化

DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  broken BOOLEAN := FALSE;
BEGIN sms_ts; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

3.該SQL執(zhí)行（Execution）四次，加載（LOADS）2次平均每次執(zhí)行耗時(shí)25秒。加載較頻繁需要重點(diǎn)優(yōu)化行 sms_ts操作

SELECT COUNT(DISTINCT "A2"."QLBSM") FROM "BDCDJ"."DJFZ_CQZS" "A2","BDCDJ"."QLR" "A1" WHERE "A2"."QLBSM"="A1"."QLBSM" AND "A2"."QSZT"=1 AND TRIM("A2"."BDCQZH")=:1 AND "A1"."QLRMC" LIKE :2

4.該SQL執(zhí)行（Execution）317次，加載（LOADS）29次平均每次執(zhí)行耗時(shí)9秒。執(zhí)行和加載較頻繁需要重點(diǎn)優(yōu)化行

select * from ( select a.*, ROWNUM rnum from (     select RES.*, H.NAME_ as bizName, H.XZQ_ as bizXzq, H.DUE_DATE_ as bizDueDate, H.PROC_DEF_KEY_ as bizProcDefKey,        H.CATEGORY_ as bizCategory, H.DATUM_TYPE_ as bizDatumType, H.START_USER_ID_ as bizStartUserId,       H.F1_ as bizF1, H.F2_ as bizF2, H.F3_ as bizF3,H.F4_ as bizF4, H.F5_ as bizF5, H.F6_ as bizF6, H.F7_ as bizF7,        H.F8_ as bizF8, H.F9_ as bizF9, H.F10_ as bizF10, H.F11_ as bizF11, H.F12_ as bizF12, H.F13_ as bizF13, H.F14_ as bizF14,       H.F15_ as bizF15, H.F16_ as bizF16, H.F17_ as bizF17, H.F18_ as bizF18, H.F19_ as bizF19, H.F20_ as bizF20                  from ACT_HI_PROCINST RES            left join ACT_HI_BIZ_PROCINST H on  H.PROC_INST_ID_ = RES.PROC_INST_ID_                  WHERE  (RES.DELETE_REASON_ <> :1 or RES.DELETE_REASON_ is null)               order by RES.START_TIME_ desc        ) a where ROWNUM < :2) where rnum  >= :3

分頁(yè)查詢(xún)語(yǔ)句執(zhí)行了7680次，平均每次執(zhí)行10s,看SQL執(zhí)行計(jì)劃走了時(shí)間字段，然而線(xiàn)上沒(méi)有，線(xiàn)上加上索引線(xiàn)上執(zhí)行為0.1秒

分析：

線(xiàn)上START_TIME_ 列沒(méi)有走索引

解決方法：

添加索引

慢SQL查詢(xún)二

select *
from (select v.sql_id,
v.SQL_FULLTEXT,
v.EXECUTIONS,
v.ELAPSED_TIME / v.executions / 1000 / 1000 ave_time,
v.parsing_user_id,
last_LOAD_TIME
from v$sql v) a
where a.last_LOAD_TIME > '2024-02-01/00:00:00'  and ave_time > 5 and a.executions > 0 
and a.parsing_user_id=(SELECT user_id FROM all_users  where username='GISQBPM')
order by ave_time desc;

擴(kuò)展：

1.loads 和execution的區(qū)別于聯(lián)系？

loads：表示SQL語(yǔ)句在共享池中被加載的次數(shù)。每當(dāng)一個(gè)SQL語(yǔ)句被解析并放入共享池中，loads的值就會(huì)增加。這個(gè)值可以幫助您了解一個(gè)SQL語(yǔ)句被重復(fù)使用的頻率。

executions：表示SQL語(yǔ)句被執(zhí)行的次數(shù)。每當(dāng)一個(gè)SQL語(yǔ)句被實(shí)際執(zhí)行，executions的值就會(huì)增加。這個(gè)值可以幫助您了解一個(gè)SQL語(yǔ)句在實(shí)際執(zhí)行過(guò)程中的頻率。

2. 同一個(gè)SQL為什么會(huì)被重復(fù)加入到共享池

在Oracle數(shù)據(jù)庫(kù)中，同一個(gè)SQL語(yǔ)句可能會(huì)被重復(fù)加入到共享池的原因有以下幾點(diǎn)：

綁定變量不同：如果SQL語(yǔ)句使用了綁定變量，即在SQL語(yǔ)句中使用了占位符，那么不同的綁定變量值會(huì)導(dǎo)致不同的SQL語(yǔ)句被加入到共享池中。

SQL語(yǔ)句文本不同：即使SQL語(yǔ)句的邏輯相同，但如果SQL語(yǔ)句的文本不同（比如空格、大小寫(xiě)等），Oracle也會(huì)將它們當(dāng)作不同的SQL語(yǔ)句進(jìn)行處理。

不同的解析環(huán)境：在不同的解析環(huán)境下，相同的SQL語(yǔ)句可能會(huì)被多次解析并加載到共享池中，比如在不同的會(huì)話(huà)或者不同的數(shù)據(jù)庫(kù)連接中。

共享池空間不足：如果共享池空間不足，Oracle可能會(huì)根據(jù)一些策略進(jìn)行SQL語(yǔ)句的淘汰和重新加載，這也會(huì)導(dǎo)致同一個(gè)SQL語(yǔ)句被重復(fù)加載到共享池中。