1 2 3 4 Previous Next 53 Replies Latest reply: May 2, 2012 12:39 AM by fuzzydba RSS

    SGA_MAX_SIZE != SGA_TARGET when?

    412527
      Just curious... under what circumstances, when using ASMM, would I not want SGA_TARGET to equal SGA_MAX_TARGET?

      Bob
        • 1. Re: SGA_MAX_SIZE != SGA_TARGET when?
          Hans Forbrich
          Since you must bounce the database to increase above SGA_MAX_SIZE, suppose you have the following situation:

          - Three database instances on your machine;
          - You machine has 2 GB RAM;
          - Under normal operation, each database needs 500MB RAM to serve the users;
          - Once a month one database (DB1) does extra-heavy batch processing that requires SGA of 1GB

          Note that once a month the total SGA requirement would be 2GB, which matches or exceeds the machine RAM.

          Therefore I would set
          - my MAX_SGA on DB1 to 1.3GB and DB2 & DB3 to 800M
          - my SGA_TARGET on DB1, DB2 and DB3 to 500M
          - once a month I could simply change DB2 and DB3 to 350M and increase DB1 to 1GB, run the batch job with minimum swapping, and then restore the SGAs to regular operation WITHOUT bouncing the databases.

          This is an idealistic scenario - it does not take into account the PGA, OS or other memory requirements, but it does give you the idea.
          • 2. Re: SGA_MAX_SIZE != SGA_TARGET when?
            Madrid
            There is no reason for it. You could leave the value less than the sga_target, just to leave a growing margin. If your metrics show your instance is currently running fine, there would not be any need to increase the value. But if you check your performance metrics start to degrade leaving this growing margin will allow you to dynamically increase the SGA_TARGET and let the ASMM'ed components to grow on free memory without stealing each other memory granules. If you set sga_max_size=sga_target and you notice there are frequent requests for memory there will be nothing to do but to wait until you can rebounce your instance and grow your memory limits.


            ~ Madrid.
            • 3. Re: SGA_MAX_SIZE != SGA_TARGET when?
              412527
              Hans,

              Doesn't Oracle pre-allocate SGA_MAX_SIZE worth of physical memory? It seems to me that the actual size of the shared memory segment allocated (at least under a *nix system) is fixed so it's not as though I can create three instances with 1GB sga_max_size on a single box with 2GB total physical RAM.  SGA_TARGET just seems to address the allocation of sga_max_size to service various SGA components.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
              • 4. Re: SGA_MAX_SIZE != SGA_TARGET when?
                Hans Forbrich
                Depends on the version. Under 9i and older, you are absolutely correct.

                Under 10g, I defer to the following answer, quoted Page 154 from Tom Kyte's Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions book

                "
                Under automatic SGA memory management, the primary parameter for sizing the autotuned
                components is SGA_TARGET, which may be dynamically sized while the database is up
                and running, up to the setting of the SGA_MAX_SIZE parameter (which defaults to be equal to
                the SGA_TARGET, so if you plan on increasing the SGA_TARGET, you must have set the SGA_MAX_SIZE larger before starting the database instance).
                "
                • 5. Re: SGA_MAX_SIZE != SGA_TARGET when?
                  412527
                  That quote seems to support my question, to be honest. It doesn't say that SGA_MAX_SIZE is dynamic at all, and therefore merely by specifying it, aren't I allocating that that OS RAM to Oracle during instance startup?

                  Doesn't that mean that if my sga_target < sga_max_size then all I'm really doing is running a risk of wasting RAM?
                  • 6. Re: SGA_MAX_SIZE != SGA_TARGET when?
                    esears1
                    Hans,

                    Does 9i have auto memory management? I didn't think it did, cause I know sga_target is not valid until 10g

                    Edward
                    • 7. Re: SGA_MAX_SIZE != SGA_TARGET when?
                      Hans Forbrich
                      Let's try this quote, from the Concepts manual, Chapter 8

                      Oracle Database can set limits on how much virtual memory the database uses for the SGA. It can start instances with minimal memory and[i] allow the instance to use more memory by expanding the memory allocated for SGA components, up to a maximum determined by the SGA_MAX_SIZE initialization parameter.


                      SGA_MAX_SIZE is not dynamic. You can NOT change the value.

                      However, as compared to previous versions of Oracle, in 10g SGA_MAX_SIZE does not define the size of memory allocated, but rather the MAXIMUM size that CAN be allocated.


                      Further, (although it's been over 6 months ago, I am assuming memory works correctly, as I have taught and taken many courses since them) in my Oracle 10g DBA Workshop classes, I actually demonstrated changing the SGA_TARGET and proving that the memory is actually released (using top) when the target is reduced. It is not instantaneous, as the buffers need to be cleaned.

                      Message was edited by:
                      Hans Forbrich
                      • 8. Re: SGA_MAX_SIZE != SGA_TARGET when?
                        Hans Forbrich
                        You are correct.

                        From the Oracle9i DB Reference manual, in the SGA_MAX_SIZE definition we read

                        Default value

                        Initial size of SGA at startup, dependent on the sizes of different pools in the SGA, such as buffer cache, shared pool, large pool, and so on.

                        Parameter class
                        Static
                        • 9. Re: SGA_MAX_SIZE != SGA_TARGET when?
                          247514
                          Just to clarify things up,

                          Oracle will allocate SGA size equal to SGA_MAX_SIZE from system initially.
                          SGA_TARGE can be smaller than SGA_MAX_SIZE because they are buffer caches, such as KEEP, RECYCLE, and other block sizes, not affected by SGA_TARGET.

                          See the result below,
                          SYS@etest> show sga

                          Total System Global Area  264241152 bytes
                          Fixed Size                  1301408 bytes
                          Variable Size             216540256 bytes
                          Database Buffers           46137344 bytes
                          Redo Buffers                 262144 bytes
                          SYS@etest> show parameter sga

                          NAME                                 TYPE        VALUE
                          ------------------------------------ ----------- ------------------------------
                          lock_sga                             boolean     FALSE
                          pre_page_sga                         boolean     TRUE
                          sga_max_size                         big integer 252M
                          sga_target                           big integer 200M
                          Oracle SGA size is 264241152/1024/1024=252M equal to SGA_MAX_SIZE while SGA_TARGET is 200M
                          Since I don't have other buffers set, Where the 50M goes ?
                          SYS@etest> select * from v$sgainfo
                            2  /

                          NAME                                  BYTES RES
                          -------------------------------- ---------- ---
                          Fixed SGA Size                      1301408 No
                          Redo Buffers                         262144 No
                          Buffer Cache Size                  46137344 Yes
                          Shared Pool Size                  150994944 Yes
                          Large Pool Size                     4194304 Yes
                          Java Pool Size                      4194304 Yes
                          Streams Pool Size                         0 Yes
                          Granule Size                        4194304 No
                          Maximum SGA Size                  264241152 No
                          Startup overhead in Shared Pool    37748736 No
                          Free SGA Memory Available 54525952

                          11 rows selected.
                          It went to Free SGA Memory Available. Hence you can dynamically increase SGA_TARGET without reboot instance.
                          • 10. Re: SGA_MAX_SIZE != SGA_TARGET when?
                            Hans Forbrich
                            Please verify that Oracle is actually USING 252M of OS memory for SGA.



                            Also note TOm's response to an AskTom thread on this same topic. http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:30011178429375 - search for

                            Followup December 21, 2004
                            Followup December 28, 2006

                            Message was edited by:
                            Hans Forbrich
                            • 11. Re: SGA_MAX_SIZE != SGA_TARGET when?
                              247514
                              An "ipcs -ma" reveal that Oracle did allocate 252M from OS
                              T         ID      KEY        MODE        OWNER    GROUP  CREATOR   CGROUP NATTCH      SEGSZ  CPID  LPID   ATIME    DTIME    CTIME 
                              Shared Memory:
                              m       5001   0x753f960c rw-r---   oracle      dba   oracle      dba     21  264249344 25248 25977 13:39:56 13:39:56 12:11:16
                              • 12. Re: SGA_MAX_SIZE != SGA_TARGET when?
                                247514
                                Also note TOm's response to an AskTom thread on this
                                same topic.
                                http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P
                                1_QUESTION_ID:30011178429375 - search for

                                Followup December 21, 2004
                                Followup December 28, 2006

                                Message was edited by:
                                Hans Forbrich
                                That mean our mighty Tom was wrong in this respect.
                                Let's do an extreme test, set an 8G SGA_MAX_SIZE on my server only have 2G memory. (don't try this at home)
                                SYS@etest> alter system set sga_max_size=8G scope=spfile;

                                System altered.

                                SYS@etest> shutdown immediate
                                --------------------
                                Connected to an idle instance.

                                SYS@etest> startup
                                ORA-27102: out of memory
                                SVR4 Error: 12: Not enough space
                                This error because 8G is higher than our max shm size allowed.
                                If we set to lower value to 3G for example, the instance may start but the system will be unreasonablely slow.
                                   PID USERNAME THR PRI NICE  SIZE   RES STATE    TIME    CPU COMMAND
                                26275 oracle     1  60    0 3095M   12M cpu/2    0:10 10.51% oracle
                                Add some few notes

                                Message was edited by:
                                yingkuan
                                • 13. Re: SGA_MAX_SIZE != SGA_TARGET when?
                                  Hans Forbrich
                                  You, of course, have SGA_TARGET set to a small value, say 1G?
                                  • 14. Re: SGA_MAX_SIZE != SGA_TARGET when?
                                    Hans Forbrich
                                    Interesting.

                                    This is causing me to review a lot of what I understand about that setting.
                                    1 2 3 4 Previous Next