Friday, August 9, 2019

How to find the number of executions for a specific SQL ID during a day



This query will gather data from DBA_HIST_ACTIVE_SESS_HISTORY for a specific day, and count the number of executions within that day:
select to_char(sample_time,'dd.mm.yyyy hh24') "day", count(*) "num executions"
from DBA_HIST_ACTIVE_SESS_HISTORY 
where sql_id = '7x0v1s9aq4y9t'
and to_date(to_char(sample_time,'dd.mm.yyyy')) = '05.08.2019'
group by to_char(sample_time,'dd.mm.yyyy hh24')
order by 1 desc;

day num executions
05.08.2019 23 302
05.08.2019 22 285
05.08.2019 21 333
05.08.2019 20 300
05.08.2019 19 393
05.08.2019 18 255
05.08.2019 17 351
05.08.2019 16 426
05.08.2019 15 450
05.08.2019 14 624
05.08.2019 13 842
05.08.2019 12 621
05.08.2019 11 503
05.08.2019 10 461
05.08.2019 09 444
05.08.2019 08 279
05.08.2019 07 148
05.08.2019 06 79
05.08.2019 05 77
05.08.2019 04 80
05.08.2019 03 3396
05.08.2019 02 3680
05.08.2019 01 2808
05.08.2019 00 123

No comments:

Post a Comment