ORACLE巡检报告模板

ORACLE 数据库系统维护检查报告

注：红色字体表示需要尽快解决的问题。

其它检查内容：

1、alert文件：

有无错误？

2、表空间使用情况：

set linesize 300

SELECT upper(f.tablespace_name) "tablespace_name",

d.Tot_grootte_Mb "tablespace(M)",

d.Tot_grootte_Mb - f.total_bytes "used(M)",

round((d.Tot_grootte_Mb - f.total_bytes) / d.Tot_grootte_Mb * 100,2) "use%",

f.total_bytes "free_space(M)",

round(f.total_bytes / d.Tot_grootte_Mb * 100,2) "free%"

FROM

(SELECT tablespace_name,

round(SUM(bytes)/(1024*1024),2) total_bytes,

round(MAX(bytes)/(1024*1024),2) max_bytes

FROM sys.dba_free_space

GROUP BY tablespace_name) f,

(SELECT dd.tablespace_name, round(SUM(dd.bytes)/(1024*1024),2) Tot_grootte_Mb

FROM sys.dba_data_files dd

GROUP BY dd.tablespace_name) d

WHERE d.tablespace_name = f.tablespace_name

ORDER BY 4 DESC

/

3、Shared Pool Size 命中率：

select round((sum(gets)-sum(reloads))/sum(gets)*100,1) "libiary cache hit ratio %"

from v$librarycache where namespace

in ('SQL AREA','TABLE/PROCEDURE','BODY','TRIGGER');

4、数据字典命中率：

select round((1-sum(getmisses)/sum(gets))*100,1) "data dictionary hit ratio %"

from v$rowcache;

5、锁竞争：

select substr(https://www.360docs.net/doc/3a3637522.html,,1,25) Name,

l.gets, l.misses,

100*(l.misses/l.gets) "% Ratio (STAY UNDER 1%)"

from v$latch l, v$latchname ln

where https://www.360docs.net/doc/3a3637522.html, in ('cache buffers lru chain')

and https://www.360docs.net/doc/3a3637522.html,tch# = https://www.360docs.net/doc/3a3637522.html,tch#;

6、排序命中率：

select a.value "Sort(Disk)", b.value "Sort(Memory)",

round(100*(a.value/decode((a.value+b.value), 0,1,(a.value+b.value))),2) "% Ratio (STAY UNDER 5%)"

from v$sysstat a, v$sysstat b

where https://www.360docs.net/doc/3a3637522.html, = 'sorts (disk)'

and https://www.360docs.net/doc/3a3637522.html, = 'sorts (memory)';

7、数据缓冲区命中率：

select round((1-(phy.value/(cur.value+con.value)))*100,1)||'%' ratio

from v$sysstat phy,v$sysstat cur,v$sysstat con

where https://www.360docs.net/doc/3a3637522.html,='physical reads' and https://www.360docs.net/doc/3a3637522.html,='db block gets' and https://www.360docs.net/doc/3a3637522.html,='consistent gets';

8、v$session_wait：

select sid,seq#,event,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where event not like 'SQL%' and event not like 'rdbms%';

9、回滚段的争用情况：

select name, waits, gets, waits/gets "Ratio"

from v$rollstat a, v$rollname b

where https://www.360docs.net/doc/3a3637522.html,n = https://www.360docs.net/doc/3a3637522.html,n;

10、无效对象情况：

col OBJECT_NAME for a36

SELECT object_name, object_type,status FROM dba_objects WHERE status like 'INVALID';

结论：

（范文素材和资料部分来自网络，供参考。可复制、编制，期待你的好评与关注）