RE: SQL Tuning Question

Good idea on stats – very possible that tables and/or indexes grew and crossed a threshold and performance suffered. DBA’s in 10G need to create jobs to keeps key schema stats updated lest these kinds of things can happen.

From: [email protected] [mailto:[email protected]] On Behalf Of Lisa Hu
Sent: Monday, February 27, 2012 4: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