14 Replies Latest reply: Jan 28, 2013 6:10 AM by Umesh Gupta RSS

    Oracle Process

    oracleRaj
      Dear Gurus

      We are working in Online Application, and certain peak time our database activity increases and more than 1100 users login. ( checked in view v$session and v$parameter )

      Process parameter value = 1100
      sessions paramter value = 1215
      OS : Windows 2003 server
      Oracle : 10.2.0
      RAM : 20 GB
      SGA_TARGET : 7GB
      Server : Intel Xeon (R) 8 cpu


      Issues : DB server usage went 100% for few seconds, and then back to around 50%. Some users get DB server errors, because the process parameter is limited to 1100.

      Can you guide me ? should I increase the process parameter to 2000 ??
      or should I replace the existing server with heavy server and then increase the process parameter to 2000.

      Kind Regards.
        • 1. Re: Oracle Process
          ora_tech
          Hi Raj,
          Can you guide me ? should I increase the process parameter to 2000 ??
          or should I replace the existing server with heavy server and then increase the process parameter to 2000.
          Its seems the problem is with the value of process. Users will not receive errors if you increase this value to 2000.

          But make sure you have enough resources available on your server in order to handle *2000 sessions.*

          thanks,
          X A H E E R
          • 2. Re: Oracle Process
            j_DBA_sourav
            Hello,

            Please be sure that you have enough resource to handle that much of load on your DB.. Get a help of your system administrator to extract the log of system level. What is causing the problem.
            As far as your parameter of processes and sessions are concern, according to theory they look all right...
            Get a AWR report and study it , if you receive any important information out of that..

            -Regards,
            J_DBA
            • 3. Re: Oracle Process
              oracleRaj
              ora_tech wrote:
              Hi Raj,
              Can you guide me ? should I increase the process parameter to 2000 ??
              or should I replace the existing server with heavy server and then increase the process parameter to 2000.
              Its seems the problem is with the value of process. Users will not receive errors if you increase this value to 2000.

              But make sure you have enough resources available on your server in order to handle *2000 sessions.*

              thanks,
              X A H E E R
              Dear XAHEER,

              Thanks for reply.
              If I change process to 2000, what kind of resources should I check ? increase ?
              • 4. Re: Oracle Process
                oracleRaj
                j_DBA_sourav wrote:
                Hello,

                Please be sure that you have enough resource to handle that much of load on your DB.. Get a help of your system administrator to extract the log of system level. What is causing the problem.
                As far as your parameter of processes and sessions are concern, according to theory they look all right...
                Get a AWR report and study it , if you receive any important information out of that..

                -Regards,
                J_DBA
                Thanks for reply,
                When I check > task manager > CPU Usage > Oracle were consuming over 90%.

                What should I look in AWR Report. ?

                Kind Regards.
                • 5. Re: Oracle Process
                  j_DBA_sourav
                  Yes, please..
                  Could you not get any OS log why the user is getting any servers related error?
                  Everytime we face performance issue, does not mean that wrong is with DB, The machine which is performing the job has much to do sometimes. :)

                  -Regards,
                  J_DBA
                  • 6. Re: Oracle Process
                    Girish Sharma
                    Dedicated Server or Shared Server ?

                    (eg: it depends. shared server, it would be less than if you use dedicated server probably)

                    think about how many processes you'll have - the Oracle backgrounds (give them 15) plus your user sessions (number of shared servers/dedicated servers), number of job processes/aq processes you want to permit, parallel execution servers if applicable.
                    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:670546500346748368

                    What is value of open_cursor parameter ?

                    Regards
                    Girish Sharma
                    • 7. Re: Oracle Process
                      oracleRaj
                      Girish Sharma wrote:
                      Dedicated Server or Shared Server ?

                      (eg: it depends. shared server, it would be less than if you use dedicated server probably)

                      think about how many processes you'll have - the Oracle backgrounds (give them 15) plus your user sessions (number of shared servers/dedicated servers), number of job processes/aq processes you want to permit, parallel execution servers if applicable.
                      http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:670546500346748368

                      What is value of open_cursor parameter ?

                      Regards
                      Girish Sharma
                      select server from v$session;
                      returns 100s of rows as

                      SERVER
                      ---------
                      DEDICATED

                      If I set value of process to 2000 then ?

                      open_cursors = 700.
                      • 8. Re: Oracle Process
                        Girish Sharma
                        If I set value of process to 2000 then ?
                        Then you should identified how many processes are actually needed. How you calculated this 2000 magic figure ?

                        Look at your machine, if you have say an 8 cpu machine and you have processes set to more than 200 or so - you have set it too high. Your 8 cpu machine cannot possibly do 2000 processes. You need to get your connection pool(s) under control or start employing shared server.

                        In the same thread. Please read above asktom link which have similar discussion.
                        open_cursors = 700
                        Its too big. 700 open cursors means, there can be 700 opened cursors in a single session. Why are you allowing database users to let cursor open and waste memory when they easily close the cursor ?

                        Regards
                        Girish Sharma
                        • 9. Re: Oracle Process
                          Billy~Verreynne
                          Server resources determines the number of threads Windows can handle.

                          Oracle runs as threads on Windows. Single oracle.exe process image loaded into the kernel. PMON, SMON, log/db writers and so on are threads.

                          When a client requests a dedicated server session, the Listener process uses (I think) the CreateRemoteThread() call to create the dedicated Oracle sever process (aka thread) to service that session.

                          Each thread has stack size - despite running inside the same physical kernel process image. The article Thread Stack Size (Windows) discusses this.

                          The default is 1MB. I've heard comment that Oracle sets dwStackSize to 2MB for each thread.

                          A 1000 threads (dedicated Oracle client-server sessions). Almost 2GB of basic RAM footprint needed. This excludes the UGA needed per session. And the PGA growth per session.

                          Not a major issue on a 64bit o/s. Will be an issue on a 32bit o/s using PAE.

                          I suggest taking a couple of o/s performance snapshots. During peak usage you ideally want no hard swap errors (no pages required to be swapped out to disks, in order to read pages from disk into memory). You also want some spare CPU capacity. E.g. CPUs 0 to 6 showing around 90% utilisation and CPU 7 being mostly idle.

                          If the resource utilisation is not at capacity then bump up the process parameter in Oracle.
                          • 10. Re: Oracle Process
                            oracleRaj
                            Girish Sharma wrote:
                            If I set value of process to 2000 then ?
                            Then you should identified how many processes are actually needed. How you calculated this 2000 magic figure ?

                            Look at your machine, if you have say an 8 cpu machine and you have processes set to more than 200 or so - you have set it too high. Your 8 cpu machine cannot possibly do 2000 processes. You need to get your connection pool(s) under control or start employing shared server.

                            In the same thread. Please read above asktom link which have similar discussion.
                            open_cursors = 700
                            Its too big. 700 open cursors means, there can be 700 opened cursors in a single session. Why are you allowing database users to let cursor open and waste memory when they easily close the cursor ?

                            Regards
                            Girish Sharma
                            Thanks Grish Sir...but I have also checked this thread.
                            http://www.dba-oracle.com/t_mts_multithreaded_servers_shared.htm
                            • 11. Re: Oracle Process
                              oracleRaj
                              Billy  Verreynne  wrote:
                              Server resources determines the number of threads Windows can handle.

                              Oracle runs as threads on Windows. Single oracle.exe process image loaded into the kernel. PMON, SMON, log/db writers and so on are threads.

                              When a client requests a dedicated server session, the Listener process uses (I think) the CreateRemoteThread() call to create the dedicated Oracle sever process (aka thread) to service that session.

                              Each thread has stack size - despite running inside the same physical kernel process image. The article Thread Stack Size (Windows) discusses this.

                              The default is 1MB. I've heard comment that Oracle sets dwStackSize to 2MB for each thread.

                              A 1000 threads (dedicated Oracle client-server sessions). Almost 2GB of basic RAM footprint needed. This excludes the UGA needed per session. And the PGA growth per session.

                              Not a major issue on a 64bit o/s. Will be an issue on a 32bit o/s using PAE.

                              I suggest taking a couple of o/s performance snapshots. During peak usage you ideally want no hard swap errors (no pages required to be swapped out to disks, in order to read pages from disk into memory). You also want some spare CPU capacity. E.g. CPUs 0 to 6 showing around 90% utilisation and CPU 7 being mostly idle.

                              If the resource utilisation is not at capacity then bump up the process parameter in Oracle.
                              Thanks alot Billy Sir, it's really a helpful reply from you.
                              • 12. Re: Oracle Process
                                Umesh Gupta
                                oracleRaj wrote:
                                Dear Gurus

                                We are working in Online Application, and certain peak time our database activity increases and more than 1100 users login. ( checked in view v$session and v$parameter )

                                Process parameter value = 1100
                                sessions paramter value = 1215
                                OS : Windows 2003 server
                                Oracle : 10.2.0
                                RAM : 20 GB
                                SGA_TARGET : 7GB
                                Server : Intel Xeon (R) 8 cpu


                                Issues : DB server usage went 100% for few seconds, and then back to around 50%. Some users get DB server errors, because the process parameter is limited to 1100.

                                Can you guide me ? should I increase the process parameter to 2000 ??
                                or should I replace the existing server with heavy server and then increase the process parameter to 2000.

                                Kind Regards.
                                HI,

                                You can check v$resource_limit
                                select * from v$resource_limit where resource_name in ('processes','sessions');
                                
                                RESOURCE_NAME   CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL     LIMIT_VALU     
                                ---------------- ------------------- --------------- ----------     ----------
                                
                                processes     32               150            150            150
                                sessions     37               155            170            170
                                If MAX_UTILIZATION is equal to Initial value of parameter and you are getting error regarding the same.
                                You can think of increasing the values of the parameters


                                Regards

                                Umesh
                                • 13. Re: Oracle Process
                                  oracleRaj
                                  Umesh Gupta wrote:
                                  oracleRaj wrote:
                                  Dear Gurus

                                  We are working in Online Application, and certain peak time our database activity increases and more than 1100 users login. ( checked in view v$session and v$parameter )

                                  Process parameter value = 1100
                                  sessions paramter value = 1215
                                  OS : Windows 2003 server
                                  Oracle : 10.2.0
                                  RAM : 20 GB
                                  SGA_TARGET : 7GB
                                  Server : Intel Xeon (R) 8 cpu


                                  Issues : DB server usage went 100% for few seconds, and then back to around 50%. Some users get DB server errors, because the process parameter is limited to 1100.

                                  Can you guide me ? should I increase the process parameter to 2000 ??
                                  or should I replace the existing server with heavy server and then increase the process parameter to 2000.

                                  Kind Regards.
                                  HI,

                                  You can check v$resource_limit
                                  select * from v$resource_limit where resource_name in ('processes','sessions');
                                  
                                  RESOURCE_NAME   CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL     LIMIT_VALU     
                                  ---------------- ------------------- --------------- ----------     ----------
                                  
                                  processes     32               150            150            150
                                  sessions     37               155            170            170
                                  If MAX_UTILIZATION is equal to Initial value of parameter and you are getting error regarding the same.
                                  You can think of increasing the values of the parameters


                                  Regards

                                  Umesh
                                  SQL> select * from v$resource_limit;

                                  RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
                                  ------------------------------ ------------------- --------------- ---------- ----------
                                  processes 98 1100 1100 1100
                                  sessions 102 1215 1215 1215
                                  enqueue_locks 17 329 14890 14890
                                  • 14. Re: Oracle Process
                                    Umesh Gupta
                                    Your current utilization has already been reached to max value,then you should think of increasing the values of Sessions and processes parameter..


                                    Regards

                                    Umesh