oracle数据库慢查询SQL

场景：

线上环境出现办件列表查询非常慢大概要1分钟才刷出来，及很多功能都出现系统性卡顿。

环境：

oracle数据库，工作表历史表act_hi_proinst单表数据量一百多万

慢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语句的文本内容
v.sql_fulltext: 包含完整的SQL语句文本内容
v.FIRST_LOAD_TIME: SQL语句第一次加载到共享池中的时间
v.last_load_time: SQL语句最后一次加载到共享池中的时间
v.elapsed_time: SQL语句的总执行时间（以微秒为单位）
v.cpu_time: SQL语句的总CPU执行时间（以微秒为单位）
v.disk_reads: SQL语句的总磁盘读取次数
v.EXECUTIONS: SQL语句的总执行次数
v.LOADS: SQL语句的总加载次数
ave_cpu_time: 每次执行的平均CPU执行时间（以秒为单位）
ave_time: 每次执行的平均总执行时间（以秒为单位）

问题一：办件列表查询慢

办件查询列表主要涉及到如下两个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_;  

但是分页查询总数的sql语句执行五次，5.932s，3.78s，2.89s, 2.5s，1.9s

分析：

原因是前端刚打开办件查询列表时，由于查询总数的sql语句，没有任何过滤条件导致两种表只有关联查询并没有过滤故全表扫描耗时较长。

解决方法：

由于两张关联表中数据是一对一的，因此如果仅仅考虑第一次查询慢的问题，直接可以去掉关联，单表查询的总数就可以了。

但是事与愿违，这只能解决办件查询第一进入的问题，如果有条件参数过滤的话（关联表的参数）还要加上这个关联表，后端改动有点大。

因此建议线上前端处理办件查询第一次进入时带上时间范围。

问题二：系统性卡顿

描述也不算是系统系卡顿吧，有写接口还是比较快的，只能说有很多重要的操作反应都很慢，下面是获取的当天的慢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执行（Execution）一次 ，加载（LOADS）一次 平均耗时将近一个小时。执行 pro_inert_rybjlcx_sed慢

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

2..该SQL执行（Execution）11次 ，加载（LOADS）216次 平均每次执行耗时接近半个小时。需要对该语句重点优化

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执行（Execution）四次 ，加载（LOADS）2次 平均每次执行耗时25秒。加载较频繁需要重点优化行 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执行（Execution）317次 ，加载（LOADS）29次 平均每次执行耗时9秒。执行和加载较频繁需要重点优化行

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

分页查询语句执行了7680次，平均每次执行10s,看SQL执行计划走了时间字段，然而线上没有，线上加上索引线上执行为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的区别于联系？

1. loads：表示SQL语句在共享池中被加载的次数。每当一个SQL语句被解析并放入共享池中，loads的值就会增加。这个值可以帮助您了解一个SQL语句被重复使用的频率。
2. executions：表示SQL语句被执行的次数。每当一个SQL语句被实际执行，executions的值就会增加。这个值可以帮助您了解一个SQL语句在实际执行过程中的频率。

1. 同一个SQL为什么会被重复加入到共享池

在Oracle数据库中，同一个SQL语句可能会被重复加入到共享池的原因有以下几点：

1. 绑定变量不同：如果SQL语句使用了绑定变量，即在SQL语句中使用了占位符，那么不同的绑定变量值会导致不同的SQL语句被加入到共享池中。
2. SQL语句文本不同：即使SQL语句的逻辑相同，但如果SQL语句的文本不同（比如空格、大小写等），Oracle也会将它们当作不同的SQL语句进行处理。
3. 不同的解析环境：在不同的解析环境下，相同的SQL语句可能会被多次解析并加载到共享池中，比如在不同的会话或者不同的数据库连接中。
4. 共享池空间不足：如果共享池空间不足，Oracle可能会根据一些策略进行SQL语句的淘汰和重新加载，这也会导致同一个SQL语句被重复加载到共享池中。