Home » RDBMS Server » Performance Tuning » Inconsistent performance in database (Oracle 10g)
Inconsistent performance in database [message #387463] Thu, 19 February 2009 05:47 Go to next message
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 #387470 is a reply to message #387463] Thu, 19 February 2009 06:01 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Quote:
Can someone please let me know what are the potential reasons for this behaviour?



far too many to list.

Run statspack/awr/oracle tracing and find out where the time is actually being spent.
Re: Inconsistent performance in database [message #387480 is a reply to message #387470] Thu, 19 February 2009 07:13 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
BlackSwan
Messages: 25050
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: Inconsistent performance in database [message #388218 is a reply to message #387463] Mon, 23 February 2009 22:25 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Thanks for the clarification which I agree with.
Previous Topic: tuning
Next Topic: SQL tuning (merged)
Goto Forum:
  


Current Time: Sat Dec 10 17:00:11 CST 2016

Total time taken to generate the page: 0.07989 seconds