50 lines
1.2 KiB
Plaintext
50 lines
1.2 KiB
Plaintext
set pages 999
|
|
set linesize 200
|
|
|
|
column nbr_exec format 99G999G999
|
|
column ela_sec format 999G999G999
|
|
column dreads format 999G999G999
|
|
column bgets format 999G999G999
|
|
column avg_ela_sec format 9G999G999D99
|
|
column avg_dreads format 99G999G999
|
|
column avg_bgets format 99G999G999
|
|
|
|
with exec_stats as
|
|
( select
|
|
sqlstat.sql_id,
|
|
sqlstat.plan_hash_value phash_value,
|
|
min(sqlstat.snap_id) min_snap_id,
|
|
max(sqlstat.snap_id) max_snap_id,
|
|
sum(sqlstat.executions_delta) nbr_exec,
|
|
sum(sqlstat.elapsed_time_delta)/1000000 ela_sec,
|
|
sum(sqlstat.disk_reads_delta) dreads,
|
|
sum(sqlstat.buffer_gets_delta) bgets
|
|
from
|
|
dba_hist_sqlstat sqlstat
|
|
where
|
|
sql_id = '&sql_id'
|
|
group by
|
|
sql_id,
|
|
plan_hash_value
|
|
)
|
|
select
|
|
to_char(snap1.begin_interval_time, 'DD/MM/YYYY HH24:MI') earliest_occur,
|
|
to_char(snap2.end_interval_time, 'DD/MM/YYYY HH24:MI') latest_occur,
|
|
sql_id,
|
|
phash_value,
|
|
nbr_exec,
|
|
ela_sec,
|
|
dreads,
|
|
bgets,
|
|
(ela_sec/nbr_exec) avg_ela_sec,
|
|
(dreads/nbr_exec) avg_dreads,
|
|
(bgets/nbr_exec) avg_bgets
|
|
from
|
|
exec_stats,
|
|
dba_hist_snapshot snap1,
|
|
dba_hist_snapshot snap2
|
|
where
|
|
exec_stats.min_snap_id = snap1.snap_id
|
|
and exec_stats.max_snap_id = snap2.snap_id
|
|
;
|
|
|