查出使用cpu最高的sql 和各种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 = '232279'))
ORDER BY piece ASC

或者也可以通过一下查询

select t3.SQL_TEXT

from v$process t1

inner join v$session t2

on t1.ADDR = t2.PADDR

inner join v$sql t3

on t2.SQL_ID = t3.SQL_ID

where t1.SPID = 172928（这个pid就是进程id）;

 

 

 

列出使用频率最高的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;