Oracle ログスイッチ状況確認


## 内容
ログスイッチの発生状況を「日別」「時間別」「日時別」「曜日別」に確認するSQL。


## 日別
▼ SQL

```sql:
select 
    first_date, count(*) cnt 
from (
    select to_char(first_time,'yyyy/mm/dd') first_date 
    from v$log_history )
group by first_date 
order by first_date;

```
▼ 結果

```bash:
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

```sql:
select 
    first_hh, count(*) cnt 
from (
    select to_char(first_time,'hh24') first_hh 
    from v$log_history)
group by first_hh 
order by first_hh;

```

▼ 結果

```bash:
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

```sql:
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;

```

▼ 結果

```bash:
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

```sql:
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;

```

▼ 結果

```bash:
Sunday Monday Tuesday Wednesday Thursday Friday Saturday
------ ------ ------- --------- -------- ------ --------
     0      0       0        75        0      1        0

```