Oracle資料庫的SQL效能問題分析

在Oracle 10.2.0.4資料庫中，有一個SQL執行緩慢，超過數分鐘無結果，等待事件又是閒置 SQL*Net message事件，最後只好強行中斷。

這個SQL是一個普通的UPDATE語句，where子句中多張表 關聯，關聯的表都是暫存資料表。

update t_fund_product_info       set is_valid = 'N'   where prdt_id not in         (select a.prdt_id              from tmp_crm_DX_PRDT_FOR_INFO a, tmp_crm_PRDT_CATA_FOR_INFO b             where a.prdt_type = '501040'             and a.prdt_id = b.prdt_id               and b.prdt_key in (select distinct prdt_id                                    from tmp_crm_PRDT_CHANNEL                                   where dg_ch = 'XX商城'));

這個SQL語句，多次執行都沒有出來結果。

在PL SQL DEV中F5得到的執行計畫是這樣：

UPDATE STATEMENT, GOAL = ALL_ROWS          UPDATE    SCOTT    T_FUND_PRODUCT_INFO    FILTER            TABLE ACCESS FULL    SCOTT    T_FUND_PRODUCT_INFO     NESTED LOOPS             NESTED LOOPS              TABLE ACCESS FULL    SCOTT    TMP_CRM_DX_PRDT_FOR_INFO       TABLE ACCESS FULL    SCOTT    TMP_CRM_PRDT_CATA_FOR_INFO      TABLE ACCESS FULL    SCOTT    TMP_CRM_PRDT_CHANNEL

雖然都是全表掃描，但表也不大，都是幾千條記錄。 NESTED LOOPS    （嵌套串連）也 沒有什麼問題。

(miki西遊 @mikixiyou 原文連結: http://mikixiyou.iteye.com/blog/1755048)

我在表t_fund_product_info的欄位prdt_id上建立主鍵，執行就OK。得到的執行計畫是這樣：

UPDATE STATEMENT, GOAL = ALL_ROWS          UPDATE    SCOTT    T_FUND_PRODUCT_INFO    HASH JOIN RIGHT ANTI            VIEW    SYS    VW_NSO_1      HASH JOIN            HASH JOIN             TABLE ACCESS FULL    SCOTT    TMP_CRM_PRDT_CHANNEL        TABLE ACCESS FULL    SCOTT    TMP_CRM_PRDT_CATA_FOR_INFO       TABLE ACCESS FULL    SCOTT    TMP_CRM_DX_PRDT_FOR_INFO     TABLE ACCESS FULL    SCOTT    T_FUND_PRODUCT_INFO

這個執行符合我的要求，是HASH JOIN RIGHT ANTI（雜湊反串連）。

這個庫建立的，表剛剛建立 。檢查user_tables，確認所有的表都沒有被分析過。插一句，資料庫的配置沒有問題。於是，手工分析 一下這四張表。

Trc代碼

       TABLE_NAME    NUM_ROWS    BLOCKS    LAST_ANALYZED  TMP_CRM_DX_PRDT_FOR_INFO    3624    103    2012-12-27 14:33:22  TMP_CRM_PRDT_CATA_FOR_INFO    2149    20    2012-12-27 14:05:02  TMP_CRM_PRDT_CHANNEL    6695    58    2012-12-27 14:05:02  T_FUND_PRODUCT_INFO    875    13    2012-12-27 14:05:02