统计信息不准确导致执行计划走了笛卡尔积
昨天有事没有上班,今天早上来查看系统的时候发现了很多笛卡尔积的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
ORDER 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;
其他3个sql都是一样的,只是表a变了,
大概看了一下几个sql语句,涉及到的都是同几个表,所以这里列出其中一个执行计划,查看sqlid为5r911ty8dnkwk的sql在内存中的执行计划:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('5r911ty8dnkwk',0,'advanced'));
这里典型的是rows都为1,可以大胆揣测是统计信息有问题导致sql的执行计划走了笛卡尔积的连接了,有关这个rows还有一个例子在我的blog上,连接如下:,另外从执行计划可以看出2个分区表,第一个分区表是RKO_ACCT_SNAP_HIS,是第90个分区统计信息有问题,第二个分区表是CRLIMSET_ROA_HIS,是第54个分区的统计信息有问题,
好吧,我们先看一下第一个表的相关分区的统计信息:
SELECT v.TABLE_NAME,
v.partitioning_type,
v.PARTITION_NAME,
v.partition_size,
v.LAST_ANALYZED,
v.NUM_ROWS,
v.BLOCKS,
v.HIGH_VALUE2
FROM VW_TABLE_PART_LHR V
WHERE V.TABLE_NAME = 'RKO_ACCT_SNAP_HIS'
AND v.PARTITION_POSITION >= 85;
由图可以看出6月和7月的分区分别为13G和14G,但是统计行数却为0,另外分析时间可以看出是13年6月的,这个很老了的,,,,,好吧,分别运行如下脚本收集这2个分区的统计信息,当然对于当前脚本我们只需要分析6月这个分区即可,但是发现问题了就一并解决了呗:
BEGIN
dbms_stats.gather_table_stats('RISKREPT',
'RKO_ACCT_SNAP_HIS',
partname => 'P201406',
cascade => TRUE,
granularity => 'PARTITION',
degree => 8);
END;
BEGIN
dbms_stats.gather_table_stats('RISKREPT',
'RKO_ACCT_SNAP_HIS',
partname => 'P201407',
cascade => TRUE,
granularity => 'PARTITION',
degree => 8);
END;
收集完成后,看看统计信息,发现6月有2000W的数据量:
2个分区的统计信息收集完成后,我们在plsql developer 中查看一下执行计划,可以看出还是有笛卡尔积的:
那说明第二个表的统计信息仍然有问题,继续看看第二个表CRLIMSET_ROA_HIS:
SELECT v.TABLE_NAME,
v.partitioning_type,
v.PARTITION_NAME,
v.partition_size,
v.LAST_ANALYZED,
v.NUM_ROWS,
v.BLOCKS,
v.HIGH_VALUE2,
v.PARTITION_POSITION
FROM VW_TABLE_PART_LHR V
WHERE V.TABLE_NAME = 'CRLIMSET_ROA_HIS'
AND v.PARTITION_POSITION >= 50
and v.TABLE_OWNER='RISKDW';
可以看出6月的统计信息数据是有问题的,那么就继续分析第二个表CRLIMSET_ROA_HIS:
BEGIN
dbms_stats.gather_table_stats('RISKDW',
'CRLIMSET_ROA_HIS',
partname => 'P201406',
cascade => TRUE,
granularity => 'PARTITION',
degree => 8);
END;
好吧,分析完成后我们仍然在plsql developer中看一下执行计划,这次发现没有笛卡尔积了:
总结:
这个例子只是想说明统计信息的重要性,以及如何预测统计信息是否有问题,并没有针对该sql来优化,系统RUBBISH的sql太多,优化不过来的,先解决主要矛盾呗
附加例子,有一个job的插入语句也跑了2天多的时间了,看了下sql也是上边的例子中的表riskrept.rko_acct_snap_his引起的,简单记录一下:
SELECT a.SQL_TEXT,
a.SQL_ID,
a.ELAPSED_TIME 已运行时间
FROM VW_SQL_RUBBISH_LHR a
WHERE a.SID = 189;
插入语句为:
INSERT /*+append*/
INTO temp_H_RPC_GOLDCARD_RAW_SNAP
(MONTH_STAMP,
PARTY_NO,
ACCT,
RMB_CREDIT_LIMIT_6,
RMB_TEMP_LIMIT_6,
POST_RMB_PURCHASE_AMT_6,
INDUSTRY_TYPE,
ACTIVE_CARD_CNT_6,
CITY,
AI,
Source_code,
INST_RMB_UNPOSTING_AMT_6)
SELECT b.MONTH_STAMP,
a.PARTY_NO,
a.ACCT,
a.RMB_CREDIT_LIMIT,
a.RMB_TEMP_LIMIT,
a.POST_RMB_PURCHASE_AMT,
a.INDUSTRY_TYPE,
a.ACTIVE_CARD_CNT,
a.CITY,
a.AI,
a.Source_code,
a.INST_RMB_UNPOSTING_AMT
FROM riskrept.rko_acct_snap_his PARTITION(p201407) a,
temp_H_RPC_GOLDCARD_IND_ACCT b
WHERE a.acct = b.acct
AND 201407 = b.month_stamp;
内存中的执行计划:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('cjyfzyhhkpmvg',0,'advanced'));
看来是统计信息不准确导致走了nl连接了,由于在第一个例子中已经分析过表了,这里直接新的执行计划,新的执行计划是hash_join连接,应该没有问题的:
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26736162/viewspace-1254942/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26736162/viewspace-1254942/