*******************************************************************************
SQL ORDER BY TOP CPU*******************************************************************************
column sqltext format a65
set pages 60
set lines 132
set trims on
set numwidth 20
select x.*, dbms_lob.substr(d.sql_text,3990,1) sqltext
from (
select distinct sql.dbid, sql.sql_id, sum(nvl(sql.cpu_time_delta,0))
cpu_total
from dba_hist_sqlstat sql
where sql.snap_id > &begin_id
and sql.snap_id <= &end_id
group by
sql.dbid, sql.sql_id
order by 3 desc
) x,
dba_hist_sqltext d
where d.sql_id = x.sql_id
and d.dbid = x.dbid
and rownum <= 10
/
*******************************************************************************
SQL ORDERS BY TOP ELAPSED TIME
*******************************************************************************
column sqltext format a65
column "ela per exec" format 999999999.99
set pages 60
set lines 300
set trims on
set numwidth 20
select x.*, dbms_lob.substr(d.sql_text,3990,1) sqltext
from (
select distinct sql.dbid, sql.sql_id,
round(sum(nvl(sql.elapsed_time_delta,0))/(sum(decode(nvl(sql.executions_delta,0),0,1,nvl(sql.executions_delta,0)))*1000000),2) "Ela per exec",
sum(nvl(sql.executions_delta,0)) Executions, sum(nvl(sql.elapsed_time_delta,0))/1000000 "Elapsed (in secs)"
from dba_hist_sqlstat sql
where sql.snap_id > &begin_id
and sql.snap_id <= &end_id
group by
sql.dbid, sql.sql_id
order by 3 desc
) x,
dba_hist_sqltext d
where d.sql_id = x.sql_id
and d.dbid = x.dbid
and rownum <= 10
/
*******************************************************************************
SQL ORDER BY TOP EXECUTION :
*******************************************************************************
*******************************************************************************
column sqltext format a65
set pages 60
set lines 132
set trims on
set numwidth 20
select x.*, dbms_lob.substr(d.sql_text,3990,1) sqltext
from (
select distinct sql.dbid, sql.sql_id, sum(nvl(sql.executions_delta,0))
executions
from dba_hist_sqlstat sql
where sql.snap_id > &begin_id
and sql.snap_id <= &end_id
group by
sql.dbid, sql.sql_id
order by 3 desc
) x,
dba_hist_sqltext d
where d.sql_id = x.sql_id
and d.dbid = x.dbid
and rownum <= 10
/
*******************************************************************************
SQL ORDER BY TOP GETS:
*******************************************************************************
*******************************************************************************
column sqltext format a65
set pages 60
set lines 132
set trims on
set numwidth 20
select x.*, dbms_lob.substr(d.sql_text,3990,1) sqltext
from (
select distinct sql.dbid, sql.sql_id, sum(nvl(sql.buffer_gets_delta,0))
buffer_gets
from dba_hist_sqlstat sql
where sql.snap_id > &begin_id
and sql.snap_id <= &end_id
group by
sql.dbid, sql.sql_id
order by 3 desc
) x,
dba_hist_sqltext d
where d.sql_id = x.sql_id
and d.dbid = x.dbid
and rownum <= 10
/
*******************************************************************************
*******************************************************************************
SQL ORDER BY TOP READS
*******************************************************************************
*******************************************************************************
column sqltext format a65
set pages 60
set lines 132
set trims on
set numwidth 20
select x.*, dbms_lob.substr(d.sql_text,3990,1) sqltext
from (
select distinct sql.dbid, sql.sql_id, sum(nvl(sql.disk_reads_delta,0))
disks_total
from dba_hist_sqlstat sql
where sql.snap_id > &begin_id
and sql.snap_id <= &end_id
group by
sql.dbid, sql.sql_id
order by 3 desc
) x,
dba_hist_sqltext d
where d.sql_id = x.sql_id
and d.dbid = x.dbid
and rownum <= 10
/