sum(less5) "Log Switch interval <= 5 min",
sum(bet5_10) "5 ~ 10 min",
sum(bet10_30) "10 ~ 30 min",
sum(over30) "> 30 min",
count(*) "7-day Total"
from (
select
thread#,
case when (first_time-lag(first_time) over (partition by thread# order by first_time))*24*60 <= 5 then 1
when (first_time-lag(first_time) over (partition by thread# order by first_time)) is null then null else 0 end less5,
case when (first_time-lag(first_time) over (partition by thread# order by first_time))*24*60 > 5
and (first_time-lag(first_time) over (partition by thread# order by first_time))*24*60 <= 10 then 1 else 0 end bet5_10,
case when (first_time-lag(first_time) over (partition by thread# order by first_time))*24*60 > 10
and (first_time-lag(first_time) over (partition by thread# order by first_time))*24*60 <= 30 then 1 else 0 end bet10_30,
case when (first_time-lag(first_time) over (partition by thread# order by first_time))*24*60 > 30 then 1 else 0 end over30
from v$log_history
where first_time >= trunc(sysdate-7)
)
where less5 is not null
group by thread#
order by thread#;
THREAD# Log Switch interval <= 5 min 5 ~ 10 min 10 ~ 30 min > 30 min 7-day Total
---------- ---------------------------- ---------- ----------- ---------- -----------
1 892 42 83 99 1116
2 141 118 53 92 404
3 139 117 56 92 404
