12 Replies Latest reply on Dec 21, 2015 2:48 AM by Beauty_and_dBest

    Auto Memory Tuning

    Beauty_and_dBest

      Hi ALL,

       

      EBS R12.2.4

      OEL6

       

      We are now setting up for PROD instance.

      Our default target SGA for now is 1Gb.

      How do I setup our database to "auto memory tuning" with target SGA = 6Gb?

      What other parameters need to be change that is affected by it?

       

       

      Kind regards,

      jc

        • 1. Re: Auto Memory Tuning
          Pravin Takpire

          Hi,

          I am not sure if you mean to say Automatic Memory Management. Please check below for Automatic Memory Management

          https://oracle-base.com/articles/11g/automatic-memory-management-11gr1

          But for EBS better you don't use AMM. Set SGA_TARGET and SGA_MAX_SIZE

           

          Automatic Shared Memory Management

          Automatic Shared Memory Management puts Oracle in control of allocating memory within the SGA. The SGA_TARGET parameter sets the amount of memory available to the SGA. This parameter can be altered dynamically up to a maximum of the SGA_MAX_SIZE parameter value. Provided the STATISTICS_LEVEL is set to TYPICAL or ALL and the SGA_TARGET is set to a value other than "0" Oracle will control the memory pools which would otherwise be controlled by the following parameters.

          • DB_CACHE_SIZE (default block size)
          • SHARED_POOL_SIZE
          • LARGE_POOL_SIZE
          • JAVA_POOL_SIZE

            If these parameters are set to a non-zero value they represent the minimum size for the pool. These minimum values may be necessary if you experience application errors when certain pool sizes drop below a specific threshold. 

          The following parameters must be set manually and take memory from the quota allocated by the SGA_TARGET parameter.

          • DB_KEEP_CACHE_SIZE
          • DB_RECYCLE_CACHE_SIZE
          • DB_nK_CACHE_SIZE (non-default block size)
          • STREAMS_POOL_SIZE
          • LOG_BUFFER

          So I would suggest you set SGA_TARGET to 6G and SGA_MAX_SIZE to 6g(or more) and also set PGA_AGGREGATE_TARGET to some value based on your server configuration and requirement

           

          regards

          Pravin

          1 person found this helpful
          • 2. Re: Auto Memory Tuning
            Beauty_and_dBest

            Thanks pravin,

             

            I do not know what  is our PGA_AGGREGATE_TARGET  config  requirement Is there any estimate or default I can use?

            What if I set only the two parameters:

            SGA_TARGET=6Gb

            SGA_MAX_SIZE=6Gb


            Will it be okay?


            Kind regards,


            • 3. Re: Auto Memory Tuning
              Srini Chavali-Oracle

              Review and understand MOS Doc 396009.1

              1 person found this helpful
              • 4. Re: Auto Memory Tuning
                Pravin Takpire

                what are your server configuration ? but if you have 6G of SGA then PGA can be in range of 1.5 to 2 G

                regards

                Pravin

                1 person found this helpful
                • 5. Re: Auto Memory Tuning
                  Beauty_and_dBest

                  For now we are using the default parameters used in the cloning of UAT, which is SGA of 2Gb.

                   

                  Kind regards.

                  • 6. Re: Auto Memory Tuning
                    Hussein Sawwan-Oracle

                    Jc,

                     

                    For now we are using the default parameters used in the cloning of UAT, which is SGA of 2Gb.


                    2 GB is not sufficient, please increase this value along with other init parameters under "Section 9: Database Initialization Parameter Sizing" in (Doc ID 396009.1).


                    Thanks,

                    Hussein

                    1 person found this helpful
                    • 7. Re: Auto Memory Tuning
                      Beauty_and_dBest

                      Thanks ALL!!

                      • 8. Re: Auto Memory Tuning
                        Beauty_and_dBest

                        Hi hussein and ALL,

                         

                        I checked the MOS, what is #MP?

                         

                        <deleted contents of MOS Doc>

                         

                        I am confused 

                         

                        We have only 30 users, but we have 24Gb Physical Memory. So I will go  for 501-1000 Users because I do not want my resources idle?

                        Is it okay to maximize resource usage? even if it will not be used?

                         

                        Kind regards,

                        • 9. Re: Auto Memory Tuning
                          Hussein Sawwan-Oracle

                          Jenna_C wrote:

                           

                          Hi hussein and ALL,

                           

                          I checked the MOS, but it did not say about SGA settings

                          Only the following parameters where considered important??


                          compatible = 11.2.0 #MP
                          diagnostic_dest = ?/prod12
                          undo_management=AUTO #MP
                          undo_tablespace=APPS_UNDOTS1
                          #plsql_code_type = NATIVE #Uncomment if you want to use NATIVE compilation.
                          _optimizer_autostats_job=FALSE #MP Turn off automatic statistics.

                          parallel_force_local=TRUE #MP

                          sec_case_sensitive_logon = FALSE

                           

                           

                          How can I increase SGA when the NOTES does not mentioned  about it?

                           

                           

                          Kind regards,

                           

                          It's in section 9 as mentioned in my previous post -- Look for "sga_target".

                           

                          Thanks,

                          Hussein

                          • 10. Re: Auto Memory Tuning
                            Beauty_and_dBest

                            ok, I updated my previous post.

                            • 11. Re: Auto Memory Tuning
                              Hussein Sawwan-Oracle

                              We have only 30 users, but we have 24Gb Physical Memory. So I will go  for 501-1000 Users because I do not want my resources idle?

                               

                              Why do you need to enable AMM then? Why not stick with the init parameters mentioned in the same doc? If you have certain performance issues then you should be addressing and fixing it instead of enabling AMM. If no other env running on this server you can definitely increase the SGA size to utilize resources.

                               

                              I don't really see any issues in this thread and if we keep running in circles to identify an issue that does NOT exist OR to mark every reply as helpful/correct like it's the case with most of your threads then this thread should be locked.

                               

                              Thanks,

                              Hussein

                              1 person found this helpful
                              • 12. Re: Auto Memory Tuning
                                Beauty_and_dBest

                                Thanks hussein,

                                 

                                Sorry for being redundant

                                 

                                My point  or my understanding or afaik is that... AMM is created to make DBA life easy

                                I just wish someone can explain why is it not applicable to EBS.

                                Well, if it not applicable, then no questions asked, I will follow what you all advised.

                                 

                                 

                                Thanks you ALL!!!