오라클 성능 트러블슈팅의 기초 (2012년)
X$ 테이블
X$KSMSS - shared pool
-- 특정 서브 풀만 크기가 커지면서 ORA-4031 에러 발생 가능 : 서브 풀 불균형
SELECT KSMCHIDX "SUBPOOL",
SUM(KSMCHSIZ) BYTES
FROM SYS.X$KSMSP
GROUP BY KSMCHIDX;
/*
SUBPOOL BYTES
---------- ----------
3 889187784
2 973073424
1 922742040
*/
WITH X AS (
SELECT KSMDSIDX AS POOLIDX,
'shared pool' AS POOL,
KSMSSNAM AS NAME,KSMSSLEN AS BYTES,
SUM(KSMSSLEN) OVER (PARTITION BY KSMDSIDX) AS SP_BYTES,
SUM(KSMSSLEN) OVER () AS TOTAL_BYTES
FROM SYS.X$KSMSS
WHERE KSMSSLEN > 0
)
SELECT POOLIDX, POOL, NAME
, TRUNC(BYTES/1024/1024,2) AS MB
, TRUNC(100*BYTES/SP_BYTES,2) AS PCT
FROM X
ORDER BY POOLIDX, PCT DESC;
/*
POOLIDX POOL NAME MB PCT
---------- ----------- -------------------------- ---------- ----------
1 shared pool SQLA 510.61 58.02
1 shared pool CCUR 97.49 11.07
1 shared pool free memory 91.9 10.44
1 shared pool private strands 5.19 .59
...
2 shared pool SQLA 504.24 54.33
2 shared pool free memory 108.36 11.67
2 shared pool CCUR 99.85 10.76
2 shared pool FileOpenBlock 15.19 1.63
2 shared pool KQR M PO 11.91 1.28
...
3 shared pool SQLA 505.32 59.58
3 shared pool CCUR 101.99 12.02
3 shared pool free memory 67.14 7.91
3 shared pool PCUR 46.12 5.43
3 shared pool KGLHD 20.29 2.39
*/
X$KSMLS - large pool
WITH X AS (
SELECT KSMDSIDX AS POOLIDX,
'large pool' AS POOL,
KSMSSNAM AS NAME,KSMSSLEN AS BYTES,
SUM(KSMSSLEN) OVER (PARTITION BY KSMDSIDX) AS SP_BYTES,
SUM(KSMSSLEN) OVER () AS TOTAL_BYTES
FROM SYS.X$KSMLS
WHERE KSMSSLEN > 0
)
SELECT POOLIDX, POOL, NAME
, TRUNC(BYTES/1024/1024,2) AS MB
, TRUNC(100*BYTES/SP_BYTES,2) AS PCT
FROM X
ORDER BY POOLIDX, PCT DESC;
/*
POOLIDX POOL NAME MB PCT
------- ---------- -------------------------- -- ---
1 large pool free memory 63.53 99.26
1 large pool PX msg pool 0.46 0.73
*/
X$KSMLS - java pool
WITH X AS (
SELECT KSMDSIDX AS POOLIDX,
'java pool' AS POOL,
KSMSSNAM AS NAME,KSMSSLEN AS BYTES,
SUM(KSMSSLEN) OVER (PARTITION BY KSMDSIDX) AS SP_BYTES,
SUM(KSMSSLEN) OVER () AS TOTAL_BYTES
FROM SYS.X$KSMJS
WHERE KSMSSLEN > 0
)
SELECT POOLIDX, POOL, NAME
, TRUNC(BYTES/1024/1024,2) AS MB
, TRUNC(100*BYTES/SP_BYTES,2) AS PCT
FROM X
ORDER BY POOLIDX, PCT DESC;
/*
POOLIDX POOL NAME MB PCT
------- --------- -------------------------- -- ---
0 java pool free memory 64 100
*/
X$KSMLS - streams pool
WITH X AS (
SELECT KSMDSIDX AS POOLIDX,
'streams pool' AS POOL,
KSMSSNAM AS NAME,KSMSSLEN AS BYTES,
SUM(KSMSSLEN) OVER (PARTITION BY KSMDSIDX) AS SP_BYTES,
SUM(KSMSSLEN) OVER () AS TOTAL_BYTES
FROM SYS.X$KSMSTRS
WHERE KSMSSLEN > 0
)
SELECT POOLIDX, POOL, NAME
, TRUNC(BYTES/1024/1024,2) AS MB
, TRUNC(100*BYTES/SP_BYTES,2) AS PCT
FROM X
ORDER BY POOLIDX, PCT DESC;
/*
POOLIDX POOL NAME MB PCT
------- ------------ -------------------------- -- ---
0 streams pool free memory 64 100
*/
X$KSMSP - SGA 청크 정보
-- X$KSMSP.KSMCHDUR : 청크의 지속 시간 (공유 풀 > 서브 힙 > 미니 힙, 동일한 미니 힙에서 청크를 할당 받기 위함)
-- X$KSMSP.KSMCHCLS : 청크의 유형
-- (free: Free Chunk, perm: Permanent Chunk, recr: Recreatable Chunk, freeabl: Freeable Chunk)
-- "R-" 접두어가 붙은 경우 예비 풀 영역의 청크임
-- X$KSMSP.KSMCHCOM : 청크의 컴포넌트 타입 (예: kxt.c: Trigger ==> 트리거 실행 정보)
-- X$KSMSP.KSMCHSIZ : 청크의 크기 (BYTE)
SELECT KSMCHCLS CLASS
, COUNT(KSMCHCLS) NUM
, SUM(KSMCHSIZ) SIZ
, TO_CHAR(((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)), '999,999.00')||'KB' "AVG SIZE"
FROM X$KSMSP
GROUP BY KSMCHCLS
ORDER BY 4 DESC;
/*
CLASS NUM SIZ AVG SIZE
-------- --- --- -------------
perm 2 127498952 62,255.35KB
R-free 5 16792680 3,279.82KB
free 144 108427992 735.32KB
freeabl 14801 47377384 3.13KB
recr 20124 35446392 1.72KB
R-freea 10 480 .05KB
*/
SELECT KSMCHCOM CLASS
, COUNT(KSMCHCLS) NUM
, SUM(KSMCHSIZ) SIZ
, TO_CHAR(((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)), '999,999.00')||'KB' "AVG SIZE"
FROM X$KSMSP
GROUP BY KSMCHCOM
ORDER BY 4 DESC;
/*
CLASS NUM SIZ AVG SIZE
---------------- --- --- -------------
permanent memor 2 127498952 62,255.35KB
KSFD SGA I/O b 1 4190296 4,092.09KB
free memory 145 125192608 843.16KB
character set o 5 534912 104.48KB
MS alert log 1 72728 71.02KB
NETWORK BUFFER 1 32832 32.06KB
PX subheap 1 32744 31.98KB
KGSK scheduler 2 52280 25.53KB
dbgefgHtAddSK-1 24 482624 19.64KB
kwqmncini-slv 1 18424 17.99KB
...
*/
-- X$KSMSP 엑세스시 래치 필요(Shared Pool Latch) - 래치 경합 발생 가능성 있음 (운영중 조회 금지)
-- 11g NF
SELECT KSMCHCOM,
KSMCHSIZ
FROM SYS.X$KSMSP
WHERE REGEXP_LIKE(KSMCHCOM, '[[:alnum:]]+\^[[:xdigit:]]+')
AND ROWNUM <= 100;
/*
KSMCHCOM KSMCHSIZ
---------------- ----------
SQLA^aa0beccb 4096
KGLS^283faff0 4096 <<< KGLS : X$KGLOB, 283faff0 : X$KGLOB.KGLNAHSH
KGLS^f678506c 4096
KGLS^19563d19 4096
SQLA^aa0beccb 4096 <<< SQLA : V$SQLAREA, aa0beccb : V$SQLAREA.HASH_VALUE
SQLA^ac17a07b 4096
SQLA^ac17a07b 4096
SQLA^ac17a07b 4096
SQLA^ac17a07b 4096
SQLA^ac17a07b 4096
SQLA^2b3396e2 4096
...
*/
SELECT SQL_TEXT
FROM V$SQLAREA
WHERE HASH_VALUE = TO_NUMBER('aa0beccb', 'xxxxxxxxxxx')
/*
SQL_TEXT
--------------------------------------------------------------------------------
select java_pool_size_for_estimate s, java_pool_size_factor * 100 f...
*/
SELECT KGLNAOBJ
FROM X$KGLOB
WHERE KGLNAHSH = TO_NUMBER('283faff0', 'xxxxxxxxxxx');
/*
KGLNAOBJ
--------------------------------------------------------------------------------
GV$JAVA_POOL_ADVICE
*/
X$KSMPP - PGA 청크 정보
SELECT KSMCHCLS CLASS
, COUNT(KSMCHCLS) NUM
, SUM(KSMCHSIZ) SIZ
, TO_CHAR(((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)), '999,999.00')||'KB' "AVG SIZE"
FROM X$KSMPP
GROUP BY KSMCHCLS
ORDER BY 4 DESC;
/*
CLASS NUM SIZ AVG SIZE
-------- --- --- -------------
free 6 60400 9.83KB
perm 19 186568 9.59KB
recr 6 24384 3.97KB
freeabl 59 195104 3.23KB
*/
SELECT KSMCHCOM CLASS
, COUNT(KSMCHCLS) NUM
, SUM(KSMCHSIZ) SIZ
, TO_CHAR(((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)), '999,999.00')||'KB' "AVG SIZE"
FROM X$KSMPP
GROUP BY KSMCHCOM
ORDER BY 4 DESC;
/*
CLASS NUM SIZ AVG SIZE
---------------- --- --- -------------
Fixed Uga 1 38248 37.35KB
kgh stack 1 17024 16.63KB
free memory 6 60400 9.83KB
KFK_IO_SUBHEAP 1 9976 9.74KB
permanent memor 19 186568 9.59KB
Alloc environm 8 50192 6.13KB
PLS PGA hp 3 18672 6.08KB
diag pga 14 59368 4.14KB
peshm.c:Proces 2 5616 2.74KB
kjztprq struct 1 2600 2.54KB
KSFQ heap 1 2040 1.99KB
...
*/
-- X$KSMPP 엑세스시 래치 불필요 (PGA 영역은 프로세스간 공유 안됨)
-- X$KSMPP(PGA) 에는 "R-" 로 시작하는 예비 풀이 없음
-- X$KSMPP 테이블은 프로세스 자기 자신만 조회 가능, 다른 프로세스의 PGA 분석은 PGA 힙 덤프 사용
"데이터베이스 스터디모임" 에서 2012년에 "오라클 성능 트러블슈팅의 기초
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4311
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.