3 Replies Latest reply on Oct 27, 2019 8:29 AM by Beauty_and_dBest

    EBS R12.2.x SGA/PGA Allocation

    Beauty_and_dBest

      11g/12c

      OL6

       

      Hi ALL,

       

      I have set our database total memory to approximately 34Gb,

      Broken down as follows:

       

      SGA_TARGET=14G

      PGA_AGGREGATE_TARGET=20G

      PROCESSES=2500

      SESSIONS=5000

      SHARED_POOL_SIZE=3000M

      SHARED_POOL_RESERVED_SIZE=300M

       

      Is there a sql query that can check how much of the 34G  is being used?

      Or how do I know if it is  enough for our current production system?

       

       

      Please help...

       

       

      Kind regards,

      jc

        • 2. Re: EBS R12.2.x SGA/PGA Allocation
          Beauty_and_dBest

          Thanks Micheal,

           

          I reading them, but can not understand so much what it said, coz it mentioned a lot of topics

           

          Can you suggest which sql script is the appropriate one?

           

           

          Kind regards,

          • 3. Re: EBS R12.2.x SGA/PGA Allocation
            Beauty_and_dBest

            Hi Michael and ALL,

             

            Can you please validate if below query is near correct

            Or can you try the query in your PROD instance if result is near accurate?

             

             

            For the SGA part you can check v$sgastat to see how much of the 34GB is currently not in use - free memory

             

            SQL> break on report skip 1

            SQL> compute sum of bytes on report

            SQL> column bytes format 999,999,999,999

            SQL> select * from V$sgastat where name = 'free memory';

             

            POOL          NAME                    BYTES    CON_ID

            -------------- ------------ ---------------- ----------

            shared pool    free memory       121,147,544          0

            large pool     free memory        29,622,272          0

            java pool      free memory        16,777,216          0

                                        ----------------

            sum                              167,547,032

             

             

            For the PGA part you can check v$pgastat from the CDB if you're using pluggables.

             

            SQL> column name format a42

            SQL> column value format 999,999,999,999

            SQL> select * from v$pgastat;

             

            NAME                                                  VALUE UNIT            CON_ID

            ------------------------------------------ ---------------- ------------ ----------

            aggregate PGA target parameter                  436,207,616 bytes                 0

            aggregate PGA auto target                       178,292,736 bytes                 0

            global memory bound                              87,240,704 bytes                 0

            total PGA inuse                                 237,954,048 bytes                 0

            total PGA allocated                             287,746,048 bytes                 0

            maximum PGA allocated                           614,696,960 bytes                 0

             

            (There are more figures than this)

            The first is your setting for pga_aggregate_target

            The second is the current target - given that some has been allocated and not release.

            The third is the largest memory allocation a single process will currently be allowed

            the "total PGA" figures remind you that a process can have memory allocated that it is currently not using.

            The maximum PGA allocated is exactly what is sounds like - and you can see that I've gone quite a long way over the limit in the past.

            There is an "overallocation count" which gives you some idea of how frequently you go over the limit.

             

            It's important to remember that if you have free memory in the SGA Oracle will use it up if you keep firing lots of "literal string" - i.e. very similar, differing only in actual values used - SQL to be optimised and executed; so a very low free memory doesn't mean you need more memory, it could mean you're wasting memory (and CPU) on lots of hard parsing of non-sharable SQL.  This is why the AWR report shows you figures like  "% of memory used by SQL execute > 1 time"  (I've probably not quoted the label correctly, but it's an indication of the right idea.)

             

             

            Kind regards,