Re: SQL Tuning Question

I rebuilt all indexes and ran Gathertable_stats.  The Update ran 30 minutes with a similar execution plan which is still longer than normal.  Because it’s a delivered patch process, I can’t run the 10046 on Update process.  I did run the AWR, ADDM, ASH reports on database and found that there were many Commit processes which used 80% system resources during poor performance period.  I don't know where these Commits come from.  
Thanks,
Lisa

________________________________
From: Norman Dunbar <[email protected]>
To: [email protected] 
Sent: Tuesday, February 28, 2012 2:17 AM
Subject: Re: [toad] SQL Tuning Question


  
Hi Lisa,

On 28/02/12 00:03, Lisa Hu wrote:
> Thank you for all tips. I added a hint in to rid of FTS. However, the
> cost went to 3 times higher.

I suspect the FTS is part of your main WHERE clause:

> WHERE mbr_medicaid_id in
> (
> SELECT mbr_medicaid_id
> FROM prembr_834_lockupdt_KY
> );

Getting rid of that FTS might well increase the execution times which 
would account for the cost going up.

I would suggest that the best way to find out what is really happening 
is to add an alter session set events '10046 trace name context forever' 
before the statement and 'alter session set events '10046 trace name 
context off' after it.

The trace file generate will show you exactly where the time is being 
spent. You can test it manually in a Toad session just by running the 
alter, update, alter.

HTH

-- 
Cheers,
Norm. [TeamT]