OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 : 오라클 19c latch: cache buffers chains 이벤트 발생시 핫블록 찾기
latch: cache buffers chains 이벤트가 발생했을 때 핫블록을 찾는 방법에 대해 설명함
latch: cache buffers chains 이벤트란
먼저 cache buffers chains 래치는 SGA에 캐시된 데이터 블록을 액세스할 필요가 있을 때 획득하는 래치임
버퍼 캐시에는 버퍼 헤드 블록들이 체인으로 연결되어 있는데
이 체인들을 스캔하는 동안 체인이 변경되는 것을 방지하기 위해 cache buffers chains 래치를 획득하여 보호하는 구조로 되어 있음
하지만 단일 블록에 대해 과도한 액세스가 발생하게 되면 이 래치에 경합이 발생하게 되고 latch: cache buffers chains 이벤트로 표현됨
이 이벤트는 일반적으로 SQL 문이 필요한 것보다 더 많은 버퍼를 읽고 여러 세션이 동일한 블록을 읽기 위해 대기하기 때문에 발생함
그리고 이 래치에 대한 경합은 일반적으로 경합이 심한 블록(핫 블록)이 있음을 의미함
이 경우 어플리케이션이나 쿼리를 확인하여 튜닝(SQL 튜닝 및 핫블록 분산) 해주어야함
이 대기 이벤트가 발생하면 세션 대기로 인해 CPU 사용량이 증가하고 시스템 장애로 이어질 가능성이 있음
Hot Block에 의한 latch 경합
Bad SQL에 의한 latch 경합
이벤트 발생시 해결 방법
- 악성 쿼리(Bad SQL, 악성 SQL) 식별 후 조치
NL 조인시 선행 테이블의 결과 집합이 커 후행 테이블에 엑세스를 불필요하게 많이 한다거나(여러세션에서 해당 쿼리 과도하게 수행 시)
1회 실행시 logical read 가 많은 쿼리들을 확인하여 논리적인 일양(logical read)을 줄이는 조치 필요(최소한의 블록만 읽도록 조치)
- 문제 블록에 접근하는 쿼리 식별 후 반복적인 엑세스가 필요한지 확인
쿼리가 단일 세션에서 수행되고 있을수도 있고 여러 세션들에서 동시에 수행되고 있을수도 있음
- 병렬 쿼리를 적절히 활용
병렬 쿼리 사용시 메모리(SGA)를 거치지 않고 direct path 방식으로 블록을 읽기 때문에 버퍼캐시 경합이 발생할수가 없음
- SQL 튜닝만으로 조치되지 않는 경우 핫블록 데이터 분산을 위해 파티셔닝 또는 PCTFREE 증가
파티셔닝 시 블록을 분산하여 저장하기 때문에 핫블록 이슈가 줄어들 수 있음
PCTFREE를 높이게 되면 한 블록에 삽입되는 row 수를 줄여주기 때문에 row 들이 더 많은 블록에 분산되어 저장되기 때문에 핫블록 이슈가 줄어들 수 있음
다만 이 경우 블록의 갯수가 늘어나기 때문에 전체범위 처리방식을 사용할 경우 더 많은 블록을 접근할수 있으므로 충분한 검증이 필요함
이벤트 발생 및 확인 테스트
프로세스 max 값 1000으로 상향 후 재기동
|
1
2
3
|
SQL>
alter system set processes = 1000 scope=spfile;
startup force
|
샘플 유저 생성
|
1
2
3
|
SQL>
create user imsi identified by imsi account unlock default tablespace users quota unlimited on users;
grant resource, connect, dba to imsi;
|
샘플 테이블 생성
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
conn imsi/imsi
drop table sample purge;
create table sample (
col1 number
,col2 number
) pctfree 0
initrans 255;
Table created.
|
샘플 인덱스 생성
|
1
2
3
|
SQL> create index sample_ix1 on sample(col1,col2) pctfree 0;
Index created.
|
샘플 테이블 데이터 삽입
|
1
2
|
SQL> insert into sample values (1, 1);
commit;
|
해당 데이터 블록 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
set lines 200 pages 1000
select
dbms_rowid.rowid_relative_fno(rowid) rel_fno,
dbms_rowid.rowid_block_number(rowid) blockno,
rowid,
col1
from sample;
REL_FNO BLOCKNO ROWID COL1
---------- ---------- ------------------ ----------
5 1066758 AAAH9OAAFAAEEcGAAA 1
|
동시 업데이트 및 조회 스크립트 생성
|
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
|
$ cat dml.sql
DECLARE
v_dummy NUMBER;
v_dummy2 NUMBER;
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'HOTBLK_TEST', action_name => 'HOTBLK_TEST');
FOR i IN 1..10000 LOOP
UPDATE sample SET col2 = col2 + 1 WHERE col1 = 1;
SELECT count(col2) INTO v_dummy FROM sample WHERE col1 = 1;
SELECT col2 INTO v_dummy2 FROM sample WHERE col1 = 1;
DELETE FROM sample WHERE col1 = 1 AND col2 = 10000;
COMMIT;
END LOOP;
END;
/
SET HEADING OFF FEEDBACK OFF PAGESIZE 0 ECHO OFF;
SPOOL kill_test_sess.sql APPEND;
SELECT 'alter system kill session ''' || t1.sid || ',' || t1.serial# || ''' immediate;' "session kill"
FROM GV$SESSION t1
WHERE username = 'IMSI'
AND module = 'HOTBLK_TEST'
AND action = 'HOTBLK_TEST';
PROMPT
SPOOL OFF;
|
동시 세션 실행용 스크립트 생성(1분동안만 실행되고 이후에 종료되게끔 셋팅)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
$ cat run_sess.sh
#!/bin/bash
# 동시 실행할 세션의 수
session_count=900
# PL/SQL 스크립트 파일 경로
sql_script="dml.sql"
kill_script="kill_test_sess.sql"
# time limit
timeout_duration=60
for i in $(seq 1 $session_count)
do
timeout $timeout_duration sqlplus -S imsi/imsi < $sql_script &
done
wait
sqlplus imsi/imsi < $kill_script
rm kill_test_sess.sql
echo "SQL excuted."
|
스크립트 권한 부여
|
1
|
$ chmod u+x run_sess.sh
|
스크립트 실행
|
1
|
$ sh run_sess.sh
|
ash 모니터링
|
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 wait_class for a12
col event for a50
select * from (
select
wait_class ,
event,
--sql_id,
count(sample_time) as est_secs_in_wait
from v$active_session_history
--where sample_time between sysdate - interval '1' hour and sysdate
--where sample_time between sysdate - interval '1' minute and sysdate
where sample_time between sysdate - interval '30' second and sysdate
group by wait_class, event --, sql_id
order by count(sample_time) desc
)
where rownum <= 5;
WAIT_CLASS EVENT EST_SECS_IN_WAIT
------------ -------------------------------------------------- ----------------
Application enq: TX - row lock contention 2431
Concurrency buffer busy waits 644
Concurrency latch: cache buffers chains 624 ***
180
Concurrency row cache mutex 177
|
enq: TX - row lock contention이 가장 대기가 많지만 latch: cache buffers chains 이벤트도 발생함
latch: cache buffers chains 이벤트 발생한 래치 확인
|
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
|
SQL>
set lines 200 pages 1000
select * from(
select child# "cCHILD" ,
addr "sADDR" ,
gets "sGETS" ,
misses "sMISSES" ,
sleeps "sSLEEPS"
from v$latch_children
where name = 'cache buffers chains'
order by 5 desc, 1, 2, 3)
where rownum <= 10;
cCHILD sADDR sGETS sMISSES sSLEEPS
---------- ---------------- ---------- ---------- ----------
1907 000000007999EF38 6366264 19441 5121 ***
940 000000007978BBC0 2993712 32092 99 ***
37 000000007959BA48 29287 131 3
127 00000000795CC098 24261 197 3
608 00000000796D3860 22512 121 3
125 00000000795CBF08 30123 52 2
265 000000007961AC68 29671 127 2
285 0000000079623C08 14760 53 2
322 00000000796398F0 15108 55 2
437 00000000796772C8 25745 151 2
10 rows selected.
|
cCHILD이 1907, 940에 sADDR이 000000007999EF38, 000000007978BBC0 인 row가 sMISSES가 확연히 높음
위 결과 중 원하는 라인까지 sADDR(주소)복사
해당 래치 주소로 핫블록 확인(tch가 높은 순으로 확인)
|
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
|
SQL>
set lines 200 pages 1000
col object_name format a35
select * from (
select hladdr, obj,
( select object_name
from dba_objects
where ( data_object_id is null and object_id = x.obj)
or data_object_id = x.obj
and rownum = 1) as object_name, dbarfil, dbablk, tch
from x$bh x
where hladdr in (
'000000007999EF38', '000000007978BBC0'
)
--order by hladdr, obj
order by tch desc, object_name)
where rownum <= 20
;
HLADDR OBJ OBJECT_NAME DBARFIL DBABLK TCH
---------------- ---------- ----------------------------------- ---------- ---------- ----------
000000007978BBC0 32598 SAMPLE 5 1066759 49 ***
000000007978BBC0 32598 SAMPLE 5 1066759 20 ***
000000007978BBC0 32598 SAMPLE 5 1066759 14 ***
000000007978BBC0 8 SEG$ 1 18999 3
000000007978BBC0 2 CLU$ 1 17402 2
000000007978BBC0 2 CLU$ 1 12234 2
000000007978BBC0 2 CLU$ 1 21583 2
000000007978BBC0 36 I_OBJ1 1 54421 1
000000007978BBC0 718 OPTSTAT_SNAPSHOT$ 1 58602 1
000000007978BBC0 28789 WRI$_OPTSTAT_HISTGRM_HISTORY 2 82350 1
000000007978BBC0 26668 WRI$_OPTSTAT_HISTGRM_HISTORY 2 81363 1
000000007978BBC0 26850 WRI$_OPTSTAT_HISTGRM_HISTORY 2 37579 1
000000007978BBC0 4294967295 3 1040833 1
000000007978BBC0 4294967295 3 1042430 1
000000007978BBC0 4294967295 3 84816 1
000000007978BBC0 4294967295 3 1038249 1
000000007978BBC0 4294967295 3 80635 1
000000007978BBC0 4294967295 3 1034068 1
000000007978BBC0 4294967295 3 69689 1
000000007978BBC0 4294967295 3 87400 1
20 rows selected.
|
SAMPLE_IX1 인덱스의 tch(touch count) 값이 높음
중간에 object_id가 4294967295로 나오는 건은 아마 delete 구문으로 인해 발생한 지워진 블록인듯함
위 2개 쿼리 하나로 합친 쿼리(sleep가 높은순으로 top 5에 대해서만 확인)
|
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
|
SQL>
set lines 200 pages 1000
col object_name format a35
select * from (
select hladdr, obj,
( select object_name
from dba_objects
where ( data_object_id is null and object_id = x.obj)
or data_object_id = x.obj
and rownum = 1) as object_name,
dbarfil, dbablk, tch
from x$bh x
where hladdr in (
select addr from(
select addr
from v$latch_children
where name = 'cache buffers chains'
order by sleeps desc)
where rownum <= 5
)
--order by hladdr, obj
order by tch desc, object_name)
where rownum <= 10
;
HLADDR OBJ OBJECT_NAME DBARFIL DBABLK TCH
---------------- ---------- ----------------------------------- ---------- ---------- ----------
000000007978BBC0 32598 SAMPLE 5 1066759 49 ***
000000007978BBC0 32598 SAMPLE 5 1066759 20 ***
000000007978BBC0 32598 SAMPLE 5 1066759 17 ***
00000000799BDD50 18 OBJ$ 1 31847 5
000000007978BBC0 32598 SAMPLE 5 1066759 5 ***
0000000079749C70 8 SEG$ 1 52366 5
00000000799BDD50 84 ACCESS$ 1 14136 4
000000007999EF38 2 CLU$ 1 12238 4
0000000079749C70 4294967295 3 86942 4
00000000797D6858 4294967295 3 1038652 4
10 rows selected.
|
이렇게 원인 쿼리 및 테이블 식별 후 튜닝 등의 조치 필요함
참고용 기타 쿼리(느림)
|
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
|
SQL>
col segment_name for a35
SELECT /*+ RULE */
e.owner || '.' || e.segment_name segment_name,
e.extent_id extent#,
x.dbablk - e.block_id + 1 block#,
x.tch,
l.child#
FROM sys.v$latch_children l,
sys.x$bh x,
sys.dba_extents e
WHERE x.hladdr in (
select addr from(
select addr
from v$latch_children
where name = 'cache buffers chains'
order by sleeps desc)
where rownum <= 5
)
AND e.file_id = x.file#
AND x.hladdr = l.addr
AND x.dbablk BETWEEN e.block_id AND e.block_id + e.blocks -1
ORDER BY x.tch DESC ;
SEGMENT_NAME EXTENT# BLOCK# TCH CHILD#
----------------------------------- ---------- ---------- ---------- ----------
IMSI.SAMPLE 0 8 78 152
IMSI.SAMPLE 0 7 78 543
IMSI.SAMPLE 0 5 77 302
IMSI.SAMPLE 0 4 68 693
IMSI.SAMPLE 0 5 25 302
IMSI.SAMPLE 0 7 25 543
IMSI.SAMPLE 0 4 8 693
SYS.C_OBJ# 20 10 3 636
SYS.C_OBJ# 9 5 3 636
|
이렇게 테스트를 진행 했지만..
조금더 찾아본 결과
오라클 코어(DBA와 개발자를 위한 필수 메커니즘) 책에서는 tch 이용한 방법이 적절한 방법이 아니라고 함(p146)
[일반적으로 cache buffers chains 래치 경합을 유발하는 블록(hot 블록)을 식별하기 위한 방법으로 버퍼의 터치 카운트(tch)를 이용한다.
하지만 이것은 적절한 방법이 아니다. 30분 동안 평균적으로 초당 1회씩 접근한 버퍼의 터치 카운트는 대략 600 이다.
그리고 최근 5분 동안 천만번 접근한 버퍼의 터치 카운트는 대략 100 이다. 즉, 터치 카운트는 유용한 정보이긴 하지만 정답은 아니다.
(중략) 하지만 이보다는 Tanel Poder의 latchprofx 툴을 이용하는것이 바람직하다.]
(실제 위 방법으로 테스트 할때 cache buffers chains 이벤트가 발생하는 중에 실시간으로 tch 카운트를 확인하지 않으면
몇초 뒤 금방 tch 높은 쿼리가 밀려나버려 문제 세션을 정확히 식별하는것이 어려울 수 있음)
그래서 이 책에서 설명하고 있는 latchprofx 툴로 확인해봄
툴 다운로드 ( https://tanelpoder.com/downloads/ )
스크립트 실행
|
1
|
$ sh run_sess.sh
|
다른 세션에서 latchprofx 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL> @latchprofx sid,name,hmode,func % "cache buffers chains" 100000
SID NAME HMODE FUNC Held Gets Held % Held ms Avg hold ms
---------- ---------------------------------------- ------------ ---------------------------------------- ---------- ---------- ------- ----------- -----------
906 cache buffers chains shared kcbxbh 15645 1592 15.65 1738.160 1.092
221 cache buffers chains shared kcbxbh 3713 664 3.71 412.514 .621
221 cache buffers chains maybe-shared kcbgtcr: fast path pin 2779 675 2.78 308.747 .457
1156 cache buffers chains maybe-shared kcbgtcr: fast path pin 1703 522 1.70 189.203 .362
955 cache buffers chains shared kcbgtcr: fast path exam 1038 273 1.04 115.322 .422
1013 cache buffers chains maybe-shared kcbgtcr: fast path pin 1023 39 1.02 113.655 2.914
67 cache buffers chains maybe-shared kcbgtcr: fast path exam 746 146 .75 82.881 .568
634 cache buffers chains exclusive kcbgtcr: slow path excl 541 73 .54 60.105 .823
1331 cache buffers chains maybe-shared kcbgtcr: fast path exam 473 18 .47 52.550 2.919
519 cache buffers chains maybe-shared kcbgtcr: fast path exam 471 17 .47 52.328 3.078
18 cache buffers chains shared kcbgtcr: fast path exam 465 444 .47 51.662 .116
596 cache buffers chains shared kcbgtcr: fast path exam 453 135 .45 50.328 .373
1382 cache buffers chains shared kcbgtcr: fast path exam 422 169 .42 46.884 .277
592 cache buffers chains shared kcbgtcr: fast path exam 404 384 .40 44.884 .117
101 cache buffers chains shared kcbgtcr: fast path exam 378 375 .38 41.996 .112
1201 cache buffers chains shared kcbrls: fast pin release 369 18 .37 40.996 2.278
242 cache buffers chains shared kcbgtcr: fast path exam 360 321 .36 39.996 .125
.
.
|
위 결과에 존재하는 세션의 sql_id 확인
|
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
|
SQL>
set lines 200 pages 1000
col wait_class for a12
col event for a50
select a.*, (select substr(sql_text, 1, 20) from v$sql where sql_id = a.sql_id and rownum <= 1) sql_text
from ( select
session_id,
max(wait_class) wait_class,
max(event) event,
max(sql_id) sql_id
from v$active_session_history
--where sample_time between sysdate - interval '1' hour and sysdate
where sample_time between sysdate - interval '20' minute and sysdate
--where sample_time between sysdate - interval '30' second and sysdate
and session_id in (906,221,1156,955,1013,67,634,1331,519,18,1156,1382,592,101,1201,242)
and event = 'latch: cache buffers chains'
group by session_id
order by session_id) a
where rownum <= 10;
SESSION_ID WAIT_CLASS EVENT SQL_ID SQL_TEXT
---------- ------------ -------------------------------------------------- ------------- ----------------------------------------
18 Concurrency latch: cache buffers chains 75h2knpn4gabh UPDATE SAMPLE SET CO
101 Concurrency latch: cache buffers chains 75h2knpn4gabh UPDATE SAMPLE SET CO
221 Concurrency latch: cache buffers chains 75h2knpn4gabh UPDATE SAMPLE SET CO
592 Concurrency latch: cache buffers chains 75h2knpn4gabh UPDATE SAMPLE SET CO
906 Concurrency latch: cache buffers chains 75h2knpn4gabh UPDATE SAMPLE SET CO
955 Concurrency latch: cache buffers chains 75h2knpn4gabh UPDATE SAMPLE SET CO
1013 Concurrency latch: cache buffers chains 75h2knpn4gabh UPDATE SAMPLE SET CO
1201 Concurrency latch: cache buffers chains 75h2knpn4gabh UPDATE SAMPLE SET CO
1331 Concurrency latch: cache buffers chains 75h2knpn4gabh UPDATE SAMPLE SET CO
9 rows selected.
|
sql_id 로 sql fulltext 확인
|
1
2
3
4
5
6
7
|
SQL>
set long 99999
select sql_fulltext from v$sql where sql_id = '75h2knpn4gabh';
SQL_FULLTEXT
--------------------------------------------------------------------------------
UPDATE SAMPLE SET COL2 = COL2 + 1 WHERE COL1 = 1
|
이렇게 원인 쿼리 및 테이블 식별 후 튜닝 등의 조치 필요함
참조 :
163424.1, 62172.1, 1342917.1, 22908.1, 1970450.1
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/instance-tuning-using-performance-views.html#GUID-64F78817-8B4C-4392-B518-CA31CF728B69
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/instance-tuning-using-performance-views.html#GUID-07982549-507F-4465-8843-7F753BCF8F99
https://positivemh.tistory.com/994
http://www.gurubee.net/wiki/pages/6259330
https://dataonair.or.kr/db-tech-reference/d-lounge/technical-data/?mod=document&uid=234791
https://www.anbob.com/archives/1156.html
https://exem-academy.com/%EC%A0%9C5%ED%9A%8C-latch-cache-buffers-chains/
https://tech.e2sn.com/oracle-scripts-and-tools
https://tanelpoder.com/
그림으로 명쾌하게 풀어 쓴 Practical OWI in Oracle 10g
오라클 성능 고도화 원리와 해법
오라클 코어(DBA와 개발자를 위한 필수 메커니즘)
https://tanelpoder.com/2009/08/27/latch-cache-buffers-chains-latch-contention-a-better-way-for-finding-the-hot-block/
'ORACLE > Performance Tuning ' 카테고리의 다른 글
| 오라클 19c Parallel DML 시 Direct Path Write 이벤트 관련 (0) | 2024.04.19 |
|---|---|
| 오라클 19c 다른 세션 트레이스 설정 (0) | 2024.04.06 |
| 오라클 19c FULL TABLE SCAN (FTS)시 direct path read 이벤트 관련 (0) | 2024.03.21 |
| 오라클 19c group by 컬럼 나열 및 집계함수 적용 성능 비교 (0) | 2024.03.19 |
| 오라클 19c Prefetch, Batch I/O, Table access by rowid batched 설명 (0) | 2024.02.29 |