首先用操作系统命令找出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