ORACLE数据库优化
SGA内存优化
1、oracle9i后增加的内存优化建议的视图:
select tname from tab where tname like '%ADVICE%';
和SGA相关的是v$DB_CACHE_ADVICE和v$SHARED_POOL_ADVICE,这两个视图中的数据是系统动态收集的信息,提供了SGA的优化建议。
2、缓冲区高速缓存建议(buffer cache advisory)首初始化参数DB_CACHE_ADVICE控制。该参数为动态参数,有三个选项:
OFF:关闭建议并且不为建议分配内存
ON:开启建议,并却CPU和内存开销都会发生
READY:关闭建议但是仍保留分配给建议的内存
使用show parameter DB_CACHE_AD参数查看系统设置的参数
共享池缓存建议(share pool advisory)受statistics_level初始化参数影响,有三个选项: BASIC:收集基本信息
TYPICAL:收集大部分信息,这是系统默认设置
ALL:收集全部信息
通过v$statistics_level视图查看该参数的影响范围。使用v$shared_pool_advice视图查看建议内存
PGA内存优化
1、有关PGA的几个参数:
a) Pga_aggregate_target:所用session可以使用的PGA大小,可动态调整,大小从10M~(4096G-1)byte;oracle9i中该参数只针对专用服务器模式;oracle10g以后,专用和共享服务器模式都支持。PGA分为可调内存区(TUNABLE MEMERY SIZE)和不可调内存区(UNTUNABLE MEMERY SIZE),可调区有SQL操作使用,其余为不可调区。该参数同时控制全局PGA分配和私有工作区内存分配。
对于串行操作,单个SQL操作能够使用的内存按照以下原则分配:
MIN (5% PGA_AGGREGATE_TARGET ,100M)
对于并行操作:
30%PGA_AGGREGATE_TARGET/DOP(并行度)
b) Workarea_size_policy:此参数控制PGA是否可以动态调整。AUTO表示启动了自动调整;MANUAL表示关闭自动调整。
2、PGA的分配原则:
对于OLTP系统,PGA_AGGREGATE_TARGET=(<Total Physical Memery>*80%)*20%;
对于DOS系统:PGA_AGGREGATE_TARGET=(<Total Physical Memery>*80%)*50%.
也就是说,对于单独的数据库系统,20%的物理内存留给操作系统使用,其余内存分配给SGA和PGA,PGA可以占用总内存的20%(OLTP)~50%(DOP)。
3、在$process视图中查看PGA损耗
[oracle@kvm ~]$ ps -ef |grep ora_|head -1
oracle 4141 1 0 10:33 ? 00:00:00 ora_pmon_orcl
PID |
SPID |
PGA_USED_MEM |
PGA_ALLOC_MEM |
PGA_FREEABLE_MEM |
PGA_MAX_MEM |
2 |
4141 |
304117 |
451141 |
0 |
451141 |
SQL在工作区中以三种方式执行:
Optional(优化方式):指所有处理操作可以在内存中完成
Onepass:大部分操作可以在内存中完成,大需要磁盘操作
Multipass:大量操作需要磁盘交互,性能极差。
4、v$pagstat视图查看PGA使用量
select name,value/1024/1024 from v$pgastat where name in('aggregate PGA target parameter','global memory bound')
5、V$pga_target_advice视图查看系统建议的PGA值
--------------------------------------------------------------------
select pga_target_for_estimate / 1024 / 1024 pag_target_for_estimate,
pga_target_factor,
estd_pga_cache_hit_percentage,
estd_overalloc_count
from v$pga_target_advice;
-------------------------------------------------------------------
PAG_TARGET_FOR_ESTIMATE |
PGA_TARGET_FACTOR |
ESTD_PGA_CACHE_HIT_PERCENTAGE |
ESTD_OVERALLOC_COUNT |
12.5 |
0.125 |
73 |
2 |
25 |
0.25 |
73 |
2 |
50 |
0.5 |
91 |
0 |
75 |
0.75 |
91 |
0 |
100 |
1 |
91 |
0 |
120 |
1.2 |
91 |
0 |
140 |
1.4 |
100 |
0 |
160 |
1.6 |
100 |
0 |
180 |
1.8 |
100 |
0 |
200 |
2 |
100 |
0 |
300 |
3 |
100 |
0 |
400 |
4 |
100 |
0 |
600 |
6 |
100 |
0 |
800 |
8 |
100 |
0 |
PAG_TARGET_FOR_ESTIMATE:对应的PGA值,
PGA_TARGET_FACTOR:建议值和当前值的比例,值为1对应的PGA为系统当前值。
ESTD_PGA_CACHE_HIT_PERCENTAGE:对应PGA值的命中率
ESTD_OVERALLOC_COUNT:对应PGA值的负载,该值为0时PGA没有过载
----------------------------------------------------------------------------------------------------------------------根据系统建议PGA值为140M时,命中率最高,并且没有过载;PGA为50M时,系统消除PGA过载。
-----------------------------------------------------------------------------------------------------------------------可以通过OEM或oracle客户端查看系统建议。
6、v$pga_target_advice_histogram视图
PGA_TARGET_FACTOR |
LOW_OPTIMAL_SIZE |
HIGH_OPTIMAL_SIZE |
ESTD_OPTIMAL_EXECUTIONS |
ESTD_ONEPASS_EXECUTIONS |
ESTD_MULTIPASSES_EXECUTIONS |
ESTD_TOTAL_EXECUTIONS |
0.25 |
2199023255552 |
4398046510079 |
0 |
0 |
0 |
0 |
0.25 |
1099511627776 |
2199023255551 |
0 |
0 |
0 |
0 |
0.25 |
549755813888 |
1099511627775 |
0 |
0 |
0 |
0 |
0.25 |
274877906944 |
549755813887 |
0 |
0 |
0 |
0 |
0.25 |
137438953472 |
274877906943 |
0 |
0 |
0 |
0 |
0.25 |
68719476736 |
137438953471 |
0 |
0 |
0 |
0 |
0.25 |
34359738368 |
68719476735 |
0 |
0 |
0 |
0 |
0.25 |
17179869184 |
34359738367 |
0 |
0 |
0 |
0 |
0.25 |
8589934592 |
17179869183 |
0 |
0 |
0 |
0 |
0.25 |
4294967296 |
8589934591 |
0 |
0 |
0 |
0 |
0.25 |
2147483648 |
4294967295 |
0 |
0 |
0 |
0 |
0.25 |
1073741824 |
2147483647 |
0 |
0 |
0 |
0 |
0.25 |
536870912 |
1073741823 |
0 |
0 |
0 |
0 |
0.25 |
268435456 |
536870911 |
0 |
0 |
0 |
0 |
0.25 |
134217728 |
268435455 |
0 |
0 |
0 |
0 |
0.25 |
67108864 |
134217727 |
0 |
0 |
0 |
0 |
0.25 |
33554432 |
67108863 |
0 |
0 |
0 |
0 |
0.25 |
16777216 |
33554431 |
5 |
0 |
2 |
7 |
0.25 |
8388608 |
16777215 |
0 |
0 |
0 |
0 |
0.25 |
4194304 |
8388607 |
0 |
0 |
0 |
0 |
0.25 |
2097152 |
4194303 |
3 |
0 |
0 |
3 |
0.25 |
1048576 |
2097151 |
30 |
0 |
0 |
30 |
0.25 |
524288 |
1048575 |
77 |
0 |
0 |
77 |
0.25 |
262144 |
524287 |
2 |
0 |
0 |
2 |
0.25 |
131072 |
262143 |
0 |
0 |
0 |
0 |
0.25 |
65536 |
131071 |
34 |
0 |
0 |
34 |
0.25 |
32768 |
65535 |
0 |
0 |
0 |
0 |
0.25 |
16384 |
32767 |
0 |
0 |
0 |
0 |
0.25 |
8192 |
16383 |
0 |
0 |
0 |
0 |
0.25 |
4096 |
8191 |
0 |
0 |
0 |
0 |
0.25 |
2048 |
4095 |
6585 |
0 |
0 |
6585 |
0.25 |
1024 |
2047 |
0 |
0 |
0 |
0 |
0.25 |
0 |
1023 |
0 |
0 |
0 |
0 |
如果觉得我的文章对您有用,请点赞。您的支持将鼓励我继续创作!
赞0
添加新评论1 条评论
2018-05-14 20:27