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
ofMEMORY_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
theMEMORY_TARGETinitialization
parameter is dynamic, you can
changeMEMORY_TARGETat
any time without restarting the
database.MEMORY_MAX_TARGET,
which is not dynamic, serves as an upper limit so that you cannot
accidentally setMEMORY_TARGETtoo
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 settingMEMORY_TARGETtoo
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
memory_max_target big integer
memory_target big integer
shared_memory_address integer
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
memory_max_target big integer
1504M
memory_target big integer
1504M
shared_memory_address integer
發現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
memory_max_target big integer
1600M
memory_target big integer
shared_memory_address integer
通過幾次嘗試，發現你設定數值後oracle會調整該值為最近(變大)的16的倍數。
檢查共享記憶體ID
[[email protected] ~]$
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段
[[email protected] ~]$ 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執行個體程序
[[email protected] ~]$ 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目錄下檔案資訊
[[email protected] ~]$ 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
[[email protected] ~]$ du –sm
ora_loves_10190848_0
16 ora_loves_10190848_0
停止oracle執行個體，檢視/dev/shm目錄
[[email protected] ~]$ ls -l
/dev/shm
total 0
下面是在tanel poder
先生部落格中摘取的一段話，主要有兩點，一是memory_target值在linux中受限于/dev/shm大小，二是在設定memory值時大約1G，在/dev/shm目錄生成的是16M的檔案，小于1G則為4M。
pmap output
reveals that Oracle 11g likes to use /dev/shm for shared memory
implementation instead. There are multiple 16MB "files" mapped to
Oracle server processes address space.
This is the Linux'es POSIX-oriented SHM implementation, where
everything, including shared memory segments, is a file.
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).
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/