統計資訊不準確導致執行計劃走了笛卡爾積
統計資訊不準確導致執行計劃走了笛卡爾積
昨天有事沒有上班,今天早上來檢視系統的時候發現了很多笛卡爾積的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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- ORACLE analyse table方式收集表統計資訊導致SQL執行計劃不準確而效能下降OracleSQL
- 統計資訊不正確導致執行計劃的錯誤選擇
- sql中使用函式導致explain plan for和set autotrace得到執行計劃不準確SQL函式AI
- set autotrace on 產生不準確的執行計劃
- 【TUNE_ORACLE】列出走了笛卡爾積的SQL參考OracleSQL
- 笛卡爾積的應用——商品 SKU 計算
- 一次ORACLE SQL謂詞跨界導致的執行計劃不準OracleSQL
- java 笛卡爾積(迴圈)Java
- 怎樣得到準確的執行計劃
- _optimizer_invalidation_periond導致收集統計資訊後執行計劃沒有改變
- date列統計資訊陳舊導致sql沒有選擇最優執行計劃SQL
- 執行計劃錯誤導致系統負載高負載
- 笛卡爾積與全連線
- Grant許可權導致執行計劃失效
- 【PG執行計劃】Postgresql資料庫執行計劃統計資訊簡述SQL資料庫
- php計算多個集合的笛卡爾積例項詳解PHP
- 統計資訊過舊導致SQL無法執行出來SQL
- 完美的執行計劃導致的效能問題
- 執行計劃的偏差導致的效能問題
- autotrace 和explain plan for可能導致執行計劃錯誤AI
- 看執行計劃是否正確
- 二維陣列笛卡爾積js實現陣列JS
- Oracle的表連線方法(四)笛卡爾積Oracle
- 【YashanDB知識庫】收集分割槽表統計資訊取樣率小於1導致SQL執行計劃走偏SQL
- expdp不導資料而匯出統計資訊導致統計資訊鎖定(ORA-20005)
- 資料庫統計資訊不更新導致的效能問題資料庫
- 笛卡爾樹
- 執行計劃__獲取方法、檢視執行順序、統計資訊詳解
- Python如何從列表中獲取笛卡爾積Python
- [JavaScript] 求解任意n個集合的笛卡爾積JavaScript
- 笛卡爾積和NEST LOOP產生的影響OOP
- 11G的SORT GROUP BY NOSORT導致錯誤執行計劃
- 手工收集統計資訊及立即產生新的執行計劃
- oracle執行計劃與統計資訊的一些總結Oracle
- 笛卡爾乘積的javascript版實現和應用JavaScript
- 成績錄入SQL語句 笛卡爾積 LEFT JOINSQL
- 執行計劃變化導致CPU負載高的問題分析負載
- 導致代理IP驗證不準確的四種原因