資料庫統計資訊不更新導致的效能問題

heroqsx發表於2008-05-06
資料庫

SQL如下，系統動態生成的，主要是檢視符合多個條件付款對應的所有明細記錄。語句本身有問題，因為，多個in可以合併成一個in，相關的條件and起來即可，這個暫時忽略。

SELECT doc.* FROM TLK_PAYMENT_DETAIL doc
WHERE ISTMP=0 AND (((((((ITEM_contractnumber = '06603833'))
AND (doc.parent in (select parentdoc.id from TLK_TEPAYMENT_MAIN parentdoc where parentdoc.ITEM_單據型別 IN ('4','8'))))
AND (doc.parent in (select parentdoc.id from TLK_TEPAYMENT_MAIN parentdoc where parentdoc.ITEM_IsReverse = '0')))
AND (doc.parent in (select parentdoc.id from TLK_TEPAYMENT_MAIN parentdoc where parentdoc.stateint = 1048576)))
AND (doc.parent in (select parentdoc.id from TLK_TEPAYMENT_MAIN parentdoc where parentdoc.istmp <> 1)))
AND ((ITEM_expensecode = '33000100')))

[@more@]

執行計劃: (第4步是一個笛卡爾集！)

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
| 1 | NESTED LOOPS | | 1 | 20853 | 0 (0)|
| 2 | NESTED LOOPS | | 1 | 20338 | 0 (0)|
| 3 | NESTED LOOPS | | 1 | 19823 | 0 (0)|
| 4 | MERGE JOIN CARTESIAN | | 1 | 2008 | 0 (0)|
| 5 | TABLE ACCESS BY INDEX ROWID | TLK_TEPAYMENT_MAIN | 1 | 1004 | 0 (0)|
|* 6 | INDEX SKIP SCAN | TLK_TEPAYMENT_MAIN_06 | 1 | | 0 (0)|
| 7 | BUFFER SORT | | 1 | 1004 | 0 (0)|
| 8 | INLIST ITERATOR | | | | |
| 9 | TABLE ACCESS BY INDEX ROWID| TLK_TEPAYMENT_MAIN | 1 | 1004 | 0 (0)|
|* 10 | INDEX RANGE SCAN | TLK_TEPAYMENT_MAIN_06 | 1 | | 0 (0)|
|* 11 | TABLE ACCESS BY INDEX ROWID | TLK_PAYMENT_DETAIL | 1 | 17815 | 0 (0)|
|* 12 | INDEX RANGE SCAN | TLK_TEPAYMENT_DETAIL_INDEX | 1 | | 0 (0)|
|* 13 | TABLE ACCESS BY INDEX ROWID | TLK_TEPAYMENT_MAIN | 1 | 515 | 0 (0)|
|* 14 | INDEX UNIQUE SCAN | SYS_C006361 | 1 | | 0 (0)|
|* 15 | TABLE ACCESS BY INDEX ROWID | TLK_TEPAYMENT_MAIN | 1 | 515 | 0 (0)|
|* 16 | INDEX UNIQUE SCAN | SYS_C006361 | 1 | | 0 (0)|
-----------------------------------------------------------------------------------------------------

思考了一下，為什麼系統選擇產生笛卡爾集？笛卡爾集未必是壞事，對小的結果集，這種操作還會省時間，但這裡，對兩個大的結果集產生笛卡爾集，說明系統認為這兩個大的結果集"不大"，也就是說系統的統計資訊有誤，呼叫dbms_stats相關的函式gether statistics即可

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/136097/viewspace-1003464/，如需轉載，請註明出處，否則將追究法律責任。

相關文章