oracle故障快速定位

首先用操作系统命令找出CPU利用率较高的进程ID

1.查看数据库当前执行的所有SQL和对应的进程ID

column machine format a16;
column program format a16;
select p.spid,s.status,s.machine,p.program,q.sql_text
from v$session s,v$sqlarea q,v$process p
where s.sql_address=q.address and s.sql_hash_value=q.hash_value
and s.paddr=p.addr
order by 5;

 

2.查看当前被锁的表和对应的进程ID---------

column object_name format a20;
column program format a30;
column username format a18;
column machine format a16;
select o.object_name, p.spid,l.session_id, s.serial#, s.program,
s.username, s.machine,s.seconds_in_wait
from v$locked_object l, all_objects o, v$session s,v$process p
where o.object_id = l.object_id   and s.sid = l.session_id 
and s.paddr=p.addr and s.status='ACTIVE' 
order by 1 desc;

 查看长时间操作的SQL

column target_desc format a15;
column message format a50;
column opname format a12;
select sid, opname, target_desc,sofar, totalwork, trunc(sofar/totalwork*100,2) || '%' as perwork,
to_char(start_time,'DD HH24:MI:SS') starttime,elapsed_seconds,message
from v$session_longops order by 8

 

-----Top Event------
select sid,state,wait_class,event from v$session_wait;

-----历史分析---------- v$active_session_history,DBA_HIST_ACTIVE_SESS_HISTORY
select to_char(sample_time,'DDHH24MI'),count(*) from
DBA_HIST_ACTIVE_SESS_HISTORY
where sample_time>sysdate-1
group by to_char(sample_time,'DDHH24MI')
order by 1

历史不同时段的共同sql

set pagesize 0
column sql_id format a14;
column sql_text format a50;
select distinct d.sql_id,q.sql_text
from DBA_HIST_ACTIVE_SESS_HISTORY d,v$sqlarea q
where sample_time between to_date('02131558','MMDDHH24MI')  and to_date('02131559','MMDDHH24MI')
and d.sql_id=q.sql_id 
intersect
select distinct d.sql_id,q.sql_text
from DBA_HIST_ACTIVE_SESS_HISTORY d,v$sqlarea q
where sample_time between to_date('02131551','MMDDHH24MI')  and to_date('02131552','MMDDHH24MI')
and d.sql_id=q.sql_id