Hi Aman and Sb

I think I misunderstand the concept:

ALTER SYSTEM FLUSH BUFFER CACHE;

This statement do not delete the data inside the buffer cache, it writes everything to inside to cache to disk. (like checkpoint)
Is that correct?

Nope. You must remember tthat checkppointing is an event which further causes ddbwr to write. Iit itself doesnt write and neither does ckpt does any io by iself. at flsh command, indeed cache is made empty and data is writeen to disk.

HTH
Aman.....

yup its correct!

Aman....

PS- If you are clear with question, mark the helpful an correct replies and close the thread.

Aman....

The purpose of "alter system flush buffer cache" is to make all the buffers in the buffer cache free. However a buffer cannot become free if it is "pinned" (i.e. actualy in use) or if it is "dirty" (i.e. needs to be written to disc). There's nothing that Oracle can do about the pinned buffers, but it can write the content of the dirty buffers to disk before freeing them. Your description here is a good enough approximation.

Side not regarding checkpoints: there was a link to a list of checkpoint reasons earlier on, but things keep changing in Oracle and the manuals (and commentary) don't always keep up. In 10.2 there are lots more checkpoints than shown in the previous list. (I've published a list, which I think was complete, [of checkpoint names|http://jonathanlewis.wordpress.com/2007/04/12/log-file-switch/] on my blog a couple of years ago).

If you're interested in seeing the effect of the call, you could run this query (as SYS) before and after:

select 
	status, count(*) 
from 
	v$bh 
group by 
	status
;

Responding to another detail mentioned above relating to that list of checkpoints - again things change. A log file switch used to result in an immediate thread checkpoint, but in 10.2 Oracle got even lazier about clearing the dirty blocks, and will only start a thread checkpoint if the switch takes it into the "last available" redo log. Otherwise it just hopes that the incremental checkpointing activity will allow the "first" log file to become free again in time for the next switch.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk

"For every expert there is an equal and opposite expert."
Arthur C. Clarke

That is correct - the impact and side effects are quite different though.
It's possible that the rate at which the database writer writes may be different (there is a "priority" concept that might be set differently for the two commands); and when you flush the cache you (or other sessions) may have to re-read the data that they had been using if they still need it, so the flush could be followed by an enormous I/O load as these reads take place.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk

New Scientist: "Would you prefer a cautious expert or a confident ignoramus"
http://www.newscientist.com/article/mg20227115.500-humans-prefer-cockiness-to-expertise.html

There are four or five effects that could come together to cause this:

a) The flush could cause an I/O overload on the disc subsystem

b) As current blocks are written from this note, interconnect traffic has to go to other nodes to tell them to flush their PI buffers.

c) As buffers are freed, the global cache resources and locks have to be updated and released, leading to increased CPU and interconnect traffic. You will have local shadows for every buffer freed and remote resources for typically (n-1)/nths (viz. half in the case of two nodes) of the buffers.

d) As the cache clears, other processes on your node (and on the nodes which have released their PIs) are likely to want re-read at least some of the blocks from disc - so the I/O subsystem picks up another load, and the interconnect gets another load as global resources and locks are negotiated and re-acquired.

e) If you're unlucky, the sudden demand for reads on a specific object from a specific node may result in a "DRM freeze" and object-remastering takes place.

Any of the above might be sufficient to cause enough of a delay to result in node eviction if the volume of dirty data and rate of data change are relatively high.

All in all, if anything is going to give you an easy way to crash out a node, flushing the buffer cache on a busy system is a good candidate for the job.

I think it's probably more likely to happen if you're running slightly older versions of Oracle at the smaller end of the Linux range.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk

"For every expert there is an equal and opposite expert."
Arthur C. Clarke