Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: November 08, 2011 - 3:57 pm UTC

Last updated: November 11, 2011 - 10:56 am UTC

Version: 10.2.0.4

Viewed 10K+ times! This question is

You Asked

Hi Tom - I am debugging a performance issue and the first time I ran the query it took 10 mins to complete. When I ran it the second time, the query came back in 20 secs and has been consistently at 20 secs ever since. I did the following but still the query comes back in 20 secs. SQL> alter system flush buffer_cache;
SQL> alter system flush shared_pool;
Why am I not able to replicate the issue and how do I do it other than bouncing the database. What does flushing the shared_pool and buffer_cache do ?

and Tom said...

definitely sounds like the

"secondary sga"

effect - search this site for that term in quotes to read more about it. But basically I'll guess that you are running on a normal file system, it is a buffered file system. You have plenty of free memory for the OS. The first time you ran the query - the blocks needed by the query were not in the SGA so we had to do a physical IO. When we did the physical IO - the OS had to actually read it in from disk because that block had never been requested before. BUT, since there is plenty of free memory - the OS cached the block as well.

The second time you ran the query - the block came either from the SGA or the file system buffer cache - physical IO's weren't truly physical IO's anymore (although we can only report them as such - we have no idea if the OS went to disk or its own cache).

Even when you flushed the buffer cache - the physical IO went to the OS cache - not to disk.

You'd have to

a) reboot the server to get the initial query response time again OR
b) do a ton of other physical IO to overwrite the contents of the file system cache OR
c) increase the size of the SGA so the OS doesn't have room to cache.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you

A reader, November 09, 2011 - 8:59 am UTC

Thanks a ton Tom, that helps. I will try one of the recommendations.

Flush filesystem cache

mark, November 11, 2011 - 9:35 am UTC

Hi

If you are running Linux and using cooked filesystems for your datafiles I think you can get your system admin to run the following to flush out the filesystem cache and help support the theory of the secondary SGA:

sync; echo 3 > /proc/sys/vm/drop_caches

....might be easier to get done than a reboot....

I guess there can also be caching at other layers that cannot be disregarded (SAN controllers etc).

Tom Kyte
November 11, 2011 - 10:56 am UTC

I guess there can also be caching at other layers that cannot be disregarded


correct, and even a reboot wouldn't get those.


very nice though, thanks for that

http://www.linuxinsight.com/proc_sys_vm_drop_caches.html

has more info, could be handy.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.