| Slow queries, configuration problem ? [message #425078] |
Wed, 07 October 2009 07:14  |
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 #425097 is a reply to message #425078] |
Wed, 07 October 2009 09:34  |
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 |
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 |
rleishman
Messages: 3728
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 #425587 is a reply to message #425483] |
Fri, 09 October 2009 16:34 |
rleishman
Messages: 3728
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 |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
