Home » RDBMS Server » Performance Tuning » Slow queries, configuration problem ? (Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit)
Slow queries, configuration problem ? [message #425078] Wed, 07 October 2009 07:14 Go to next message
daff24
Messages: 11
Registered: June 2008
Junior Member
What does it exactly mean when query runs ca 1 minute after we have made 'alter system flush buffer_cache' ? The second time it already runs under 1 sec.

As much as I understand when application executes the query it is exactly in the situation where we are after flushing buffer_cache.

The query itselt seems to be nothing difficult, no full table scans and cost only 76.

We also have many other queries that act the same way. Could this be a symptom that something is wrong with server settings?
Re: Slow queries, configuration problem ? [message #425082 is a reply to message #425078] Wed, 07 October 2009 07:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Could this be a symptom that something is wrong with server settings?

No.

Regards
Michel
Re: Slow queries, configuration problem ? [message #425097 is a reply to message #425078] Wed, 07 October 2009 09:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you've flushed the buffer cache, then the query will have to read all the blocks in from the disk.

The second time you run the query, (almost) all the data the query needs is already in memory (in the buffer cache) and it can be accessed far quicker.

Why are you flushing the buffer cache?
Re: Slow queries, configuration problem ? [message #425221 is a reply to message #425097] Thu, 08 October 2009 02:13 Go to previous messageGo to next message
daff24
Messages: 11
Registered: June 2008
Junior Member
Quote:
Why are you flushing the buffer cache?


Maybe I get this all wrong. Let me tell my story.

Client system of our application says that some query is slow. So I take the query and try to run it. And it really takes time. So what ever I change and run the query again, it runs fast.

So why I have been flushing buffer cache is to simulate situation when client comes and executes this query.

Or I should assume that the data is always on buffer_cache? In my opinion this isn't possible ? Or when the specific data gets into buffer cache?

Thanks!
Re: Slow queries, configuration problem ? [message #425252 is a reply to message #425221] Thu, 08 October 2009 04:40 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Flushing the buffer cache is a reasonable way to replicate the performance of a query over infrequently accessed data. Just don't do it on your live production box.

Even if the data is accessed more frequently in Production, still flushing the buffer cache will give you a nice baseline that allows you to compare two different plans in a test environment; it just won't be comparable to a real production run.

Note that due to the effect of disk caching, this method is not perfect. The first time you run your query it reads it off the disk. The second time - after flushing the Buffer Cache - it still goes to the disk but the disk has some/all data in its own cache.

Ross Leishman
Re: Slow queries, configuration problem ? [message #425480 is a reply to message #425078] Fri, 09 October 2009 05:23 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Here is an interesting article that touches on the cursor cache.

Kevin
Re: Slow queries, configuration problem ? [message #425483 is a reply to message #425078] Fri, 09 October 2009 05:27 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Here is an interesting article on the cursor cache.

And some supporting doc to go with it.

Just FYI, Kevin
Re: Slow queries, configuration problem ? [message #425587 is a reply to message #425483] Fri, 09 October 2009 16:34 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Glad you posted that first link. I didn't know you could flush a single cursor out of the shared pool. That's two hits in two days for the KM school of tuning!

But this thread is talking about the relative merits of flushing the buffer cache (cached data blocks), not the shared pool.

Ross Leishman
Re: Slow queries, configuration problem ? [message #425589 is a reply to message #425587] Fri, 09 October 2009 16:37 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Actually, it's worth noting that for a better comparative baseline of two queries, flushing the cursor from the shared pool (Kevin's link) in addition to flushing the blocks from the buffer cache will give an even more stable comparison.

Ross Leishman
Previous Topic: regarding AWK in oracle
Next Topic: Need help on Performance tuning
Goto Forum:
  


Current Time: Sun Dec 11 04:22:26 CST 2016

Total time taken to generate the page: 0.10321 seconds