ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 查看ORACLE性能的几个基础SQL

查看ORACLE性能的几个基础SQL

原创 Linux操作系统 作者:jxga 时间:2006-04-28 00:00:00 0 删除 编辑

判断回滚段竞争的SQL语句:(当Ratio大于2时存在回滚段竞争,需要增加更多的回滚段)
select rn.name, rs.GETS, rs.WAITS, (rs.WAITS / rs.GETS) * 100 ratio
from v$rollstat rs, v$rollname rn
where rs.USN = rn.usn


判断回滚段竞争的SQL语句:(当Ratio大于2时存在回滚段竞争,需要增加更多的回滚段)
select rn.name, rs.GETS, rs.WAITS, (rs.WAITS / rs.GETS) * 100 ratio
from v$rollstat rs, v$rollname rn
where rs.USN = rn.usn

判断恢复日志竞争的SQL语句:(immediate_contention或wait_contention的值大于1时存在竞争)
select name,
(t.IMMEDIATE_MISSES /
decode((t.IMMEDIATE_GETS + t.IMMEDIATE_MISSES),
0,
-1,
(t.IMMEDIATE_GETS + t.IMMEDIATE_MISSES))) * 100 immediate_contention,
(t.MISSES / decode((t.GETS + t.MISSES), 0, -1, (t.GETS + t.MISSES))) * 100 wait_contention
from v$latch t
where name in ('redo copy', 'redo allocation')

判断表空间碎片:(如果最大空闲空间占总空间很大比例则可能不存在碎片,如果比例较小,且有许多空闲空间,则可能碎片很多)
select t.tablespace_name,
sum(t.bytes),
max(t.bytes),
count(*),
max(t.bytes) / sum(t.bytes) radio
from dba_free_space t
group by t.tablespace_name
order by t.tablespace_name

确定命中排序域的次数:
select t.NAME, t.VALUE from v$sysstat t where t.NAME like 'sort%'

查看当前SGA值:
select * from v$sga

确定高速缓冲区命中率:(如果命中率低于70%,则应该加大init.ora参数中的DB_BLOCK_BUFFER的值)
select 1 - sum(decode(name, 'physical reads', value, 0)) /
(sum(decode(name, 'db block gets', value, 0)) +
sum(decode(name, 'consistent gets', value, 0))) hit_ratio
from v$sysstat t
where name in ('physical reads', 'db block gets', 'consistent gets')

确定共享池中的命中率:(如果ratio1大于1时,需要加大共享池,如果ratio2大于10%时,需要加大共享池SHARED_POOL_SIZE)
select sum(pins) pins,
sum(reloads) reloads,
(sum(reloads) / sum(pins)) * 100 ratio1
from v$librarycache

select sum(gets) gets,
sum(getmisses) getmisses,
(sum(getmisses) / sum(gets)) * 100 ratio2
from v$rowcache

查询INIT.ORA参数:
select * from v$parameter

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

下一篇: 读书随想
请登录后发表评论 登录
全部评论

注册时间:2008-01-08

  • 博文量
    185
  • 访问量
    1296070