1 2 Previous Next 22 Replies Latest reply: Jul 23, 2013 4:54 PM by 1027561 RSS

    ALTER SYSTEM SET SGA_TARGET = 0 scope=spfile; not working

    755353
      SQL> ALTER SYSTEM SET SGA_TARGET = 0 scope=spfile;

      System altered.

      SQL> shutdown immediate;
      Database closed.
      Database dismounted.
      ORACLE instance shut down.
      SQL> startup;
      ORACLE instance started.

      Total System Global Area 2121256960 bytes
      Fixed Size 2243280 bytes
      Variable Size 1140852016 bytes
      Database Buffers 973078528 bytes
      Redo Buffers 5083136 bytes
      Database mounted.
      Database opened.
      SQL> show parameters sga

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      lock_sga boolean FALSE
      pre_page_sga boolean FALSE
      sga_max_size big integer 2032M
      sga_target big integer 0
      SQL> create pfile=#/home/oracle/per03pfile' from spfile;


      pfile has
      *.sga_max_size=0
      *.sga_target=0

      and

      instance1.__sga_target=2130706432
      instance2.__sga_target=2130706432

      tried ALTER SYSTEM SET SGA_TARGET = 0 scope=spfile sid='*'; aswell ...

      oracle v11.2.0.2
      GI 11.2.0.2
      linux 64 bit
        • 1. Re: ALTER SYSTEM SET SGA_TARGET = 0 scope=spfile; not working
          Thierry H.
          Hi,

          did you also try:
          SQL>ALTER SYSTEM SET SGA_TARGET = 0 scope=spfile sid='instance1';
          SQL>ALTER SYSTEM SET SGA_TARGET = 0 scope=spfile sid='instance2';

          Thanks,
          Thierry
          • 2. Re: ALTER SYSTEM SET SGA_TARGET = 0 scope=spfile; not working
            Chinar
            user9198889 wrote:
            SQL> ALTER SYSTEM SET SGA_TARGET = 0 scope=spfile;

            System altered.

            SQL> shutdown immediate;
            Database closed.
            Database dismounted.
            ORACLE instance shut down.
            SQL> startup;
            ORACLE instance started.

            Total System Global Area 2121256960 bytes
            Fixed Size 2243280 bytes
            Variable Size 1140852016 bytes
            Database Buffers 973078528 bytes
            Redo Buffers 5083136 bytes
            Database mounted.
            Database opened.
            SQL> show parameters sga

            NAME TYPE VALUE
            ------------------------------------ ----------- ------------------------------
            lock_sga boolean FALSE
            pre_page_sga boolean FALSE
            sga_max_size big integer 2032M
            sga_target big integer 0
            SQL> create pfile=#/home/oracle/per03pfile' from spfile;


            pfile has
            *.sga_max_size=0
            *.sga_target=0

            and

            instance1.__sga_target=2130706432
            instance2.__sga_target=2130706432

            tried ALTER SYSTEM SET SGA_TARGET = 0 scope=spfile sid='*'; aswell ...

            oracle v11.2.0.2
            GI 11.2.0.2
            linux 64 bit
            While SGA_MA_SIZE <> 0 then it seems SHARED MEMORY constructed according this setting.So it is not recommended to avoid automatic shared memory management.But if you decide this then you have to identify SHARED_PLOOL_SIZE,DB_CACHE_SIZE(in additionally LOG_BUFFER and LARGE_POOL) parameter manually and
            ALTER SYSTEM SET SGA_TARGET=o scope=spfile and ALTER SYSTEM SET SGA_MAX_SIZE=o scope=spfile and restart database again.
            • 3. Re: ALTER SYSTEM SET SGA_TARGET = 0 scope=spfile; not working
              755353
              Chinar wrote:
              user9198889 wrote:
              SQL> ALTER SYSTEM SET SGA_TARGET = 0 scope=spfile;

              System altered.

              SQL> shutdown immediate;
              Database closed.
              Database dismounted.
              ORACLE instance shut down.
              SQL> startup;
              ORACLE instance started.

              Total System Global Area 2121256960 bytes
              Fixed Size 2243280 bytes
              Variable Size 1140852016 bytes
              Database Buffers 973078528 bytes
              Redo Buffers 5083136 bytes
              Database mounted.
              Database opened.
              SQL> show parameters sga

              NAME TYPE VALUE
              ------------------------------------ ----------- ------------------------------
              lock_sga boolean FALSE
              pre_page_sga boolean FALSE
              sga_max_size big integer 2032M
              sga_target big integer 0
              SQL> create pfile=#/home/oracle/per03pfile' from spfile;


              pfile has
              *.sga_max_size=0
              *.sga_target=0

              and

              instance1.__sga_target=2130706432
              instance2.__sga_target=2130706432

              tried ALTER SYSTEM SET SGA_TARGET = 0 scope=spfile sid='*'; aswell ...

              oracle v11.2.0.2
              GI 11.2.0.2
              linux 64 bit
              While SGA_MA_SIZE <> 0 then it seems SHARED MEMORY constructed according this setting.So it is not recommended to avoid automatic shared memory management.But if you decide this then you have to identify SHARED_PLOOL_SIZE,DB_CACHE_SIZE(in additionally LOG_BUFFER and LARGE_POOL) parameter manually and
              ALTER SYSTEM SET SGA_TARGET=o scope=spfile and ALTER SYSTEM SET SGA_MAX_SIZE=o scope=spfile and restart database again.
              sorry forgot to mention I have AMM enabled ...
              SQL> show parameters memor

              NAME TYPE VALUE
              ------------------------------------ ----------- ------------------------------
              hi_shared_memory_address integer 0
              memory_max_target big integer 3104M
              memory_target big integer 3104M
              shared_memory_address integer 0
              SQL>
              • 4. Re: ALTER SYSTEM SET SGA_TARGET = 0 scope=spfile; not working
                755353
                Chinar wrote:
                user9198889 wrote:
                SQL> ALTER SYSTEM SET SGA_TARGET = 0 scope=spfile;

                System altered.

                SQL> shutdown immediate;
                Database closed.
                Database dismounted.
                ORACLE instance shut down.
                SQL> startup;
                ORACLE instance started.

                Total System Global Area 2121256960 bytes
                Fixed Size 2243280 bytes
                Variable Size 1140852016 bytes
                Database Buffers 973078528 bytes
                Redo Buffers 5083136 bytes
                Database mounted.
                Database opened.
                SQL> show parameters sga

                NAME TYPE VALUE
                ------------------------------------ ----------- ------------------------------
                lock_sga boolean FALSE
                pre_page_sga boolean FALSE
                sga_max_size big integer 2032M
                sga_target big integer 0
                SQL> create pfile=#/home/oracle/per03pfile' from spfile;


                pfile has
                *.sga_max_size=0
                *.sga_target=0

                and

                instance1.__sga_target=2130706432
                instance2.__sga_target=2130706432

                tried ALTER SYSTEM SET SGA_TARGET = 0 scope=spfile sid='*'; aswell ...

                oracle v11.2.0.2
                GI 11.2.0.2
                linux 64 bit
                While SGA_MA_SIZE <> 0 then it seems SHARED MEMORY constructed according this setting.So it is not recommended to avoid automatic shared memory management.But if you decide this then you have to identify SHARED_PLOOL_SIZE,DB_CACHE_SIZE(in additionally LOG_BUFFER and LARGE_POOL) parameter manually and
                ALTER SYSTEM SET SGA_TARGET=o scope=spfile and ALTER SYSTEM SET SGA_MAX_SIZE=o scope=spfile and restart database again.
                SQL> alter system set sga_max_size=0 scope=spfile sid='instance1';
                System altered.

                SQL> shutdown immediate;
                Database closed.
                Database dismounted.
                ORACLE instance shut down.
                SQL> startup;
                ORACLE instance started.


                SQL> show parameters sga

                NAME TYPE VALUE
                ------------------------------------ ----------- ------------------------------
                lock_sga boolean FALSE
                pre_page_sga boolean FALSE
                sga_max_size big integer 2032M
                sga_target big integer 0
                SQL>


                has it got todo with AMM being enabbled?
                • 5. Re: ALTER SYSTEM SET SGA_TARGET = 0 scope=spfile; not working
                  Chinar
                  sorry forgot to mention I have AMM enabled ...
                  SQL> show parameters memor

                  NAME TYPE VALUE
                  ------------------------------------ ----------- ------------------------------
                  hi_shared_memory_address integer 0
                  memory_max_target big integer 3104M
                  memory_target big integer 3104M
                  shared_memory_address integer 0
                  SQL>
                  So if you want disable AMM then you have to set zero both memory_max_target/memory_target and set manually SGA components.
                  • 6. Re: ALTER SYSTEM SET SGA_TARGET = 0 scope=spfile; not working
                    Uwehesse-Oracle
                    You are on 11g, right? And you have set MEMORY_TARGET>0, right? The you set SGA_TARGET=0 and you wonder about
                    __sga_target=<some value>, right?

                    It helps if you tell us the output of select * from v$version, by the way :-)

                    That was because MMAN (Memory Manager) has set sga_target automatically together with pga_aggregate_target in the boundary of memory_target. Your setting of SGA_TARGET=0 just means that you set no lower boundary for that value.

                    Kind regards
                    Uwe Hesse

                    http://uhesse.wordpress.com
                    • 7. Re: ALTER SYSTEM SET SGA_TARGET = 0 scope=spfile; not working
                      755353
                      Uwe Hesse wrote:
                      You are on 11g, right? And you have set MEMORY_TARGET>0, right? The you set SGA_TARGET=0 and you wonder about
                      __sga_target=<some value>, right?

                      It helps if you tell us the output of select * from v$version, by the way :-)

                      That was because MMAN (Memory Manager) has set sga_target automatically together with pga_aggregate_target in the boundary of memory_target. Your setting of SGA_TARGET=0 just means that you set no lower boundary for that value.

                      Kind regards
                      Uwe Hesse

                      http://uhesse.wordpress.com
                      SQL> select * from v$version;

                      BANNER
                      --------------------------------------------------------------------------------
                      Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
                      PL/SQL Release 11.2.0.2.0 - Production
                      CORE 11.2.0.2.0 Production
                      TNS for Linux: Version 11.2.0.2.0 - Production
                      NLSRTL Version 11.2.0.2.0 - Production

                      the issue is not with sga target, its with sga max size ( upper boundary)

                      SQL> show parameters sga

                      NAME TYPE VALUE
                      ------------------------------------ ----------- ------------------------------
                      lock_sga boolean FALSE
                      pre_page_sga boolean FALSE
                      sga_max_size big integer 2032M
                      sga_target big integer 0
                      • 8. Re: ALTER SYSTEM SET SGA_TARGET = 0 scope=spfile; not working
                        364594
                        As you say it's not working, what did you expect to see after running this command and restarting?
                        • 9. Re: ALTER SYSTEM SET SGA_TARGET = 0 scope=spfile; not working
                          Uwehesse-Oracle
                          From the doc
                          http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/initparams230.htm#CHDJFEBE
                          >
                          On 64-bit platforms and non-Windows 32-bit platforms, when either MEMORY_TARGET or MEMORY_MAX_TARGET is specified, the default value of SGA_MAX_SIZE is set to the larger of the two parameters. This causes more address space to be reserved for expansion of the SGA.
                          >

                          I don't see an "issue" here. The SGA will not get larger than the whole memory (SGA + PGA) available :-)

                          Kind regards
                          Uwe Hesse

                          http://uhesse.wordpress.com
                          • 10. Re: ALTER SYSTEM SET SGA_TARGET = 0 scope=spfile; not working
                            755353
                            OracleDBA wrote:
                            As you say it's not working, what did you expect to see after running this command and restarting?
                            Im expecting sga max size to show 0.
                            isnt correct that if Im using AMM then sga max size should be set to zero ?

                            SQL> show parameters memor

                            NAME TYPE VALUE
                            ------------------------------------ ----------- ------------------------------
                            hi_shared_memory_address integer 0
                            memory_max_target big integer 3104M
                            memory_target big integer 3104M
                            shared_memory_address integer 0
                            SQL> show parameters sga

                            NAME TYPE VALUE
                            ------------------------------------ ----------- ------------------------------
                            lock_sga boolean FALSE
                            pre_page_sga boolean FALSE
                            sga_max_size big integer 2032M
                            sga_target big integer 0
                            SQL>
                            • 11. Re: ALTER SYSTEM SET SGA_TARGET = 0 scope=spfile; not working
                              364594
                              Please have a look at:
                              http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1137385000346840516
                              and in this page search for MEMORY_TARGET and SGA_MAX_SIZE
                              • 12. Re: ALTER SYSTEM SET SGA_TARGET = 0 scope=spfile; not working
                                755353
                                OracleDBA wrote:
                                Please have a look at:
                                http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1137385000346840516
                                and in this page search for MEMORY_TARGET and SGA_MAX_SIZE
                                Tom replied to him :
                                1) so? you are using automatic memory management, we set things - the caches, everything, to whatever we feel like. If you set memory_target, you need set nothing else. Just don't set it at all. Unset it.


                                2) if using memory_target - that controls sga and pga, it is highly "not ever likely" that the sga will be 10gb as some of that memory would necessarily be set aside for the pga.

                                but it would be OS dependent as to whether a backing store for the maximum sga size would be allocated upon instance startup. Even if it is however, it is just told to the OS we are wanting to be able to be this big - we don't touch that memory, we don't use it, it stays out there in the page files until we do. In other words - even if "allocated" it really doesn't matter - it doesn't get paged in or anything until we USE it.

                                does thiss mean oracle is telling Os i need to be able to have 2032M for sga max size ( in my case?)?

                                Edited by: user9198889 on Jan 26, 2011 6:19 AM
                                • 13. Re: ALTER SYSTEM SET SGA_TARGET = 0 scope=spfile; not working
                                  364594
                                  You've not assinged any thing to sga_max_size in your command! you've done it to sga_target .
                                  Try this:
                                  Re: ALTER SYSTEM SET SGA_MAX_SIZE = 0 scope=spfile;
                                  and then bounce the db.
                                  • 14. Re: ALTER SYSTEM SET SGA_TARGET = 0 scope=spfile; not working
                                    755353
                                    OracleDBA wrote:
                                    You've not assinged any thing to sga_max_size in your command! you've done it to sga_target .
                                    Try this:
                                    Re: ALTER SYSTEM SET SGA_MAX_SIZE = 0 scope=spfile;
                                    and then bounce the db.
                                    I tried , but no joy .....

                                    SQL> ALTER SYSTEM SET SGA_MAX_SIZE = 0 scope=spfile;

                                    System altered.

                                    SQL> shutdown immediate;
                                    Database closed.
                                    Database dismounted.
                                    ORACLE instance shut down.
                                    SQL> startup;
                                    ORACLE instance started.

                                    Total System Global Area 2121256960 bytes
                                    Fixed Size 2243280 bytes
                                    Variable Size 1325401392 bytes
                                    Database Buffers 788529152 bytes
                                    Redo Buffers 5083136 bytes
                                    Database mounted.
                                    Database opened.
                                    SQL> show parameters sga

                                    NAME TYPE VALUE
                                    ------------------------------------ ----------- ------------------------------
                                    lock_sga boolean FALSE
                                    pre_page_sga boolean FALSE
                                    sga_max_size big integer 2032M
                                    sga_target big integer 0
                                    SQL> alter system set SGA_MAX_SIZE =0 scope=spfile sid='instance1';

                                    System altered.

                                    SQL> shutdown immediate;
                                    Database closed.
                                    Database dismounted.
                                    ORACLE instance shut down.
                                    SQL> startup;
                                    ORACLE instance started.

                                    Total System Global Area 2121256960 bytes
                                    Fixed Size 2243280 bytes
                                    Variable Size 1325401392 bytes
                                    Database Buffers 788529152 bytes
                                    Redo Buffers 5083136 bytes
                                    Database mounted.
                                    Database opened.
                                    SQL> show parameters sga

                                    NAME TYPE VALUE
                                    ------------------------------------ ----------- ------------------------------
                                    lock_sga boolean FALSE
                                    pre_page_sga boolean FALSE
                                    sga_max_size big integer 2032M
                                    sga_target big integer 0
                                    SQL>
                                    1 2 Previous Next