PGA中存储了用户会话数据,PGA大小由参数PGA_AGGREGATE_TARGET决定,
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
pga_aggregate_target big integer
190M
可以通过查询V$PGA_TARGET_ADVICE,来判断PGA_AGGREGATE_TARGET设置得是否合理。Oracle会根据过去的负载来判断。
MB ESTD_PGA_CACHE_HIT_PERCENTAGE
---------- -----------------------------
23.75 100
47.5 100
95 100
142.5 100
190 100
228 100
266 100
304 100
342 100
380 100
570 100
MB ESTD_PGA_CACHE_HIT_PERCENTAGE
---------- -----------------------------
760 100
1140 100
1520 100
如上例,最小可将PGA_AGGREGATE_TARGET设置为23.75MB。
SGA相关参数,
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
sga_max_size big integer
572M
sga_target big integer
572M
默认情况下,SGA各组件的初始参数为0,要查看实际使用状况,
COMPONENT Current MB Min MB
------------------------------ ---------- ----------
shared pool 176 172
large pool 4 4
java pool 4 4
streams pool 0 0
DEFAULT buffer cache 380 380
KEEP buffer cache 0 0
RECYCLE buffer cache 0 0
DEFAULT 2K buffer cache 0 0
DEFAULT 4K buffer cache 0 0
DEFAULT 8K buffer cache 0 0
DEFAULT 16K buffer cache 0 0
COMPONENT Current MB Min MB
------------------------------ ---------- ----------
DEFAULT 32K buffer cache 0 0
ASM Buffer Cache 0 0
各组件总和大约等于SGA_TARGET。
点击(此处)折叠或打开
- show parameter pga_aggregate_target
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
pga_aggregate_target big integer
190M
点击(此处)折叠或打开
- select pga_target_for_estimate/1024/1024 "MB"
- ,estd_pga_cache_hit_percentage
- from v$pga_target_advice
- where estd_pga_cache_hit_percentage > 95;
MB ESTD_PGA_CACHE_HIT_PERCENTAGE
---------- -----------------------------
23.75 100
47.5 100
95 100
142.5 100
190 100
228 100
266 100
304 100
342 100
380 100
570 100
MB ESTD_PGA_CACHE_HIT_PERCENTAGE
---------- -----------------------------
760 100
1140 100
1520 100
SGA相关参数,
点击(此处)折叠或打开
- show parameter sga_%
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
sga_max_size big integer
572M
sga_target big integer
572M
点击(此处)折叠或打开
- select component
- ,current_size/1024/1024 "Current MB"
- ,min_size/1024/1024 "Min MB"
- from v$sga_dynamic_components
COMPONENT Current MB Min MB
------------------------------ ---------- ----------
shared pool 176 172
large pool 4 4
java pool 4 4
streams pool 0 0
DEFAULT buffer cache 380 380
KEEP buffer cache 0 0
RECYCLE buffer cache 0 0
DEFAULT 2K buffer cache 0 0
DEFAULT 4K buffer cache 0 0
DEFAULT 8K buffer cache 0 0
DEFAULT 16K buffer cache 0 0
COMPONENT Current MB Min MB
------------------------------ ---------- ----------
DEFAULT 32K buffer cache 0 0
ASM Buffer Cache 0 0
各组件总和大约等于SGA_TARGET。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22621861/viewspace-1273000/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22621861/viewspace-1273000/