Ordering by "cpu_time", "disk_reads", "buffer_gets", "executions", "elapsed_time"
set linesize 200
select * from (
select sql_id , plan_hash_value,
(cpu_time/1000000) "cpu_Secs",
disk_reads "Dsk_Rds",
buffer_gets "Buf_Gets",
executions "Execs",
(elapsed_time/1000000) "Elps_Secs"
from gv$sql s
order by &orderby desc nulls last
) where rownum <=5;
Query by the sql_id and phv from gv$sql to display the sql text.
select sql_text from gv$sql where sql_id = '&sqlid' and plan_hash_value = '&phv';
The same again for only sql loaded (first load time) in the last 10 minutes.
set linesize 200
select * from (
select sql_id , plan_hash_value,
(cpu_time/1000000) "cpu_Secs",
disk_reads "Dsk_Rds",
buffer_gets "Buf_Gets",
executions "Execs",
(elapsed_time/1000000) "Elps_Secs"
from gv$sql s
where to_date(first_load_time, 'YYYY-MM-DD/HH24:MI:SS') >= sysdate - 10 / (24 * 60)
order by &orderby desc nulls last
) where rownum <=5;
******************************************
keywords: performance top sql by
******************************************
rdbms version: 11g
******************************************
No comments:
Post a Comment