RE: SQL Tuning Question

Yeah, the hard part of this is your statement that "I can't run the 10046 on Update process".

Given that, we're mostly limited to guessing. We can't trace the actual detailed resource use.

How similar is the test environment? Do you have the option of getting real data volumes over there, and then doing a trace or using SQL*Optimizer to test out a bunch of query rewrites? You might find a hint that would help.

Is it possible to run the process on production when there's not much else going on, so you can see if the delay is because of a change in this process or its data, vs. a change in something else in the environment ("Oh, Joe just added 3 big jobs to this processing window").

I'm assuming you don't have a stored outline from the period when things were running fine? That would be great, but it never happens in real life. Hardly anybody managed to keep that sort of data; I know we don't.

Can you maybe take the update statement, create a pure query version, and run that on production with trace turned on? Might tell you something.

Hard to do much more remotely. Except to say we've all been there. Good luck.

-- jim

________________________________
From: [email protected] [mailto:[email protected]] On Behalf Of Lisa Hu
Sent: Tuesday, February 28, 2012 08:15 AM
To: [email protected]
Subject: Re: [toad] 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]