希望能对大家有所帮助!
很多时候大家想查看oracle数据库中的sql语句执行情况,但是又不知道如何是好,今天在这里为大家提供一个sql语句,大家可以通过以下的sql语句查询Oracle数据库中Top Sql情况:
查询结果可按照PCT、总耗费时间、CPU使用时间、平均执行时间、执行次数、关联行数等排序。
TOP sql语句如下:
select round(100 * a.pct, 2) pct,
round(a.elapsed_time/1000000, 2) elapsed_time,
round(a.elapsed_time/a.executions/1000) ms_by_exec,
round(a.cpu_time/1000000, 2) cpu_time,
a.buffer_gets total_cost,
round(a.buffer_gets/a.executions) elem_cost,
a.executions exec,
a.rows_processed nb_rows,
s.sql_text
from (select *
from (select elapsed_time,
ratio_to_report(elapsed_time) over () pct,
cpu_time,
buffer_gets,
executions,
rows_processed,
address,
hash_value
from v$sql
order by elapsed_time desc)
where rownum < 26) a,
v$sqlarea s
where a.address = s.address
and a.hash_value = s.hash_value
and a.executions <> 0
order by pct desc, cpu_time desc
session相关:
select sesion.sid,
username,
osuser,
machine,
sesion.module,
status,
optimizer_mode,
sql_text
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value(+)
and sesion.sql_address = sqlarea.address(+)
and sesion.username is not null
order by username, sql_text
locks相关:
select
username,
osuser,
machine,
s.module,
l.sid,
decode(l.type,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', '