oracle11g memory management系列(一)

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