ITPub博客

首页 > 数据库 > Oracle > 【AMM】关于数据库实例AMM参数说明

【AMM】关于数据库实例AMM参数说明

原创 Oracle 作者:xysoul_云龙 时间:2014-02-16 22:03:25 0 删除 编辑

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即可管理SGAPGA,参数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,此前我们查看到SGAPGA有固定值,再此不做讨论。

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等分配为多个小块,更容易释放资源。

 

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/

 

欢迎同志们激烈交流,大家一起的力量是无穷的!

 

                                     新浪微博:xysoul_云龙
                                    
博客地址:http://blog.sina.com.cn/longzhimeng99

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

请登录后发表评论 登录
全部评论
主要从事数据库相关工作,其他操作系统、中间件等也有涉及,热衷分享、开源,支持国产,期待中华民族全面的伟大复兴。近日骤然醒悟,欲在IT江湖中闯荡一番,如有幸在诸多侠客中留点踪迹,也算不虚此行。 【文盲筱烨】好读书爱运动的IT技术爱好者 微博:文盲筱烨 微信公众号:筱烨视点

注册时间:2014-02-15

  • 博文量
    168
  • 访问量
    743309