11 Replies Latest reply: Jul 20, 2012 5:04 AM by Nicosa-Oracle RSS

    Number of processes in Oracle

    user625850
      Hi We are using single database server 10g with number of schemas working on Java application. off late we are getting ORA-00020 number of processes exceeded error and database is not taking any new connections. Already connected users are able to continue to work.

      I changed the number of processes to 300 and re started the DB but very soon the number of processes are increasing. what is the reason for number of processes increase?

      How can i fix this problem ?

      Thanks in adv
        • 1. Re: Number of processes in Oracle
          sb92075
          user625850 wrote:
          Hi We are using single database server 10g with number of schemas working on Java application. off late we are getting ORA-00020 number of processes exceeded error and database is not taking any new connections. Already connected users are able to continue to work.

          I changed the number of processes to 300 and re started the DB but very soon the number of processes are increasing. what is the reason for number of processes increase?

          How can i fix this problem ?

          Thanks in adv
          exactly what generates new sessions against the database.

          post results of the following SQL

          SELECT MACHINE, COUNT(*) FROM V$SESSION GROUP BY MACHINE;
          • 2. Re: Number of processes in Oracle
            HuaMin Chen
            Check this
            http://www.dba-oracle.com/t_ora_00020_maximum_processes_exceeded.htm

            Edited by: HuaMin Chen on Jul 20, 2012 2:37 PM
            • 3. Re: Number of processes in Oracle
              user625850
              A1MD12663     12
              A1MD12600     5
              A1MD14215     100
              A1LOYMD12658     1
              A1MD16086     5
              A1MD12513     10

              The one A1MD14215 with 100 processes is used as server in which java application is running.
              • 4. Re: Number of processes in Oracle
                user625850
                Thanks for reply. My current processes is 300. Can I make it 500?

                any impact or any other changes?

                Thanks
                • 5. Re: Number of processes in Oracle
                  indra budiantho
                  https://blogs.oracle.com/AlejandroVargas/entry/cleanup_zombies_consuming_cpu
                  http://www.shutdownabort.com/errors/ORA-00020.php
                  • 6. Re: Number of processes in Oracle
                    Billy~Verreynne
                    HuaMin Chen wrote:
                    The ORA-00020 is caused by two things:

                    Disconnected processes: Rogue “zombie” connections to Oracle that are idle (not working). To fix this, use the ALTER SYSTEM KILL command. You may also need to kill session at the OS level with the KILL -9 or the ORAKILL command.

                    Too few process buckets: Oracle limits the number of connected processes with the processes parameter, and you may get the ORA-00020 error as the natural result in growth of system usage.

                    To fix this, increase the processes parameter, usually doubling the value to allow for future growth.
                    You are truly pathetic with your continued copying and pasting of other people's content, without giving them credit, and providing their content as if it is your own.

                    This time you have copied from http://www.dba-oracle.com/t_ora_00020_maximum_processes_exceeded.htm.
                    • 7. Re: Number of processes in Oracle
                      Billy~Verreynne
                      user625850 wrote:
                      Thanks for reply. My current processes is 300. Can I make it 500?

                      any impact or any other changes?

                      Thanks
                      HuaMin is a fraud. He copy and pasted verbatim from Don Burleson and presented that as his personal answer to you.

                      He has no clue as to what the cause of your problem are, or how to fix it. All he can do is use google to search for a specific topic and then copy-and-paste search results and present that as his answer.
                      • 8. Re: Number of processes in Oracle
                        Billy~Verreynne
                        user625850 wrote:
                        Hi We are using single database server 10g with number of schemas working on Java application. off late we are getting ORA-00020 number of processes exceeded error and database is not taking any new connections. Already connected users are able to continue to work.

                        I changed the number of processes to 300 and re started the DB but very soon the number of processes are increasing. what is the reason for number of processes increase?

                        How can i fix this problem ?
                        Bumping up the number of processes is addressing the symptom and not the problem. What do you do when at 500 processes, you still get the error? Increase it to a 1000 and wait for the error to catch up?

                        Rather identify the actual cause of the problem, than to address the symptom by bumping up the process ceiling.

                        Something is generating these additional processes. Oracle itself is unlikely to blame. The processing pools it uses (like shared servers, job servers, parallel servers, dispatchers, etc) are all managed via configuration parameters. The defaults of which are far below a 100 processes per pool.

                        Assuming this is not caused by Oracle, it then must be caused by client s/w. And seeing that you are using Java, that immediately sounds a couple of warning bells. Java developers are known for often abusing Oracle through ignorance.

                        You are likely using a Java database connection pool. This pool is typically created when the Java app server starts - it creates a bunch of db connections for use and reuse. My guess is that this pool is either incorrectly configured, or leaking sessions (connections are not properly closed). And that this is causing an ever increasing number of Oracle server processes to be created. (I have seen this myself with Java app servers a couple of times in the past)

                        Do you have a Java admin-type guy that is responsible for the Java app server config? Have this person take a look at the Java db connection pool configuration and fix it.
                        • 9. Re: Number of processes in Oracle
                          user625850
                          Thanks for the reply Billy. I too feel only increasing the number of processes is not the fix. However want to find out the fix.
                          • 10. Re: Number of processes in Oracle
                            Billy~Verreynne
                            As already mentioned by sb92075, you need to look at what these sessions are.

                            Have a look at the v$session and what is available via it.

                            sb92075 asked you to use this view to see which platform is generating all these sessions. You can now use that data and drill down into more detailed data of these sessions. Are they idle? Were they created at the same time, or at interval periods (e.g. 10 sessions every 30 minutes)? Are they shared server or dedicated server sessions? Are they blocked by another session? What is the last SQL executed by the session? Etc.

                            This will give you a clear idea of what these sessions are and what they are doing.

                            However, as they all originate from your Java app server, I'm pretty sure that the problem is related to the configuration of the db connection pool on the Java app server. As I've mentioned, I've seen the issue that you are facing before - pretty much the same symptoms and coming from a Java server. And the cause was due to an error in the session pool config of the Java app server.
                            • 11. Re: Number of processes in Oracle
                              Nicosa-Oracle
                              Hi,
                              user625850 wrote:
                              (...) working on Java application
                              Go ask the Java guys how much connection they expect to need simultaneously.

                              If they can't tell, that might means that the java is either :
                              - not closing connections
                              - not configured to limit the number of simultaneous connection.

                              Be sure to understand that on a 128 Core server, with 500 simultaneous active connections, each connection will roughly have 128/500=~25%CPU at a time (ie. each process will be waiting on CPU 75% of its time...)