v$相关与Oracle SQL耗时检测

v$:
oracle系统视图都是已v$开头的，具体存放数据库相关动态信息。比如说会话信息v$session,日志信息v$log

v$sqltext ---- 存储的是完整的SQL

v$sqlarea --------- 存储的SQL 和一些相关的信息，比如累计的执行次数，逻辑读，物理读等统计信息

v$sql --- 存储的是具体的SQL和执行计划相关信息实际上v$sqlarea可以看做v$sql根据sqltext等做了group by之后的信息

--查耗时长的SQL
select sql_text
from v$sqltext
where (hash_value,address)
in (select * from
(select sql_hash_value,sql_address from v$session_longops order by start_time desc)
where rownum<10)
order by hash_value,piece;

 

--查看等待的sid
select sid ,event from v$session_wait where event not like 'SQL%' and event not like 'rdbms%';

 

--知道系统的spid ，得到该进程所执行的sql
select /*+ ORDERED */ sql_text from v$sqltext a
where a.hash_value = (
select sql_hash_value from v$session b
where b.paddr = (
select addr from v$process c
where c.spid = '&spid'
)
)
order by piece asc