【AMM】關於資料庫例項AMM引數說明

About Automatic Memory Management

The simplest way  to manage instance memory is to allow the Oracle Database instance to  automatically manage and tune it for you. To do so (on most platforms), you  set only a  target memory size initialization parameter ( MEMORY_TARGET ) and optionally a  maximum memory  size initialization parameter ( MEMORY_MAX_TARGET ). The total memory that the instance uses remains  relatively constant, based on the value of  MEMORY_TARGET , and the instance automatically  distributes memory between the system global area (SGA) and the instance  program global area (instance PGA). As memory requirements change, the  instance dynamically redistributes memory between the SGA and instance PGA.

When automatic  memory management is not enabled, you must size both the SGA and instance PGA  manually.

Because  the  MEMORY_TARGET  initialization  parameter is dynamic, you can change  MEMORY_TARGET  at any time without restarting the  database.  MEMORY_MAX_TARGET , which is not  dynamic, serves as an upper limit so that you cannot accidentally set  MEMORY_TARGET  too high, and so that enough  memory is set aside for the database instance in case you do want to increase  total instance memory in the future. Because certain SGA components either  cannot easily shrink or must remain at a minimum size, the instance also  prevents you from setting  MEMORY_TARGET  too low.

If you create  your database with Database Configuration Assistant (DBCA) and choose the  basic installation option, automatic memory management is enabled. If you  choose advanced installation, Database Configuration Assistant (DBCA) enables  you to select automatic memory management.

SQL> show parameter mem

NAME                                 TYPE        VALUE

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

hi_shared_memory_address             integer     0

memory_max_target                    big integer 0

memory_target                        big integer 0

shared_memory_address                integer     0

SQL> show parameter sga

NAME                                 TYPE        VALUE

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

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     FALSE

sga_max_size                         big integer 512M

sga_target                           big integer 512M

SQL> show parameter pga

NAME                                 TYPE        VALUE

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

pga_aggregate_target                 big integer 2680M

SQL> alter system set  memory_max_target=1500M scope=spfile;

System altered.

SQL> alter system set  memory_target=1500M scope=spfile;

System altered.

SQL> startup force

ORACLE instance started.

Total System Global Area 1570009088 bytes

Fixed Size                  2253584 bytes

Variable Size            1308626160 bytes

Database Buffers          251658240 bytes

Redo Buffers                7471104 bytes

Database mounted.

Database opened.

SQL> show parameter mem

NAME                                 TYPE        VALUE

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

hi_shared_memory_address             integer     0

memory_max_target                    big integer 1504M

memory_target                        big integer 1504M

shared_memory_address                integer     0

SQL> alter system set  memory_max_target=1591M scope=spfile;

System altered.

SQL> alter system set  memory_target=1591M scope=spfile;

System altered.

SQL> startup force

ORACLE instance started.

Total System Global Area 1670221824 bytes

Fixed Size                  2253824 bytes

Variable Size            1409289216 bytes

Database Buffers          251658240 bytes

Redo Buffers                7020544 bytes

Database mounted.

Database opened.

SQL> show parameter mem

NAME                                 TYPE        VALUE

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

hi_shared_memory_address             integer     0

memory_max_target                    big integer 1600M

memory_target                        big integer 0

shared_memory_address                integer     0

[oracle@jjgkTdb ~]$ sysresv

IPC Resources for ORACLE_SID "loves"  :

Shared Memory:

ID              KEY

10190848        0x00000000

10223617        0x00000000

10256386        0x00000000

10289155        0x00000000

10321934        0x00000000

10354703        0x6f008b80

Semaphores:

ID              KEY

8617984         0x905a7e14

Oracle Instance alive for sid  "loves"

[oracle@jjgkTdb ~]$ ipcs -m

------ Shared Memory Segments --------

key         shmid      owner      perms      bytes      nattch     status     

0x00000000 10190848   oracle     640        4096       0                      

0x00000000 10223617   oracle     640        4096       0 

……………….

[oracle@jjgkTdb ~]$ pmap `pgrep -f lgwr`

30531:    ora_lgwr_loves

0000000000400000 189264K r-x--  /oracle11g/product/11.2.0/db_1/bin/oracle

000000000bed4000   2020K rw---  /oracle11g/product/11.2.0/db_1/bin/oracle

000000000c0cd000    348K rw---    [ anon ]

000000001ea7b000    264K rw---    [ anon ]

0000000060000000      4K r--s-  /dev/shm/ora_loves_10452992_0

0000000060001000  16380K rw-s-  /dev/shm/ora_loves_10452992_0

0000000061000000  16384K rw-s-  /dev/shm/ora_loves_10485761_0

0000000062000000  16384K rw-s-  /dev/shm/ora_loves_10485761_1

[oracle@jjgkTdb ~]$ ls -l /dev/shm/

總計 569952

-rw-r----- 1 oracle oinstall 16777216  02-14 08:45 ora_loves_10190848_0

-rw-r----- 1 oracle oinstall 16777216  02-14 08:45 ora_loves_10223617_0

-rw-r----- 1 oracle oinstall 16777216  02-14 08:45 ora_loves_10223617_1

[oracle@jjgkTdb ~]$ du –sm ora_loves_10190848_0

16  ora_loves_10190848_0

[oracle@jjgkTdb ~]$ ls -l /dev/shm

total 0

Thanks to allocating SGA in many smaller chunks, Oracle is  easily able to release some parts of SGA memory back to OS and server  processes are allowed to increase their aggregate PGA size up to the amount  of memory released.
(Btw, if your MEMORY_MAX_TARGET parameter is larger than 1024 MB then  Oracle’s memory granule size is 16MB on Linux, otherwise it’s 4MB).

<1span style="font-size:10.5pt;font-family:" color:#303030;"="">Note that the PGA memory is still completely independent  memory, allocated just by mmap’ing /dev/zero, it doesn’t really have anything  to do with shared memory segments ( unless you’re using some hidden  parameters on Solaris, but that’s another story ).
PGA_AGGREGATE_TARGET itself is just a recommended number, leaving over from  MEMORY_TARGET – SGA_TARGET (if it’s set). And Oracle uses that number to  decide how big PGAs it will “recommend” for sessions that are using  WORKAREA_SIZE_POLICY=AUTO