数据库巡检 (1).doc - 墨天轮文档

数据库巡检 (1).doc

鲁鲁

9页

2次

2024-12-16

免费下载

日常巡检

1.1

数据库巡检

为了保证

oracle

数据库稳定，高效的运行，每个季度初需要对

oracle

数据库进行健康

检查。以确定数据库是否存在故障及性能问题。

对于异常状况，上报

http://support.ultrapower.com.cn

，进一步诊断、分析，及时解决。

巡检工作包括以下细则：



ALERT

文件

(alertSID.log)

是否出现错误信息



top10

等待事件



数据库大小



表空间使用情况



内存配置



三个

Top10 SQL



内存命中率



归档方式及备份情况

1.1.1

巡检脚本

1.1.1.1 AlertSID.log

文件位置：

SQL> show parameter background_core_dump

1.1.1.2

归档方式及备份情况

（

）查看是否为归档方式：

SQL>archive log list;

（

）说明该数据库备份情况，是否有备份策略。

1.1.1.3 top10

等待事件：

 不同的版本，事件的多少不同

 Oracle9i

select t2.event,round(100*t2.time_waited/(t1.w1+t3.cpu),2) event_wait_percent from (

SELECT SUM(time_waited) w1 FROM v$system_event WHERE event NOT IN

('smon timer','pmon timer','rdbms ipc message','Null event','parallel query dequeue','pipe get',

'client message','SQL*Net message to client','SQL*Net message from client','SQL*Net more

data from client',

'dispatcher timer','virtual circuit status','lock manager wait for remote message','PX Idle Wait',

'PX Deq: Execution Msg','PX Deq: Table Q Normal','wakeup time manager','slave wait','i/o

slave wait',

'jobq slave wait','null event','gcs remote message','gcs for action','ges remote message','queue

messages') ) t1,

(select * from (

select t.event,t.total_waits,t.total_timeouts,t.time_waited,t.average_wait,rownum num from

(select event,total_waits,total_timeouts,time_waited,average_wait from v$system_event

where event not in

('smon timer','pmon timer','rdbms ipc message','Null event','parallel query dequeue','pipe get',

'client message','SQL*Net message to client','SQL*Net message from client','SQL*Net more

data from client',

'dispatcher timer','virtual circuit status','lock manager wait for remote message','PX Idle Wait',

'PX Deq: Execution Msg','PX Deq: Table Q Normal','wakeup time manager','slave wait','i/o

slave wait',

'jobq slave wait','null event','gcs remote message','gcs for action','ges remote message','queue

messages')

order by time_waited desc ) t) where num<11) t2,

(SELECT VALUE CPU FROM v$sysstat WHERE NAME LIKE 'CPU used by this session'

) t3

 Oracle10g

select t2.event,round(100*t2.time_waited/(t1.w1+t3.cpu),2) event_wait_percent from (

SELECT SUM(time_waited) w1 FROM v$system_event WHERE event NOT IN

('smon timer','pmon timer','rdbms ipc message','Null event','parallel query dequeue','pipe

get','client message','SQL*Net message to client','SQL*Net message from client','SQL*Net more

data from client','dispatcher timer','virtual circuit status','lock manager wait for remote

message','PX Idle Wait','PX Deq: Execution Msg','PX Deq: Table Q Normal','wakeup time

manager','slave wait',

'i/o slave wait','jobq slave wait','null event','gcs remote message','gcs for action','ges remote

message','queue messages','wait for unread message on broadcast channel','PX Deq Credit: send

blkd','PX Deq: Execute Reply','PX Deq: Signal ACK','PX Deque wait','PX Deq Credit: need

buffer','STREAMS apply coord waiting for slave message',

'STREAMS apply slave waiting for coord message', 'Queue Monitor Wait', 'Queue Monitor

Slave Wait', 'wakeup event for builder', 'wakeup event for preparer', 'wakeup event for reader',

'wait for activate message', 'PX Deq: Par Recov Execute','PX Deq: Table Q

Sample','STREAMS apply slave idle wait','STREAcapture process filter callback wait for

ruleset','STREAMS fetch slave waiting for txns',

of 9

免费下载

巡检

相关文档

评论