Dの小部屋

忘れやすい私のメモ帳

ログスイッチの状況確認

日毎

SQL

set pages 1000
with date_history as (
  select to_char(first_time,'yyyy/mm/dd') first_date from v$log_history
)
select first_date, count(*) cnt from date_history group by first_date order by first_date;

結果

FIRST_DATE        CNT
---------- ----------
2014/05/25          3
2014/05/28          3
2014/06/03         56
2014/06/29         64
2014/06/30         45
2014/07/02         16
2014/07/03         14
2014/07/13         34
2014/07/21         57

時間毎

SQL

set pages 1000
with hh_history as (
  select to_char(first_time,'hh24') first_hh from v$log_history
)
select first_hh, count(*) cnt from hh_history group by first_hh order by first_hh;

結果

FI        CNT
-- ----------
00         11
01          1
11          1
12          7
13          2
14         12
15         18
16         20
17         18
18         18
19         60
20         70
21         24
22         18
23         12

日、時間毎

SQL

set pages 10000
set lines 1000
SET NUMFORMAT 99

with dh_history as (
  select 
    to_char(first_time,'yyyy/mm/dd') first_date, 
    decode(to_char(first_time,'hh24'),'00',1,0) "00",
    decode(to_char(first_time,'hh24'),'01',1,0) "01",
    decode(to_char(first_time,'hh24'),'02',1,0) "02",
    decode(to_char(first_time,'hh24'),'03',1,0) "03",
    decode(to_char(first_time,'hh24'),'04',1,0) "04",
    decode(to_char(first_time,'hh24'),'05',1,0) "05",
    decode(to_char(first_time,'hh24'),'06',1,0) "06",
    decode(to_char(first_time,'hh24'),'07',1,0) "07",
    decode(to_char(first_time,'hh24'),'08',1,0) "08",
    decode(to_char(first_time,'hh24'),'09',1,0) "09",
    decode(to_char(first_time,'hh24'),'10',1,0) "10",
    decode(to_char(first_time,'hh24'),'11',1,0) "11",
    decode(to_char(first_time,'hh24'),'12',1,0) "12",
    decode(to_char(first_time,'hh24'),'13',1,0) "13",
    decode(to_char(first_time,'hh24'),'14',1,0) "14",
    decode(to_char(first_time,'hh24'),'15',1,0) "15",
    decode(to_char(first_time,'hh24'),'16',1,0) "16",
    decode(to_char(first_time,'hh24'),'17',1,0) "17",
    decode(to_char(first_time,'hh24'),'18',1,0) "18",
    decode(to_char(first_time,'hh24'),'19',1,0) "19",
    decode(to_char(first_time,'hh24'),'20',1,0) "20",
    decode(to_char(first_time,'hh24'),'21',1,0) "21",
    decode(to_char(first_time,'hh24'),'22',1,0) "22",
    decode(to_char(first_time,'hh24'),'23',1,0) "23"
  from v$log_history
)
select 
  first_date, 
  sum("00") "00",
  sum("01") "01",
  sum("02") "02",
  sum("03") "03",
  sum("04") "04",
  sum("05") "05",
  sum("06") "06",
  sum("07") "07",
  sum("08") "08",
  sum("09") "09",
  sum("10") "10",
  sum("11") "11",
  sum("12") "12",
  sum("13") "13",
  sum("14") "14",
  sum("15") "15",
  sum("16") "16",
  sum("17") "17",
  sum("18") "18",
  sum("19") "19",
  sum("20") "20",
  sum("21") "21",
  sum("22") "22",
  sum("23") "23"
from dh_history group by first_date order by first_date;

結果

FIRST_DATE  00  01  02  03  04  05  06  07  08  09  10  11  12  13  14  15  16  17  18  19  20  21  22  23
---------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
2014/05/28   0   0   0   0   0   0   0   0   0   0   0   0   1   1   0   0   0   0   0   0   0   0   0   0
2014/06/03   0   0   0   0   0   0   0   0   0   0   0   0   1   0   0   0   2   0   0   0  47   6   0   0
2014/06/29   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0  41   5   6   6   6
2014/06/30   6   1   0   0   0   0   0   0   0   0   0   0   0   0   5   6   6   6   6   6   3   0   0   0
2014/07/02   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   5   6   5
2014/07/03   5   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   2   6   1   0   0
2014/07/13   0   0   0   0   0   0   0   0   0   0   0   0   0   0   2   6   6   6   6   5   3   0   0   0
2014/07/21   0   0   0   0   0   0   0   0   0   0   0   1   4   0   5   6   6   6   6   6   6   6   6   3

曜日毎

SQL

set pages 1000
set numf 9999
alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
with week_history as (
  select first_time, trim(to_char(first_time,'Day', 'NLS_DATE_LANGUAGE=english')) first_week from v$log_history  
)
select 
  sum(decode(first_week,'Sunday',1,0))    "Sunday",
  sum(decode(first_week,'Monday',1,0))    "Monday",
  sum(decode(first_week,'Tuesday',1,0))   "Tuesday",
  sum(decode(first_week,'Wednesday',1,0)) "Wednesday",
  sum(decode(first_week,'Thursday',1,0))  "Thursday",
  sum(decode(first_week,'Friday',1,0))    "Friday",
  sum(decode(first_week,'Saturday',1,0))  "Saturday"
from week_history;

結果

Sunday Monday Tuesday Wednesday Thursday Friday Saturday
------ ------ ------- --------- -------- ------ --------
    98    109      55        16       14      0        0