1 2 Previous Next 26 Replies Latest reply: Jun 14, 2009 5:00 PM by 706417 Go to original post RSS
      • 15. Re: flush buffer cache
        706712
        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?
        • 16. Re: flush buffer cache
          Girish Sharma
          I too found the same link for the topic in this thread; but i am not finding the documentation link which says that checkpoint will occur in the following events. Can you please post the link from official documentation of 9i and/or 10g?

          Regards
          Girish Sharma
          • 17. Re: flush buffer cache
            Aman....
            Zapotocny wrote:
            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.....
            • 18. Re: flush buffer cache
              706712
              so as a result;

              after;
              ALTER SYSTEM FLUSH BUFFER CACHE;

              data is written to disk and cache is made empty.


              Is it right? I was thinking previously that, all the data in cache is deleted and not writen to datafiles
              • 19. Re: flush buffer cache
                Aman....
                yup its correct!

                Aman....

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

                Aman....
                • 20. Re: flush buffer cache
                  706712
                  ..
                  • 21. Re: flush buffer cache
                    Jonathan Lewis
                    Zapotocny wrote:
                    so as a result;

                    after;
                    ALTER SYSTEM FLUSH BUFFER CACHE;

                    data is written to disk and cache is made empty.

                    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
                    • 22. Re: flush buffer cache
                      706712
                      Thanks Jonathan
                      I appreciate that.
                      You have been really helpfull.

                      If I want to write dirty blocks to datafiles, I can issue either "alter system checkpoint" or "alter system flush buffer_cache"

                      Is that right?
                      • 23. Re: flush buffer cache
                        Jonathan Lewis
                        Zapotocny wrote:
                        Thanks Jonathan
                        I appreciate that.
                        You have been really helpfull.

                        If I want to write dirty blocks to datafiles, I can issue either "alter system checkpoint" or "alter system flush buffer_cache"

                        Is that right?
                        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
                        • 24. Re: flush buffer cache
                          706417
                          I have seen 2-node RAC systems (I imagine it could be a lot worse with >2 nodes) grind to a halt, finally leading to CRS node reboot, followed by a slooooow recovery, following cache flushing, probably owing to just this cause.

                          Regards - Don Lewis
                          • 25. Re: flush buffer cache
                            Jonathan Lewis
                            Don Lewis wrote:
                            I have seen 2-node RAC systems (I imagine it could be a lot worse with >2 nodes) grind to a halt, finally leading to CRS node reboot, followed by a slooooow recovery, following cache flushing, probably owing to just this cause.
                            There are four or five effects that could come together to cause this:
                            <ul>
                            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.
                            </ul>

                            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
                            • 26. Re: flush buffer cache
                              706417
                              CRS is as bug-ridden and likely to fail at inopportune times as ever, if my recent experiences are anything to go by. That's 11g CRS, with virtually the latest patches applied, on a high-end IBM AIX system.

                              Regards - Don Lewis
                              http://little-shop-of-oras.blogspot.com
                              1 2 Previous Next