Automatic Memory Management 引數說明

一、官方介紹

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.

簡單點說，AMM=SGA+PGA ，引數memory_target 即可管理SGA 和PGA ，引數memory_max_target 最大記憶體引數，前者為動態引數，後者靜態引數，可作為前者設定引數值的上限及防止前者設定太低。

二、AMM 記憶體引數值設定的參考

檢視當前記憶體情況，現在是未啟動AMM( 此為測試庫，引數設定會不太合理，而且資源有限)

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

設定AMM ，此前我們檢視到SGA 、PGA 有固定值，再此不做討論。

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

發現memory 兩個引數值增加了4M ，檢視警告日誌，發現啟動時讀取引數檔案後，其兩個引數值已為1504M ，但引數檔案還是1500M 。
重新設定memory 兩個值，再次檢視

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 會調整該值為最近（變大）的16 的倍數。

檢查共享記憶體ID

[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"

檢視上述對應的SHM 段

[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 例項程式發現/dev/shm/ 下檔案都為16M

[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

檢視/dev/shm 目錄下檔案資訊，確認檔案大小

[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 例項，檢視/dev/shm 目錄

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

total 0

下面是在tanel poder 先生部落格中摘取的一段話，主要有兩點，一是memory_target 值在linux 中受限於/dev/shm 大小，二是在設定memory 值時大於1G ，在/dev/shm 目錄生成的是16M 的檔案，小於1G 則為4M ，將SGA 等分配為多個小塊，更容易釋放資源。

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

三、總結：

最近才算真正使用oracle11g ，在一個新產品出來後，我們總是討論它的新特性，增加了多少功能，在研究過程中，可能大多數人都不會像我這麼吝嗇，才給幾百MB 的記憶體，不過還是建議在設定一個新的引數或者一個新功能時先檢視相關文件，設定後並確認結果。我們也許不用研究太底層的東西，但也需儘量知道執行原理。

技術不是太牢、原理層次更需多多補充，也許會慢、也許還是離那些大佬太遠，但我想接近，雖不能確定它是否是終身職業，但我會“當一天和尚撞一天鐘”，腳踏實地的好好整整，看看前面有啥.

寫的還是太籠統，沒完全理解其意，可參考一下博文。

參考部落格：

http://blog.tanelpoder.com/2007/08/21/oracle-11g-internals-part-1-automatic-memory-management/

歡迎同志們激烈交流，大家一起的力量是無窮的！

新浪微博：xysoul_ 雲龍
部落格地址：http://blog.sina.com.cn/longzhimeng99

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

相關文章