快捷導航

oracle數(shù)據(jù)庫慢查詢SQL實例詳解

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

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

場景：

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

環(huán)境：

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

慢SQL查詢一：

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

問題一：辦件列表查詢慢

辦件查詢列表主要涉及到如下兩個SQL語句

    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

線上測試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_;

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

分析：

原因是前端剛打開辦件查詢列表時，由于查詢總數(shù)的sql語句，沒有任何過濾條件導致兩種表只有關(guān)聯(lián)查詢并沒有過濾故全表掃描耗時較長。

解決方法：

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

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

因此建議線上前端處理辦件查詢第一次進入時帶上時間范圍。

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

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

這里挑選了幾個耗時較長的簡單的分析（這里面的sql是另外一個部門的）

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）一次平均耗時將近一個小時。執(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í)行耗時接近半個小時。需要對該語句重點優(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í)行耗時25秒。加載較頻繁需要重點優(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í)行耗時9秒。執(zhí)行和加載較頻繁需要重點優(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

分頁查詢語句執(zhí)行了7680次，平均每次執(zhí)行10s,看SQL執(zhí)行計劃走了時間字段，然而線上沒有，線上加上索引線上執(zhí)行為0.1秒

分析：

線上START_TIME_ 列沒有走索引

解決方法：

添加索引

慢SQL查詢二

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;

擴展：

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

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

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

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

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

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

SQL語句文本不同：即使SQL語句的邏輯相同，但如果SQL語句的文本不同（比如空格、大小寫等），Oracle也會將它們當作不同的SQL語句進行處理。

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

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