1 2 Previous Next 19 Replies Latest reply: Mar 23, 2013 9:13 PM by sb92075 RSS

    memory of each connection

    user88831
      Hi Guys,

      11.2.0.3
      HP-UX

      Currently my weblogic has 120 concurrent connections to the database.
      There is a requirement to scale up the number of concurrent connections to 300.
      We have did some study and quite be quite sure that the current sga and pga is required. (the additional connections will be firing the same sql which is very well tuned.
      However any idea each additional connection to the database will take up additional memory just for the connection?
      Will is be using the memory in the PGA? Or outside the PGA for the connection overhead?
      We are not using MTS.


      thanks
        • 1. Re: memory of each connection
          606331
          can you please share your SGA ,PGA size and RAM....

          thanks,
          DBC,
          Sr DBA,
          OCE.
          • 2. Re: memory of each connection
            jgarry
            [url http://docs.oracle.com/cd/B28359_01/server.111/b28318/memory.htm]There is one PGA for each server process, the total memory used by all individual PGAs is known as the total instance PGA memory.

            [url http://docs.oracle.com/cd/B28359_01/server.111/b28318/process.htm#CNCPT1247]Oracle Database creates server processes to handle the requests of user processes connected to the instance.

            So yes, each connection will take up memory. If you don't add more memory to the computer, or shrink the SGA and PGA, or do some other configuration things, you might run into issues of using too much memory and swapping. It depends how much memory you are using now. hp-ux is pretty good about using up all the memory it has available, whether or not your oracle is using it.

            You can figure out how much pga is being used by joining v$sesstat with v$statname where the stat name is ‘session pga memory’ or there is probably a pga view I'm not remembering just now.
            • 3. Re: memory of each connection
              606331
              Hi,

              hope you have more RAM on your machine....
              configure Automatic memory management based on RAM and load on your database....test it on 1st test environment...then fallow the prod....

              Automatic memory management is configured using two new initialization parameters:

              MEMORY_TARGET is The amount of shared memory available for Oracle to use when dynamically controlling the SGA and PGA. it is dynamic, so the total amount of memory available to Oracle can be increased or
              decreased, provided it does not exceed the MEMORY_MAX_TARGET limit. The default value is "0".
              MEMORY_MAX_TARGET defines the maximum size the MEMORY_TARGET can be increased to without an instance restart. If the MEMORY_MAX_TARGET is not specified, it defaults to
              MEMORY_TARGET setting.
              When using automatic memory management, the SGA_TARGET and PGA_AGGREGATE_TARGET act as minimum size settings for their respective memory areas.
              To allow Oracle to take full control of the memory management, these parameters should be set to zero.
              'hope this will helpful to you'......
              'source is oracle.com and oracle-base.com'
              please check below for more info.....
              http://docs.oracle.com/cd/B28359_01/server.111/b28310/memory003.htm
              http://www.oracle-base.com/articles/11g/automatic-memory-management-11gr1.php#parameters


              thanks,
              DBC,
              Sr DBA,
              OCE.
              • 4. Re: memory of each connection
                user88831
                thanks for the help.

                So yes, each connection will take up memory. If you don't add more memory to the computer, or shrink the SGA and PGA, or do some other configuration things, you might run into issues of using too much memory and swapping. It depends how much memory you are using now. hp-ux is pretty good about using up all the memory it has available, whether or not your oracle is using

                --> Do u mean each connection will take up memory outside the PGA?

                thanks!
                • 5. Re: memory of each connection
                  user88831
                  Actually i wish to know, will each connection take up additional os memory that are not part of PGA and SGA.
                  Eg, the connection to the databae via sql net. Memory needed is in PGA?

                  thanks
                  • 6. Re: memory of each connection
                    TSharma-Oracle
                    The answer is YES. Each connection will need around 1-3meg. These below link will help. Check for 1-3meg of ram per user in this link.

                    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:404110378143

                    If you have very limited resource, you can go for MTS instead of dedicated server.
                    • 7. Re: memory of each connection
                      Jan-Marten Spit
                      "Actually i wish to know, will each connection take up additional os memory that are not part of PGA and SGA."

                      yes. and it depends on your architecture how much. i used to use 4M as a rough measure on 64 bit AIX as per-server-process OS overhead, so it typically is not your primary concern compared to SGA and PGA sizes.

                      you can query v$process to get current pga usage per process.
                      • 8. Re: memory of each connection
                        user88831
                        thanks. Will read up the link.

                        So lets say if let's say application end need to increase connections from 300 to 600.
                        Firstly we can find out typically how much pga each connection will utilized currently.

                        select a.SID, b.name , a.value/1024/1024 MB
                        from v$sesstat a, v$statname b
                        where b.name ='session pga memory'
                        and a.statistic#=b.statistic#
                        ORDER BY a.value desc;


                        And estimate how much additional 300 connections going to use by : 300 x < (1mb to 3mb) connection over head + pga used by each session> right?

                        sga is not a concern here as will be firing the same type of sql.

                        thanks
                        • 9. Re: memory of each connection
                          user88831
                          Hi,

                          that is what i'm confused about. is the 1-3 mb memory tom is talking about taken from the pga memory (for dedicated server).
                          Or outside the PGA, from the os?

                          thanks
                          • 10. Re: memory of each connection
                            TSharma-Oracle
                            Outside of PGA. Every connection is a new process on OS and every process takes little amount of memory. If you have 10 connections, then reserve 10x3 = 30 meg reserved. PGA consumption is a whole different topic.
                            • 11. Re: memory of each connection
                              jgarry
                              Note he also says of the size: "I benchmark since it is *very* much application specific." And later: "So, you benchmark your application (eg: run it against representative data, using the real application, exercising it as much as you can). Then you observe what it used."

                              Someone asked how much RAM you have. If you have gigabytes, a few hundred M won't make much difference unless you are right on the edge. If you are right on the edge, any little thing will push you over, including the java part of EM, which has been known to leak memory. It's the PGA that will overwhelm you, one big DSS query can ruin your whole day.
                              • 12. Re: memory of each connection
                                Billy~Verreynne
                                dbaing wrote:

                                Currently my weblogic has 120 concurrent connections to the database.
                                There is a requirement to scale up the number of concurrent connections to 300.
                                We have did some study and quite be quite sure that the current sga and pga is required. (the additional connections will be firing the same sql which is very well tuned.
                                However any idea each additional connection to the database will take up additional memory just for the connection?
                                Will is be using the memory in the PGA? Or outside the PGA for the connection overhead?
                                We are not using MTS.
                                MTS could lower the memory footprint on the server. Any specific reason why it is not considered?

                                A connection itself does not have memory.

                                The client (weblogic in this case) initiates a connection request to the Listener. The Listener starts a dedicated Oracle server process for that client (by executing <i>$ORACLE_HOME/bin/oracle</i>).

                                So 300 dedicated connections means 300 Unix oracle processes. Each process needs a code and data segment. So you need 300x this.

                                The PGA resides in this server processes's private process memory. The UGA (state data) resides in the PGA. The PGA size depends on exactly what the server process is tasked by the client to do, and the SQL and PL/SQL code it executes. For example, should the PL/SQL code use large collections for bulk processing, the PGA will increase. If all 300 processes are running the same PL/SQL code, then all 300 processes will be increasing their private process memory (PGA).

                                The SGA is fixed in size. The total PGA (the sum of 300 processes's private process memory) is not - as this depends entirely on the number of Oracle server processes and how much memory each of those server processes needs.

                                To get a handle on this, use HP-UX glance. It has an excellent overview of memory allocated by a process.

                                Create an idle weblogic connection to the database. Identify the Oracle session (v$session) and then the Oracle process (v$process) for that session. This will also give you the Unix PID for the actual Unix process. Run glance and use this PID to investigate the memory allocation of the process. This will give you a good idea what the baseline memory requirement is for a connection. It also includes file handles, text areas and so on, and is thus a fairly comprehensive look at the process's memory footprint.

                                Next, have weblogic run some tests via that connection (typically stuff that it would do in production). Use glance to monitor memory growth. Determine the peak.

                                You will now have a baseline and peak that you can use for basic estimation of how much server memory will be needed for 300 such processes.
                                • 13. Re: memory of each connection
                                  user88831
                                  Hi Billy,

                                  thanks for your advise.

                                  I have a 11.2.0.3 database running on hp-ux.

                                  I have only allocated 2048MB (memory_taget) to Oracle.
                                  However from kmeninfo (summing up all the oracle processes), oracle is using about 5GB +.
                                  Each process is using between 14MB to 32 MB memory, as listed in kmeminfo.
                                  Is the memory listed for each process in kmeminfo part of of SGA and PGA?

                                  Wierd thing is PGA is not using that much also.
                                  SQL> show parameter memory_target;

                                  NAME TYPE VALUE

                                  --------------------------------------------------------------------------------
                                  -----------
                                  --------------------------------------------------------------------------------
                                  memory_target big integer 2048 M

                                  SQL> select n.name, sum(value)/1024/1024 as Mbytes from v$sesstat s, v$statname n
                                  2 where
                                  3 n.STATISTIC# = s.STATISTIC# and
                                  4 n.name like '%pga%'
                                  5 group by n.name;

                                  NAME MBYTES



                                  --------------------------------------------------------------------------------
                                  ----------
                                  session pga memory max 671.01059
                                  session pga memory 451.600494


                                  Any idea why when summing u from kmeminfo is 5GB+ where as i have only allocated 2GB to oracle (and pga is not using much).
                                  What is using the additional memory?
                                  thanks
                                  • 14. Re: memory of each connection
                                    user88831
                                    Hi Billy,

                                    I tired creating a connection to oracle and execute only "select name from v$database";
                                    from kmeminfo, the process is taking about 14 MB of memory.
                                    when querying the database to check how much pga it's using, it is only 2MB.

                                    Can advise on this?

                                    thanks
                                    1 2 Previous Next