oracle11g memory management系列(一)_oracle的ememory-CSDN博客

http://space.itpub.net/9240380/viewspace-753708

1,自oracle11g始,sga和pga管理可以由 oracle完全自动管理
2,11g内存管理有2种方法:自动管理(指定一个内存总大小);
手工管理(想直接手工控制各个内存组件使用)

3,内存结构:
1,sga
a,sga由后台进程和 server进程共享
b,sga包括数据块和控制结构
2,pga
a,pga排它性,仅由创建它的server process独享
b,给后台进程也会分配pga,比如lgwr,可以查阅v$process
3,flush cache
a,11g新内存组件
b,驻存在sga基础之上
c,Database Smart Flash Cache (the flash cache),
d,为数据块提供2级缓存功能,尤其适用于读密集型的oltp环境;大幅提升响度时间和整体吞吐量
e,flush cache基于一个或多个flash disk设备,这些固态存储设备使用flash memory
(注:如何配置flush cache请参阅:http://docs.oracle.com/cd/E11882_01/server.112/e25494/memory005.htm#BABHEDBH)
f,须运行 linux平台

4,开启自动内存管理(即同时管控sga和pga)
alter system set memory_max_target=ngb scope=spfile;--ngb是要设置的值,此参数为静态参数,须重启库
alter system set memory_target=ngb;--动态参数,只要在memory_max_target范围内即可
ALTER SYSTEM SET SGA_TARGET = 0;--关闭手工管理sga和pga
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0;--同上

---重启库,生效上述参数
shutdown immediate
startup

--memory_max_target的值如何配置呢,oracle提供如下方法:
---查阅为pga分配的最大值
SQL> select * from v$pgastat ps where ps.name='maximum PGA allocated';

NAME VALUE UNIT
---------------------------------------------------------------- ---------- ------------
maximum PGA allocated 91763712 bytes

--memory_target(或memory_max_target)的值:
--如物理内存非常充足,可以考虑在其基础上适当增加大小
memory_target = sga_target + max(pga_aggregate_target, maximum PGA allocated)

----监控oracle内存结构

---动态查阅内存各组件的当前实时大小
SQL> select * from v$memory_dynamic_components;

COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYPE LAST_OPER_MODE LAST_OPER_TIME GRANULE_SIZE
---------------------------------------------------------------- ------------ ---------- ---------- ------------------- ---------- -------------- -------------- -------------- ------------
shared pool 301989888 301989888 301989888 0 0 STATIC 16777216
large pool 16777216 16777216 16777216 0 0 STATIC 16777216
java pool 16777216 16777216 16777216 0 0 STATIC 16777216
streams pool 0 0 0 0 0 STATIC 16777216
SGA Target 2566914048 2566914048 2566914048 0 0 STATIC 16777216
DEFAULT buffer cache 2197815296 2197815296 2197815296 0 0 INITIALIZING 16777216
KEEP buffer cache 0 0 0 0 0 STATIC 16777216
RECYCLE buffer cache 0 0 0 0 0 STATIC 16777216
DEFAULT 2K buffer cache 0 0 0 0 0 STATIC 16777216
DEFAULT 4K buffer cache 0 0 0 0 0 STATIC 16777216
DEFAULT 8K buffer cache 0 0 0 0 0 STATIC 16777216
DEFAULT 16K buffer cache 0 0 0 0 0 STATIC 16777216
DEFAULT 32K buffer cache 0 0 0 0 0 STATIC 16777216
Shared IO Pool 0 0 0 0 0 STATIC 16777216
PGA Target 1728053248 1728053248 1728053248 0 0 STATIC 16777216
ASM Buffer Cache 0 0 0 0 0 STATIC 16777216

16 rows selected

SQL>

---调节memory target的诊断工具
---memory_target_factor为1,表示当前memory_target所属行
--其上和其下为<memory_target的内存大小或>memory_target的内存大小
--estd_db_time为不同内存大小完成工作所需要的db time
--此例中,增大和减少内存大小estd_db_time皆为138,因为当前 数据库未运行什么任务
SQL> select * from v$memory_target_advice;

MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION
----------- ------------------ ------------ ------------------- ----------
1024 0.25 138 1 0
2048 0.5 138 1 0
2560 0.625 138 1 0
3072 0.75 138 1 0
3584 0.875 138 1 0
4096 1 138 1 0
4608 1.125 138 1 0
5120 1.25 138 1 0
5632 1.375 138 1 0
6144 1.5 138 1 0
6656 1.625 138 1 0
7168 1.75 138 1 0
7680 1.875 138 1 0
8192 2 138 1 0

14 rows selected

---sga各组件信息,sga分配和释放以granule单位进行
SQL> select * from v$sgainfo;

NAME BYTES RESIZEABLE
-------------------------------- ---------- ----------
Fixed SGA Size 2182592 No
Redo Buffers 12185600 No
Buffer Cache Size 2197815296 Yes
Shared Pool Size 301989888 Yes
Large Pool Size 16777216 Yes
Java Pool Size 16777216 Yes
Streams Pool Size 0 Yes
Shared IO Pool Size 0 Yes
Granule Size 16777216 No ---16mb的粒度
Maximum SGA Size 4275781632 No
Startup overhead in Shared Pool 100663296 No
Free SGA Memory Available 1728053248

12 rows selected

---如下测试说明sga_target>=1g,Granule为16mb
SQL> show parameter sga_target

NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
sga_target big integer
1G
SQL> select * from v$sgainfo;

NAME BYTES RESIZE
---------------------------------------- ---------- ------
Fixed SGA Size 2182592 No
Redo Buffers 12185600 No
Buffer Cache Size 738197504 Yes
Shared Pool Size 268435456 Yes
Large Pool Size 16777216 Yes
Java Pool Size 16777216 Yes
Streams Pool Size 0 Yes
Shared IO Pool Size 0 Yes
Granule Size 16777216 No
Maximum SGA Size 4275781632 No
Startup overhead in Shared Pool 100663296 No
Free SGA Memory Available 3221225472

12 rows selected.

SQL> show parameter memory

NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
hi_shared_memory_address integer
0
memory_max_target big integer
4G
memory_target big integer
0
shared_memory_address integer
0
SQL> alter system set sga_target=2g scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size 2182592 bytes
Variable Size 2449474112 bytes
Database Buffers 1811939328 bytes
Redo Buffers 12185600 bytes
Database mounted.
Database opened.
SQL> select * from v$sgainfo;

NAME BYTES RESIZE
---------------------------------------- ---------- ------
Fixed SGA Size 2182592 No
Redo Buffers 12185600 No
Buffer Cache Size 1811939328 Yes
Shared Pool Size 268435456 Yes
Large Pool Size 16777216 Yes
Java Pool Size 16777216 Yes
Streams Pool Size 0 Yes
Shared IO Pool Size 0 Yes
Granule Size 16777216 No
Maximum SGA Size 4275781632 No
Startup overhead in Shared Pool 117440512 No
Free SGA Memory Available 2147483648

12 rows selected.

---换算粒度为mb
SQL> select 16777216/1024/1024 mb from dual;

MB
----------
16

---如下视图可查oracle参数可配置的值
SQL> select num,name,ordinal,value,isdefault from v$parameter_valid_values where
name='statistics_level';

NUM NAME ORDINAL
---------- ---------------------------------------- ----------
VALUE
--------------------------------------------------------------------------------

ISDEFAULT
--------------------------------------------------------------------------------

1826 statistics_level 1
BASIC
FALSE

1826 statistics_level 2
TYPICAL
TRUE

1826 statistics_level 3
ALL
FALSE

SQL> alter system set statistics_level=basic scope=spfile;

System altered.

SQL> show parameter sga_

NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
sga_max_size big integer
2G
sga_target big integer
2G
SQL> show parameter statis

NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
optimizer_use_pending_statistics boolean
FALSE
statistics_level string
TYPICAL
timed_os_statistics integer
0
timed_statistics boolean
TRUE
SQL> alter system set statistics_level=basic scope=spfile;

System altered

相关阅读:

Oracle 诊断一例　ADDM Reports Significant Virtual Memory Paging (Paranoimer, 2012-2-15)
Oracle 11g DRCP连接方式——基本原理 (realkid4, 2012-3-04)
设置系统全局区SGA命令 (leonarding, 2012-3-31)
AIX: Upgraded To 10.2.0.4 (or 10.2.0.5) And Large Page Memory Is No Longer Used (尛样儿, 2012-9-05)
将linux数据库用rman备份到远端win共享目录 (YallonKing, 2012-9-21)
第十五、十六章 (to_be_dba, 2012-12-26)
tuning 第七章（一） (to_be_dba, 2013-1-05)
tuning 第七章（二） (to_be_dba, 2013-1-08)
tuning 第七章（三） (to_be_dba, 2013-1-09)
tuning 第七章（四） (to_be_dba, 2013-1-09)

导入论坛引用链接收藏分享给好友推荐到圈子管理举报

TAG: management memory

引用删除yjjvxcuu / 2013-06-14 00:33:38

s4ObUs , [url=http://vrtrpbtujevf.com/]vrtrpbtujevf[/url], [link=http://zywvmotjsgsz.com/]zywvmotjsgsz[/link], http://bqnwotzjmnpc.com/

引用删除tfoeozlbv / 2013-06-11 13:10:23

PRH4l0 , [url=http://ugvyhzvhadfq.com/]ugvyhzvhadfq[/url], [link=http://lkbilwbizmrl.com/]lkbilwbizmrl[/link], http://gozskwdplefq.com/

引用删除Humed / 2013-06-07 10:58:07

You're a real deep thniekr. Thanks for sharing.

引用删除 wisdomone1 / 2013-01-31 21:12:18

SQL> show parameter memory

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

---关闭了sga自动管理功能
SQL> show parameter sga_

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 64G
sga_target big integer 0

---开启手工sga各组件自调功能
SQL> show parameter share_pool
SQL> show parameter shared_pool

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 429496729
shared_pool_size big integer 8G

SQL> show parameter large_pool

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
large_pool_size big integer 1G

SQL> show parameter db_cach

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice string ON
db_cache_size big integer 48G

SQL> show parameter sga_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 0

SQL> show parameter sga_max

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 64G

SQL> select * from v$sga_target_advice;

SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------

引用删除 wisdomone1 / 2013-01-31 21:12:17

引用删除 wisdomone1 / 2013-01-31 21:11:40

SQL> show parameter _cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_size big integer 0
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_size big integer 0
db_flash_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0

---辅助调节sga_target的大小,与v$memory_target_advice同理
SQL> select * from v$sga_target_advice;

SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
512 0.25 17 1 9635
1024 0.5 17 1 9635
1536 0.75 17 1 9635
2048 1 17 1 9635
2560 1.25 17 1 9635
3072 1.5 17 1 9635
3584 1.75 17 1 9635
4096 2 17 1 9635

8 rows selected

---如要配置sga_target的值,可参考如下步骤
------查看sga实际大小
SQL> select * from v$sga;

NAME VALUE
-------------------- ----------
Fixed Size 2177456
Variable Size 503318096
Database Buffers 1627389952
Redo Buffers 5001216

---查看sga_target配置大小
SQL> show parameter sga_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 2G

----换算获知sga即2g和sga_target相同
SQL> select sum(value)/1024/1024/1024 gb from v$sga;

GB
----------
1.99106216

---sga实时空闲内存
SQL> select * from v$sga_dynamic_free_memory;

CURRENT_SIZE
------------
0

---如下sql可以计算得到sga_target的配置值
SELECT (
(SELECT SUM(value) FROM V$SGA) -
(SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY)
) "SGA_TARGET"
FROM DUAL;

---------特附生产系统memory_target与sga_target相关配置信息

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Connected as e_channel@176sh_wtdb

---关闭了memory_target自动内存管理功能
SQL> select * from v$memory_target_advice;

MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION
----------- ------------------ ------------ ------------------- ----------

SQL> show parameter memory_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_target big integer 0

引用删除 wisdomone1 / 2013-01-31 21:08:49

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00824: cannot set SGA_TARGET or MEMORY_TARGET due to existing internal setti
ngs
ORA-00848: STATISTICS_LEVEL cannot be set to BASIC with SGA_TARGET or MEMORY_TAR
GET
SQL>

--小结:开启sga_target自动sga管理,STATISTICS_LEVEL只能设置为typical和all,不能是basic

---开启sga自动管理,如下参数需要配置为0
---当然你也可以把上述自动调节的内存组件设置为非0值,这样当sga自动调节时,对这些组件采用最小配置值
SQL> show parameter shared_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 0

SQL> show parameter large_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
large_pool_size big integer 0

SQL> show parameter java_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_pool_size big integer 0

SQL> show parameter db_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 0

SQL> show parameter streams_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size big integer 0

---如何参数可手工配置
SQL> show parameter log_buffer

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_buffer integer 4743168

SQL> show parameter db_keep_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_keep_cache_size big integer 0

SQL> show parameter db_recycle_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recycle_cache_size big integer 0