Oracle数据库经常会遇到CPU利用率很高的情况怎么解决？

根据占用cpu高的进程号来查询者个进程执行的sql语句：

select sql_text 
from v$sqltext a 
where (a.hash_value, a.address) 
in (select decode(sql_hash_value, 0, prev_hash_value, sql_hash_value), 
decode(sql_hash_value, 0, prev_sql_addr, sql_address) 
from v$session b 
where b.paddr = (select addr from v$process c where c.spid = '&pid')) 
order by piece asc;

例：查询31968进程对应的sql语言：

select sql_text 
from v$sqltext a 
where (a.hash_value, a.address) 
in (select decode(sql_hash_value, 0, prev_hash_value, sql_hash_value), 
decode(sql_hash_value, 0, prev_sql_addr, sql_address) 
from v$session b 
where b.paddr = (select addr from v$process c where c.spid = '31968')) 
order by piece asc;

通过pid查看程序执行对应的sql:

select sql_text 
from v$process pr,v$session ss,v$sqlarea sl 
where pr.addr=ss.paddr  
and ss.sql_hash_value=sl.hash_value 
and pr.spid=10840;

查看当前正在执行的sql：

select a.program, b.spid, c.sql_text,c.sql_id 
from v$session a, v$process b, v$sqlarea c 
where a.paddr = b.addr 
and a.sql_hash_value = c.hash_value 
and a.username is not null;

1，查看cpu占用高的进程号

2，根据进程号查看该进程在做什么

select sql_text,spid,v$session.program,process 
from v$sqlarea,v$session,v$process 
where v$sqlarea.address=v$session.sql_address 
and v$sqlarea.hash_value=v$session.sql_hash_value 
and v$session.paddr=v$process.addr 
and v$process.spid in(pid);

3，看看的等待事件都有些什么

select sid,event,p1,p1text from v$session_wait;

看看等待事件由什么进程造成的

select spid from v$process where addr in(select paddr from v$session where sid in(84,102,101));

利用一下脚本可以由已知session的sid来获得sql语句

select sql_text 
from v$sql_text a 
where a.hash_value=(
  select sql_hash_value 
  from v$session b 
  where b.sid=’&sid’) 
order by piect asc;

查看当前会话sql_id

select sql_id ,username,status,event from v$session;

根据sql_id查看sql语句

select sql_text from v$sql where sql_id='cx7sxk891r782';

oracle查询当前执行效率低的sql

--cpu高的sql
select sql_text from v$sql order by cpu_time desc
--逻辑读多的sql：
select * from (select buffer_gets, sql_text
from v$sqlarea
where buffer_gets > 500000
order by buffer_gets desc) where rownum<=30;
--执行次数多的sql ：
select sql_text,executions from
(select sql_text,executions from v$sqlarea order by executions desc)
where rownum<81;
--读硬盘多的sql ：
select sql_text,disk_reads from
(select sql_text,disk_reads from v$sqlarea order by disk_reads desc)
where rownum<21;

select * 
from (
  select sql_text,sql_id,cpu_time 
  from v$sql 
  order by cpu_time desc) 
where ownum<=10 
order by rownum asc;

select * 
from (
  select sql_text,sql_id,cpu_time 
  from v$sqlarea 
  order by cpu_time desc) 
where rownum<=10 
order by rownum asc;

--这2个语句效果基本一样，一个从v$sql视图查询一个从v$sqlarea视图查询。


--列出使用频率最高的5个查询：
select sql_text,executions
from (select sql_text,executions,
   rank() over
    (order by executions desc) exec_rank
   from v$sql)
where exec_rank <=5;



--消耗磁盘读取最多的sql top5：

Oracle数据库经常会遇到CPU利用率很高的情况怎么解决？

相关推荐