Oracle数据库经常会遇到CPU利用率很高的情况
Oracle数据库经常会遇到CPU利用率很高的情况
独钓渔 发表于3个月前
Oracle数据库经常会遇到CPU利用率很高的情况
  • 发表于 3个月前
  • 阅读 15
  • 收藏 0
  • 点赞 0
  • 评论 0

腾讯云 技术升级10大核心产品年终让利>>>   

 

根据占用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:
select disk_reads,sql_text
from (select sql_text,disk_reads,
   dense_rank() over
     (order by disk_reads desc) disk_reads_rank
   from v$sql)
where disk_reads_rank <=5;

 

--找出需要大量缓冲读取(逻辑读)操作的查询:
select buffer_gets,sql_text
from (select sql_text,buffer_gets,
   dense_rank() over
     (order by buffer_gets desc) buffer_gets_rank
   from v$sql)
where buffer_gets_rank<=5;

 

共有 人打赏支持
粉丝 43
博文 358
码字总数 131263
×
独钓渔
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: