1 2 Previous Next 20 Replies Latest reply: Jul 26, 2013 10:44 AM by StefanKoehler RSS

    Question about db_writer_processes

    Mr.D.

      Hi,

      i've 2 server AIX P570 and SO changes the CPU from physical to shared (i don't know what is the correct term).

      Now my databases (9.2 and 11.2)  see 12 cpu instead 2.

       

      [code]SQL> show parameter cpu

      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      cpu_count                            integer     12[/code]

       

      So also db_writer_processes was automatically increased from 1 to 2.

      My database has not experiences of buffer problem.

      This 2 processes consume cpu and ram, i think, so if the server already suffer with the memory,

      this 2 processes goes to paging.

       

      PIDTTYSTATTIMEPAGINSIZERSSLIMTSIZTRRS%CPU%MEMCOMMAND
      1249412-A8:2412.464.0025.96415.852xx50.46715.3960.00.0ora_dbw
      2560176-A8:2612.427.9377.73215.844xx50.46715.3960.00.0ora_dbw
      3547156-A24:336.940.46579.82021.392xx71.6202.8400.00.0/opt/or

       

      So, it is the best choice?

        • 1. Re: Question about db_writer_processes
          sb92075

          Mr.D. wrote:

           

          Hi,

          i've 2 server AIX P570 and SO changes the CPU from physical to shared (i don't know what is the correct term).

          Now my databases (9.2 and 11.2)  see 12 cpu instead 2.

           

          [code]SQL> show parameter cpu

          NAME                                 TYPE        VALUE
          ------------------------------------ ----------- ------------------------------
          cpu_count                            integer     12[/code]

           

          So also db_writer_processes was automatically increased from 1 to 2.

          My database has not experiences of buffer problem.

          This 2 processes consume cpu and ram, i think, so if the server already suffer with the memory,

          this 2 processes goes to paging.

           

          PID TTY STAT TIME PAGIN SIZE RSS LIM TSIZ TRRS %CPU %MEM COMMAND
          1249412 - A 8:24 12.464.002 5.964 15.852 xx 50.467 15.396 0.0 0.0 ora_dbw
          2560176 - A 8:26 12.427.937 7.732 15.844 xx 50.467 15.396 0.0 0.0 ora_dbw
          3547156 - A 24:33 6.940.465 79.820 21.392 xx 71.620 2.840 0.0 0.0 /opt/or

           

          So, it is the best choice?

           

          take 2 aspirin, close your eyes & forget what you saw since it is not what you think it is.

          • 3. Re: Question about db_writer_processes
            Hemant K Chitale

            No , the two DB Writer Processes do not really consume much memory.

             

            Hemant K Chitale

            • 4. Re: Question about db_writer_processes
              Mr.D.

              Hi Hermant,

              I know that DBWR do not  consume much memory but it is paging, the problem is that there isn't available memory on the server.

              I've wait on I/O and the most used volume is the swap.

              My question is, in this situation, where there isn't available memory on the server, is the best choise virtualize the cpu into 12 parts?

              Was no better 8? So i've only 1 process that swap?

               

              Tnx

              • 5. Re: Question about db_writer_processes
                Hemant K Chitale

                IF there is paging on the system  (paging is a system behaviour across all processes, not tied to specific processes), then it is not because of the DB Writer processes count increasing from 1 to 2.

                 

                IF there is paging on the system, you have undersized RAM or oversized SGA/PGA.

                 

                 

                Hemant  K Chitale


                • 6. Re: Question about db_writer_processes
                  Mr.D.

                  So it is no coincidence that the process that consumes more is a DBWR?

                  • 7. Re: Question about db_writer_processes
                    JohnWatson

                    I can't answer your question directly, but perhaps I can offer some things to consider.

                    AIX has a very sophisticated paging algorithm. Among other things, it will page out data only if it is not being used, and furthermore will page it in advance of needing the main memory as resources permit. So swapping out has no impact on your sessions. The DBWn also has a very clever algorithm for deciding which dirty buffers to clean to the datafies: it will write out only those buffers which are no longer being used by your sessions. And of course all this work is in the background. Combining these behaviours will mean that when the system comes under memory pressure and needs to page out buffers, this should have no effect on your sessions. DBWn will need to bring the buffers back into main memory (hence the relatively high pagein figures) to clean them: this will slow the writing process, but again that shouldn't affect your sessions.

                    In the absence of any other diagnostics indicating that you have a problem, the paging may not be the reason for any performance issues your users may be experiencing.

                    • 8. Re: Question about db_writer_processes
                      Mr.D.

                      JohnWatson ha scritto:

                       

                      So swapping out has no impact on your sessions.

                       

                      ....

                       

                      In the absence of any other diagnostics indicating that you have a problem, the paging may not be the reason for any performance issues your users may be experiencing.

                       

                      Hi John,

                      the system is very slow, database is very slow.

                      Stopping some cluster instance the system seems works fine.

                       

                      So, i will check perfstat and awr to see if there are some particular wait.

                      What other diagnostic i can perform?

                       

                      Thank you

                      • 9. Re: Question about db_writer_processes
                        Hemant K Chitale

                        >Now my databases (9.2 and 11.2)  see 12 cpu instead 2.

                         

                        Possibly Parallel Queries are being used against the "12"  virtual CPUs ?

                         

                         

                        Hemant K Chitale

                        • 10. Re: Question about db_writer_processes
                          Mr.D.

                          HemantKChitale ha scritto:

                           

                          >Now my databases (9.2 and 11.2)  see 12 cpu instead 2.

                           

                          Possibly Parallel Queries are being used against the "12"  virtual CPUs ?

                           

                           

                          Hemant K Chitale

                          Parallel Queries? No, the developer does not even know what

                          • 11. Re: Question about db_writer_processes
                            509894

                            check which session using most of the PGA :

                             

                            SELECT        ssn.SID "SESSION_ID",

                                          ssn.SERIAL#,

                                          se1.value PGAcurr,

                                          ssn.last_call_et, ssn.status, ssn.username, ssn.logon_time , 'alter system kill session ''' || ssn.SID || ',' || ssn.serial# || ''';'

                                FROM     v$sesstat se1, v$session ssn

                                WHERE    se1.statistic# = (select statistic# from  v$statname n where n.name = 'session pga memory')

                                AND      se1.sid        = ssn.sid

                                AND      ssn.username IS NOT null

                                ORDER BY 3 desc;

                            • 12. Re: Question about db_writer_processes
                              Mr.D.

                              Hi Meser,

                              the session with the most PGA used is:

                               

                              SESSION_IDSERIAL#PGACURRLAST_CALL_ETSTATUS
                              537.21915.888.7447.203INACTIVE

                               

                              Are in 15Mb? It's not too high.

                              • 13. Re: Question about db_writer_processes
                                509894

                                your question seem to me more to be an OS problem than a DB problem. 

                                You may need to add more RAM to your system or you may want to decrese your SGA/PGA values. 

                                • 14. Re: Question about db_writer_processes
                                  Mr.D.

                                  Hi, i've stopped some database instances.

                                  Now there is no swap on the server.

                                  My original question was, is the best choise create 12 virtual cpu (2 physical) so that oracle choose to use 2 dbwr?

                                  1 2 Previous Next