以下为oracle dba常用检查的脚本
1、检查命中率
1.1 SGA部分
1.1.1 检查缓冲区命中率
select sum(decode(name, 'physical reads', value, 0)) phys,
sum(decode(name, 'db block gets', value, 0)) gets,
sum(decode(name, 'Consistent Gets', value, 0)) con_gets,
(1 - (sum(decode(name, 'physical reads', value, 0)) /
(sum(decode(name, 'db block gets', value, 0)) +
sum(decode(name, 'consistent gets', value, 0))))) * 100 HitRatio
from v$sysstat;
1.1.2 缓冲池命中率
COLUMN size_for_estimate FORMAT 999,999,999,999 heading 'Cache Size in MB'
COLUMN buffers_for_estimate FORMAT 999,999,999 heading 'Buffers'
COLUMN estd_physical_read_factor FORMAT 999.99 heading 'Estd Phys Read Fctr'
COLUMN estd_physical_reads FORMAT 999,999,999 heading 'Estd Phys Reads'
SELECT size_for_estimate,
buffers_for_estimate,
estd_physical_read_factor,
estd_physical_reads
FROM V$DB_CACHE_ADVICE
WHERE name = 'DEFAULT'
AND block_size = (SELECT value FROM V$PARAMETER
WHERE name = 'db_block_size')
AND advice_status = 'ON';
1.1.3 数据字典缓存命中率
数据字典缓存命中率显示了对数据字典和其他对象的内存读操作所占的百分比。
select (1-(sum(getmisses)/sum(gets)))*100 "Hit Ratio"
from v$rowcache;
1.1.4 库缓存命中率
库缓存命中率显示了对实际语句和PL/SQL对象的内存读操作所占的百分比。注意,很高的命中率并不总是一件好事。
select Sum(Pins) / (Sum(Pins) + Sum(Reloads)) * 100 "Hit Ratio"
from V$LibraryCache;
1.2 PGA部分
1.2.1查询PGA内存排序命中率
select a.value "Disk Sorts", b.value "Memory Sorts",
round((100*b.value)/decode((a.value+b.value),0,1,(a.value+b.value)),2)
"Pct Memory Sorts"
from v$sysstat a, v$sysstat b
where a.name = 'sorts (disk)'
and b.name = 'sorts (memory)';
1.3 空闲的数据缓冲区的比例
select decode(state,0, 'FREE',
1,decode(lrba_seq,0,'AVAILABLE','BEING USED'),
3, 'BEING USED', state) "BLOCK STATUS",
count(*)
from x$bh
group by decode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE',
'BEING USED'),3, 'BEING USED', state);
2、检查表空间的是使用情况
3、检查和表空间生成语句
4、找出系统中cpu消耗和io比较大的语句
5、找出当前系统中比较慢的sql
①aix中通过topas命令找出cpu系统比较大的pid
②通过sql语句方法找出具体语句
6、找出比较多的表空间碎片
说明:得分越高,表空间的碎片越大