8 Replies Latest reply on Aug 23, 2019 11:24 AM by Beauty_and_dBest

    What SGA for EBS R12.1 or R12.2 ?

    Beauty_and_dBest

      12c

       

      Hi ALL,

       

      Which portion  in AWR report will show that SGA or memory allocation in our database is good, or enough, or correct?

      Can AWR show which hour of the day was there peak usage in Memory/CPU in our EBS database?

      Is there an estimate or recommended size for SGA/Memory for EBS R12?

       

       

      WORKLOAD REPOSITORY report for

      PROD157292119PROD124-Jun-19 05:0611.1.0.7.0NO
      prod.oracle.comLinux x86 64-bit88238.38
      Begin Snap:7314124-Jul-19 01:00:2030523.9
      End Snap:7314224-Jul-19 02:00:2230324.5
      Elapsed:60.04 (mins)
      DB Time:9.88 (mins)

      Report Summary

      Cache Sizes

      Buffer Cache:11,776M11,776MStd Block Size:8K
      Shared Pool Size:3,200M3,200MLog Buffer:58,728K

      Load Profile

      DB Time(s):0.20.10.000.01
      DB CPU(s):0.20.10.000.01
      Redo size:14,286.55,654.5
      Logical reads:18,178.07,194.7
      Block changes:100.339.7
      Physical reads:2.30.9
      Physical writes:2.30.9
      User calls:22.69.0
      Parses:6.92.7
      Hard parses:0.10.0
      W/A MB processed:554,938.5219,641.2
      Logons:0.10.0
      Executes:42.216.7
      Rollbacks:1.50.6
      Transactions:2.5

      Instance Efficiency Percentages (Target 100%)

      Buffer Nowait %:100.00Redo NoWait %:100.00
      Buffer Hit %:99.99In-memory Sort %:100.00
      Library Hit %:97.10Soft Parse %:99.11
      Execute to Parse %:83.66Latch Hit %:99.99
      Parse CPU to Parse Elapsd %:0.01% Non-Parse CPU:99.86

      Shared Pool Statistics

      Memory Usage %:43.4143.36
      % SQL with executions>1:94.8796.81
      % Memory for SQL w/exec>1:95.1897.49

      Top 5 Timed Foreground Events

      DB CPU58398.41
      db file sequential read7,331611.08User I/O
      log file sync2,853210.37Commit
      local write wait594110.10User I/O
      control file sequential read1,063000.07System I/O

      Host CPU (CPUs: 8 Cores: 8 Sockets: 2)

      0.331.192.30.10.197.6

      Instance CPU

      2.292.90.0

      Memory Statistics

      Host Mem (MB):39,299.739,299.7
      SGA use (MB):15,360.015,360.0
      PGA use (MB):1,906.21,906.6
      % Host Mem used for SGA+PGA:43.9343.93

      Main Report

       

       

      Please help...

       

       

      Kind regards,

      jc

        • 1. Re: What SGA for EBS R12.1 or R12.2 ?
          VishnuVinnakota

          Hi,

           

          I will take them as 3 different question. Lets see:

           

          Which portion  in AWR report will show that SGA or memory allocation in our database is good, or enough, or correct?

          -- You need to refer to the "SGA target advisory" section or you can simply run the Memory Advisor manually any time. Based on the values you need to decide the improvement of performance. For e.g. if 1.25 SGA shows less Physical reads, then you need to definitely increase. Similarly for higher values too.

           

          Can AWR show which hour of the day was there peak usage in Memory/CPU in our EBS database?

          -- No. Usually most DBAs know it but you can start comparing the DB times for hourly AWR or use SYSSTAT related views.

           

          Is there an estimate or recommended size for SGA/Memory for EBS R12?

          -- It purely depends on the environment and each environment differs. The answer for 1st question applies here as well.

           

          Thanks,

          Vishnu

          • 2. Re: What SGA for EBS R12.1 or R12.2 ?
            Kanda-Oracle

            Hi JC

             

            You can go through (Doc ID 396009.1) and go to the last page.

             

            Section 9: Database Initialization Parameter Sizing

             

            Example It says 500 users x SGA size.

             

            It just give you a ballpark figure.

             

            Hope this helps!

             

             

             

            Thanks


            N Kandasamy

            • 3. Re: What SGA for EBS R12.1 or R12.2 ?
              Kanda-Oracle

              Hi

               

              To answer you may go through "SGA Target Advisory" (AWR report).

               

               

              1. To start with.... You can go through (Doc ID 396009.1) and Section 9: Database Initialization Parameter Sizing

               

              2. To tune further.... You may go through "SGA Target Advisory" (AWR report)

               

                 >> If the "Est DB Time (s)" decreases significantly as the "Size Factor" increases then increasing the SGA will significantly reduce the physical reads and improve performance.

               

              Thanks!

              • 4. Re: What SGA for EBS R12.1 or R12.2 ?
                Beauty_and_dBest

                Thanks ALL!

                 

                 

                For our 11g database:

                 

                For our 12c database:

                 

                How much memory do we need to add to our 11g, and 12c database?

                Do we need adjust related parameters like java_pool, pga, etc?

                 

                 

                Kind regards,

                • 5. Re: What SGA for EBS R12.1 or R12.2 ?
                  lmu

                  Look at the note Kanda -Oracle suggested:

                  Under Section: Section 5: Release-Specific Database Initialization Parameters for  Oracle 12c Release 1.  There is a Removal list:

                   

                  Go through the list. It is different for both 11g and 12c. Look up the parameters for each.

                   

                  5.2 Parameter Removal List for Oracle Database 12c Release 1

                  hash_area_size

                  java_pool_size

                  job_queue_interval

                  large_pool_size

                   

                  There is also a bde script you can run but it is basic and the results may be lacking.  it's best to check the note.

                  bde_chk_cbo.sql - EBS initialization parameters - Healthcheck ( Doc ID 174605.1  )

                  • 6. Re: What SGA for EBS R12.1 or R12.2 ?
                    Beauty_and_dBest

                    Thanks ALL!

                     

                    Our database is 12c,

                    Is bde_chk_cbo.sql - EBS initialization parameters - Healthcheck still applicable in 12c?

                     

                    We have EBS Linux Server with 32Gb and  8 CPU cores.

                    Both apps & db are in one node.

                    Can you share me your existing SGA parameter size for similar config with 100 concurrent users (or any number of users).

                    So I can use it as template model for reference.

                    Is below memory sizing still applicable in 12c? Or should I use  the automatic memory allocation?

                    Is AMM recommended ins EBS? I have not seen docs that recommend AMM in EBS.

                    What is the command again to check if we enabled or disabled AMM?

                    Which is better to use ? disabled AMM or enabled AMM?

                    Have you used AMM in your EBS instance?

                     

                     

                    Kind regards,

                    • 7. Re: What SGA for EBS R12.1 or R12.2 ?
                      lmu

                      You can't use huge pages if you use amm.

                       

                      Requirements

                      • Oracle Database instance(s) are up and running
                      • Oracle Database 11g Automatic Memory Management (AMM) is not setup  (See Note 749851.1)
                      • The shared memory segments can be listed by command "ipcs -m"
                      • Oracle Linux
                      • Package 'bc' installed

                       

                       

                      To check if you have it set,

                      check for the db parameters:

                      MEMORY_MAX_TARGET and MEMORY_TARGET

                       

                      show parameter memory_target

                      if it = 0 then it's not set.

                       

                      Here is a note:

                      • 8. Re: What SGA for EBS R12.1 or R12.2 ?
                        Beauty_and_dBest

                        Thanks ALL!