This discussion is archived
12 Replies Latest reply: Nov 14, 2012 5:38 AM by Dom Brooks RSS

multiple dbwr processes

546494 Journeyer
Currently Being Moderated
Hi,
just looking for peoples thoughts on how many dbwr processes a system should have.

We have a database instance doing OLTP 24 * 7, with a single dbwr but linux ASYNC I/O enabled.
The server has 8 logical cpus and the thought is that there should be a dbwr process for each cpu.
My understanding however was that with ASYNC I/O enabled at OS level that the database required just a single DBWR process.

regards

Alan
  • 1. Re: multiple dbwr processes
    PavanKumar Guru
    Currently Being Moderated
    Hi,

    What the ORACLE Version.. ??
    AFAIK, the db_writer_processes will be equal to the number o LRU Latches (db_block_lru_latches), since each latch set will be handled by one db_writer process. But From 9i version the "db_block_lru_latches" is hidden and the db_writer_processes must be less than the number of CPUs .

    From the query x$ table you can get the number of Cpus like X$X$KVII
    example :- select KVIIVAL from X$KVII where KVIITAG='ksbcpu'

    Note:- make sure that DBWRs and DBWR IO slaves cannot coexist.

    As you said that ASYNC I/O enabled at OS level I think present configurtion with single dbwr is enough. Are you facing any performance Issue.??

    - Pavan Kumar N
  • 2. Re: multiple dbwr processes
    181444 Expert
    Currently Being Moderated
    If the database is using ASYNC IO then one dbwr process is generally all you need. On some platforms if Oracle detects ASYNC IO is enabled in the OS Oracle will automatically use it. On Linux I believe you have to configure the database to use ASYNC IO via a database parameter: " FILESYSTEMIO_OPTIONS = { none | setall | directIO | asynch } " I believe both setall and async will enable async IO operations but I am not a Linux person.

    HTH -- Mark D Powell --
  • 3. Re: multiple dbwr processes
    Aman.... Oracle ACE
    Currently Being Moderated
    pavan,
    Where did you read it that one process is managed by one lru latch? It would be good if you can quoe the source?
    Will the result of cpu_count parameter be different from the fixed table you mentioned?
    Amann....
  • 4. Re: multiple dbwr processes
    PavanKumar Guru
    Currently Being Moderated
    Hi,

    I read this with respect to ORACLE 8i Verison and check with respect 9i.
    Will the result of cpu_count parameter be different from the fixed table you mentioned?
    Are you sure on this.. ??

    Refer : http://www.pvmehta.com/new/db_writer_process%20or%20dbwr_io_slaves.htm (History ;-))

    Let me know ur views..waiting for your comments.

    - Pavan Kumar N
  • 5. Re: multiple dbwr processes
    Aman.... Oracle ACE
    Currently Being Moderated
    Pavan Kumar wrote:
    Hi,

    I read this with respect to ORACLE 8i Verison and check with respect 9i.
    Will the result of cpu_count parameter be different from the fixed table you mentioned?
    Are you sure on this.. ??

    Refer : http://www.pvmehta.com/new/db_writer_process%20or%20dbwr_io_slaves.htm (History ;-))

    Let me know ur views..waiting for your comments.

    - Pavan Kumar N
    Pavan,
    AFAIK, the db_writer_processes will be equal to the number o LRU Latches (db_block_lru_latches), since each latch set will be handled by one db_writer process. But From 9i version the "db_block_lru_latches" is hidden and the db_writer_processes must be less than the number of CPUs .
    Either you read and understood it wrong or I am missing something? Where it is mentioned that LRU latches are going to control the DBWR process?
    Here is the link to 8i,
    http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/ch131.htm#50255
    DB_BLOCK_LRU_LATCHES specifies the maximum number of LRU latch sets. The buffers of a buffer pool are equally divided among the working LRU latch sets of the buffer pool so that each buffer is protected by one LRU latch. Normally, the more latches you specify, the less contention exists for those latches. However, too many latches may result in small LRU lists, potentially reducing the cache life of a database block. The maximum of (CPU_COUNT x 2 x 3) ensures that the number of latches does not exceed twice the product of the number of CPUs and the number of buffer pools.
    LRU latch control the buffers movement in the LRU list within one workarea. Workareas are 2*cpu_count( I need to check that whether the same is true for 10g or not) so this means that there will be 1:1 relationship between LRU latch and one workarea. Now , here there "may be" a possibility that one DBWR may not be sufficient for the entire cache so there may be a need for multiples of them.But still, that doesn't signfy anywhere that the LRU latches and DBWRs are supposed to be in 1:1 ratio or the former control or determines the other.Itmay be a need that one has to go for mor e number of DBWRs. But that still doesn't mean that there must to be a DBWR for one workarea set in correlation with LRU latch.
    I couldn't find any given reference in 9i or beyond as the parameter was deprecated from 9i. Can you point me to the 9i docs where it is mentioned?

    I didin't understand the link that youhave given.Its seems to be never updated beyond oracle 8 and a lot has changed since.I don't see that its of much use.
    Lastly, I asked about the X$ table that you mentioned for the number of CPUs,
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL> show parameter count
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    active_instance_count                integer
    cpu_count                            integer     2
    db_file_multiblock_read_count        integer     16
    plsql_native_library_subdir_count    integer     0
    SQL> select KVIIVAL from X$KVII where KVIITAG='ksbcpu'
      2  /
    
    no rows selected
    
    SQL>  select KVIIVAL from X$KVII where KVIITAG='KSBCPU';
    
    no rows selected
    
    SQL>
    I have got no result from the query of yours while simply the parameter is giving number of cpus.
    Cheers
    Aman....
  • 6. Re: multiple dbwr processes
    Mahesh Menon Journeyer
    Currently Being Moderated
    Hi,

    I will give you one thing I have studied/read from Oracle Documentation Library.

    You can have multiple DB writer processes for an instance. A maximum of 20 DBWR processes. But multiple DBWR processes are needed only when you have multiple number of CPU's.
    And also, a single DBWR process can handle requests of upto maximum 8 CPU's. I mean ONE DBWR is enough for 8 CPU's.

    SYNCHRONOUS I/O (blocking I/O) and ASYNCHRONOUS I/O (non-blocking I/O)

    As someone here said, if your operating system supports ASYNC I/O, then Oracle automatically detects it and make use of it.

    But if your OS is not supporting ASYNC I/O, then you have to configure DBWR_IO_SLAVES to simulate ASYNC I/O.

    HTH
    Mahesh

    Edited by: Street Hawk on Dec 24, 2008 8:26 AM
  • 7. Re: multiple dbwr processes
    PavanKumar Guru
    Currently Being Moderated
    Hi,
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
    With the Partitioning, Data Mining and Real Application Testing options
    
    SQL> select KVIIVAL from X$KVII where KVIITAG='ksbcpu_static';
    
       KVIIVAL
    ----------
             2
    
    SQL> desc X$KVII
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     ADDR                                               RAW(4)
     INDX                                               NUMBER
     INST_ID                                            NUMBER
     KVIIVAL                                            NUMBER
     KVIITAG                                            VARCHAR2(64)
     KVIIDSC                                            VARCHAR2(64)
    
    SQL> select KVIIVAL,KVIITAG from X$KVII where KVIITAG='ksbcpu_static';
    
       KVIIVAL KVIITAG
    ---------- ----------------------------------------------------------------
             2 ksbcpu_static
    - Pavan Kumar N
  • 8. Re: multiple dbwr processes
    Aman.... Oracle ACE
    Currently Being Moderated
    Pavaan,
    My question remains,what's the added advantage of using this and not cpu count parameter?
    Also can you disk the latch part which I asked?

    Aman....
  • 9. Re: multiple dbwr processes
    PavanKumar Guru
    Currently Being Moderated
    Hi Aman,

    Sure, I need to discuss on that.. Give me some time.. Checking the ORACLE Doc's where my understanding is wrong or some thing I am missing from my side..

    - Pavan Kumar N
  • 10. Re: multiple dbwr processes
    Aman.... Oracle ACE
    Currently Being Moderated
    You are the boss sir, take your time :-) .
    Aman....
  • 11. Re: multiple dbwr processes
    923751 Newbie
    Currently Being Moderated
    Still have similar questions with 11gR2 in 2012.

    Caused bei cpu_count=256, number of db writers has been defaulted to 32.
    ASYNC IO has been enabled and should be available (on NFS mounted) storage.
    We have IO contentions with high numbers of "db block changes".
    "db file parallel writes" are spread evenly about these writers.

    Q: Could it be useful to have less db writers (and therefore a better async write optimization) ?
    Q: Is there any other reason to look for db writer optimization before seeing "free buffer" waits ?

    Rainer Stenzel
  • 12. Re: multiple dbwr processes
    Dom Brooks Guru
    Currently Being Moderated
    It would be better to open up your own thread.

    In general, better to stick to the defaults unless you know you have a problem.
    If you do have a problem, please post some evidence.

    Regarding DBWR see Kevin Closson:
    http://kevinclosson.wordpress.com/2007/08/10/learn-how-to-obliterate-processor-caches-configure-lots-and-lots-of-dbwr-processes/
    http://kevinclosson.wordpress.com/2007/08/17/over-configuring-dbwr-processes-part-ii/

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points