OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.15.0.0
방법 : 오라클 19c 기간별 세션, 프로세스 현황 확인 쿼리
일반적으로 현재 세션 갯수를 확인하고 싶을때는 v$session의 status를 count group by해서 세션을 확인할 수 있음
하지만 과거의 active 세션이 몇개였는지 확인하고 싶은 경우가 있을수 있음
일반적으로 awr(dba_hist 뷰)에 과거 데이터들이 들어있는데,
dba_hist_active_sess_history에서는 그런 정보를 확인할수가 없는것으로 보임
구글링을 하다가 찾은 뷰인데 각각 dba_hist_sysmetric_summary 뷰와 dba_hist_resource_limit 뷰를 이용해서 세션의 기록들을 확인할 수 있음
먼저 dba_hist_sysmetric_summary 뷰를 이용해 일별 또는 시간대별 세션 평균, 최대 접속 량을 확인할 수 있음
with 절에 있는 begin_time을 조절해 필요한 기간 만큼 확인가능
(원본 쿼리는 dba_hist_sysmetric_summary 를 여러번 조회해서 너무 느리고 부하가 있었어서 with절로 변경함)
|
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
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
|
SQL>
with dba_hist_sys_summ as
(
select SNAP_ID, DBID, INSTANCE_NUMBER, BEGIN_TIME, END_TIME, METRIC_ID, METRIC_NAME, AVERAGE, MAXVAL, METRIC_UNIT
from dba_hist_sysmetric_summary
where METRIC_ID between 2137 and 2148 and begin_time > sysdate-3 and instance_number = 1
)
SELECT
AVG_SES_COUNT.SNAP_ID, AVG_SES_COUNT.DBID, AVG_SES_COUNT.INSTANCE_NUMBER,
to_char(AVG_SES_COUNT.BEGIN_TIME, 'yyyy/mm/dd hh24') "BEGIN_TIME", to_char(AVG_SES_COUNT.END_TIME, 'yyyy/mm/dd hh24') "END_TIME",
--AVG_SES_COUNT.METRIC_NAME,
AVG_SES_COUNT.AVERAGE AVG_TOTAL_SESSIONS, AVG_SES_COUNT.MAXVAL MAX_TOTAL_SESSIONS,
--
ROUND(AVG_ACT_SES.AVERAGE,2) AVG_ACTIVE_SESSIONS, ROUND(AVG_ACT_SES.MAXVAL,2) MAX_ACTIVE_SESSIONS,
--
AVG_SERIAL_SESSIONS.AVERAGE AVG_SERIAL_SESSIONS, AVG_SERIAL_SESSIONS.MAXVAL MAX_SERIAL_SESSIONS,
--
AVG_PARALLEL_SESSIONS.AVERAGE AVG_PARALLEL_SESSIONS, AVG_PARALLEL_SESSIONS.MAXVAL MAX_PARALLEL_SESSIONS,
--
AVG_PQ_SESSIONS.AVERAGE AVG_PQ_SESSIONS, AVG_PQ_SESSIONS.MAXVAL MAX_PQ_SESSIONS,
--
AVG_PQ_SLV_SESSIONS.AVERAGE AVG_PQ_SLAVE_SESSIONS, AVG_PQ_SLV_SESSIONS.MAXVAL MAX_PQ_SLAVE_SESSIONS
FROM
(select SNAP_ID, DBID, INSTANCE_NUMBER, BEGIN_TIME, END_TIME, METRIC_NAME, AVERAGE, MAXVAL, METRIC_UNIT
from dba_hist_sys_summ
where METRIC_ID=2143
order by INSTANCE_NUMBER,SNAP_ID ) AVG_SES_COUNT,
(select SNAP_ID, DBID, INSTANCE_NUMBER, BEGIN_TIME, END_TIME, METRIC_NAME, AVERAGE, MAXVAL, METRIC_UNIT
from dba_hist_sys_summ
where METRIC_ID=2147
order by INSTANCE_NUMBER,SNAP_ID ) AVG_ACT_SES,
(select SNAP_ID, DBID, INSTANCE_NUMBER, BEGIN_TIME, END_TIME, METRIC_NAME, AVERAGE, MAXVAL, METRIC_UNIT
from dba_hist_sys_summ
where METRIC_ID=2148
order by INSTANCE_NUMBER,SNAP_ID ) AVG_SERIAL_SESSIONS,
(select SNAP_ID, DBID, INSTANCE_NUMBER, BEGIN_TIME, END_TIME, METRIC_NAME, AVERAGE, MAXVAL, METRIC_UNIT
from dba_hist_sys_summ
where METRIC_ID=2148
order by INSTANCE_NUMBER,SNAP_ID ) AVG_PARALLEL_SESSIONS,
(select SNAP_ID, DBID, INSTANCE_NUMBER, BEGIN_TIME, END_TIME, METRIC_NAME, AVERAGE, MAXVAL, METRIC_UNIT
from dba_hist_sys_summ
where METRIC_ID=2137
order by INSTANCE_NUMBER,SNAP_ID ) AVG_PQ_SESSIONS,
(select SNAP_ID, DBID, INSTANCE_NUMBER, BEGIN_TIME, END_TIME, METRIC_NAME, AVERAGE, MAXVAL, METRIC_UNIT
from dba_hist_sys_summ
where METRIC_ID=2138
order by INSTANCE_NUMBER,SNAP_ID ) AVG_PQ_SLV_SESSIONS
WHERE 1=1
AND AVG_SES_COUNT.SNAP_ID = AVG_ACT_SES.SNAP_ID (+)
AND AVG_SES_COUNT.DBID = AVG_ACT_SES.DBID (+)
AND AVG_SES_COUNT.INSTANCE_NUMBER = AVG_ACT_SES.INSTANCE_NUMBER (+)
--
AND AVG_SES_COUNT.SNAP_ID = AVG_SERIAL_SESSIONS.SNAP_ID (+)
AND AVG_SES_COUNT.DBID = AVG_SERIAL_SESSIONS.DBID (+)
AND AVG_SES_COUNT.INSTANCE_NUMBER = AVG_SERIAL_SESSIONS.INSTANCE_NUMBER (+)
--
AND AVG_SES_COUNT.SNAP_ID = AVG_PARALLEL_SESSIONS.SNAP_ID (+)
AND AVG_SES_COUNT.DBID = AVG_PARALLEL_SESSIONS.DBID (+)
AND AVG_SES_COUNT.INSTANCE_NUMBER = AVG_PARALLEL_SESSIONS.INSTANCE_NUMBER (+)
--
AND AVG_SES_COUNT.SNAP_ID = AVG_PQ_SESSIONS.SNAP_ID (+)
AND AVG_SES_COUNT.DBID = AVG_PQ_SESSIONS.DBID (+)
AND AVG_SES_COUNT.INSTANCE_NUMBER = AVG_PQ_SESSIONS.INSTANCE_NUMBER (+)
--
AND AVG_SES_COUNT.SNAP_ID = AVG_PQ_SLV_SESSIONS.SNAP_ID (+)
AND AVG_SES_COUNT.DBID = AVG_PQ_SLV_SESSIONS.DBID (+)
AND AVG_SES_COUNT.INSTANCE_NUMBER = AVG_PQ_SLV_SESSIONS.INSTANCE_NUMBER (+)
order by INSTANCE_NUMBER,SNAP_ID;
SNAP_ID DBID INSTANCE_NUMBER BEGIN_TIM END_TIME AVG_TOTAL_SESSIONS MAX_TOTAL_SESSIONS AVG_ACTIVE_SESSIONS MAX_ACTIVE_SESSIONS AVG_SERIAL_SESSIONS MAX_SERIAL_SESSIONS AVG_PARALLEL_SESSIONS MAX_PARALLEL_SESSIONS AVG_PQ_SESSIONS MAX_PQ_SESSIONS AVG_PQ_SLAVE_SESSIONS MAX_PQ_SLAVE_SESSIONS
---------- ---------- --------------- --------- --------- ------------------ ------------------ ------------------- ------------------- ------------------- ------------------- --------------------- --------------------- --------------- --------------- --------------------- ---------------------
3063 2425221903 1 15-AUG-23 15-AUG-23 129.066667 131 .14 1.01 2.03333333 3 2.03333333 3 0 0 0 0
3064 2425221903 1 15-AUG-23 15-AUG-23 129.066667 131 0 0 1.95 3 1.95 3 0 0 0 0
3065 2425221903 1 15-AUG-23 15-AUG-23 129.065574 131 0 0 1.24590164 4 1.24590164 4 0 0 0 0
3066 2425221903 1 15-AUG-23 15-AUG-23 127.5 129 0 0 1.83333333 3 1.83333333 3 0 0 0 0
3067 2425221903 1 15-AUG-23 15-AUG-23 129.067797 131 0 0 1.86440678 3 1.86440678 3 0 0 0 0
3068 2425221903 1 15-AUG-23 15-AUG-23 129.216667 133 .08 1.63 1.05 2 1.05 2 0 0 0 0
3069 2425221903 1 15-AUG-23 15-AUG-23 129.066667 131 0 .01 1.03333333 2 1.03333333 2 0 0 0 0
3070 2425221903 1 15-AUG-23 16-AUG-23 129.066667 131 0 .14 1 1 1 1 0 0 0 0
3071 2425221903 1 16-AUG-23 16-AUG-23 129.065574 131 0 .03 1.03278689 2 1.03278689 2 0 0 0 0
3072 2425221903 1 16-AUG-23 16-AUG-23 129.066667 131 0 .09 1.03333333 2 1.03333333 2 0 0 0 0
3073 2425221903 1 16-AUG-23 16-AUG-23 129.067797 131 0 0 1 1 1 1 0 0 0 0
3074 2425221903 1 16-AUG-23 16-AUG-23 129.066667 131 0 0 1.01666667 2 1.01666667 2 0 0 0 0
3075 2425221903 1 16-AUG-23 16-AUG-23 129.066667 131 0 0 1.05 2 1.05 2 0 0 0 0
3076 2425221903 1 16-AUG-23 16-AUG-23 129.066667 131 0 0 1.03333333 2 1.03333333 2 0 0 0 0
3077 2425221903 1 16-AUG-23 16-AUG-23 129.065574 131 0 .01 1.04918033 2 1.04918033 2 0 0 0 0
3078 2425221903 1 16-AUG-23 16-AUG-23 129.067797 131 0 0 1.01694915 2 1.01694915 2 0 0 0 0
3079 2425221903 1 16-AUG-23 16-AUG-23 129.05 131 0 0 1.01666667 2 1.01666667 2 0 0 0 0
3080 2425221903 1 16-AUG-23 16-AUG-23 129.066667 131 0 0 1.03333333 2 1.03333333 2 0 0 0 0
3081 2425221903 1 16-AUG-23 16-AUG-23 129.066667 131 0 0 1 1 1 1 0 0 0 0
3082 2425221903 1 16-AUG-23 16-AUG-23 129.066667 131 0 0 1.66666667 2 1.66666667 2 0 0 0 0
3083 2425221903 1 16-AUG-23 16-AUG-23 129.065574 131 0 .05 2.36065574 28 2.36065574 28 0 0 0 0
3084 2425221903 1 16-AUG-23 16-AUG-23 129.067797 131 0 0 1.28813559 2 1.28813559 2 0 0 0 0
3085 2425221903 1 16-AUG-23 16-AUG-23 129.066667 131 0 .08 1.03333333 2 1.03333333 2 0 0 0 0
23 rows selected.
|
일별 또는 시간대별 세션 평균, 최대 접속 량을 확인할 수 있음
dba_hist_resource_limit 를 이용해 sessions나 process 기록을 확인할 수 있음
먼저 sessions 기록 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL>
set lines 200 pages 1000
col resource_name for a10
select rl.snap_id, to_char(s.begin_interval_time, 'yyyy/mm/dd hh24:mi') "begin_interval_time"
, to_char(s.end_interval_time, 'yyyy/mm/dd hh24:mi') "end_interval_time"
,rl.instance_number, rl.resource_name, rl.current_utilization, rl.max_utilization
from dba_hist_resource_limit rl, dba_hist_snapshot s
where s.snap_id = rl.snap_id and rl.resource_name = 'sessions'
and s.begin_interval_time>=to_date('20230801','yyyymmdd')
and s.end_interval_time<=to_date('20230802','yyyymmdd')
order by s.begin_interval_time, rl.instance_number;
SNAP_ID begin_interval_t end_interval_tim INSTANCE_NUMBER RESOURCE_N CURRENT_UTILIZATION MAX_UTILIZATION
---------- ---------------- ---------------- --------------- ---------- ------------------- ---------------
91 2023/08/01 00:00 2023/08/01 01:00 1 sessions 37 41
92 2023/08/01 01:00 2023/08/01 02:00 1 sessions 35 41
93 2023/08/01 02:00 2023/08/01 03:00 1 sessions 35 41
94 2023/08/01 03:00 2023/08/01 04:00 1 sessions 35 41
95 2023/08/01 04:00 2023/08/01 05:00 1 sessions 35 41
96 2023/08/01 05:00 2023/08/01 06:00 1 sessions 35 41
6 rows selected.
|
process 기록 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL>
set lines 200 pages 1000
col resource_name for a10
select rl.snap_id, to_char(s.begin_interval_time, 'yyyy/mm/dd hh24:mi') "begin_interval_time"
, to_char(s.end_interval_time, 'yyyy/mm/dd hh24:mi') "end_interval_time"
, rl.instance_number, rl.resource_name, rl.current_utilization,
rl.max_utilization
from dba_hist_resource_limit rl, dba_hist_snapshot s
where s.snap_id = rl.snap_id and rl.resource_name = 'processes'
and s.begin_interval_time>=to_date('20230801','yyyymmdd')
and s.end_interval_time<=to_date('20230802','yyyymmdd')
order by s.begin_interval_time, rl.instance_number;
SNAP_ID begin_interval_t end_interval_tim INSTANCE_NUMBER RESOURCE_N CURRENT_UTILIZATION MAX_UTILIZATION
---------- ---------------- ---------------- --------------- ---------- ------------------- ---------------
91 2023/08/01 00:00 2023/08/01 01:00 1 processes 31 35
92 2023/08/01 01:00 2023/08/01 02:00 1 processes 30 35
93 2023/08/01 02:00 2023/08/01 03:00 1 processes 30 35
94 2023/08/01 03:00 2023/08/01 04:00 1 processes 30 35
95 2023/08/01 04:00 2023/08/01 05:00 1 processes 30 35
96 2023/08/01 05:00 2023/08/01 06:00 1 processes 30 35
6 rows selected.
|
위 쿼리 이외에 더 좋거나 간단한 다른 쿼리가 있다면 의견 부탁드립니다.
참조 : https://dba.stackexchange.com/questions/62982/oracle-historical-session-information
https://www.nazmulhuda.info/session-or-process-count-from-history
'ORACLE > Sql' 카테고리의 다른 글
| 오라클 19c 최근 ash event 확인 쿼리 (0) | 2024.01.19 |
|---|---|
| 오라클 19c 서버 기동시 db 자동실행 설정 스크립트 (0) | 2023.12.19 |
| 오라클 19c v$로 시작하는 뷰 원본 쿼리 확인 (0) | 2023.07.13 |
| 오라클 19c log file sync 발생 시 정보 수집 쿼리 (2) | 2023.03.06 |
| 오라클 19c 일별, 시간별 아카이브 갯수 및 용량 확인 (0) | 2022.08.08 |