Re: SQL Tuning Question

Thank you for all tips.  I added a hint in to rid of FTS.  However, the cost went to 3 times higher.  Because it's a production DB, I haven't tested it the run time with a hint.  The Dev and Test DBs are fine.  I'll rebuild indexes and update stats during maintenance window tonight, and keep you updateed.  
Thanks again,
Lisa


________________________________
From: "Simoneau, Roger" <[email protected]>
To: "'[email protected]'" <[email protected]> 
Sent: Monday, February 27, 2012 5:46 PM
Subject: RE: [toad] SQL Tuning Question


  
One possible big hit is the full table scan on the table you are updating.  That would be dependent on the number of rows in your update table.
 
The explain plan shows prembr_834_data_KY table doing a full table scan with an index usage against the prembr_834_lockupdt_KY table.  That seems like a likely candidate for your issue.
 
It would make more sense if your update table had fewer rows in it then your primary table.  After you update the stats, this part of the explain plan may reverse itself and correct the issue.
 
Roger S.
 
 
From:[email protected] [mailto:[email protected]] On Behalf Of Lisa Hu
Sent: Monday, February 27, 2012 3:46 PM
To: [email protected]
Subject: [toad] SQL Tuning Question
 
  
Dear all,
I know it’s not a Toad related issue, but would like to ask experts’ help in tuning an Update process.  One Update suddenly ran 2 hours vs. normally it runs 10 + minutes.  To my knowledge, nothing was changed (of course).  Following is the SQL and execution plan.  The DB is in 10g.  Any help will be greatly appreciated.  I am planning to rebuild the indexes and then gather new stats just for sake of better performance.
UPDATE prembr_834_data_KY pd1  
   SET mbr_age = trunc(to_number(file_effective_date - to_date(mbr_ymdbirth,'yyyymmdd'))/365.25 ),  
   class_value =  
   ( 
   SELECT DISTINCT (trim(lockin_grp_new)||trim(lockin_div_new))  
   FROM prembr_834_lockupdt_KY pu1  
   WHERE pu1.mbr_medicaid_id = pd1.mbr_medicaid_id  
  AND trim(pu1.class_value_834) = trim(pd1.class_value)  
   )  
WHERE mbr_medicaid_id in  
   ( 
   SELECT mbr_medicaid_id  
   FROM prembr_834_lockupdt_KY 
  );
Execution Plan:
UPDATE STATEMENT Optimizer=ALL_ROWS (Cost=758)
 UPDATE OF PREMBR_834_DATA_KY
  HASH JOIN (RIGHT SEMI) (Cost=758 Cardinality=3992 Bytes=331336)
   INDEX (FAST FULL SCAN) OF IX_PREMBR_834_LOCKUPDT_01 (INDEX) (Cost=5 Cardinality=2347 Bytes=35205)
   TABLE ACCESS (FULL) OF PREMBR_834_DATA_KY (TABLE) (Cost=752 Cardinality=3992 Bytes=271456)
  HASH (UNIQUE) (Cost=11 Cardinality=1 Bytes=53)
   TABLE ACCESS (BY INDEX ROWID) OF PREMBR_834_LOCKUPDT_KY (TABLE) (Cost=10 Cardinality=1 Bytes=53)
    INDEX (RANGE SCAN) OF IX_PREMBR_834_LOCKUPDT_01 (INDEX) (Cost=1 Cardinality=9)
Thanks,
Lisa

________________________________
This communication, including any attached documentation, is intended only for the person or entity to which it is addressed, and may contain confidential, personal and/or privileged information. Any unauthorized disclosure, copying, or taking action on the contents is strictly prohibited. If you have received this message in error, please contact us immediately so we may correct our records. Please then delete or destroy the original transmission and any subsequent reply.