ITPub博客

首页 > 数据库 > Oracle > 统计信息不准确导致执行计划走了笛卡尔积

统计信息不准确导致执行计划走了笛卡尔积

Oracle 作者:xiaoyan5686670 时间:2016-01-15 10:36:24 0 删除 编辑


统计信息不准确导致执行计划走了笛卡尔积


 

昨天有事没有上班,今天早上来查看系统的时候发现了很多笛卡尔积的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为5r911ty8dnkwksql在内存中的执行计划:

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来优化,系统RUBBISHsql太多,优化不过来的,先解决主要矛盾呗

 

 

 

附加例子,有一个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/22569416/viewspace-1976932/,如需转载,请注明出处,否则将追究法律责任。

上一篇: tail实现断点续传
请登录后发表评论 登录
全部评论

注册时间:2012-07-25

  • 博文量
    108
  • 访问量
    196564