Use Oracle Databases 11g AMM

Use Oracle Databases 11g AMM

 

        In this blog, I will presnt an user case about how to use AMM in Oracle 11g and how to monitor the memory status.

 

        AMM Parameters

    Automatic memory management is configured using two new initialization parameters:

 

MEMORY_TARGET: The amount of shared memory available for  Oracle to use when dynamically controlling the SGA and PGA. This parameter is  dynamic, so the total amount of memory available to Oracle can be increased or decreased, provided it does not exceed the  MEMORY_MAX_TARGET limit. The default value is "0".

 

For example of one production database:

CMSPRD,

memory_target                        big integer 5G

 

MEMORY_MAX_TARGET: This defines the maximum size the MEMORY_TARGET can be increased to without an instance restart. If the  MEMORY_MAX_TARGET is not specified, it defaults to MEMORY_TARGET setting.

 

When using automatic memory management, the SGA_TARGET and PGA_AGGREGATE_TARGET act as minimum size settings for their respective memory  areas. To allow Oracle to take full control of the memory management, these parameters should be set to zero.

 

For CMSPRD,

pga_aggregate_target                 big integer 0

sga_target                           big integer 0

 

 

For detail information, we can check in AWR report,

 

WORKLOAD REPOSITORY report for

 

DB Name         DB Id    Instance     Inst Num Startup Time    Release     RAC

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

CMSPRD        3842382103 CMSPRD              1 26-Apr-10 14:32 11.1.0.6.0  NO

 

Host Name        Platform                         CPUs Cores Sockets Memory(GB)

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

ctmcmsdb.ctm.plc Linux 64-bit for AMD                4     4       1      15.68

 

              Snap Id      Snap Time      Sessions Curs/Sess

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

Begin Snap:     20156 10-Oct-11 00:00:29        43       5.4

  End Snap:     20181 11-Oct-11 00:00:31        45       5.6

   Elapsed:            1,440.05 (mins)

   DB Time:              245.61 (mins)

 

 

Memory Statistics

~~~~~~~~~~~~~~~~~                       Begin          End

                  Host Mem (MB):     16,053.8     16,053.8

                   SGA use (MB):      3,840.0      3,840.0

                   PGA use (MB):        608.1        319.1

    % Host Mem used for SGA+PGA:        27.71        27.71

 

It shows clearly the SGA and PGA sizes in the CMSPRD instance.

 

 

Instance Efficiency Percentages (Target 100%)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

            Buffer Nowait %:  100.00       Redo NoWait %:   92.37

            Buffer  Hit   %:   99.70    In-memory Sort %:  100.00

            Library Hit   %:   99.93        Soft Parse %:   99.85

         Execute to Parse %:    8.56         Latch Hit %:   99.59

Parse CPU to Parse Elapsd %:    0.00     % Non-Parse CPU:   98.99

 

All hit ratios are > 99.5%, which show that the SGA is large enough.

 

 

PGA Memory Advisory                        DB/Inst: CMSPRD/CMSPRD  Snap: 20181

-> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value

   where Estd PGA Overalloc Count is 0

 

                                       Estd Extra    Estd P Estd PGA

PGA Target    Size           W/A MB   W/A MB Read/    Cache Overallo    Estd

  Est (MB)   Factr        Processed Written to Disk   Hit %    Count    Time

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

       160     0.1     19,340,813.2     15,305,452.0   56.0  130,094   7.3E+07

       320     0.3     19,340,813.2      9,423,401.9   67.0   60,605   6.0E+07

       640     0.5     19,340,813.2      3,261,446.6   86.0    5,357   4.7E+07

       960     0.8     19,340,813.2      2,951,280.8   87.0    2,674   4.7E+07

     1,280     1.0     19,340,813.2      2,059,645.5   90.0        0   4.5E+07

     1,536     1.2     19,340,813.2      1,230,484.7   94.0        0   4.3E+07

     1,792     1.4     19,340,813.2      1,230,484.7   94.0        0   4.3E+07

     2,048     1.6     19,340,813.2      1,230,484.7   94.0        0   4.3E+07

     2,304     1.8     19,340,813.2      1,230,484.7   94.0        0   4.3E+07

     2,560     2.0     19,340,813.2      1,230,484.7   94.0        0   4.3E+07

     3,840     3.0     19,340,813.2      1,230,484.7   94.0        0   4.3E+07

     5,120     4.0     19,340,813.2      1,230,484.7   94.0        0   4.3E+07

     7,680     6.0     19,340,813.2      1,230,484.7   94.0        0   4.3E+07

    10,240     8.0     19,340,813.2      1,230,484.7   94.0        0   4.3E+07

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

 

It shows that the minimally choose a pga_aggregate_target value is only 1280 MB.

 

 

 

Therefore the CMSPRD instance only uses 5 GB and the current SGA and PGA sizes are adequate.

 

Reference:

http://www.oracle-base.com/articles/11g/AutomaticMemoryManagement_11gR1.php

 

严歌苓.jpg

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26136400/viewspace-709155/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26136400/viewspace-709155/