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

 

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