Analysis of SQL performance problems in Oracle database

In Oracle 10.2.0.4 database, there is a slow SQL execution, more than a few minutes without results, waiting for the event is idle Sql*net message events, and finally forced to break.

This SQL is an ordinary UPDATE statement that has multiple tables associated in the WHERE clause and the associated tables are temporary tables.

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 mall '));

This SQL statement, repeated execution does not come out the result.

The implementation plan F5 in PL SQL Dev is this:

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

Although all are full table scans, but the table is not small, are thousands of records. There is no problem with NESTED LOOPS (nested connections).

(Miki West Tour @mikixiyou original link: http://mikixiyou.iteye.com/blog/1755048)

I set the primary key on the field prdt_id of the table t_fund_product_info, and the execution is OK. The resulting implementation plan is this:

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

This execution meets my requirements and is hash JOIN right ANTI (hash back join).

This library is new and the table is just new. Check the user_tables to make sure all the tables have not been analyzed. In a word, there is no problem with the configuration of the database. So, hand-analyzed these four tables.

TRC Code

       table_name    num_rows    BLOCKS    last_analyzed  
tmp_crm_dx_prdt_for_info    3624    2012-12-27 14:33:22  
tmp_crm_prdt_cata_for_info    2149    2012-12-27    14:05:02 Tmp_crm_prdt  
_channel    6695    2012-12-27 14:05:02  
t_fund_product_info 875    2012-12-27 14:05:02