RE: SQL Tuning Question

Keep in mind the common misconception that any cost that is higher is worse – that is not always true. I have no idea in your case – but don’t just always assume higher cost means worst performance ☺

From: [email protected] [mailto:[email protected]] On Behalf Of Lisa Hu
Sent: Monday, February 27, 2012 6:03 PM
To: [email protected]
Subject: Re: [toad] 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]<mailto:[email protected]>>
To: "'[email protected]'" <[email protected]<mailto:[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]> [mailto:[email protected]]<mailto:[mailto:[email protected]]> On Behalf Of Lisa Hu
Sent: Monday, February 27, 2012 3:46 PM
To: [email protected]<mailto:[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.