ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 巡检2

oracle 巡检2

原创 Linux操作系统 作者:sqleee 时间:2009-02-27 09:54:31 0 删除 编辑

引用:http://space.itpub.net/756652/viewspace-242447

SCRIPTS:


execute.sql

spool OralceHealthReport.txt
@OracleHealthReport.sql
spool off

OracleHealthReport.sql

SET NEWPAGE NONE LINESIZE 2000 TRIMSPOOL ON TERMOUT OFF
COL FILE FORMAT A80
COL FILE_NAME FORMAT A80


PROMPT --************************************************
PROMPT -- ** 项目:数据库状态
PROMPT -- ** 等级:高
PROMPT -- ** 正常值:数据库状态正常
PROMPT -- ** 说明:如果发现该项目没有输出,马上报告
PROMPT -- ** 监控频度:定期
PROMPT -- ** Sql:DbStatus.sql
PROMPT

SELECT '数据库'||INSTANCE_NAME||'状态正常' 数据库状态 FROM V$INSTANCE;

PROMPT --************************************************
PROMPT -- ** 项目:表空间使用情况
PROMPT -- ** 等级:高
PROMPT -- ** 正常值:
PROMPT -- ** 说明:如果发现用户表空间超过85%,马上报告
PROMPT -- ** 监控频度:定期
PROMPT -- ** Sql:TableSpaceStatus.sql
PROMPT

SELECT D.TABLESPACE_NAME,FILE_NAME "FILE_NAME",SPACE "SUM_SPACE(M)",SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",
ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",AUTOEXTENSIBLE
FROM (SELECT FILE_ID,FILE_NAME,TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME,FILE_ID,FILE_NAME) D,
(SELECT FILE_ID,TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME,FILE_ID) E,
(SELECT FILE_ID,AUTOEXTENSIBLE FROM DBA_DATA_FILES) F
WHERE D.TABLESPACE_NAME = E.TABLESPACE_NAME(+) AND D.FILE_ID = E.FILE_ID(+) AND D.FILE_ID = F.FILE_ID(+)
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,FILE_NAME "FILE_NAME",SPACE "SUM_SPACE(M)", USED_SPACE "USED_SPACE(M)",
ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",AUTOEXTENSIBLE
FROM
(SELECT FILE_ID,FILE_NAME,TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME,FILE_ID,FILE_NAME) D,
(SELECT FILE_ID,TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME,FILE_ID) E,
(SELECT FILE_ID,AUTOEXTENSIBLE FROM DBA_TEMP_FILES) F
WHERE D.TABLESPACE_NAME = E.TABLESPACE_NAME(+) AND D.FILE_ID = E.FILE_ID(+) AND D.FILE_ID = F.FILE_ID(+)
ORDER BY TABLESPACE_NAME,FILE_NAME;

PROMPT --************************************************
PROMPT -- ** 项目:缓冲区命中率(Buffer Cache)
PROMPT -- ** 等级:中
PROMPT -- ** 正常值:>95%
PROMPT -- ** 说明:如果发现该值低于90%,马上报告
PROMPT -- ** 监控频度:定期
PROMPT -- ** Sql:BufferHitRatio.sql
PROMPT

SELECT (1 - (SUM(DECODE(NAME, 'physical reads', VALUE, 0)) /
(SUM(DECODE(NAME, 'db block gets', VALUE, 0)) +
SUM(DECODE(NAME, 'consistent gets', VALUE, 0))))) * 100
"缓冲区命中率"
FROM V$SYSSTAT;

PROMPT --************************************************
PROMPT -- ** 项目:数据字典命中率(Dictionary Cache)
PROMPT -- ** 等级:中
PROMPT -- ** 正常值:>95%
PROMPT -- ** 说明:如果发现该值低于90%,马上报告
PROMPT -- ** 监控频度:定期
PROMPT -- ** Sql:DictionaryHitRatio.sql
PROMPT

SELECT (1 - (SUM(GETMISSES) / SUM(GETS))) * 100 "数据字典命中率"
FROM V$ROWCACHE;

PROMPT --************************************************
PROMPT -- ** 项目:库缓存命中率(Library Cache)
PROMPT -- ** 等级:中
PROMPT -- ** 正常值:>95%
PROMPT -- ** 说明:如果发现该值低于90%,马上报告
PROMPT -- ** 监控频度:定期
PROMPT -- ** Sql:LibraryHitRatio.sql
PROMPT

SELECT SUM(PINS) / (SUM(PINS) + SUM(RELOADS)) * 100 "库缓存命中率"
FROM V$LIBRARYCACHE;

PROMPT --************************************************
PROMPT -- ** 项目:内存排序百分比(Sort in Memory)
PROMPT -- ** 等级:中
PROMPT -- ** 正常值:>95%
PROMPT -- ** 说明:如果发现该值低于90%,马上报告
PROMPT -- ** 监控频度:定期
PROMPT -- ** Sql:MemorySortRatio.sql
PROMPT

select a.value "磁盘排序",
b.value "内存排序",
round((100 * b.value) /decode((a.value + b.value), 0, 1, (a.value + b.value)),2) "内存排序百分比"
from v$sysstat a, v$sysstat b
where a.name = 'sorts (disk)'
and b.name = 'sorts (memory)';

PROMPT --************************************************
PROMPT -- ** 项目:空闲的数据缓冲区比例
PROMPT -- ** 等级:中
PROMPT -- ** 正常值:>95%
PROMPT -- ** 说明:如果发现该值低于90%,马上报告
PROMPT -- ** 监控频度:多次
PROMPT -- ** Sql:FreeBufferRatio.sql
PROMPT

SELECT SUM(DECODE(STATUS,'AVAILABLE',VAL,0)) "AVAILABLE",SUM(DECODE(STATUS,'BEING USED',VAL,0)) "BEING USED",
SUM(DECODE(STATUS,'AVAILABLE',VAL,0))/(SUM(DECODE(STATUS,'AVAILABLE',VAL,0))+SUM(DECODE(STATUS,'BEING USED',VAL,0)))*100||'%' "AVAILABLE PERCENT"
FROM(SELECT DECODE(STATE,0,'FREE',1,DECODE(LRBA_SEQ, 0, 'AVAILABLE', 'BEING USED'),3,'BEING USED',STATE) "STATUS",
COUNT(*) VAL
FROM X$BH
GROUP BY DECODE(STATE,0,'FREE',1,DECODE(LRBA_SEQ, 0, 'AVAILABLE', 'BEING USED'),3,'BEING USED',STATE));

PROMPT --************************************************
PROMPT -- ** 项目:重做日志命中率
PROMPT -- ** 等级:中
PROMPT -- ** 正常值:>95%
PROMPT -- ** 说明:如果发现该值低于90%,马上报告
PROMPT -- ** 监控频度:定期
PROMPT -- ** Sql:RedoHitRatio.sql
PROMPT

SELECT name, gets, misses, immediate_gets, immediate_misses,
100 - Decode(gets,0,0,misses/(gets+misses))*100 ratio1,
100 - Decode(immediate_gets+immediate_misses,0,0,immediate_misses/(immediate_gets+immediate_misses))*100 ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');

PROMPT --************************************************
PROMPT -- ** 项目:多版本Sql
PROMPT -- ** 等级:中
PROMPT -- ** 正常值:
PROMPT -- ** 说明:提交开发人员建议对这些SQL进行调整
PROMPT -- ** 监控频度:多次
PROMPT -- ** Sql:MultiVersionSql.sql
PROMPT

SELECT SUBSTR(SQL_TEXT,1,80) "SQL", COUNT(*) "记录数", SUM(EXECUTIONS) "执行次数"
FROM V$SQLAREA
WHERE EXECUTIONS < 5
GROUP BY SUBSTR(SQL_TEXT,1,80)
HAVING COUNT(*) > 30
ORDER BY 2 DESC;

PROMPT --************************************************
PROMPT -- ** 项目:低效Sql
PROMPT -- ** 等级:中
PROMPT -- ** 正常值:
PROMPT -- ** 说明:提交开发人员建议对这些SQL进行调整
PROMPT -- ** 监控频度:多次
PROMPT -- ** Sql:InefficientSql.sql
PROMPT

SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>0
AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC;


PROMPT --************************************************
PROMPT -- ** 项目:长时间运行的Sql
PROMPT -- ** 等级:中
PROMPT -- ** 正常值:
PROMPT -- ** 说明:提交开发人员建议对这些SQL进行调整
PROMPT -- ** 监控频度:多次
PROMPT -- ** Sql:LongRuningSql.sql
PROMPT

SELECT sql_text "SQL", executions "运行次数", buffer_gets / decode(executions, 0, 1, executions) / 4000 "响应时间"
FROM v$sql
WHERE buffer_gets / decode(executions, 0,1, executions) / 4000 > 10
AND executions > 0;

PROMPT --************************************************
PROMPT -- ** 项目:表空间IO比例
PROMPT -- ** 等级:中
PROMPT -- ** 正常值:
PROMPT -- ** 说明:发现并报告物理读、物理写特别大的表空间
PROMPT -- ** 监控频度:定期
PROMPT -- ** Sql:TablespaceIO.sql
PROMPT

select df.tablespace_name name,df.file_name "file",f.phyrds "物理读次数",
f.phyblkrd "物理读BLOCKS",f.phywrts "物理写次数", f.phyblkwrt "物理写BLOCKS"
from v$filestat f, dba_data_files df
where f.file# = df.file_id
order by df.tablespace_name;

PROMPT --************************************************
PROMPT -- ** 项目:文件系统IO比例
PROMPT -- ** 等级:中
PROMPT -- ** 正常值:
PROMPT -- ** 说明:发现并报告物理读、物理写特别大的数据文件
PROMPT -- ** 监控频度:定期
PROMPT -- ** Sql:DatafileIO.sql
PROMPT

select substr(a.file#,1,2) "#", substr(a.name,1,30) "Name",
a.status, a.bytes, b.phyrds, b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#;

PROMPT --************************************************
PROMPT -- ** 项目:临时表空间使用情况
PROMPT -- ** 等级:中
PROMPT -- ** 正常值:
PROMPT -- ** 说明:发现并报告临时表空间使用过高的SESSION机PROGRAM
PROMPT -- ** 监控频度:多次
PROMPT -- ** Sql:TempUsage.sql
PROMPT

SELECT se.username 用户名,
se.sid,
se.serial#,
se.sql_address,
se.machine,
se.program,
su.tablespace,
su.blocks*8192/1024/1024 "Used Space(M)",
su.segtype,
su.contents
FROM v$session se,
v$sort_usage su
WHERE se.saddr=su.session_addr;

PROMPT --************************************************
PROMPT -- ** 项目:锁与等待
PROMPT -- ** 等级:中
PROMPT -- ** 正常值:
PROMPT -- ** 说明:发现并报告锁与等待的对象
PROMPT -- ** 监控频度:多次
PROMPT -- ** Sql:LockAndWait.sql
PROMPT

SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,
o.owner,o.object_name,o.object_type,s.sid,s.serial#
FROM v$locked_object l,dba_objects o,v$session s
WHERE l.object_id=o.object_id AND l.session_id=s.sid
ORDER BY o.object_id,xidusn DESC;

PROMPT --************************************************
PROMPT -- ** 项目:Lock,Process,Session
PROMPT -- ** 等级:中
PROMPT -- ** 正常值:
PROMPT -- ** 说明:发现并报告MAX_UTILIZATION 接近INITIAL_ALLOCATION的项目
PROMPT -- ** 监控频度:定期
PROMPT -- ** Sql:ResourceLimit.sql
PROMPT

select * from v$resource_limit;

PROMPT --************************************************
PROMPT -- ** 项目:活动会话
PROMPT -- ** 等级:中
PROMPT -- ** 正常值:
PROMPT -- ** 说明:同时活动的会话过高,通知开发人员
PROMPT -- ** 监控频度:多次
PROMPT -- ** Sql:SameTimeActive.sql
PROMPT

select count(1) "同时ACTIVE的会话数量"
from v$session
where status='ACTIVE' and username not in ('SYS','SYSTEM');

PROMPT --************************************************
PROMPT -- ** 项目:日志切换间隔
PROMPT -- ** 等级:中
PROMPT -- ** 正常值:>=(15-20)分钟
PROMPT -- ** 说明:业务高峰期日志切换频繁,马上报告
PROMPT -- ** 监控频度:定期
PROMPT -- ** Sql:LogSyncTime.sql
PROMPT

SELECT B.RECID,B.FIRST_TIME,A.FIRST_TIME,ROUND((A.FIRST_TIME-B.FIRST_TIME)*24*60,2) MINATES
FROM V$LOG_HISTORY A,V$LOG_HISTORY B
WHERE A.RECID=B.RECID +1 AND A.FIRST_TIME>SYSDATE - 20 AND ROUND((A.FIRST_TIME-B.FIRST_TIME)*24*60,2)<30
ORDER BY A.FIRST_TIME DESC;

PROMPT --************************************************
PROMPT -- ** 项目:Invalid Objects
PROMPT -- ** 等级:中
PROMPT -- ** 正常值:
PROMPT -- ** 说明:检查INVALID对象,马上报告
PROMPT -- ** 监控频度:定期
PROMPT -- ** Sql:InvalidObject.sql
PROMPT

SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS
WHERE STATUS = 'INVALID';

PROMPT --************************************************
PROMPT -- ** 项目:Disabled Indexes
PROMPT -- ** 等级:中
PROMPT -- ** 正常值:
PROMPT -- ** 说明:检查UNUSABLE索引,马上报告
PROMPT -- ** 监控频度:定期
PROMPT -- ** Sql:UnusableIndex.sql
PROMPT

SELECT OWNER,INDEX_NAME,INDEX_TYPE,TABLE_NAME,STATUS FROM DBA_INDEXES
WHERE STATUS = 'UNUSABLE';

PROMPT --************************************************
PROMPT -- ** 项目:Disabled Constraints
PROMPT -- ** 等级:中
PROMPT -- ** 正常值:
PROMPT -- ** 说明:检查Disabled Constraints,马上报告
PROMPT -- ** 监控频度:定期
PROMPT -- ** Sql:DisabledConstraints.sql
PROMPT

SELECT OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME FROM DBA_CONSTRAINTS
WHERE STATUS='DISABLED';

PROMPT --************************************************
PROMPT -- ** 项目:Disabled Triggers
PROMPT -- ** 等级:中
PROMPT -- ** 正常值:
PROMPT -- ** 说明:检查Disabled Triggers,马上报告
PROMPT -- ** 监控频度:定期
PROMPT -- ** Sql:DisabledTriggers.sql
PROMPT

SELECT OWNER,TRIGGER_NAME,TRIGGER_TYPE FROM DBA_TRIGGERS
WHERE STATUS='DISABLED';

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

下一篇: oracle 巡检1
请登录后发表评论 登录
全部评论

注册时间:2008-03-21

  • 博文量
    31
  • 访问量
    114551