欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-06-17 19:28:42
根据占用cpu高的进程号来查询者个进程执行的sql语句: select sql_text from v$sqltext a where (a.hash_value, a.address)...

根据占用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: