Oracle-SQL优化案例一

前言:

近期做了3条Oracle数据库的慢SQL优化，把慢SQL优化的过程记录了下来分享给大家，希望对大家有所帮助。

案例1：

sql_text:

select  cc.*   
from CIF_XXX cc       
where 1=1
AND cc.ch_XXX_name like '%刘璐%'
AND cc.XXX_no in (select XXX_no from cif_XXX_document where iss_country =  :1 )
order by cc.XXX_no ASC;

执行计划:

执行计划问题分析:

1 CIF_XXX进行了全表扫描，因为列ch_xxx_name有索引，但由于ch_xxx_name采用双边的模糊匹配%xxx%，这导致条件减少了走索引的可能，即使走索引也走索引全扫描。

2 索引CIF_XXX_DOCUMENT_IND1以及PK_CIF_XXX_DOCUMENT进行了快速索引全扫描(index fast full index )消耗较大,这是因为where条件列iss_country以及xxx_no只有单列索引没有组合索引，导致进行索引hash连接。

优化建议:

1 可以强制ch_xxx_name走全索引扫描，因为列的可选择率较大回表的消耗较小

2 对iss_country以及xxx_no创建组合索引，避免进行两次index fast full index 以及hash join

create index xxxx.CIF_XXX_DOCUMENT_IND2 on CIF_CLIENCIF_XXX_DOCUMENT(ISS_COUNTRY,XXX_NO) online;

优化后的提升:​

优化后的执行计划，时间从原来是1.2s 减少为0.21秒

优化后的执行计划:

案例2：

sql_text:

​SELECT /*太长内容省略 */ '',  
CASE WHEN T1.AAAA_NUM IN (SELECT A.AAAA_NUM FROM XXXXODS.XXXX_MB_ACCT_INFO A WHERE A.CLOSE_DT IS NULL) THEN '0' ELSE '1' END,  'C',  
NULL,  NULL 
FROM XXXXODS.XXXX_AAAA_INFO T1 
LEFT JOIN XXXXODS.XXXX_AAAA_ADDIT_INFO T4 ON T1.AAAA_NUM=T4.AAAA_NUM 
LEFT JOIN (SELECT * FROM 
(SELECT AAAA_NUM, ISSUE_DT, BBBB_TYPE_CD, MATURE_DT, BBBB_NO, ISSUE_GROUND,  ROW_NUMBER()OVER (PARTITION BY AAAA_NUM ORDER BY BBBB_NO ) U 
FROM XXXXODS.XXXX_AAAA_BBBB
) 
WHERE U=1
) T2 ON T1.AAAA_NUM=T2.AAAA_NUM 
LEFT JOIN CCXX_CHANGE_CODE T3 ON T3.ODSMZ=T1.COMN_INDU_CODE 
LEFT JOIN CCXX_CHANGE_CODE T9 ON T9.ODSMZ=T4.LP_BBBB_TYPE_CD 
LEFT JOIN CCXX_CHANGE_CODE T11 ON T11.ODSMZ=T2.BBBB_TYPE_CD 
LEFT JOIN (SELECT * FROM (SELECT CBK_AAAA_NUM,  ROW_NUMBER()OVER(PARTITION BY CBK_AAAA_NUM ORDER BY AAAA_SEQ_NUM) RM 
FROM XXXXODS.F_CBANK_CTM_AAAA_INFO) 
WHERE RM =1
) T5 ON T1.AAAA_NUM=T5.CBK_AAAA_NUM 
LEFT JOIN (SELECT * FROM XXXXODS.XXXX_AAAA_CONT_INFO WHERE CONT_TYPE_CD='02') T8 ON T1.AAAA_NUM=T8.AAAA_NUM 
LEFT JOIN (SELECT * FROM XXXXODS.XXXX_AAAA_CONT_INFO WHERE CONT_TYPE_CD='04') T10 ON T1.AAAA_NUM=T10.AAAA_NUM 
LEFT JOIN (SELECT * FROM XXXXODS.XXXX_AAAA_CONT_INFO WHERE CONT_TYPE_CD='06') T12 ON T1.AAAA_NUM=T12.AAAA_NUM 
WHERE T1.AAAA_TYPE_CD != '01' 
;

执行计划:

执行计划问题分析:

 查询select里面存在标量子查询"CASE WHEN T1.AAAA_NUM IN (SELECT A.AAAA_NUM FROM XXXXODS.XXXX_MB_ACCT_INFO A WHERE A.CLOSE_DT IS NULL)"，访问路径类似for循环导致进行了1418次对表XXXX_MB_ACCT_INFO的全表扫描

优化建议:

对标量子查询进行改写为外部连接，走hash join

改写后的SQL

SELECT   /*太长内容省略 */ '',  
CASE WHEN T1.AAAA_NUM =  A.AAAA_NUM THEN '0' ELSE '1' END,  'C',  NULL,  NULL 
FROM XXXXODS.XXXX_AAAA_INFO T1 
  LEFT JOIN XXXXODS.XXXX_AAAA_ADDIT_INFO T4 ON T1.AAAA_NUM=T4.AAAA_NUM 
  LEFT JOIN (SELECT * FROM 
      (SELECT AAAA_NUM, ISSUE_DT, BBBB_TYPE_CD, MATURE_DT, BBBB_NO, ISSUE_GROUND,  ROW_NUMBER()OVER (PARTITION BY AAAA_NUM ORDER BY BBBB_NO ) U 
        FROM XXXXODS.XXXX_AAAA_BBBB
      ) 
        WHERE U=1
      ) T2 ON T1.AAAA_NUM=T2.AAAA_NUM 
  LEFT JOIN CCXX_CHANGE_CODE T3 ON T3.ODSMZ=T1.COMN_INDU_CODE 
  LEFT JOIN CCXX_CHANGE_CODE T9 ON T9.ODSMZ=T4.LP_BBBB_TYPE_CD 
  LEFT JOIN CCXX_CHANGE_CODE T11 ON T11.ODSMZ=T2.BBBB_TYPE_CD 
  LEFT JOIN (SELECT * FROM (SELECT CBK_AAAA_NUM,  ROW_NUMBER()OVER(PARTITION BY CBK_AAAA_NUM ORDER BY AAAA_SEQ_NUM) RM 
        FROM XXXXODS.F_CBANK_CTM_AAAA_INFO) 
        WHERE RM =1
      ) T5 ON T1.AAAA_NUM=T5.CBK_AAAA_NUM 
  LEFT JOIN (SELECT * FROM XXXXODS.XXXX_AAAA_CONT_INFO WHERE CONT_TYPE_CD='02') T8 ON T1.AAAA_NUM=T8.AAAA_NUM 
  LEFT JOIN (SELECT * FROM XXXXODS.XXXX_AAAA_CONT_INFO WHERE CONT_TYPE_CD='04') T10 ON T1.AAAA_NUM=T10.AAAA_NUM 
  LEFT JOIN (SELECT * FROM XXXXODS.XXXX_AAAA_CONT_INFO WHERE CONT_TYPE_CD='06') T12 ON T1.AAAA_NUM=T12.AAAA_NUM 
  ---改写的子查询
  LEFT join (SELECT distinct AAAA_NUM FROM XXXXODS.XXXX_MB_ACCT_INFO  WHERE CLOSE_DT IS NULL) A on T1.AAAA_NUM=A.AAAA_NUM
  WHERE T1.AAAA_TYPE_CD != '01' 
;

优化后的提升:

优化后的执行计划，时间从原来是4分钟减少为2秒

优化后的执行计划:

案例3：

sql_text:

insert into tmp_cccc_282001_02( aaaa_id,bbb_cell, count_bbb ) 
  select s.aaaa_id, s.bbb_cell, s.count_bbb 
  from (select t.aaaa_id, t1.bbb_cell, count(t.aaaa_id) over(partition by
    t1.bbb_cell) as count_bbb 
  from t2a_aaaa_i t, (select distinct bbb_cell from tmp_cccc_282001_01) t1 
  where (t.office_bbb = t1.bbb_cell or t.home_bbb = t1.bbb_cell or t.mobile_phone = t1.bbb_cell) )s 
  where s.count_bbb >= 3

执行计划:

执行计划问题分析:

tmp_cccc_282001_01执行计划由于统计信息不正确估算只有1行数据，导致走了nested loop，导致对大表T2A_aaaa_I进行了2753次扫描，正常应该是走hash join

优化建议:

对表进行统计信息收集

BEGIN 
	DBMS_STATS.GATHER_TABLE_STATS(ownname => 'XXXXAML', 
	tabname => 'TMP_cccc_282001_01', 
	estimate_percent => 100, 
	method_opt => 'for all columns size skewonly',
	no_invalidate => FALSE, 
	degree => 8, 
	cascade => TRUE); 
END; 
 / 
 
 
BEGIN 
	DBMS_STATS.GATHER_TABLE_STATS(ownname => 'XXXXAML', 
	tabname => 'T2A_aaaa_I', 
	estimate_percent => 100, 
	method_opt => 'for all columns size skewonly',
	no_invalidate => FALSE, 
	degree => 8, 
	cascade => TRUE); 
END; 
 /

优化后的提升:

优化后的执行计划，时间从原来是25秒减少为0.38秒

优化后的执行计划: