55 lines
1.4 KiB
SQL
55 lines
1.4 KiB
SQL
/*
|
|
|
|
Output looks like
|
|
|
|
SQL_ID COUNT(*) MX AV MIN
|
|
------------- ---------- ------- --------- ----------
|
|
0fvrpk7476b7y 26 3068 133.1 0
|
|
1pjp66rxcj6tg 15 3106 767.7 57
|
|
8r5wuxk1dprhr 39 3510 841.0 24
|
|
0w5uu5kngyyty 21 3652 442.3 0
|
|
0hbv80w9ypy0n 161 4089 1183.9 0
|
|
71fwb4n6a92fv 49 4481 676.9 30
|
|
0bujgc94rg3fj 604 4929 24.7 0
|
|
64dqhdkkw63fd 1083 7147 7.2 0
|
|
990m08w8xav7s 591 7681 51.8 0
|
|
2n5369dsuvn5a 16 10472 5726.8 303
|
|
2spgk3k0f7quz 251 29607 546.1 0
|
|
36pd759xym9tc 12 37934 23861.9 1391
|
|
497wh6n7hu14f 49 69438 5498.2 0
|
|
|
|
*/
|
|
|
|
col mx for 999999
|
|
col mn for 999999
|
|
col av for 999999.9
|
|
|
|
select
|
|
sql_id,
|
|
count(*),
|
|
max(tm) mx,
|
|
avg(tm) av,
|
|
min(tm) min
|
|
from (
|
|
select
|
|
sql_id,
|
|
sql_exec_id,
|
|
max(tm) tm
|
|
from ( select
|
|
sql_id,
|
|
sql_exec_id,
|
|
((cast(sample_time as date)) -
|
|
(cast(sql_exec_start as date))) * (3600*24) tm
|
|
from
|
|
dba_hist_active_sess_history
|
|
where sql_exec_id is not null
|
|
)
|
|
group by sql_id,sql_exec_id
|
|
)
|
|
group by sql_id
|
|
having count(*) > 10
|
|
order by mx,av
|
|
/
|
|
|
|
|