树形查询的优化案例

某数仓系统一条SQL执行了很长时间，要求分析原因

使用如下查询获取时间段内SQL语句的执行情况

select count(1),sql_id
  from dba_hist_active_sess_history
 where to_char(sample_time, 'yyyy-mm-dd hh24:mi:ss') >
       '2018-07-13 12:03:57'
   and to_char(sample_time, 'yyyy-mm-dd hh24:mi:ss') <
       '2018-07-13 17:13:57'
       group by sql_id
order by 1 desc;

1	1772	4wy3j1nz5rs1u
2	795	
3	781	73830bz989abk
4	637	g3f5uca2xtxbp

可知4wy3j1nz5rs1u执行了1772*10s，大约5小时，通过SQL_ID 定位到SQL语句和执行计划如下：

WITH T AS
 (SELECT CRDT_CTR_ID
    FROM USERUSER.S14_AAAA_AAA_H T1
   INNER JOIN USERUSER.S11_CCCC_CCCC_INFO_H B
      ON T1.Crdt_Main_Pty_Id = B.Corp_Cust_Id
     AND B.DATA_SRC_CD = 'CMS'
     AND B.ST_DT <= date '2018-07-12'
     AND B.END_DT > date '2018-07-12'
   WHERE T1.ST_DT <= date '2018-07-12'
     AND T1.END_DT > date '2018-07-12'
     AND T1.CRDT_CTR_STATUS_CD IN ('CMS0_A', 'CMS0_AV'))
SELECT CONNECT_BY_ROOT(T1.CRDT_CTR_ID),
       T1.CRDT_CTR_ID,
       T1.CRDT_DUE_DT,
       T1.CRDT_PRD_ID,
       T1.APRVED_LMT,
       T1.SPEC_CRDT_FLG,
       T1.CRDT_MAIN_PTY_ID,
       T1.CCY_CD
  FROM USERUSER.S14_AAAA_AAA_H T1
 WHERE T1.ST_DT <= date '2018-07-12'
   AND T1.END_DT > date '2018-07-12'
 START WITH T1.ST_DT <= date '2018-07-12'
        AND T1.END_DT > date'2018-07-12'
        AND T1.CRDT_CTR_STATUS_CD IN ('CMS0_A', 'CMS0_AV')
        AND T1.CRDT_CTR_ID IN (SELECT CRDT_CTR_ID FROM T)
CONNECT BY NOCYCLE PRIOR T1.CRDT_CTR_ID = T1.PARENT_CRDT_CTR_ID;



Plan hash value: 696009752
 
-------------------------------------------------------------