14 Replies Latest reply: Sep 8, 2008 9:20 AM by Aman.... RSS

    DB_WRITER_PROCESSES

    OracleGuy777
      hi guys,

      The [Oracle Reference|http://www.acs.ilstu.edu/docs/oracle/server.101/b10755/initparams055.htm] says for DB_WRITER_PROCESSES

      Parameter type      Integer
      Default value      1 or CPU_COUNT / 8, whichever is greater
      Modifiable      No
      Range of values      1 to 20
      Basic      No

      Why does it say it is not modifiable? I thought we could change this parameter to specify the number of DBW we want started when the instance starts.

      thanks
        • 1. Re: DB_WRITER_PROCESSES
          587671
          Why would you want to modify these values ? Do you have some performance issues ? the DB_WRITER_PROCESSES should be increased only if you experience extensive waits on buffer LRU latch. IF you're using dynamic SGA, this is automatically computed for you
          • 2. Re: DB_WRITER_PROCESSES
            F.Munoz Alvarez
            Hi OracleGuy777,

            Take a look on this link ;)

            - http://kevinclosson.wordpress.com/2007/08/17/over-configuring-dbwr-processes-part-ii/

            Cheers,

            Francisco Munoz Alvarez
            http://oraclenz.wordpress.com

            Edited by: F.Munoz Alvarez on Nov 30, 2012 11:05 AM
            • 3. Re: DB_WRITER_PROCESSES
              F.Munoz Alvarez
              Remember that:

              Database Writer process (DBWR)

              1. Performs all writing of buffers to datafiles.
              2. Is responsible for buffer cache management.

              Buffer Cache Management.

              1. When a buffer in the buffer cache is modified, it is marked "dirty".
              2. DBWR keeps the buffer cache "clean" by writing dirty buffers to disk.
              3. As buffers are filled and dirtied by user processes, the number of free buffers diminishes. If the number of free buffers drops too low, user processes that must read blocks from disk into the cache are not able to find free buffers. DBWR manages the buffer cache so that user processes can always find free buffers.
              4. A LRU (least recently used) algorithm keeps the most recently used data blocks in memory


              The DBWR process writes dirty buffers to disk under these conditions:

              * When a server process moves a buffer to the dirty list and discovers that the dirty list has reached a threshold length, the server process signals DBWR to write.
              * When a server process searches a threshold limit of buffers in the LRU list without finding a free buffer, it stops searching and signals DBWR to write (because not enough free buffers are available and DBWR must make room for more).
              * When a time-out occurs (every three seconds), DBWR signals itself.
              * When a checkpoint occurs, the Log Writer process (LGWR) signals DBWR.


              Reference: Oracle Concepts http://tahiti.oracle.com

              Cheers,

              Francisco Munoz Alvarez
              http://oraclenz.wordpress.com

              Edited by: F.Munoz Alvarez on Nov 30, 2012 11:05 AM
              • 4. Re: DB_WRITER_PROCESSES
                Aman....
                Oracleguy777,
                Docs some times are confusing and some times are obsolete,it happens.
                This modification which is mentioned for NO is that parameter can't be changed at runtime.If you see this parameter in the old releases of docs, its more clear.For example, see in 9i docs.
                http://download.oracle.com/docs/cd/B10501_01/server.920/a96536/ch149.htm#REFRN10043
                The parameter is static.Surely enough this can be changed but there are further rules to makethe changes happen.

                Aman....
                • 5. Re: DB_WRITER_PROCESSES
                  OracleGuy777
                  This modification which is mentioned for NO is that parameter can't be changed at runtime.If you see this parameter in the old releases of docs, its more clear.For example, see in 9i docs.
                  http://download.oracle.com/docs/cd/B10501_01/server.920/a96536/ch149.htm#REFRN10043
                  The parameter is static.Surely enough this can be changed but there are further rules to makethe changes happen.
                  many thanks, that helped
                  • 6. Re: DB_WRITER_PROCESSES
                    OracleGuy777
                    The DBWR process writes dirty buffers to disk under these conditions:

                    ...
                    * When a time-out occurs (every three seconds), DBWR signals itself.*
                    ...
                    Many thanks.

                    Just one more question, is this 3 seconds related to LGWR's 3 seconds?

                    thanks
                    • 7. Re: DB_WRITER_PROCESSES
                      Aman....
                      No this is called Dbwrs Time out. lgwr time out of three seconds is an independant work. A lot of stuff happens in dbwr besides what francisco has mentioned.
                      Aman....
                      • 8. Re: DB_WRITER_PROCESSES
                        OracleGuy777
                        thanks!
                        • 9. Re: DB_WRITER_PROCESSES
                          F.Munoz Alvarez
                          Hi OracleGuy777,

                          This oracle documentation will help you understand a little better the Oracle Processes:

                          - http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/process.htm#i16977
                          - http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/process.htm#i7259

                          Also Take a look on this excellent presentation from Julian Dyke:

                          - [SGA Internals|http://www.juliandyke.com/Presentations/SGAInternals.ppt]

                          Regards,

                          Francisco Munoz Alvarez
                          http://oraclenz.wordpress.com

                          Edited by: F.Munoz Alvarez on Nov 30, 2012 11:06 AM
                          • 10. Re: DB_WRITER_PROCESSES
                            OracleGuy777
                            - http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/process.htm#i16977
                            - http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/process.htm#i7259
                            I had read this one (for 9.2) a few weeks ago, but hadn't made notes. I guess I skimmed over a few bits, so it might be worth revisiting.

                            Also Take a look on this excellent presentation from Julian Dyke:

                            - [SGA Internals|http://www.juliandyke.com/Presentations/SGAInternals.ppt]
                            That's another great link. Many thanks! :)
                            • 11. Re: DB_WRITER_PROCESSES
                              OracleGuy777
                              No this is called Dbwrs Time out. lgwr time out of three seconds is an independant work. A lot of stuff happens in dbwr besides what francisco has mentioned.
                              I am still confused. Before DBWn writes to the disk, it MUST call LGWR. If DBWn is writing every 3 seconds, then surely the every 3 seconds of LGWR is related to this, and not independent?!?! Or am I totally missing something here?

                              thanks
                              • 12. Re: DB_WRITER_PROCESSES
                                Jonathan Lewis
                                OracleGuy777 wrote:

                                I am still confused. Before DBWn writes to the disk, it MUST call LGWR. If DBWn is writing every 3 seconds, then surely the every 3 seconds of LGWR is related to this, and not independent?!?! Or am I totally missing something here?
                                If nothing much is going on, DBWR will set an alarm clock and wake itself up every three seconds and then decide whether or not it needs to copy some dirty blocks to disc. However, it is legal, for a few reasons, for another process will send a wake-up call to DBWR at any moment and ask it to write to disc.

                                Similarly LGWR - in a nearly idle system - will set an alarm to wake itself up every 3 seconds to see if it needs to copy some log buffer to disc. Again, other processes may send message to LGWR to wake up.

                                One of the processes that is allowed to send a message to LWGR is DBWR. It doesn't do this every three seconds, it doesn't even do it every time it write to disc; it does it if it has to write a block to disc and discovers (from various values stored on the buffer header) that the redo that made the most recent changes to that block is still in the log buffer.

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

                                "The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge." Stephen Hawking.
                                • 13. Re: DB_WRITER_PROCESSES
                                  OracleGuy777
                                  that explains it very well, particularly the last paragraph. Many thanks. Yes, I was making a silly assumption that every time DBWn writes to disk, we should also be writing to the redo log, which doesn't really make sense, as the redo log might already have captured that information!
                                  • 14. Re: DB_WRITER_PROCESSES
                                    Aman....
                                    I guess JL already gave you a very good reply :-).
                                    Cheers
                                    Aman....