統計資訊不準確導致執行計劃走了笛卡爾積

lhrbest發表於2014-08-20


統計資訊不準確導致執行計劃走了笛卡爾積


 

昨天有事沒有上班,今天早上來檢視系統的時候發現了很多笛卡爾積的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/26736162/viewspace-1254942/,如需轉載,請註明出處,否則將追究法律責任。

相關文章