OS 환경 : Oracle Linux 6.8 (64bit)
DB 환경 : Oracle Database 11.2.0.4
방법 : 로그 스위치가 시간대별로 몇번 이루어졌는지 확인하는 쿼리
첫번째부터 하루간 총 횟수, 0시 1시 2시 --- 21시 22시 23시 순 개별값 및 합계, 최고값 조회
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
|
SQL>
SELECT TO_CHAR(first_time,'yyyy/mm/dd') AS day
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'00',1,0)),'99') AS "00"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'01',1,0)),'99') AS "01"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'02',1,0)),'99') AS "02"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'03',1,0)),'99') AS "03"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'04',1,0)),'99') AS "04"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'05',1,0)),'99') AS "05"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'06',1,0)),'99') AS "06"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'07',1,0)),'99') AS "07"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'08',1,0)),'99') AS "08"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'09',1,0)),'99') AS "09"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'10',1,0)),'99') AS "10"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'11',1,0)),'99') AS "11"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'12',1,0)),'99') AS "12"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'13',1,0)),'99') AS "13"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'14',1,0)),'99') AS "14"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'15',1,0)),'99') AS "15"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'16',1,0)),'99') AS "16"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'17',1,0)),'99') AS "17"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'18',1,0)),'99') AS "18"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'19',1,0)),'99') AS "19"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'20',1,0)),'99') AS "20"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'21',1,0)),'99') AS "21"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'22',1,0)),'99') AS "22"
,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'23',1,0)),'99') AS "23"
,COUNT(*) AS "Day Sum"
,GREATEST(
SUM(DECODE(TO_CHAR(first_time,'HH24'),'00',1,0)),
SUM(DECODE(TO_CHAR(first_time,'HH24'),'01',1,0)),
SUM(DECODE(TO_CHAR(first_time,'HH24'),'02',1,0)),
SUM(DECODE(TO_CHAR(first_time,'HH24'),'03',1,0)),
SUM(DECODE(TO_CHAR(first_time,'HH24'),'04',1,0)),
SUM(DECODE(TO_CHAR(first_time,'HH24'),'05',1,0)),
SUM(DECODE(TO_CHAR(first_time,'HH24'),'06',1,0)),
SUM(DECODE(TO_CHAR(first_time,'HH24'),'07',1,0)),
SUM(DECODE(TO_CHAR(first_time,'HH24'),'08',1,0)),
SUM(DECODE(TO_CHAR(first_time,'HH24'),'09',1,0)),
SUM(DECODE(TO_CHAR(first_time,'HH24'),'10',1,0)),
SUM(DECODE(TO_CHAR(first_time,'HH24'),'11',1,0)),
SUM(DECODE(TO_CHAR(first_time,'HH24'),'12',1,0)),
SUM(DECODE(TO_CHAR(first_time,'HH24'),'13',1,0)),
SUM(DECODE(TO_CHAR(first_time,'HH24'),'14',1,0)),
SUM(DECODE(TO_CHAR(first_time,'HH24'),'15',1,0)),
SUM(DECODE(TO_CHAR(first_time,'HH24'),'16',1,0)),
SUM(DECODE(TO_CHAR(first_time,'HH24'),'17',1,0)),
SUM(DECODE(TO_CHAR(first_time,'HH24'),'18',1,0)),
SUM(DECODE(TO_CHAR(first_time,'HH24'),'19',1,0)),
SUM(DECODE(TO_CHAR(first_time,'HH24'),'20',1,0)),
SUM(DECODE(TO_CHAR(first_time,'HH24'),'21',1,0)),
SUM(DECODE(TO_CHAR(first_time,'HH24'),'22',1,0)),
SUM(DECODE(TO_CHAR(first_time,'HH24'),'23',1,0))
) AS "Day Max"
FROM v$log_history
WHERE 1=1
AND thread# IN ('1','2')
--AND first_time >= trunc(sysdate-30)
GROUP BY TO_CHAR(first_time,'yyyy/mm/dd')
ORDER BY day DESC;
DAY 00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 Day Sum Day Max
---------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---------- ----------
2019/01/20 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1
2019/01/19 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1
2019/01/17 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 0 0 0 0 0 0 3 2
2019/01/16 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1
2019/01/15 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 1 1
2019/01/14 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 1 1
2019/01/13 0 0 0 0 0 1 0 0 4 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 6 4
2019/01/11 0 0 0 0 0 0 0 0 0 0 2 0 0 0 0 0 0 0 0 0 0 0 1 0 3 2
2019/01/10 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 1 1
2019/01/09 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 18 0 18 18
10 rows selected.
|
GREATEST 부분 필요없으면 주석처리 해도됨
고객사에서 간단히 보는용
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL>
select to_char(first_time, 'yyyy/mm/dd hh24'), count(*)
from v$log_history
group by to_char(first_time, 'yyyy/mm/dd hh24');
TO_CHAR(FIRST COUNT(*)
------------- ----------
2019/01/17 17 2
2019/01/13 05 1
2019/01/11 10 2
2019/01/14 22 1
2019/01/09 22 18
2019/01/15 15 1
2019/01/17 01 1
2019/01/10 22 1
2019/01/11 22 1
2019/01/13 22 1
2019/01/13 08 4
2019/01/16 08 1
2019/01/20 08 1
2019/01/19 09 1
14 rows selected.
|
날짜별 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | SQL> set line 200 pages 1000 SELECT TO_CHAR(FIRST_TIME,'YY/MM/DD HH24') HOUR, COUNT(*) CNT FROM V$LOGHIST WHERE FIRST_TIME >= TRUNC(SYSDATE - 30) GROUP BY TO_CHAR(FIRST_TIME,'YY/MM/DD HH24') ORDER BY 1; HOUR CNT ----------- ---------- 20/12/15 02 4 20/12/17 22 1 20/12/19 20 1 20/12/21 07 7 20/12/23 08 1 20/12/23 19 1 20/12/23 22 1 20/12/25 22 1 20/12/28 19 1 20/12/29 05 18 10 rows selected. |
참조 :
'ORACLE > Sql' 카테고리의 다른 글
| 오라클 db 상태 확인용 쉘 스크립트 (0) | 2019.01.21 |
|---|---|
| 오라클 TableSpace 별 사용용량 구하는 SQL (3) | 2019.01.17 |
| 오라클에서 서버정보 확인하기 OS 정보 확인 쿼리 (0) | 2019.01.14 |
| 오라클 insert문 반복 방법 for문 (0) | 2019.01.08 |
| 오라클 datafile size 줄이기 (0) | 2019.01.02 |