合 统计信息不准确导致执行计划走了笛卡尔积
昨天有事没有上班，今天早上来查看系统的时候发现了很多笛卡尔积的sql，而且一直在跑，已经运行了10多个小时了，觉得这个比较典型，这里记录一下：
SELECT a.ELAPSED_TIME 已运行时间,a.MONITOR_TYPES,a.SQL_ID,a.SQL_TEXT
FROM XT_SQL_RUBBISH_MONITOR_LHR a
WHERE a.MONITOR_TYPES = '笛卡尔积监控'
and a.ID>=45150
ORDER BY a.IN_DATE DESC;
截取了其中一个sql：
--create table czh_temp_1312_t6 nologging as
SELECT a.CUST_TYPE_V1,
a.CUST_TYPE_V2,
a.CUST_TYPE_V3,
a.CURRENT_FLAG,
a.ACTIVE_FLAG,
a.ACTIVE2_FLAG,
a.BSCORE_SEG,
b.month_stamp,
b.DELQ_LEVEL,
SUM(b.real_dual_bal + nvl(b.inst_rmb_unposting_amt,
0)) AS AR,
SUM((c.LAST_6M_INT 2 + c.LAST_6M_CHARGEFEE 2 +
c.LAST_6M_OVERLIMIT_FEE 2 + c.LAST_6M_CA_FEE 2 +
c.LAST_6M_INST_FEE 2 - c.LAST_6M_COST_OF_BAL 2 -
c.LAST_6M_COST_OF_INST * 2 -
c.pd * (c.last_6m_bal_avg + c.last_6m_inst_avg))) AS roa_fz,
SUM((c.last_6m_bal_avg + c.last_6m_inst_avg)) AS roa_fm
FROM czh_new_dist_1312 a,
riskrept.rko_acct_snap_his PARTITION(P201406) b,
riskdw.crlimset_roa_his PARTITION(P201406) c
WHERE a.delq_level = '0'
AND a.acct = b.acct
AND a.acct = c.acct
GROUP BY a.CUST_TYPE_V1,
a.CUST_TYPE_V2,
a.CUST_TYPE_V3,
a.CURRENT_FLAG,
a.ACTIVE_FLAG,
a.ACTIVE2_FLAG,
a.BSCORE_SEG,
b.month_stamp,
b.DELQ_LEVEL
