Oracle数据库经常会遇到CPU利用率很高的情况怎么解决?,oracle利用率
根据占用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:
本站文章为和通数据库网友分享或者投稿,欢迎任何形式的转载,但请务必注明出处.
同时文章内容如有侵犯了您的权益,请联系QQ:970679559,我们会在尽快处理。