Oracle ASMM和AMM

lhrbest發表於2017-05-07
OracleASM

Oracle ASMM和AMM


ASMM（Automatic Shared Memory Management，自動共享記憶體管理）是Oracle 10g引入的概念。通過使用ASMM，就不需要手工設定相關記憶體元件的大小，而只為SGA設定一個總的大小，
Oracle的MMAN程式（Memory Manager Process，記憶體管理程式）會隨著時間推移，根據系統負載的變化和記憶體需要，自動調整SGA中各個元件的記憶體大小。
ASMM的SGA中包含的元件及對應引數如下表所示：


在Oracle 10g中，必須將STATISTICS_LEVEL引數設定為TYPICAL（預設值）或者ALL才能啟用ASMM功能，如果將其值設定為BASIC，那麼會禁用很多新特性，比如像AWR、ASMM等。
如果使用SQL*Plus來設定ASMM，那麼必須先把SGA中包含的元件的值設定為0。通過設定SGA_TARGET引數為非零值來啟用ASMM功能。

可以通過以下SQL來計算SGA_TARGET的值：

SELECT ((SELECT SUM(VALUE) FROM V$SGA) - (SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY)) "SGA_TARGET"  FROM DUAL;

設定SGA_TARGET的值，可以直接修改初始化引數檔案後重啟資料庫，或者通過下面SQL命令進行修改：

ALTER SYSTEM SET SGA_TARGET=value [SCOPE={SPFILE|MEMORY|BOTH}];

示例如下所示：

ALTER SYSTEM SET SGA_TARGET = 992M;

ALTER SYSTEM SET SHARED_POOL_SIZE = 0;

ALTER SYSTEM SET LARGE_POOL_SIZE = 0;

ALTER SYSTEM SET JAVA_POOL_SIZE = 0;

ALTER SYSTEM SET STREAMS_POOL_SIZE = 0;

ALTER SYSTEM SET DB_CACHE_SIZE = 0;

在啟用ASMM後，Oracle會自動調整SGA內部元件大小。若再手動指定某一元件值，則該值為該元件的最小值。例如，手動設定SGA_TARGET=8G，SHARED_POOL_SIZE=1G，
則ASMM在自動調整SGA內部元件大小時，保證Shared Pool不會低於1G。當設定了SGA_TARGET引數後，Oracle將會收集SGA相關的統計資料，並通過V$SGA_TARGET_ADVICE呈現出來，
因此，可以根據這些指導SGA_TARGET做相關的調整，以達到最佳情況。

Oracle 10g的ASMM實現了自動共享記憶體管理，但是具有一定的侷限性。所以，在Oracle 11g中，Oracle引入了AMM（Automatic Memory Management，自動記憶體管理）的概念，
實現了全部記憶體的自動管理。DBA可以僅僅通過設定一個目標記憶體大小的初始化引數（MEMORY_TARGET）和可選最大記憶體大小初始化引數（MEMORY_MAX_TARGET）就可以在大多數平臺上實現AMM。
AMM可以使例項總記憶體保持相對穩定的狀態，Oracle基於MEMORY_TARGET的值來自動調整SGA和PGA的大小。MEMORY_TARGET是動態初始化引數，可以隨時修改該引數的值而不用重啟資料庫。
MEMORY_MAX_TARGET作為一個記憶體上限值，是一個靜態引數，它是MEMORY_TARGET可以被配置的最大值。

如果記憶體發生變化，例項會自動在SGA和PGA之間做調整。若啟用了AMM功能，而SGA_TARGET和PGA_AGGREGATE_TARGET沒有顯式的被設定，
則預設SGA_TARGET為MEMORY_TARGET的60%，PGA_AGGREGATE_TARGET為MEMORY_TARGET的40%。
如果MEMORY_MAX_TARGET設定為1400M，而MEMORY_TARGET設定為1000M，那麼對於資料庫例項而言，只有1000M可以使用，剩下的400M會被保留下來，
但會被Oracle的MMAN程式鎖定。但是，因為MEMORY_MAX_TARGET是顯式設定的，所以，可以在不重啟資料庫的情況下動態調整MEMORY_TARGET。
如果只設定了MEMORY_TARGET的值，而MEMORY_MAX_TARGET沒有顯式設定，那麼MEMORY_MAX_TARGET的值預設是MEMORY_TARGET的大小。

當LOCK_SGA初始化引數的值設定為TRUE時，不能啟用AMM，該引數的值預設為FALSE。

只要是設定了MEMORY_MAX_TARGET或MEMORY_TARGET，那麼就說明啟用了AMM。可以使用檢視V$MEMORY_DYNAMIC_COMPONENTS動態查閱記憶體各元件的當前實時大小。

如果在建立資料庫的時候未啟用AMM，那麼可以在建庫後啟用它，啟用AMM需要重啟資料庫，具體步驟如下所示：

1、查詢SGA_TARGET和PGA_AGGREGATE_TARGET的值，從而確定MEMORY_TARGET的最小值

SYS@lhrdb> SHOW PARAMETER TARGET

NAME                                 TYPE        VALUE

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

pga_aggregate_target                 big integer 409M

sga_target                           big integer 1648M

2、確定自系統啟動以來PGA的最大值，單位為bytes

SYS@lhrdb> select value from v$pgastat where name='maximum PGA allocated';

     VALUE

----------

 248586240

3、通過以下方法來計算出SGA_TARGET的最大值

memory_target = sga_target + max(pga_aggregate_target, maximum PGA allocated)

例如：在這裡，SGA_TARGET的值為1648M，PGA_AGGREGATE_TARGET的值為409M，PGA的最大值為248586240/1024/1024=237M，所以，MEMORY_TARGET的值至少為1648+409=2057M。

4、設定系統引數啟用AMM

ALTER SYSTEM SET MEMORY_MAX_TARGET = 2100M  SCOPE=SPFILE;

--重啟資料庫

ALTER SYSTEM SET MEMORY_TARGET = 2057M;

ALTER SYSTEM SET SGA_TARGET = 0;

ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0;

另外需要說明的一點是，使用AMM經常出現的一個錯誤是“ORA-00845: MEMORY_TARGET not supported on this system”。

[ZFZHLHRDB1:oracle]:/oracle>oerr ora 845

00845, 00000, "MEMORY_TARGET not supported on this system"

// *Cause: The MEMORY_TARGET parameter was not supported on this operating system or /dev/shm was not sized correctly on Linux.

// *Action: Refer to documentation for a list of supported operating systems. Or, size /dev/shm to be at least the SGA_MAX_SIZE on each Oracle instance running on the system.

這個錯誤原因是作業系統不支援MEMORY_TARGET引數或/dev/shm的大小設定不正確。解決方法就是將/dev/shm的值增大，至少需要大於資料庫引數MEMORY_MAX_TARGET的值。修改步驟如下：

[root@LHRDB ~]# cat /etc/fstab | grep tmpfs    #檢視/dev/shm大小

tmpfs                   /dev/shm                tmpfs   defaults,size=1G 0 0

[root@LHRDB ~]# mount -o remount,size=4G /dev/shm     #臨時修改/dev/shm大小

[root@LHRDB ~]# vi /etc/fstab  #永久修改/dev/shm大小， tmpfs /dev/shm tmpfs defaults,size=4G 0 0

[root@LHRDB ~]# df -h | grep shm

tmpfs                 4.0G     0  4.0G   0% /dev/shm

[root@LHRDB ~]# df -h /dev/shm/

Filesystem      Size  Used Avail Use% Mounted on

tmpfs            4.0G    0    4.0G  0%   /dev/shm

[root@LHRDB ~]# cat /etc/fstab | grep tmpfs

tmpfs                   /dev/shm                tmpfs   defaults,size=4G        0 0

再次啟動資料庫就可以正常啟動了。

由於AMM不支援HugePage，而ASMM支援HugePage，所以，在生產庫上強烈推薦使用ASMM。有關ASMM和AMM的區別如下表所示：

表 3-10 ASMM和AMM的區別



MOS文件“SGA and PGA Management in 11g's Automatic Memory Management (AMM) (文件 ID 1392549.1)”對AMM和ASMM有非常詳細的說明。







 Oracle 11g AMM與ASMM切換 

現在的Oracle正在往智慧化方向發展。如果我們現在找一些8i/9i時代的Oracle書籍，怎麼樣配置合適的資料庫各記憶體池大小是非常重要的話題。但是進入10g之後，自動記憶體池調節成為一個重要Oracle特性。

 

在10g時，Oracle推出了ASMM（Automatic Shared Memory Management），實現了Oracle SGA和PGA內部結構的自調節。進入11g之後，AMM（Automatic Memory Management）實現了引數MEMORY_TARGET，將SGA和PGA的規劃全部統籌起來對待。

 

預設情況下，Oracle 11g是使用AMM的。我們在安裝過程中，指定Oracle使用記憶體的百分比，這個取值就作為MEMORY_TARGET和MEMORY_MAX_TARGET的初始取值使用。如果這兩個引數設定為非零取值，那麼Oracle就是採用AMM管理策略的。

 

同時，如果我們設定這兩個引數為0，則AMM自動關閉。對應的SGA_TARGET、PGA_AGGREGATE_TARGET引數取值非零之後，Oracle自動退化使用ASMM特性。

 

本篇簡單介紹一下AMM和ASMM的相互切換。

 

1、實驗環境介紹

 

我們選擇11.2.0.3進行試驗，當前狀態為ASMM。

 

 

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

PL/SQL Release 11.2.0.3.0 - Production

CORE        11.2.0.3.0         Production

 

 

當前MEMORY_TARGET設定為零，AMM沒有啟用。

 

 

SQL> show parameter target

 

NAME                                 TYPE        VALUE

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

archive_lag_target                   integer     0

db_flashback_retention_target        integer     1440

fast_start_io_target                 integer     0

fast_start_mttr_target               integer     0

memory_max_target                    big integer 0

memory_target                        big integer 0

parallel_servers_target              integer     16

pga_aggregate_target                 big integer 108M

sga_target                           big integer 252M

 

 

2、從ASMM到AMM

 

在11g中，如果使用ASMM，對應的記憶體共享段是真實的共享段。

 

 

[oracle@SimpleLinux ~]$ ipcs -m

 

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

key        shmid      owner      perms      bytes      nattch     status     

0x00000000 32768      oracle    640        4194304    32                     

0x00000000 65537      oracle    640        260046848  32                     

0x01606d30 98306      oracle    640        4194304    32  

 

 

下面進行引數的調整，這裡筆者有一個建議，很多時候啟動umount階段故障都是由於不當的引數修改造成的。在進行引數修改之前，可以使用create pfile進行一下引數備份，可以加快故障出現時候的系統修復速度。

 

 

SQL> show parameter spfile

NAME                                 TYPE        VALUE

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

spfile                               string      /u01/app/oracle/dbs/spfileora11g.ora

 

SQL> create pfile from spfile;

Done

 

 

修改系統引數，將sga和pga的target值設定為0，memory的target設定非0。注意，這個過程很多引數是靜態的引數，可以都在spfile可見性中進行修改，之後重啟伺服器生效。

 

 

SQL> alter system set memory_max_target=360m scope=spfile;

System altered

 

SQL> alter system set memory_target=360m scope=spfile;

System altered

 

SQL> alter system set sga_target=0m scope=spfile;

System altered

 

SQL> alter system set sga_max_size=0 scope=spfile;

System altered

 

SQL> alter system set pga_aggregate_target=0 scope=spfile;

System altered

 

 

重新啟動資料庫伺服器，檢視引數配置。

 

 

SQL> conn / as sysdba

Connected.

SQL> startup force

ORACLE instance started.

 

Total System Global Area  263651328 bytes

Fixed Size                  1344284 bytes

Variable Size             176164068 bytes

Database Buffers           83886080 bytes

Redo Buffers                2256896 bytes

Database mounted.

Database opened.

 

SQL> show parameter target

NAME                                 TYPE        VALUE

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

archive_lag_target                   integer     0

db_flashback_retention_target        integer     1440

fast_start_io_target                 integer     0

fast_start_mttr_target               integer     0

memory_max_target                    big integer 360M

memory_target                        big integer 360M

parallel_servers_target              integer     16

pga_aggregate_target                 big integer 0

sga_target                           big integer 0

 

 

AMM啟動之後，系統共享段變為“虛擬”共享段。

 

[oracle@SimpleLinux dbs]$ ipcs -m

 

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

key        shmid      owner      perms      bytes      nattch     status     

0x00000000 163840     oracle    640        4096       0                      

0x00000000 196609     oracle    640        4096       0                      

0x01606d30 229378     oracle    640        4096       0  

 

 

3、從AMM到ASMM

 

下面是如何從AMM到ASMM。要完全關閉AMM，一定將MEMORY_TARGET和MEMORY_MAX_TARGET都設定為0才行。

 

 

SQL> alter system set memory_max_target=0 scope=spfile;

System altered

 

SQL> alter system set memory_target=0 scope=spfile;

System altered

 

SQL> alter system set pga_aggregate_target=100m scope=spfile;

System altered

 

SQL> alter system set sga_target=260m scope=spfile;

System altered

 

SQL> alter system set sga_max_size=260m scope=spfile;

System altered

 

 

注意，此時如果重新啟動系統，會報錯。

 

 

SQL> startup force

ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account

ORA-00849: SGA_TARGET 272629760 cannot be set to more than MEMORY_MAX_TARGET 0.

SQL>

 

 

這個問題的原因是Oracle啟動過程中對於引數的內部檢查。因為MEMORY_MAX_TARGET被“顯示”的賦值，與SGA_TARGET賦值相沖突。

 

解決的方法就是使用引數預設值。建立出pfile之後，將顯示賦值為0的MEMORY_TARGET和MEMORY_MAX_TARGET記錄行刪除掉。再利用pfile啟動資料庫，重建spfile。

 

 

SQL> create pfile from spfile

  2  ;

 

File created.

 

--修改前

*.db_recovery_file_dest='/u01/app/fast_recovery_area'

*.db_recovery_file_dest_size=10737418240

*.diagnostic_dest='/u01/app'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora11gXDB)'

*.log_checkpoints_to_alert=TRUE

*.memory_max_target=0

*.memory_target=0

*.open_cursors=300

*.pga_aggregate_target=104857600

*.processes=150

 

--修改後

*.db_recovery_file_dest='/u01/app/fast_recovery_area'

*.db_recovery_file_dest_size=10737418240

*.diagnostic_dest='/u01/app'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora11gXDB)'

*.log_checkpoints_to_alert=TRUE

*.open_cursors=300

*.pga_aggregate_target=104857600

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

 

 

使用pfile啟動資料庫，重建spfile。

 

 

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup pfile=/u01/app/oracle/dbs/initora11g.ora

ORACLE instance started.

 

Total System Global Area  272011264 bytes

Fixed Size                  1344372 bytes

Variable Size             176163980 bytes

Database Buffers           88080384 bytes

Redo Buffers                6422528 bytes

Database mounted.

Database opened.

SQL> show parameter target

 

NAME                                 TYPE        VALUE

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

archive_lag_target                   integer     0

db_flashback_retention_target        integer     1440

fast_start_io_target                 integer     0

fast_start_mttr_target               integer     0

memory_max_target                    big integer 0

memory_target                        big integer 0

parallel_servers_target              integer     16

pga_aggregate_target                 big integer 100M

sga_target                           big integer 260M

 

SQL> create spfile from pfile

  2  ;

 

File created.

 

 

重新啟動之後，ASMM切換完成。

 

 

SQL> startup force

ORACLE instance started.

 

Total System Global Area  272011264 bytes

Fixed Size                  1344372 bytes

Variable Size             176163980 bytes

Database Buffers           88080384 bytes

Redo Buffers                6422528 bytes

Database mounted.

Database opened.

 

--真實的共享記憶體段結構

[oracle@SimpleLinux dbs]$ ipcs -m

 

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

key        shmid      owner      perms      bytes      nattch     status     

0x00000000 425984     oracle    640        8388608    25                     

0x00000000 458753     oracle    640        264241152  25                     

0x01606d30 491522     oracle    640        4194304    25 

 

--HugePage使用情況

[oracle@SimpleLinux dbs]$ grep Huge /proc/meminfo

HugePages_Total:    67

HugePages_Free:      1

HugePages_Rsvd:      0

Hugepagesize:     4096 kB

[oracle@SimpleLinux dbs]$

 

 

4、結論

 

AMM和ASMM是我們管理資料庫非常重要的工具，藉助自我調節的機制，我們可以做到資料庫自我管理。11g的AMM應該說是很方便的，但是在一些情況下，如HugePage，我們可能需要切換回ASMM。權當記錄，留需要的朋友待查。


每一個Oracle的初學者在入門階段都會接觸到SGA/PGA的知識，如果是從10g開始學習那麼會多或少會對ASMM有所瞭解，從使用的角度來說ASMM的出現極大地簡化了Oracle記憶體初始化引數的設定，在ASMM的使用上高階DBA和初學者不會有太大的差別；很多人因此而認為ASMM極大程度地減少了資料庫對於專業DBA的依賴：如果我們有一個足夠智慧的DB，那麼為什麼還要花費金錢僱傭DBA呢？這似乎是時下一種十分流行的想法。當然這種想法我個人是不能苟同的，ASMM一定程度上帶來了便利，更大程度上它是一個黑盒，黑盒裡面藏了很多祕密，這些祕密帶來比手動管理更多的不確定性；在10g release 1和10.2的早期版本中ASMM扮演的角色有點像一個闖禍精，另一個讓使用者對ASMM很不待見的原因是ASMM直接拖慢了startup的速度。一個個人觀點是ASMM也好AMM也罷，都要求產品資料庫DBA掌握更多SGA/PGA相關的知識才能成功”駕馭”這些”有智力”的傢伙，有點誇張的說這個時候的DBA很像一個chemist(需要和一大堆以1個或2個下劃線開頭的奇怪引數打交道)。

為了不辱使命我們真的有必要了解一下ASMM的基本知識，顯然這並不是一件容易的事情……

Oracle的SGA基本記憶體元件從9i開始並沒有非常大的變化，他們包括:

  • Buffer Cache 我們常說的資料庫快取記憶體，雖然我一直不明白要冠以高速之名
    • Default Pool                  預設的緩衝池，大小由DB_CACHE_SIZE決定
    • Keep Pool                     持久的緩衝池，大小由DB_KEEP_CACHE_SIZE決定
    • Non standard pool         非標準塊標準池，大小由DB_nK_cache_size決定
    • Recycle pool                 回收池，大小由db_recycle_cache_size決定
  • Shared Pool 共享池，大小由shared_pool_size決定
    • Library cache   俗稱的庫快取
    • Row cache      行快取，也叫字典快取
  • Java Pool         java池，大小由Java_pool_size決定
  • Large Pool       大池，大小由Large_pool_size決定
  • Fixed SGA       固定的SGA區域，包含了Oracle內部的資料結構，一般被存放在第一個granule中

在9i中尚未引入ASMM，唯一的選擇是手動管理的SGA，有時候也叫做MSMM。在9i中除去buffer cache的大小可以手動修改外，其餘元件都無法動態修改。因為缺乏一種動態管理的機制，所以在9i中如果有某個記憶體區域有急用，也無法從其他有空閒記憶體的元件中捐獻一些來解燃眉之急。

手動管理SGA的缺點在於:

  • 個別元件如shared pool、default buffer pool的大小存在最優值，但元件之間無法交換記憶體
  • 在9i中就提供了多種記憶體建議(advisor)，但都要求人工手動干預
  • 無法適應工作負載存在變化的環境
  • 往往會導致記憶體浪費，沒有用到實處
  • 若設定不當，引發著名的ORA-04031錯誤的可能性大大提高

ASMM的優勢在於:

  • 全自動的共享記憶體管理
  • 無需再配置每一個記憶體元件大小引數
  • 僅使用一個引數sga_target驅動
  • 有效利用所有可用的記憶體，極大程度上減少記憶體浪費
  • 對比MSMM其記憶體管理模式:
    • 更加動態
    • 更加靈活
    • 並具備適應性
  • 易於使用
  • 一定程度上增強了效能，因為記憶體分配更為合理了
  • 當某個元件急需更多記憶體時可以有效提供，因此可以一定程度避免ORA-04031的發生

ASMM主要可以囊括為三個部分：
1.由一個新引數sga_target驅動的管理模式
2.記憶體交換的模型，包括了：記憶體元件(memory component)，記憶體代理(memory broker)和記憶體交換機制(memory mechanism)
3.記憶體建議(memory advisor)

ASMM下一部分引數是自動設定的(Automatically set)，這些引數包括:shared_pool_size、db_cache_size、java_pool_size、large_pool _size和streams_pool_size；而另外一些是需要手動設定的靜態引數，包括了:db_keep_cache_size、db_recycle_cache_size、db_nk_cache_size、log_buffer以及固定SGA記憶體結構等，如果以上沒有手動設定這些引數的話，除去log_buffer和fixed sga其他記憶體元件一般預設大小為零。

通過ASMM使用者僅需要設定一個sga_target引數，其他引數都將由ASMM自行內部控制。但如果使用者依舊設定瞭如db_cache_size、java_pool_size等引數，那麼這些引數被認為是相關記憶體元件的最小限制，同時每個記憶體元件的大小也存在一個最大上限(內部的)。在例項啟動階段，Oracle會將必要的記憶體顆粒(granule，當SGA<1GB時granule大小為4M，當SGA>1GB時granule大小為16M)分配給記憶體元件，多餘沒有分配出去的全都分配給buffer cache，之後隨著系統的不斷活躍更多的記憶體顆粒(granule)將被分配給急需記憶體的元件。我們可以動態地修改sga_target引數，前提是所在的系統平臺支援動態地共享記憶體(dism，主流平臺都支援)。使用ASMM的一個必要條件是初始化引數statistics_level必須設定為typical或ALL，如果設定為BASIC那麼MMON後臺程式(Memory Monitor is a background process that gathers memory statistics (snapshots) stores this information in the AWR (automatic workload repository). MMON is also responsible for issuing alerts for metrics that exceed their thresholds)將無法有效分析記憶體的使用的統計資訊從而驅動ASMM的自動調優，實際上我們不能同時設定sga_target為非零值和statistics_level為BASIC: 

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 2000M
sga_target                           big integer 2000M

SQL> show parameter sga_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 2000M

SQL> alter system set statistics_level=BASIC;
alter system set statistics_level=BASIC
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00830: cannot set statistics_level to BASIC with auto-tune SGA enabled

如果使用了server parameter file即spfile的話，ASMM會在例項shutdown之前將當前實際的記憶體元件大小(Oracle認為這是最優的，但實際上可能不是)儲存到spfile中，如果你使用strings命令列印過spfile的內容的可以發現一些以雙下劃線開頭的引數，如: 

G10R2.__db_cache_size=973078528
G10R2.__java_pool_size=16777216
G10R2.__large_pool_size=16777216
G10R2.__shared_pool_size=1006632960
G10R2.__streams_pool_size=67108864

這些在spfile儲存的元件大小會在下次啟動時被沿用，以達到將已經實踐得出的”最佳值”記住的目的，這樣下次就不用從頭再”學習”了。

在ASMM的記憶體交換模型中存在三類元件

  1. 可調優元件(tunable):可調優元件是那些大小可以隨之變化且若過小僅損害少量效能。buffer cache就是一個經典的例子，cache過小的情況下應用程式仍能正常執行，但帶來的代價是更多的 IO。注意可調優元件同時存在一個下限。舉例來說共享池由庫快取Library cache和其他一些 subheap子堆組成，那麼共享池就存在一個下限要保證至少能放下某個一個時刻並行開啟的遊標(open cursors)以及負擔其他共享池client的執行時記憶體分配需求。類似的buffer cache也存在一個下限，該下限至少要大於並行被pin住的buffer的總和(雖然這部分很小)
  2. 不可調元件(Un-tunable):不可調元件是那些存在最小下限的記憶體元件，這個最小下限足夠讓應用程式正常執行，超過這個上限並不會帶來額外的效能收益。在這類元件中large pool是一個典型。
  3. 固定大小元件(Fixed Size)：自動調優框架之外的元件，一般為固定大小。這些元件的大小僅在手動調整時改變。例如非標準塊大小的高速緩衝池


記憶體交換模型中記憶體大小調整的申請(memory resize request)存在三種不同的模式，它們分別是：

立即記憶體申請(Immediate Request)：這種申請模式一般出現在ASMM管理的某個自動調優元件在無法分配到連續可用記憶體塊(chunk)時，為了避免出現OUT-OF-MEMORY(ORA-04031)的錯誤，系統嘗試從其他候選元件中轉移一個記憶體顆粒(granule)過來。需要注意的是當前可能沒有可用的全空granule，這時就會出現不完整的轉移，在此情形下系統會開始從候選元件中清理記憶體顆粒以滿足記憶體申請，並將一個granule不完整地轉移給急需記憶體的元件。

延遲記憶體申請(Deferred Request)：這種申請一般出現在系統認為存在一種更為合理的記憶體分配時，考慮在元件之間轉移一個或多個granule。用以判定最佳記憶體分配的依據是MMON程式所提供的統計資訊delta.

手動記憶體申請(Manual Request)：這種申請僅發生在使用者使用alter system命令手動調整記憶體元件的大小時。在這種模式下僅允許使用空的記憶體顆粒參與大小調整。如果當時沒有足夠的空granule，那麼針對元件grow操作會返回ORA-4033錯誤，而針對元件shrink操作會返回ORA-4034錯誤。

當ASMM被啟用後，記憶體代理(Memory Broker)定期執行上圖所示的活動。以上操作都處於延遲記憶體申請模式下(Deferred)。其目的是通過自動化的作業來調優自動調優元件(auto-tunable component)的大小以適應不斷改變的工作負載，將記憶體分配到最需要它們的地方。MMON輔助程式會在後臺不斷將統計資訊和記憶體建議定期地捕獲到迴圈使用的記憶體中，並計算不同時期快取資訊之間的差值；MMON還會基於Memory Broker的策略分析這些差值並估算出長期和短期內的趨勢。最後MMON基於以上分析生成一些記憶體元件的大小調整決議並將相應的申請傳送到一個系統申請佇列中(resize request system queue)。MMAN後臺程式(Memory Manager is a background process that manages the dynamic resizing of SGA memory areas as the workload increases or decreases)會定期掃描系統申請佇列並執行記憶體轉移。

在10gR1中Shared Pool的shrink收縮操作存在一些缺陷，造成缺陷的原因是在該版本中Buffer Cache還沒有能力共享使用一個granule，這是因為Buffer Cache的granule的尾部由granule header和Metadata(可能是buffer header或者RAC中的Lock Elements)拼接組成，在其尾部不容許存在空洞。另一個原因是當時的shared pool允許不同生命週期duration(以後會介紹)的chunk存放在同一個granule中，這造成共享池無法完全釋放granule。到10gR2中通過對Buffer Cache Granule結構的修改允許在granule header和buffer及Metadata(buffer header或LE)存在縫隙，同時shared pool中不同duration的chunk將不在共享同一個granule，通過以上改進buffer cache與shared pool間的記憶體交換變得可行。此外在10gr2中streams pool也開始支援記憶體交換(實際根據不同的streams pool duration存在限制)

當某個元件扮演捐獻者(Donor，下面的trace中會看到)角色時，它可能將一個不完整granule轉移給buffer cache，那麼在該granule被使用前需要完成以下步驟:

  1. 首先在該granule的尾部生成新的granule header
  2. 針對剩下的chunk判定在該granule中是否還有未使用的buffer header
  3. 如果有，那麼將chunk中的記憶體轉換為一個資料塊。否則將之轉換為一個metadata塊
  4. 重複以2-4步驟，直到該granule被轉換完

接著我們來了解一下記憶體轉移的基本原理，當將buffer cache中的granule轉移給shared pool時，將按照以下步驟:

  1. MMAN後臺程式找到一塊屬於buffer cache的合適granule
  2. MMAN將看中的granule置入quiesce列表中(Moving 1 granule from inuse to quiesce list of DEFAULT buffer cache for an immediate req)
  3. DBWR將負責寫出置入quiesced列表中granule裡面的髒buffer(dirty buffer)
  4. MMAN將為shared pool呼叫消費回撥函式(consume callback)，granule中free的chunk都會被shared pool消費(consume)掉，並對共享池新的記憶體分配可用。從這裡開始該granule變成一個shared granule共享記憶體顆粒，注意不要認為這個時候該granule的空間全部屬於共享池了，實際上有部分pin住的buffer及其Metadata(上述的buffer header和LE)的空間仍被buffer cache佔用著
  5. 最終該granule將完整的轉移給shared pool，這時此granule不再是一個shared共享的

實際使用中ASMM受到眾多隱藏引數的影響，其中比較主要的引數有:

  1. _enabled_shared_pool_duration:該引數控制是否啟用10g中特有的shared pool duration特性，當我們設定sga_target為0時該引數為false；同時在10.2.0.5前若cursor_space_for_time設定為true時該引數也為false，不過在10.2.0.5以後cursor_space_for_time引數被廢棄
  2. _memory_broker_shrink_heaps:若該引數設定為0那麼Oracle不會去收縮shared pool或java pool，當該引數大於0，會在shrink request失敗後等待該引數指定秒數時間後再次申請
  3. _memory_management_tracing: 該引數控制針對MMON和MMAN後臺程式包括記憶體建議(advisor)和記憶體代理(Memory Broker)決議的相關trace的級別；針對ORA-04031的診斷可以設定為36，設定為8啟用針對啟動期間元件大小的trace，設定為23啟動針對Memory Broker decision的跟蹤，設定為32將轉儲cache resize的細節；該引數的具體級別如下:
Level Contents
0x01 Enables statistics tracing
0x02 Enables policy tracing
0x04 Enables transfer of granules tracing
0x08 Enables startup tracing
0x10 Enables tuning tracing
0x20 Enables cache tracing


接下來我們通過設定_memory_management_tracing隱藏引數和DUMP_TRANSFER_OPS轉儲來實地瞭解一次完整的記憶體轉移，和不完整的記憶體轉移。以下演示的完整trace檔案可以從這裡下載mman_trace、transfer_ops_dump。 

SQL> alter system set "_memory_management_tracing"=63;
System altered Operation make shared pool grow and buffer cache shrink!!!..............

以下為一個完整granule轉移的過程，包括了對default buffer pool的resize操作: 

AUTO SGA: Request 0xdc9c2628 after pre-processing, ret=0

/* 這裡的0xdc9c2628是前臺程式的addr */

AUTO SGA: IMMEDIATE, FG request 0xdc9c2628

/* 這裡可以看到前臺程式的Immediate立即申請  */

AUTO SGA: Receiver of memory is shared pool, size=16, state=3, flg=0

/* 此次申請的收益人是shared pool，共享池，其大小為16個granule，處於grow狀態 */

AUTO SGA: Donor of memory is DEFAULT buffer cache, size=106, state=4, flg=0

/* 此處的捐獻者是Default buffer cache，快取記憶體，其大小為106個granule，處於shrink狀態 */

AUTO SGA: Memory requested=3896, remaining=3896

/* 這裡immeidate request所要求的空間是3896 bytes */

AUTO SGA: Memory received=0, minreq=3896, gransz=16777216

/* 這裡沒有free的granule，所以received為0，gransz為granule的大小 */

AUTO SGA: Request 0xdc9c2628 status is INACTIVE

/* 因為沒有空的記憶體顆粒，先將申請置於inactive狀態 */

AUTO SGA: Init bef rsz for request 0xdc9c2628

/* 為相關申請初始化before-process大小調整  */

AUTO SGA: Set rq dc9c2628 status to PENDING

/* 將request置於pending狀態 */

AUTO SGA: 0xca000000 rem=3896, rcvd=16777216, 105, 16777216, 17

/* 返回起始地址為0xca000000的16M大小granule */

AUTO SGA: Returning 4 from kmgs_process for request dc9c2628
AUTO SGA: Process req dc9c2628 ret 4, 1, a
AUTO SGA: Resize done for pool DEFAULT, 8192

/* 完成對default pool的resize */

AUTO SGA: Init aft rsz for request 0xdc9c2628
AUTO SGA: Request 0xdc9c2628 after processing
AUTO SGA: IMMEDIATE, FG request 0x7fff917964a0
AUTO SGA: Receiver of memory is shared pool, size=17, state=0, flg=0
AUTO SGA: Donor of memory is DEFAULT buffer cache, size=105, state=0, flg=0
AUTO SGA: Memory requested=3896, remaining=0
AUTO SGA: Memory received=16777216, minreq=3896, gransz=16777216
AUTO SGA: Request 0x7fff917964a0 status is COMPLETE

/* shared pool成功收到16M的granule */

AUTO SGA: activated granule 0xca000000 of shared pool

以下為一個partial granule不完全記憶體顆粒的轉移過程trace: 

AUTO SGA: Request 0xdc9c2628 after pre-processing, ret=0
AUTO SGA: IMMEDIATE, FG request 0xdc9c2628

AUTO SGA: Receiver of memory is shared pool, size=82, state=3, flg=1
AUTO SGA: Donor of memory is DEFAULT buffer cache, size=36, state=4, flg=1

/* 此處的受益者仍為shared pool，而捐獻者是default buffer cache */

AUTO SGA: Memory requested=4120, remaining=4120
AUTO SGA: Memory received=0, minreq=4120, gransz=16777216
AUTO SGA: Request 0xdc9c2628 status is INACTIVE
AUTO SGA: Init bef rsz for request 0xdc9c2628
AUTO SGA: Set rq dc9c2628 status to PENDING
AUTO SGA: Moving granule 0x93000000 of DEFAULT buffer cache to activate list
AUTO SGA: Moving 1 granule 0x8c000000 from inuse to quiesce list of DEFAULT buffer cache for an immediate req

/* 以上將buffer cache中起始地址為0x8c000000的granule從使用中列表inuse list，
    移動到靜默列表quiesce list中 */

AUTO SGA: Returning 0 from kmgs_process for request dc9c2628
AUTO SGA: Process req dc9c2628 ret 0, 1, 20a
AUTO SGA: activated granule 0x93000000 of DEFAULT buffer cache
AUTO SGA: NOT_FREE for imm req for gran 0x8c000000

/ * 等待dbwr寫出0x8c000000 granule中所有的dirty buffer */

AUTO SGA: Returning 0 from kmgs_process for request dc9c2628
AUTO SGA: Process req dc9c2628 ret 0, 1, 20a
AUTO SGA: NOT_FREE for imm req for gran 0x8c000000
AUTO SGA: Returning 0 from kmgs_process for request dc9c2628
AUTO SGA: Process req dc9c2628 ret 0, 1, 20a
AUTO SGA: NOT_FREE for imm req for gran 0x8c000000
AUTO SGA: Returning 0 from kmgs_process for request dc9c2628
AUTO SGA: Process req dc9c2628 ret 0, 1, 20a
AUTO SGA: NOT_FREE for imm req for gran 0x8c000000
AUTO SGA: Returning 0 from kmgs_process for request dc9c2628
AUTO SGA: Process req dc9c2628 ret 0, 1, 20a
AUTO SGA: NOT_FREE for imm req for gran 0x8c000000
AUTO SGA: Returning 0 from kmgs_process for request dc9c2628
AUTO SGA: Process req dc9c2628 ret 0, 1, 20a
AUTO SGA: NOT_FREE for imm req for gran 0x8c000000
......................................... AUTO SGA: Rcv shared pool consuming 8192 from 0x8c000000 in granule 0x8c000000; owner is DEFAULT buffer cache AUTO SGA: Rcv shared pool consuming 90112 from 0x8c002000 in granule 0x8c000000; owner is DEFAULT buffer cache
AUTO SGA: Rcv shared pool consuming 24576 from 0x8c01a000 in granule 0x8c000000; owner is DEFAULT buffer cache
AUTO SGA: Rcv shared pool consuming 65536 from 0x8c022000 in granule 0x8c000000; owner is DEFAULT buffer cache
AUTO SGA: Rcv shared pool consuming 131072 from 0x8c034000 in granule 0x8c000000; owner is DEFAULT buffer cache
AUTO SGA: Rcv shared pool consuming 286720 from 0x8c056000 in granule 0x8c000000; owner is DEFAULT buffer cache
AUTO SGA: Rcv shared pool consuming 98304 from 0x8c09e000 in granule 0x8c000000; owner is DEFAULT buffer cache
AUTO SGA: Rcv shared pool consuming 106496 from 0x8c0b8000 in granule 0x8c000000; owner is DEFAULT buffer cache
.....................

/* 以上shared pool開始消費0x8c000000 granule中的chunk，
    但此granule的owner暫時仍為default buffer cache */

AUTO SGA: Imm xfer 0x8c000000 from quiesce list of DEFAULT buffer cache to partial inuse list of shared pool

/* 以上將0x8c000000 granule從default buffer cache的靜默列表轉移到shared pool的不完整inuse list */

AUTO SGA: Returning 4 from kmgs_process for request dc9c2628
AUTO SGA: Process req dc9c2628 ret 4, 1, 20a
AUTO SGA: Init aft rsz for request 0xdc9c2628
AUTO SGA: Request 0xdc9c2628 after processing
AUTO SGA: IMMEDIATE, FG request 0x7fffe9bcd0e0
AUTO SGA: Receiver of memory is shared pool, size=83, state=0, flg=1
AUTO SGA: Donor of memory is DEFAULT buffer cache, size=35, state=0, flg=1
AUTO SGA: Memory requested=4120, remaining=0
AUTO SGA: Memory received=14934016, minreq=4120, gransz=16777216
AUTO SGA: Request 0x7fffe9bcd0e0 status is COMPLETE

/* 以上一個partial transfer完成 */

對應於以上partial transfer我們可以通過DUMP_TRANSFER_OPS來了解該0x8c000000 partial granule的實際使用情況，如: 

SQL> oradebug setmypid;
Statement processed.

SQL> oradebug dump DUMP_TRANSFER_OPS 1;
Statement processed.

SQL> oradebug tracefile_name;
/s01/admin/G10R2/udump/g10r2_ora_21482.trc

=======================trace content==============================

GRANULE SIZE is 16777216
COMPONENT NAME : shared pool
Number of granules in partially inuse list (listid 4) is 23
Granule addr is 0x8c000000 Granule owner is DEFAULT buffer cache

/* 該0x8c000000 granule在shared pool的partially inuse list，
    但這裡它的owner仍為default buffer cache */

Granule 0x8c000000 dump from owner perspective
gptr = 0x8c000000, num buf hdrs = 1989, num buffers = 156, ghdr = 0x8cffe000

/ * 可以看到該granule的granule header地址位於0x8cffe000，
     其中共有156個buffer block,1989個buffer header */

/* 以下granule中具體的內容，實際既包含了buffer cache也有shared pool chunk */

BH:0x8cf76018 BA:(nil) st:11 flg:20000
BH:0x8cf76128 BA:(nil) st:11 flg:20000
BH:0x8cf76238 BA:(nil) st:11 flg:20000
BH:0x8cf76348 BA:(nil) st:11 flg:20000
BH:0x8cf76458 BA:(nil) st:11 flg:20000
BH:0x8cf76568 BA:(nil) st:11 flg:20000
BH:0x8cf76678 BA:(nil) st:11 flg:20000
BH:0x8cf76788 BA:(nil) st:11 flg:20000
BH:0x8cf76898 BA:(nil) st:11 flg:20000
BH:0x8cf769a8 BA:(nil) st:11 flg:20000
BH:0x8cf76ab8 BA:(nil) st:11 flg:20000
BH:0x8cf76bc8 BA:(nil) st:11 flg:20000
BH:0x8cf76cd8 BA:0x8c018000 st:1 flg:622202
...............

Address 0x8cf30000 to 0x8cf74000 not in cache
Address 0x8cf74000 to 0x8d000000 in cache
Granule 0x8c000000 dump from receivers perspective
Dumping layout

Address 0x8c000000 to 0x8c018000 in sga heap(1,3) (idx=1, dur=4)
Address 0x8c018000 to 0x8c01a000 not in this pool
Address 0x8c01a000 to 0x8c020000 in sga heap(1,3) (idx=1, dur=4)
Address 0x8c020000 to 0x8c022000 not in this pool
Address 0x8c022000 to 0x8c032000 in sga heap(1,3) (idx=1, dur=4)
Address 0x8c032000 to 0x8c034000 not in this pool
Address 0x8c034000 to 0x8c054000 in sga heap(1,3) (idx=1, dur=4)
Address 0x8c054000 to 0x8c056000 not in this pool
Address 0x8c056000 to 0x8c09c000 in sga heap(1,3) (idx=1, dur=4)
Address 0x8c09c000 to 0x8c09e000 not in this pool
Address 0x8c09e000 to 0x8c0b6000 in sga heap(1,3) (idx=1, dur=4)
Address 0x8c0b6000 to 0x8c0b8000 not in this pool
Address 0x8c0b8000 to 0x8c0d2000 in sga heap(1,3) (idx=1, dur=4)

以上可以看到該granule真的是一個shared granule共享記憶體顆粒，其中不僅包含了部分buffer block，還包含了1號shared subpool共享池子池的durtaion為4的chunk，duration=4即execution duration;這類duration的chunk一般有著較短的生命週期，當其extent被置於quiesce list靜默列表時將很有可能變得足夠free。execution duration是共享池中唯一能可靠轉移的，因此唯有該類duration所在的extent(一般來說一個extent佔用一個granule)可以用來收縮。

以下我們列出一些有助於診斷ASMM問題的動態效能檢視，僅供參考:

V$SGAINFO
Displays summary information about the system global area (SGA).

V$SGA
Displays size information about the SGA, including the sizes of different SGA components, the granule size, and free memory.

V$SGASTAT
Displays detailed information about the SGA.

V$SGA_DYNAMIC_COMPONENTS
Displays information about the dynamic SGA components. This view summarizes information based on all completed SGA resize operations since instance startup.

V$SGA_DYNAMIC_FREE_MEMORY
Displays information about the amount of SGA memory available for future dynamic SGA resize operations.

V$SGA_RESIZE_OPS
Displays information about the last 400 completed SGA resize operations.

V$SGA_CURRENT_RESIZE_OPS
Displays information about SGA resize operations that are currently in progress. A resize operation is an enlargement or reduction of a dynamic SGA component.

V$SGA_TARGET_ADVICE
Displays information that helps you tune SGA_TARGET.

近期內會寫一篇介紹shared pool duration的文章，作為對這篇的補充。




SGA and PGA Management in 11g's Automatic Memory Management (AMM) (文件 ID 1392549.1)

In this Document

Purpose
Scope
Details
  Mandatory parameters for AMM
  Optional parameters for AMM
  How to control the memory under MEMORY_TARGET?
  Give MMAN complete control
  Take control of memory yourself
  AMM details
  CASE 1: Only MEMORY_TARGET is set
  CASE 2: MEMORY_TARGET, SGA_TARGET and PGA_AGGREGATE_TARGET are set
  CASE 3: MEMORY_TARGET, SGA_MAX_SIZE, SGA_TARGET and PGA_AGGREGATE_TARGET are set
  Summary of case studies
  ORA-4030 and ORA-4031


APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.1.0.6 and later
Information in this document applies to any platform.
***Checked for relevance on 01-Apr-2016***

PURPOSE

The purpose of this document is to demonstrate how Automatic Memory Management (AMM) manages memory in the SGA and PGA.

SCOPE

The intended audience of this document is experienced Database Administrators and Oracle Support engineers interested in Automatic Memory Management.

DETAILS

Automatic Memory Management (hereafter called AMM) is introduced in Oracle 11g to automatically manage both the SGA and PGA for a database instance. It is an extension of Automatic Shared Memory Management (ASMM) which was introduced in Oracle 10g, which manages the SGA only.

The significant instance parameters used by AMM are:

  • MEMORY_MAX_TARGET
  • MEMORY_TARGET
  • SGA_MAX_SIZE
  • SGA_TARGET
  • PGA_AGGREGATE_TARGET

AMM functionality is implemented by the Memory Manager process (hereafter called MMAN).

Mandatory parameters for AMM

AMM is enabled by the use of EITHER of these parameters:

  • MEMORY_TARGET - defines the outer limits to which the sum of the SGA and PGA can grow.
  • MEMORY_MAX_TARGET - defines the outer limit to which the MEMORY_TARGET can be manually, dynamically, extended (i.e. without a database restart).


Example:

If MEMORY_MAX_TARGET is set to 1400M, and MEMORY_TARGET is set to 1000M, only the 1000M is available to the instance. The remaining 400M is held in reserve, but locked by Oracle (MMAN). However, because MEMORY_MAX_TARGET is explicitly set, it now becomes possible to dynamically resize MEMORY_TARGET without a database restart.
          
SQL> alter system set memory_target = 1400M;
  
If MEMORY_MAX_TARGET is the same as MEMORY_TARGET, or if it is not explicitly set, this dynamic increase would not be possible and a instance restart would be required.


Regardless of the Operating System used, when the instance starts up, an amount of memory equal to MEMORY_MAX_TARGET will be locked by MMAN.

MEMORY_MAX_TARGET will always be set, either explicitly in the parameter file, or implicitly by the MMAN background process and will always define the memory locked by the database instance. If MEMORY_MAX_TARGET is not set in the parameter file, it defaults to MEMORY_TARGET.

Optional parameters for AMM

All SGA memory parameters can be set in an AMM environment. If no SGA memory parameters are set, MMAN defaults in the following ratio:

60% to SGA_TARGET
40% to PGA_AGGREGATE_TARGET


Let's look at the following parameters:

  • SGA_MAX_SIZE: this parameter sets the upper limits of the SGA within MEMORY_TARGET
  • SGA_TARGET: this parameter sets the lower limits for the SGA within MEMORY_TARGET
  • PGA_AGGREGATE_TARGET: this parameter is just a target for the total private memory the instance will allow for all processes. In AMM, this is a movable target and will slide up ad down as free space in MEMORY_TARGET is available and as the processing needs change.


The sum of SGA and 'used' PGA cannot exceed MEMORY_TARGET. The exception is PL/SQL collections (such as VARRAYs and other collection types) which will not honor the PGA_AGGREGATE_TARGET, but will continue to consume memory as long as more memory is requested by the procedure, and more memory is available on the server. This is memory in excess of MEMORY_MAX_TARGET.

If SGA_TARGET is explicitly set in the parameter file, it becomes the lower limit (minimum size) for the SGA. The PGA_AGGREGATE_TARGET will always get an amount of memory equal to MEMORY_TARGET - SGA_TARGET.

How to control the memory under MEMORY_TARGET?

There are some options available to the Database Administrator to determine how memory is allocated to the various memory components.

Give MMAN complete control

You can consider MEMORY_TARGET as one big area of memory for MMAN to use. When you set no other parameters than MEMORY_TARGET, you give MMAN complete control of the area. MMAN divides this area in a ratio or 60% to SGA and 40% to PGA. But these are only initial settings, and MMAN will freely move memory between the two areas depending on memory pressures. Within the 60%, MMAN will resize the SGA components as it did under ASMM, but the difference is MMAN will now increase SGA_TARGET if the SGA is in need of more memory. Under ASMM, SGA_TARGET, once breached, would give a ORA-4031.

Take control of memory yourself

Recall that MEMORY_TARGET manages both SGA and PGA memory - and without limitations, MMAN will freely move memory back and forth between the two. If you wish to constrain aggressive PGA growth, set SGA_TARGET in the parameter file, and this now becomes a minimum size for the SGA. In this case PGA growth cannot shrink the SGA beyond this size.

If you want to constrain aggressive SGA growth, set SGA_MAX_SIZE in the parameter file, and this becomes the ceiling for the SGA. An amount of memory equal to SGA_MAX_SIZE is carved out of MEMORY_TARGET, and the SGA must fit within it. This limits the amount SGA can grow to.

Any of the SGA components can be set if you wish to make sure you have a certain minimum amount of memory for that component. The significant SGA components are:

  • the shared pool (managed by the SHARED_POOL_SIZE instance parameter)
  • the buffer cache (managed by the DB_CACHE_SIZE instance parameter)
  • the Streams pool (managed by the STREAMS_POOL_SIZE instance parameter)
  • the Java pool (managed by the JAVA_POOL_SIZE instance parameter)
  • the large pool (managed by the LARGE_POOL_SIZE instance parameter)

If you wish to make any component larger (e.g. the buffer cache in order to maximize transaction throughput and minimize I/O), set the corresponding component parameter (DB_CACHE_SIZE for the example given) to the value you require. But remember, the new component size has to fit within the SGA_TARGET along with all other pools. So either MMAN will adjust SGA_TARGET to a larger value in case you did not set SGA_TARGET explicitly, or, you will have to reset SGA_TARGET manually to a larger value to accommodate for the component enlargement.

AMM details

One of the most significant changes of AMM, is that memory that was originally shared (SGA), can be released, and used by the Operating system for private memory (PGA). The reverse is also true: memory that was private to a process, can be released and used as shared memory. This movement of memory is contained within MEMORY_TARGET and managed by MMAN.

There are some important consequences of this. On a system with many large SQL statements, for example parallel executions, processes often do large sorts and table joins. These operations are memory intensive, and can consume a lot of the free memory under MEMORY_TARGET. As more of these processes start up, and need more private memory, MMAN will turn to the SGA in an attempt to reallocate shared memory as private memory.

Under the covers, if the PGA needs more memory, MMAN will run through the SGA free lists looking for memory chunks that are not currently in use. MMAN coalesces these free chunks, until it creates one contiguous chunk that is the size of a granule. The granule is then unlocked from the SGA, and put back in circulation for the OS to use as private memory for the PGA. Various Operating System functions are invoked to unlock the shared memory and return it to the O/S. These memory lock and unlock routines may differ by hardware platform. Running truss (or any similar utility) may show the O/S routines being used:

On UNIX, freeing memory from the SGA shows like the following in a truss output:
...
18039: munlock(0xc0000009c0000000, 16777216)                  <== this is the granule size (16M)
18039: madvise(0xc0000009c0000000, 0x1000000, MADV_DONTNEED)  <== this is the command to free the granule
18039: munlock(0xc0000009c1000000, 16777216)
18039: madvise(0xc0000009c1000000, 0x1000000, MADV_DONTNEED)
18039: munlock(0xc0000009c2000000, 16777216)
18039: madvise(0xc0000009c2000000, 0x1000000, MADV_DONTNEED)
...

The number of the freed granules will directly correspond to the amount of memory being freed. For example, if the SGA_TARGET=4G and the granule size is 16M, and you reduce SGA_TARGET to 2G, you can use this command:
SQL> alter system set SGA_TARGET=2G;
The number of granules freed will be 2048M/16M = 128. So the truss report will show 128 calls to the OS routines munlock() and madvise(MADV_DONT_NEED). When the memory is freed (unmapped) it is now free for the O/S to reallocate.

The same process occurs during a resize operation that was initiated by MMAN, instead of the manual resize shown above.


If you consider that the memory eligible for PGA is approximately MEMORY_TARGET minus the SGA_TARGET, it becomes desirable to put a lower limit on SGA_TARGET. This prevents the PGA from taking too much memory from the SGA. The lower limit is set when the parameter file contains a value for SGA_TARGET. Without this setting, MMAN will continue to try to take any available memory from the SGA, as long as that memory is not currently in use (pinned).

On Linux the behavior of AMM can be demonstrated easily; when AMM is in use on Linux, the entire amount of MEMORY_TARGET creates /dev/shm segments when the instance starts up. This is visible if you run the following command:

$ ls -l /dev/shm | grep $ORACLE_SID


Note: these /dev/shm segments disappear if MEMORY_TARGET is not set, and MMAN reverts to using only the normal shared segments (visible by the ipcs -ma command).


For the SGA portion of MEMORY_TARGET, there is an equivalent amount of /dev/shm segments created. This appears as /dev/shm segments each the size of 1 granule. The PGA (or free memory) portion of memory_target appears as /dev/shm segments of 0 length.

For example, if MEMORY_MAX_TARGET=1G, the granule size is 4M, and there would be 256 /dev/shm segments created (256 * 4M = 1G). If there are no other memory parameters set, the initial SGA size will be 612M (60% of MEMORY_TARGET) and the initial PGA will be 412M (40% of MEMORY_TARGET). So there would be 153 /dev/shm segments with size of 4M (153 * 4M = 612M). The remaining /dev/shm segments will be zero length and represent free memory. These allocation can be seen if you query the V$SGAINFO fixed table. See the case studies that follow:

Example:
If the database name is testdb, the command would be:
$ ls -l /dev/shm | grep -i testdb

which results in:
-rw-r----- 1 oracle oracle 4194304 Aug 25 09:19 ora_testdb_327688_0    <== these are shared memory segments used by SGA (each = 1 granule = 4M)
-rw-r----- 1 oracle oracle 4194304 Aug 25 09:19 ora_testdb_327688_1
-rw-r----- 1 oracle oracle 4194304 Aug 25 09:19 ora_testdb_327688_10
-rw-r----- 1 oracle oracle 4194304 Aug 25 09:19 ora_testdb_327688_100
-rw-r----- 1 oracle oracle 4194304 Aug 25 09:19 ora_testdb_327688_101
-rw-r----- 1 oracle oracle 4194304 Aug 25 09:19 ora_testdb_327688_102
...
-rw-r----- 1 oracle oracle 0 Aug 25 09:19 ora_testdb_327688_95    <== These segments with 0 length are free segments which can be (re)used by the O/S
-rw-r----- 1 oracle oracle 0 Aug 25 09:19 ora_testdb_327688_96
-rw-r----- 1 oracle oracle 0 Aug 25 09:19 ora_testdb_327688_97
-rw-r----- 1 oracle oracle 0 Aug 25 09:19 ora_testdb_327688_98
-rw-r----- 1 oracle oracle 0 Aug 25 09:19 ora_testdb_327688_99


If you dynamically resize the SGA (using an ALTER SYSTEM SET SGA_TARGET command), you will not see an immediate change in the number of /dev/shm segments, nor in V$SGAINFO. The changes will only occur when memory pressures dictate that the SGA or PGA needs to give up memory.

As the database instance matures and workloads differ, these values will all change. If SGA grows, the number of /dev/shm segments of size 1 granule will increase with a corresponding decrease in the number of /dev/shm segments of 0 length.

For other platforms, the O/S mechanism for moving shared memory between SGA and PGA is different, and will not be discussed here.

Perhaps the best way to show the memory management is by means of examples, and this document will show a few cases below.

CASE 1: Only MEMORY_TARGET is set

The following instance parameters are set:

  • MEMORY_TARGET=1G

In this case MEMORY_MAX_TARGET is not set, so it defaults to MEMORY_TARGET.

The following SQL statements highlight what is happening:

SQL> select * from v$sgainfo where name like 'Maximum SGA%' or name like 'Free SGA%';

NAME                                  BYTES RES
-------------------------------- ---------- ---
Maximum SGA Size                 1068937216 No
Free SGA Memory Available         432013312

SQL> select component,current_size from v$memory_dynamic_components where component like '%Target%';

COMPONENT                           CURRENT_SIZE
----------------------------------- ------------
SGA Target                             641728512
PGA Target                             432013312


Summary:

  1. Initially all memory (1GB) is assigned to shared memory. Here V$SGAINFO shows that 'Maximum SGA size' is 1GB. This is because SGA_MAX_SIZE is unset, and therefore defaults to MEMORY_TARGET.
  2. V$SGAINFO also shows that 412M is free memory. This free memory can be used either for the PGA, or for resizing the SGA.
  3. The initial ratio of memory allocation is 60% to SGA, 40% to PGA. This is the default if neither SGA_TARGET nor PGA_AGGREGATE_TARGET is set. This means, of the 1GB of MEMORY_TARGET, SGA got 60% (612M) and free memory was 40% (412M).
  4. The V$MEMORY_DYNAMIC_COMPONENTS output shows SGA_TARGET was set to 612M and PGA_AGGREGATE_TARGET was set to 412M. The PGA_AGGREGATE_TARGET value of 412M does not mean there is already 412M of PGA, only that there is enough free memory for it to grow to 412M. If it needs to grow beyond 412M, MMAN will request memory from the SGA.
Component Value in the parameter file MMAN assigned value
MEMORY_TARGET 1024M 1024M
SGA_MAX_SIZE not set 1024M
SGA_TARGET not set 612M (60% of MEMORY_TARGET)
PGA_AGGREGATE_TARGET not set 412M (40% of MEMORY_TARGET)


CASE 2: MEMORY_TARGET, SGA_TARGET and PGA_AGGREGATE_TARGET are set

The following instance parameters are set:

  • MEMORY_TARGET=1G
  • SGA_TARGET=300M
  • PGA_AGGREGATE_TARGET=100M

In this case MEMORY_MAX_TARGET is not set, so it defaults to MEMORY_TARGET.

The following SQL statements highlight what is happening:

SQL> select * from v$sgainfo where name like 'Maximum SGA%' or name like 'Free SGA%';

NAME                                  BYTES RES
-------------------------------- ---------- ---
Maximum SGA Size                 1068937216 No
Free SGA Memory Available         759169024

SQL> select component,current_size from v$memory_dynamic_components where component like '%Target%';

COMPONENT                           CURRENT_SIZE
----------------------------------- ------------
SGA Target                             314572800
PGA Target                             759169024


Summary:

  1. Initially all memory (1GB) is assigned as shared memory. Here V$SGAINFO shows 'Maximum SGA size' is 1G. This is because SGA_MAX_SIZE is unset, so it defaults to MEMORY_TARGET.
  2. V$SGAINFO also shows that 724M is free memory. This amount is the difference between the MEMORY_TARGET and the SGA_TARGET. This free memory can be used either for the PGA, or for resizing the SGA.
  3. The V$MEMORY_DYNAMIC_COMPONENTS fixed table shows SGA_TARGET was set to 300M as requested, but PGA_AGGREGATE_TARGET was set to 724M, which is the remainder of the MEMORY_TARGET allocation. The PGA_AGGREGATE_TARGET value of 724M does not mean there is already 724M of PGA, only that there is enough free memory to grow to 724M.
  4. Because SGA_MAX_SIZE was defaulted to 1G, in theory, this is also the maximum size of the SGA (if there was no PGA).
Component value is parameter file MMAN assigned value
MEMORY_TARGET 1024M 1024M
SGA_MAX_SIZE not set 1024M
SGA_TARGET 300M 300M
PGA_AGGREGATE_TARGET 100M 724M


CASE 3: MEMORY_TARGET, SGA_MAX_SIZE, SGA_TARGET and PGA_AGGREGATE_TARGET are set

The following instance parameters are set:

  • MEMORY_TARGET=1G
  • SGA_MAX_SIZE=500M
  • SGA_TARGET=300M
  • PGA_AGGREGATE_TARGET=100M

In this case MEMORY_MAX_TARGET is not set, so it defaults to MEMORY_TARGET.

The following SQL statements highlight what is happening:

SQL> select * from v$sgainfo where name like 'Maximum SGA%' or name like 'Free SGA%';

NAME                                  BYTES RES
-------------------------------- ---------- ---
Maximum SGA Size                  521936896 No
Free SGA Memory Available         209715200

SQL> select component,current_size from v$memory_dynamic_components where component like '%Target%';

COMPONENT                           CURRENT_SIZE
----------------------------------- ------------
SGA Target                             314572800
PGA Target                             759169024


Summary:

  1. Now that SGA_MAX_SIZE is set to 500M, the memory assigned as shared memory, shows up in V$SGAINFO as 500M under 'Maximum SGA Size'.
  2. The 'Free SGA Memory Available' in V$SGAINFO is the difference between the SGA_MAX_SIZE and SGA_TARGET (500M-300M = 200M).
  3. The memory above SGA_MAX_SIZE (500M) is unavailable to the SGA so never shows up in V$SGAINFO.
  4. The V$MEMORY_DYNAMIC_COMPONENTS fixed table shows the SGA_TARGET is 300M as set in the parameter file, but the PGA_AGGREGATE_TARGET is 724M, which is the difference between MEMORY_TARGET and SGA_TARGET.

    So the PGA has access to the memory above SGA_MAX_SIZE (500M) plus any free memory in the SGA (200M).
Component Value in the parameter file MMAN assigned value
MEMORY_TARGET 1024M 1024M
SGA_MAX_SIZE 500M 500M
SGA_TARGET 300M 300M
PGA_AGGREGATE_TARGET 100M 724M


Summary of case studies

The above examples show that:

  • In AMM, shared memory is available for use by both the SGA and PGA.
  • MMAN will lock, unlock, and transfer memory freely between the SGA and the PGA as necessary.
  • The exceptions are:
    • When SGA_MAX_SIZE is set in the parameter file, MMAN will not transfer memory into the SGA that would cause it to exceed this size.
    • When SGA_TARGET is set in the parameter file, MMAN will not transfer any memory out of the sga that will cause it to go below this size.
Note: if MEMORY_MAX_TARGET would have been manually set to a value higher than MEMORY_TARGET the following situation would occur:
  • The 'Maximum SGA Size' value in V$SGAINFO would show the MEMORY_MAX_TARGET value.
  • The 'Free SGA Memory Available' in V$SGAINFO is the difference between the SGA_MAX_SIZE (which defaults to MEMORY_MAX_TARGET)and SGA_TARGET.
  • All other parameters would act the same as described in the cases, as calculations for the initial memory sizes are based on MEMORY_TARGET and not MEMORY_MAX_TARGET.


ORA-4030 and ORA-4031

How can the database run out of memory, if it can freely move memory around between SGA and PGA?

Recall that ORA-4030 occurs when there is no more memory for the PGA, and ORA-4031 occurs when there is no more memory for the SGA.

If the SGA allocation is totally consumed, and the PGA (free memory) is totally consumed, then you have run out of memory and either ORA-4030 or ORA-4031 is imminent. This means the MEMORY_TARGET is undersized.

Some things that can initiate memory shortages are limits set on the SGA:

  • If SGA_MAX_SIZE is set, this is an upper limit. If the processing load requires more memory than this, you can get ORA-4031.
  • If SGA_TARGET is set, this is a minimum size for the SGA. If the processing load requires a lot of private memory (PGA), and that memory requirement exceeds MEMORY_TARGET - SGA_TARGET, you can get a ORA-4030. But if the process needing the memory is PL/SQL, it can take additional free memory from the server. However, it is still a good idea to set SGA_TARGET so there is always some memory available for the SGA.

For ORA-4030, there are also O/S limits that come into play. Notably, the 4G limits on 32-bit platforms, and the ulimit soft settings for UNIX type systems which limit the maximum memory a process can access.





"ipcs -m" Displays Incorrect Shared Memory Segment Sizes in Oracle 11G (文件 ID 731658.1)

In this Document

Symptoms
Cause
Solution


APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.1.0.6 and later
Generic Linux



SYMPTOMS

The "ipcs -m" indicates allocated shared memory segments. In Oracle 11G all of the segments are between 808 and 4096 bytes, where as the instance has a 4GB SGA .Output of ipcs -m:

------ Shared Memory Segments -------- 
key shmid owner perms bytes nattch status 
0x00000000 65536 oracle 660 4096 0 
0xd649823c 98305 oracle 660 4096 0 
0x7905c133 131074 oracle 666 808 0 
0xc90e3f3c 196611 oracle 660 4096 0

CAUSE

When Oracle on Linux is configured to use Auto Memory Management (AMM), it utilizes the POSIX implementation of SHM on Linux. The small shared memory segment just contains some metadata for new processes to find the /dev/shm/* files used for the SGA. POSIX shared memory is not reflected by the ipcs utility. 

When AMM is disabled, Oracle falls back to using the System V SHM implementation as seen in earlier versions of Oracle for Linux.

SOLUTION

1. Use PMAP command. PMAP output shows that Oracle 11g uses /dev/shm for shared memory implementation instead. There are many 4MB files mapped to Oracle server processes address space. The total size shows the MEMORY_TARGET size. If MEMORY_TARGET is set to 0 then the shared memory segments will be displayed by the ipcs command.

 

E.g.:

$pmap 6124 

.. .
20001000 4092K rw-s- /dev/shm/ora_ora11g_19693577_0 
20400000 4096K rw-s- /dev/shm/ora_ora11g_19693577_1 
20800000 4096K rw-s- /dev/shm/ora_ora11g_19693577_2 
20c00000 4096K rw-s- /dev/shm/ora_ora11g_19693577_3 
21000000 4096K rw-s- /dev/shm/ora_ora11g_19693577_4 
...

 

2. /dev/shm also shows the shared memory segments in use for this instance.

 

$ ls -l /dev/shm/ |grep ora11g

-rw-r----- 1 oracle oinstall 4194304 Jul 31 10:40 ora_ora11g_19693577_0 
-rw-r----- 1 oracle oinstall 4194304 Jul 31 10:40 ora_ora11g_19693577_1 
-rw-r----- 1 oracle oinstall 4194304 Jul 31 10:40 ora_ora11g_19693577_177 
-rw-r----- 1 oracle oinstall 4194304 Jul 31 10:40 ora_ora11g_19693577_178 
-rw-r----- 1 oracle oinstall 4194304 Jul 31 10:40 ora_ora11g_19693577_179







About Me

...............................................................................................................................

● 本文整理自網路

● 本文在itpub（http://blog.itpub.net/26736162）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文部落格園地址：http://www.cnblogs.com/lhrbest

● 本文pdf版及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● QQ群：230161599     微信群：私聊

● 聯絡我請加QQ好友(646634621)，註明新增緣由

● 於 2017-05-05 09:00 ~ 2017-05-05 22:00 在魔都完成

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

...............................................................................................................................

拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學習最實用的資料庫技術。

Oracle ASMM和AMM
 DBA筆試面試講解
 歡迎與我聯絡

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2138627/，如需轉載，請註明出處，否則將追究法律責任。

相關文章