1 2 Previous Next 23 Replies Latest reply: Feb 12, 2014 10:59 AM by jgarry RSS

    Memory Idle?

      Hi all,

       

      11.2.0.1

      Aix

       

      We have a PROD database that is alloted an SGA target of 10G. It this memory size fix wholly  taken  immediate from my Server RAM?

      What I mean is even if the database is idle it will consume or held up the 10G from the OS or Server RAM.

       

       

      Thanks a lot,

      pK

        • 1. Re: Memory Idle?
          Baris Yildirim

          Hello

          yes database takes memory from OS in conformity with Sga_target

          the last used and frequency used blocks are preserved in SGA

          Database always waits for client calls, it hasn't been designed that it would be idle.

          You can use memory advisor to determine your sga size properly

          and also you can use

          v$sga_target_advice and v$pga_target_advice

          Regards

          • 2. Re: Memory Idle?
            Anar Godjaev

            HI

             

            yes as said Barish you database waits for client calls and database takes memory from OS in conformity with Sga_target . More information (Thought it would be useful) please read following link:

             

            Memory Management |

            Tuning SGA |

             

            Thank you

            • 3. Re: Memory Idle?

              Thanks Anar, Baris

               

              SQL> select * from v$sga_target_advice;

               

               

                SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS

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

                     804             .75        12162              1.0363             3396858

                    1072               1        11736                   1             3389063

                    1340            1.25        11736                   1             3389063

                    1608             1.5        11736                   1             3389063

                    1876            1.75        11736                   1             3389063

                    2144               2        11736                   1             3389063

               

               

              6 rows selected.

               

              PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ADV BYTES_PROCESSED  ESTD_TIME

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

              ESTD_EXTRA_BYTES_RW ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT

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

                             75497472              .125 ON       1.5111E+10     120142

                       8466919424                            64                  282

               

               

                            150994944               .25 ON       1.5111E+10     101075

                       4725033984                            76                  227

               

               

                            301989888                .5 ON       1.5111E+10      81705

                        923583488                            94                    8

               

               

                            452984832               .75 ON       1.5111E+10      80354

                        658435072                            96                    0

               

               

                            603979776                 1 ON       1.5111E+10      79812

                        552130560                            96                    0

               

               

                            724774912               1.2 ON       1.5111E+10      78813

                        356050944                            98                    0

               

               

                            845571072               1.4 ON       1.5111E+10      78813

                        356050944                            98                    0

               

               

                            966367232               1.6 ON       1.5111E+10      78813

                        356050944                            98                    0

               

               

                           1087163392               1.8 ON       1.5111E+10      78813

                        356050944                            98                    0

               

               

                           1207959552                 2 ON       1.5111E+10      78813

                        356050944                            98                    0

               

               

                           1811939328                 3 ON       1.5111E+10      78813

                        356050944                            98                    0

               

               

                           2415919104                 4 ON       1.5111E+10      78813

                        356050944                            98                    0

               

               

                           3623878656                 6 ON       1.5111E+10      78813

                        356050944                            98                    0

               

               

                           4831838208                 8 ON       1.5111E+10      78813

                        356050944                            98                    0

               

               

              14 rows selected.

               

               

              What would be my average Memory requirement? Supposing I have initial sga target of 10G and the db advisor showed that my database only uses 1G. Is it appropriate that I deduct my memory allocation?

              • 4. Re: Memory Idle?
                Baris Yildirim

                Hi my friend,


                it seems your sga's size is 1072MB not 10G, also you can set it to 804mb, because ESTD_PHYSICAL_READS doesn't change very much.


                please post the below's result


                SELECT name,display_value FROM v$parameter where name like 'sga%;


                SELECT * FROM V$SGA_DYNAMIC_COMPONENTS;



                Regards

                • 5. Re: Memory Idle?

                  Thanks dear

                   

                  But here is the real one , can you suggest if what is the recommended or advised memory size? Thanksssssss

                   

                  We have 2 Prod databases in 1 server:

                   

                  SQL>

                   

                    SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS

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

                        2000              .5        32991              1.0288             1919151

                        3000             .75        32122              1.0017              924039

                        4000               1        32067                   1              831269

                        5000            1.25        32045               .9993              794194

                        6000             1.5        32012               .9983              732431

                        7000            1.75        32012               .9983              732431

                        8000               2        32012               .9983              732431

                   

                   

                  7 rows selected.

                   

                  SQL> connect system/@prod1

                  Connected.

                  SQL>  select * from v$sga_target_advice;

                   

                   

                    SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS

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

                        1000             .25         7770              1.0341              406501

                        2000              .5         7545              1.0041              202336

                        3000             .75         7517              1.0004              176072

                        4000               1         7514                   1              174277

                        5000            1.25         7514                   1              174085

                        6000             1.5         7514                   1              174085

                        7000            1.75         7514                   1              174085

                        8000               2         7514                   1              174085

                   

                   

                  8 rows selected.

                   

                  SQL>  SELECT name||' '||display_value FROM v$parameter where name like 'sga%';

                   

                   

                  NAME||''||DISPLAY_VALUE

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

                  sga_max_size 8G

                  sga_target 0

                   

                  COMPONENT                                                        CURRENT_SIZE   MIN_SIZE   MAX_SIZE

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

                  USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER LAST_OPER GRANULE_SIZE

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

                  shared pool                                                        1543503872 1543503872 1543503872

                                    0          0 STATIC                                16777216

                   

                   

                  large pool                                                           16777216   16777216   16777216

                                    0          0 STATIC                                16777216

                   

                   

                  java pool                                                            33554432   33554432   33554432

                                    0          0 STATIC                                16777216

                   

                   

                  streams pool                                                         33554432   33554432   33554432

                                    0          0 STATIC                                16777216

                   

                   

                  DEFAULT buffer cache                                               2516582400 2516582400 2516582400

                                    0          0 INITIALIZING                          16777216

                   

                   

                  KEEP buffer cache                                                           0          0          0

                                    0          0 STATIC                                16777216

                   

                   

                  RECYCLE buffer cache                                                        0          0          0

                                    0          0 STATIC                                16777216

                   

                   

                  DEFAULT 2K buffer cache                                                     0          0          0

                                    0          0 STATIC                                16777216

                   

                   

                  DEFAULT 4K buffer cache                                                     0          0          0

                                    0          0 STATIC                                16777216

                   

                   

                  DEFAULT 8K buffer cache                                                     0          0          0

                                    0          0 STATIC                                16777216

                   

                   

                  DEFAULT 16K buffer cache                                                    0          0          0

                                    0          0 STATIC                                16777216

                   

                   

                  DEFAULT 32K buffer cache                                                    0          0          0

                                    0          0 STATIC                                16777216

                   

                   

                  Shared IO Pool                                                              0          0          0

                                    0          0 STATIC                                16777216

                   

                   

                  ASM Buffer Cache                                                            0          0          0

                                    0          0 STATIC                                16777216

                   

                   

                   

                   

                  14 rows selected.

                  • 6. Re: Memory Idle?
                    Baris Yildirim

                    Hi

                     

                    sga_target seems zero my friend

                     

                    I think you should set sga_target to suitable value, and after a while (after a few daily workload) you should look at v$sga_target_advice again and consider it.

                     

                    Regards

                    • 7. Re: Memory Idle?
                      Harmandeep Singh

                      I observed this way. Please correct if  i am wrong.

                       

                      Say you have set SGA to 10G but as of now due to current load it is using only 6G , then at physical OS level you will see 6G used.

                      Declaring 10G is like Oracle saying to OS, I needs this much, but OS giving 6G only as per current needs.

                      The rest 4G is reserved for Oracle by OS but still uses it for OS related memory caching and file buffering purposes. This 4G will not be given to any process. When needed in future , OS will give it to Oracle.

                       

                      Thanks,

                      Harman

                      • 8. Re: Memory Idle?
                        Anar Godjaev

                        Hi,

                        SQL>  SELECT name||' '||display_value FROM v$parameter where name like 'sga%';

                         

                         

                        NAME||''||DISPLAY_VALUE

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

                        sga_max_size 8G

                        sga_target 0

                         

                         

                         

                        Default value =0 (SGA autotuning is disabled)

                        SGA_TARGET specifies the total size of all SGA components. If SGA_TARGET is specified, then the following memory pools are automatically sized:

                        Buffer cache (DB_CACHE_SIZE)

                        Shared pool (SHARED_POOL_SIZE)

                        Large pool (LARGE_POOL_SIZE)

                        Java pool (JAVA_POOL_SIZE)

                        If these automatically tuned memory pools are set to non-zero values, then those values are used as minimum levels by Automatic Shared Memory Management. You would set minimum values if an application component needs a minimum amount of memory to function properly.

                        The following pools are manually sized components and are not affected by Automatic Shared Memory Management:

                        Log buffer

                        Other buffer caches, such as KEEP, RECYCLE, and other block sizes

                        Streams pool

                        Fixed SGA and other internal allocations

                        The memory allocated to these pools is deducted from the total available for SGA_TARGET when Automatic Shared Memory Management computes the values of the automatically tuned memory pools.

                        SGA_TARGET

                        • 10. Re: Memory Idle?

                          I thank you all,

                           

                          Can I set target SGA to 8G without downtime?

                           

                          What is the effect of target sga = 0? does this mean my database will perform slow compared to SGA with target 8G?

                          • 11. Re: Memory Idle?
                            Suntrupth

                            You can set SGA_TARGET  to 8G without downtime. Setting SGA_MAX_SIZE will require a restart of your database. You can set SGA_TARGET a little lower than SGA_MAX_SIZE and observe your DB performance although you would be wasting memory if you haven't set SGA_TARGET = SGA_MAX_SIZE. Test the performance for sometime, check v$sga_target_advise or Memory Advisor on EM and you should be able to set the values optimally.

                             

                            By setting SGA_TARGET to 0 you are only disabling Auto-tuning of sga components. You can set the size of the components manually to the desired size and make sure you ALWAYS test your setup.

                             

                            Regards,

                            Suntrupth

                            • 12. Re: Memory Idle?

                              Thanks all,

                               

                              By setting SGA_TARGET to 0 you are only disabling Auto-tuning of sga components. You can set the size of the components manually to the desired size and make sure you ALWAYS test your setup.


                              Well this is our DR database and we transferred to it because our primary db is to be installed with Database Vault (DV) and Transparent Data Encryption (TDE). It is a good thing

                              the the perpormance is still acceptable to the users even if I did not set the target SGA.


                              Does this mean that this report is meaningless ? Thanks


                              QL>  select * from v$sga_target_advice;

                               

                               

                                SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS

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

                                    1000             .25         7770              1.0341              406501

                                    2000              .5         7545              1.0041              202336

                                    3000             .75         7517              1.0004              176072

                                    4000               1         7514                   1              174277

                                    5000            1.25         7514                   1              174085

                                    6000             1.5         7514                   1              174085

                                    7000            1.75         7514                   1              174085

                                    8000               2         7514                   1              174085

                              • 13. Re: Memory Idle?
                                Suntrupth

                                The V$SGA_TARGET_ADVICE view provides information that helps you decide on a value for SGA_TARGET

                                 

                                Have a look at the following:

                                 

                                SQL> show parameter sga

                                 

                                 

                                NAME                                 TYPE        VALUE

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

                                lock_sga                             boolean     FALSE

                                pre_page_sga                         boolean     FALSE

                                sga_max_size                         big integer 1G

                                sga_target                           big integer 0

                                SQL> select * from v$sga_target_advice order by sga_size;

                                 

                                 

                                  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS

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

                                       334              .5           55                   1                1768

                                       501             .75           55                   1                1768

                                       668               1           55                   1                1768

                                       835            1.25           55                   1                1768

                                      1002             1.5           55                   1                1768

                                      1169            1.75           55                   1                1768

                                      1336               2           55                   1                1768

                                 

                                 

                                7 rows selected.

                                 

                                 

                                SQL> show parameter memory

                                 

                                 

                                NAME                                 TYPE        VALUE

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

                                hi_shared_memory_address             integer     0

                                memory_max_target                    big integer 1G

                                memory_target                        big integer 1G

                                shared_memory_address                integer     0

                                SQL> ALTER SYSTEM SET SGA_TARGET=0;

                                 

                                 

                                System altered.

                                 

                                 

                                SQL> select * from v$sga_target_advice order by sga_size;

                                 

                                 

                                  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS

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

                                       334              .5          115              1.1241               17417

                                       501             .75          102                   1               15412

                                       668               1          102                   1               15412

                                       835            1.25          102                   1               15412

                                      1002             1.5          102                   1               15412

                                      1169            1.75          102                   1               15412

                                      1336               2          102                   1               15412

                                 

                                 

                                7 rows selected.

                                 

                                 

                                SQL> startup force

                                ORACLE instance started.

                                 

                                 

                                Total System Global Area 1068937216 bytes

                                Fixed Size                  2182592 bytes

                                Variable Size             796918336 bytes

                                Database Buffers          264241152 bytes

                                Redo Buffers                5595136 bytes

                                Database mounted.

                                Database opened.

                                SQL> select * from v$sga_target_advice order by sga_size;

                                 

                                 

                                  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS

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

                                       334              .5           33                   1                2258

                                       501             .75           33                   1                2258

                                       668               1           33                   1                2258

                                       835            1.25           33                   1                2258

                                      1002             1.5           33                   1                2258

                                      1169            1.75           33                   1                2258

                                      1336               2           33                   1                2258

                                 

                                 

                                7 rows selected.

                                 

                                Please go through the following link : http://docs.oracle.com/cd/E18283_01/server.112/e17120/memory004.htm

                                 

                                Regards,

                                Suntrupth

                                • 14. Re: Memory Idle?

                                  Thanks all,

                                   

                                  My question is, will it necessarily speedup my prod database if I set SGA target=8g? than compared to SGA target =0?

                                  1 2 Previous Next