ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORACLE内存调整笔记一

ORACLE内存调整笔记一

原创 Linux操作系统 作者:yxg0313 时间:2008-03-25 08:13:35 0 删除 编辑
 一、调整缓冲区缓冲

计算缓冲区命中率

select round((1-(phy.value/(cur.value+con.value)))*100,1)||'%' ratio  from v$sysstat phy ,v$sysstat cur, v$sysstat con where phy.name='physical reads' and cur.name='db block gets' and con.name='consistent gets';

缓冲区命中率在95%-90%为适当分配,90%-80%为关注调整,超过95%属于过度分配,应该减少。

每个会话的缓冲区命中率

select phy.sid,s.username,1-(phy.value)/(cur.value + con.value) "buffer_hit_ratio" from v$sesstat phy , v$sesstat cur , v$sesstat con ,v$statname s1 , v$statname s2 , v$statname s3 ,v$session s where s1.name='physical reads' and s2.name='db block gets' and s3.name='consistent gets' and phy.statistic#=s1.STATISTIC# and

cur.statistic#=s2.STATISTIC# and con.statistic#=s3.STATISTIC# and cur.value<>0 and con.value<>0 and phy.sid=cur.sid and phy.sid=con.sid

and phy.sid=s.sid;

查看个体缓冲池的命中率

select name,block_size,round((1-(physical_reads/(db_block_gets + consistent_gets)))*100) ||'%' ratio from v$buffer_pool_statistics where (db_block_gets + consistent_gets)>0;

将表缓存到缓存中

1 alter table departments storage (buffer_pool_keep);

  Alter table departments cache;

  Alter table departments nocache;

2 select /*+ cache(categories) */ * from categories;

  select /*+ nocache(categories) */ * from categories;

二、调整日志缓冲区

从v$sysstat 获取与重做日志缓冲区相关的所有统计

select name,value from v$sysstat where name like '%redo%';

1    redo synch writes  1635661

2    redo synch time    226656

3    redo entries  45379291

4    redo size 12717224856

5    redo buffer allocation retries   2978  重做缓冲区分配重试  必须接近0

6    redo wastage  958088764

7    redo writer latching time   3997

8    redo writes   3527741

9    redo blocks written    27571439

10   redo write time    379533

11   redo log space requests 3718          重做日志空间请求    必须接近0

12   redo log space wait time    5019      重做日志空间等待时间必须接近0

13   redo log switch interrupts  0

14   redo ordering marks    317

日志缓冲区空间等待事件

select event,total_waits from v$system_event where event like 'log%';

log buffer space 接近0,如果缓冲区很大,仍不接近0,可能是LGWR不够快,I/O争用或者检查点太频繁造成。

日志缓冲区请求与记录项比率

select (r.value*5000)/e.value "redo requests/entries ratio" from v$sysstat r,v$sysstat e where r.name='redo log space requests' and e.name='redo entries';  应该非常接近0

日志缓冲区重试与记录项比率

select (r.value*5000)/e.value "redo retries/entries ratio" from v$sysstat r,v$sysstat e where r.name='redo buffer allocation retries' and e.name='redo entries';  应该小于1%

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

请登录后发表评论 登录
全部评论

注册时间:2008-03-06

  • 博文量
    34
  • 访问量
    44973