v$log_history视图包含有关应用程序用户如何使用数据库的重要信息，该视图可以帮助您定义数据库中活动最多的时间段。
v$log_history查询
您可以使查询适应您的需求，只需更改日期格式的方式即可深入到所需的精度。
select round(avg(LOG_SWITCHES)) LOG_SWITCHES, DAY
from (
select to_char(trunc(first_time), 'Day') DAY, TRUNC(FIRST_TIME, 'DDD'), count(*) LOG_SWITCHES
from v$log_history
group by TRUNC(FIRST_TIME, 'DDD'), to_char(trunc(first_time), 'Day')
order by 2
)
group by day;
这给出了以下输出：
LOG_SWITCHES DAY
------------ ---------
207 Sunday
212 Monday
218 Friday
192 Thursday
207 Wednesday
216 Tuesday
209 Saturday
您可以找出星期几最活跃。您还可以对上个月进行日常分析：
select round(avg(LOG_SWITCHES)) LOG_SWITCHES, DAY
from (
select TRUNC(FIRST_TIME, 'DDD') DAY, count(*) LOG_SWITCHES
from v$log_history
where first_time between sysdate -30 and sysdate
group by TRUNC(FIRST_TIME, 'DDD'), to_char(trunc(first_time), 'Day')
order by 1
)
group by day
order by 2;
这是输出：
LOG_SWITCHES DAY
------------ ---------
91 10-AUG-15
225 11-AUG-15
233 12-AUG-15
224 13-AUG-15
221 14-AUG-15
218 15-AUG-15
217 16-AUG-15
225 17-AUG-15
218 18-AUG-15
215 19-AUG-15
212 20-AUG-15
203 21-AUG-15
198 22-AUG-15
198 23-AUG-15
200 24-AUG-15
201 25-AUG-15
202 26-AUG-15
202 27-AUG-15
190 28-AUG-15
150 29-AUG-15
151 30-AUG-15
153 31-AUG-15
182 01-SEP-15
202 02-SEP-15
201 03-SEP-15
203 04-SEP-15
203 05-SEP-15
199 06-SEP-15
202 07-SEP-15
205 08-SEP-15
124 09-SEP-15
您还可以向下钻取一天中的几个小时：
select Hour , round(avg(LOG_SWITCHES)) LOG_SWITCHES
from (
select to_char(trunc(first_time, 'HH'),'HH24') Hour, TRUNC(FIRST_TIME, 'DDD'), count(*) LOG_SWITCHES
from v$log_history
group by TRUNC(FIRST_TIME, 'DDD'), trunc(first_time, 'HH')
order by 1
)
group by Hour
order by Hour;
这里的输出：
HO LOG_SWITCHES
-- ------------
00 8
01 9
02 8
03 8
04 25
05 7
06 8
07 8
08 8
09 8
10 8
11 8
12 8
13 8
14 8
15 8
16 8
17 8
18 8
19 7
20 8
21 7
22 8
23 15
1320
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
