| Inconsistent performance in database [message #387463] |
Thu, 19 February 2009 05:47  |
prashas_d
Messages: 66
Registered: February 2007
|
Member |
|
|
Hi All,
Our database is behaving inconsistently. For the same input, one day it is working at a speed of 1Million records in 10 minutes; but on the other day it is running at a speed of 20K records in 30 minutes.
There are no other processes running in parallel.
Can someone please let me know what are the potential reasons for this behaviour?
################################################
Our code logic is as follows:
We have a total of 8 tables. Out of that, one table is the base table. Using this base table we are using a merge command to populate it in other 7 tables.
################################################
Thanks in advance.
prashas_d.
|
|
|
|
|
|
| Re: Inconsistent performance in database [message #387480 is a reply to message #387470] |
Thu, 19 February 2009 07:13  |
ahudspith
Messages: 26
Registered: January 2009
Location: Avoiding the tax man.
|
Junior Member |
|
|
I would speculate that:
You do a lot of inserts or updates and it runs at one speed.
You do this again and it runs much slower.
<automatic stale stats job or similar kicks in>
You do the same test again and it runs much quicker....
Check for stale stats prior/during a "bad" load.
It's just a guess - but its worth looking into.
Adam
|
|
|
|
| Re: Inconsistent performance in database [message #388212 is a reply to message #387480] |
Mon, 23 February 2009 19:22 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Another of the many possibilities is bind-variable peeking.
When a new query is run, 10g will peek at the bind variable values and optimise accordingly. Subsequent runs will reuse the same plan without peeking - the plan may not be optimal for other bind values.
The problem comes when you don't use the SQL for a while. It drops off the cache, you rerun it with some skewed bind values, it chooses a plan appropriate only for the skewed values, then every subsequent run using every-day values is slow.
Flushing the shared pool should fix this problem whenever it occurs.
Ross Leishman
|
|
|
|
| Re: Inconsistent performance in database [message #388213 is a reply to message #387463] |
Mon, 23 February 2009 19:31 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
Ross,
Most, most, most of the time you provide excellent advice.
>Flushing the shared pool should fix this problem whenever it occurs.
From my perspective, above is bad, bad, bad advice.
It throws the baby out with the bath water.
It invalidates every SQL within the shared pool in a feeble attempt to improve performance a single SQL statement.
When a single SQL runs slow, SQL_TRACE should be enabled to actually see where time is being spent.
Once you know where time is being spent, then decide how to reduce the elapsed time.
Alternatively, lock in a "good" execution plan so it does not "get confused" & executes a "bad" plan.
[Updated on: Mon, 23 February 2009 19:37] Report message to a moderator |
|
|
|
| Re: Inconsistent performance in database [message #388215 is a reply to message #388213] |
Mon, 23 February 2009 19:43 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Sorry, my explanation was a bit ... well ... incomplete.
The first step is to discard the bad plan. I don't know how to do that other than flushing the pool or bouncing the DB.
Once you're over the hump, you can address the real problem and stop it re-occurring. Pinning the good plan, using outlines / plan stability, or using hints are all options.
All this assumes that peeking is problem. Let's just remind the OP that it could be MANY different things.
Ross Leishman
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
