OS 환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 : 오라클 19c 유저, 백그라운드 프로세스 pga 사용량 확인
유저 세션이나 백그라운드 프로세스 세션의 pga 사용량 확인이 가능한 쿼리임
유저 세션의 pga 사용량 확인
|
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
|
SQL>
set lines 200 pages 1000
col alme heading "Allocated MB" for 99999D9
col usme heading "Used MB" for 99999D9
col frme heading "Freeable MB" for 99999D9
col mame heading "Max MB" for 99999D9
col username for a15
col program for a22
col sid for a5
col spid for a8
select s.username, substr(s.sid,1,5) sid, p.spid
, to_char(logon_time, 'yyyy/mm/dd hh24:mi:ss') logon_time,
substr(s.program,1,22) program , s.process pid_remote,
s.status,
round(pga_used_mem/1024/1024) usme,
round(pga_alloc_mem/1024/1024) alme,
round(pga_freeable_mem/1024/1024) frme,
round(pga_max_mem/1024/1024) mame
from v$session s,v$process p
where p.addr=s.paddr
and s.type = 'USER'
order by pga_max_mem desc,logon_time desc;
USERNAME SID SPID LOGON_TIME PROGRAM PID_REMOTE STATUS Used MB Allocated MB Freeable MB Max MB
--------------- ----- -------- ------------------- ---------------------- ------------------------ -------- -------- ------------ ----------- --------
IMSI 859 68423 2024/04/29 22:44:27 sqlplus@ora19 (TNS V1- 68422 ACTIVE 2.0 2.0 .0 2.0
|
백그라운드 프로세스 세션의 pga 사용량 확인
|
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
|
SQL>
set lines 200 pages 1000
col alme heading "Allocated MB" for 99999D9
col usme heading "Used MB" for 99999D9
col frme heading "Freeable MB" for 99999D9
col mame heading "Max MB" for 99999D9
col username for a15
col program for a22
col sid for a5
col spid for a8
select s.username, substr(s.sid,1,5) sid, p.spid
, to_char(logon_time, 'yyyy/mm/dd hh24:mi:ss') logon_time,
substr(s.program,1,22) program , s.process pid_remote,
s.status,
round(pga_used_mem/1024/1024) usme,
round(pga_alloc_mem/1024/1024) alme,
round(pga_freeable_mem/1024/1024) frme,
round(pga_max_mem/1024/1024) mame
from v$session s,v$process p
where p.addr=s.paddr
and s.type != 'USER'
order by pga_max_mem desc,logon_time desc;
USERNAME SID SPID LOGON_TIME PROGRAM PID_REMOTE STATUS Used MB Allocated MB Freeable MB Max MB
--------------- ----- -------- ------------------- ---------------------- ------------------------ -------- -------- ------------ ----------- --------
1149 35854 2024/04/23 22:41:57 oracle@ora19 (DBW0) 35854 ACTIVE 9.0 54.0 44.0 54.0
1156 36346 2024/04/23 22:42:09 oracle@ora19 (W004) 36346 ACTIVE 50.0 51.0 .0 51.0
1144 36255 2024/04/23 22:42:03 oracle@ora19 (W002) 36255 ACTIVE 50.0 51.0 .0 51.0
781 41415 2024/04/23 22:52:14 oracle@ora19 (W007) 41415 ACTIVE 45.0 47.0 .0 47.0
398 36342 2024/04/23 22:42:06 oracle@ora19 (W003) 36342 ACTIVE 44.0 45.0 .0 45.0
21 41370 2024/04/23 22:52:05 oracle@ora19 (W005) 41370 ACTIVE 38.0 40.0 .0 40.0
388 35874 2024/04/23 22:41:57 oracle@ora19 (W001) 35874 ACTIVE 36.0 38.0 .0 38.0
403 41384 2024/04/23 22:52:08 oracle@ora19 (W006) 41384 ACTIVE 34.0 35.0 .0 35.0
8 35880 2024/04/23 22:41:57 oracle@ora19 (MMON) 35880 ACTIVE 12.0 14.0 2.0 31.0
1151 35870 2024/04/23 22:41:57 oracle@ora19 (W000) 35870 ACTIVE 28.0 30.0 .0 30.0
771 35983 2024/04/23 22:42:01 oracle@ora19 (ARC0) 35983 ACTIVE 25.0 30.0 3.0 30.0
395 36009 2024/04/23 22:42:03 oracle@ora19 (ARC3) 36009 ACTIVE 25.0 29.0 2.0 30.0
15 36007 2024/04/23 22:42:03 oracle@ora19 (ARC2) 36007 ACTIVE 25.0 29.0 2.0 30.0
774 36005 2024/04/23 22:42:03 oracle@ora19 (ARC1) 36005 ACTIVE 25.0 29.0 2.0 30.0
392 35981 2024/04/23 22:42:01 oracle@ora19 (TT00) 35981 ACTIVE 18.0 19.0 .0 19.0
454 24005 2024/04/18 22:00:53 oracle@ora19 (M000) 24005 ACTIVE 15.0 19.0 3.0 19.0
94 20837 2024/04/19 02:40:42 oracle@ora19 (M001) 20837 ACTIVE 7.0 11.0 4.0 11.0
391 1087 2024/04/19 06:49:25 oracle@ora19 (M005) 1087 ACTIVE 8.0 9.0 1.0 9.0
767 35852 2024/04/23 22:41:57 oracle@ora19 (DIA0) 35852 ACTIVE 8.0 9.0 .0 9.0
393 35991 2024/04/23 22:42:02 oracle@ora19 (CJQ0) 35991 ACTIVE 3.0 7.0 3.0 7.0
777 36352 2024/04/23 22:42:12 oracle@ora19 (Q002) 36352 ACTIVE 2.0 4.0 1.0 7.0
1153 25743 2024/04/19 10:11:00 oracle@ora19 (M006) 25743 ACTIVE 3.0 6.0 2.0 6.0
766 35844 2024/04/23 22:41:57 oracle@ora19 (DBRM) 35844 ACTIVE 3.0 4.0 1.0 4.0
1150 35862 2024/04/23 22:41:57 oracle@ora19 (SMON) 35862 ACTIVE 2.0 4.0 1.0 4.0
7 35872 2024/04/23 22:41:57 oracle@ora19 (LREG) 35872 ACTIVE 2.0 2.0 .0 3.0
764 35830 2024/04/23 22:41:57 oracle@ora19 (GEN0) 35830 ACTIVE 2.0 3.0 1.0 3.0
SYS 3 35841 2024/04/23 22:41:57 oracle@ora19 (OFSD) 35841_35842 ACTIVE 2.0 3.0 1.0 3.0
769 35868 2024/04/23 22:41:57 oracle@ora19 (RECO) 35868 ACTIVE 2.0 3.0 1.0 3.0
5 35856 2024/04/23 22:41:57 oracle@ora19 (LGWR) 35856 ACTIVE 2.0 2.0 .0 2.0
386 35858 2024/04/23 22:41:57 oracle@ora19 (CKPT) 35858 ACTIVE 1.0 2.0 .0 2.0
390 35882 2024/04/23 22:41:57 oracle@ora19 (MMNL) 35882 ACTIVE 1.0 2.0 .0 2.0
14 35989 2024/04/23 22:42:02 oracle@ora19 (AQPC) 35989 ACTIVE 1.0 2.0 .0 2.0
4 35848 2024/04/23 22:41:57 oracle@ora19 (SVCB) 35848 ACTIVE 1.0 2.0 .0 2.0
1158 36354 2024/04/23 22:42:12 oracle@ora19 (Q003) 36354 ACTIVE 1.0 2.0 .0 2.0
383 35836 2024/04/23 22:41:57 oracle@ora19 (GEN1) 35836_35837 ACTIVE 1.0 1.0 .0 1.0
1148 35846 2024/04/23 22:41:57 oracle@ora19 (VKRM) 35846 ACTIVE 1.0 1.0 .0 1.0
770 35876 2024/04/23 22:41:57 oracle@ora19 (PXMN) 35876 ACTIVE 1.0 1.0 .0 1.0
10 35888 2024/04/23 22:41:57 oracle@ora19 (TMON) 35888 ACTIVE 1.0 1.0 .0 1.0
385 35850 2024/04/23 22:41:57 oracle@ora19 (PMAN) 35850 ACTIVE 1.0 1.0 .0 1.0
382 35826 2024/04/23 22:41:57 oracle@ora19 (VKTM) 35826 ACTIVE 1.0 1.0 .0 1.0
1147 35839 2024/04/23 22:41:57 oracle@ora19 (DIAG) 35839 ACTIVE 1.0 1.0 .0 1.0
387 35866 2024/04/23 22:41:57 oracle@ora19 (SMCO) 35866 ACTIVE 1.0 1.0 .0 1.0
1146 35832 2024/04/23 22:41:57 oracle@ora19 (MMAN) 35832 ACTIVE 1.0 1.0 .0 1.0
1 35824 2024/04/23 22:41:56 oracle@ora19 (PSP0) 35824 ACTIVE 1.0 1.0 .0 1.0
763 35820 2024/04/23 22:41:56 oracle@ora19 (PMON) 35820 ACTIVE 1.0 1.0 .0 1.0
1145 35822 2024/04/23 22:41:56 oracle@ora19 (CLMN) 35822 ACTIVE 1.0 1.0 .0 1.0
11 32868 2024/04/19 14:35:47 oracle@ora19 (M004) 32868 ACTIVE 1.0 1.0 .0 1.0
19 36348 2024/04/23 22:42:12 oracle@ora19 (QM02) 36348 ACTIVE 1.0 1.0 .0 1.0
775 36011 2024/04/23 22:42:03 oracle@ora19 (TT02) 36011 ACTIVE 1.0 1.0 .0 1.0
1154 35985 2024/04/23 22:42:02 oracle@ora19 (TT01) 35985 ACTIVE 1.0 1.0 .0 1.0
765 35836 2024/04/23 22:41:57 oracle@ora19 (SCMN) 35836_35836 ACTIVE 1.0 1.0 .0 1.0
384 35841 2024/04/23 22:41:57 oracle@ora19 (SCMN) 35841_35841 ACTIVE 1.0 1.0 .0 1.0
768 35860 2024/04/23 22:41:57 oracle@ora19 (LG00) 35860 ACTIVE 1.0 1.0 .0 1.0
6 35864 2024/04/23 22:41:57 oracle@ora19 (LG01) 35864 ACTIVE 1.0 1.0 .0 1.0
54 rows selected.
|
참조 :
https://learningoracle1.wordpress.com/2015/02/10/to-identify-where-the-memory-is-growing/
https://positivemh.tistory.com/866
https://positivemh.tistory.com/1113
'ORACLE > Sql' 카테고리의 다른 글
| DBMS_SPACE.UNUSED_SPACE 한번에 여러 세그먼트 조회 (0) | 2024.04.10 |
|---|---|
| 오라클 19c 파티션 테이블의 파티션별 row count 확인 (0) | 2024.04.01 |
| 오라클 19c 버전 및 에디션 확인 방법 (0) | 2024.03.30 |
| 오라클 19c redo log 크기 자동 변경 스크립트 (0) | 2024.03.26 |
| 오라클 19c 힌트 목록 확인 쿼리 (0) | 2024.02.05 |