oracle数据库占用率高,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：

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;