0328 RAC

현스·2024년 3월 28일

RAC

목록 보기

4/12

▣ 예제45. RAC 환경에서 락(Lock) 에 관련한 성능저하시 해결방법

오라클 db 의 성능이 느리다면 원인은 크게 2가지 ?

악성 SQL ----------> SQL 튜닝으로 해결할 수 있습니다.
Lock ---------------> 파라미터 튜닝을 해야함

※ LOCK 의 종류 두가지

enqueue 락
기타 락

■ enqueue 의 뜻 ? 큐에 들어가서 순서데로 waiting 하는 구조를 갖는 락(lock)

  en               queue(큐)
   ↓                   ↓
 enterance          큐라는 메모리 영역
 입장하다

설명: 그동안 update 문에서만 락을 사용한다라고 알고 있었는데 update 뿐만 아니라 모든 DML, DDL, DCL 문이 다 락을 획득합니다. 그런데 그 락의 종류가 다 다릅니다.
여기서 말하는 락은 enqueue 를 말합니다.

enqueue 의 종류 ?
1. SQ enqueue : 시퀀스의 번호를 획득할때 사용하는 락
2. TX enqueue : 테이블의 행의 데이터를 갱신할때 사용하는 락
3. TM enqueue : 테이블 전체에 거는 락
4. HW enqueue : HWM 를 올릴때 거는 락
5. US enqueue : Undo Segment 를 사용할 때 거는 락

기타 락(lock) ? Library cache lock
Library cache pin
row cache lock
buffer busy waits

▣ 예제46. RAC 환경에서 시퀀스 파라미터 ORDER 와 NOORDER

시퀀스에서 번호를 생성할 때 거는 락(lock)

예: 은행에 가면 번호표 기계가 있는데 그걸 시퀀스라고 생각하면 됩니다.
내가 번호를 뽑고 있는데 누군가 와서 나를 밀치고 내가 뽑으려는 번호를
뺏어가면 안되니까 락을 거는겁니다. 이게 SQ enqueue 입니다.

튜너들이나 db 엔지니어들이 사이트 들어가면?

 * db 엔지니어 성장기록 

설치와 트러블 슈팅 -----> 데이터 이행 ----> 튜닝

시퀀스 관련 튜닝

튜닝 전 : select max(empno) into v_empno
from emp;

	insert into emp(empno, ename, sal )
	values(v_empno, v_ename, v_sal);

튜닝 후

	     insert into emp(empno, ename, sal)	
	     values(seq1.nextval, v_ename, v_sal);

시퀀스 관련한 파라미터 튜닝

시퀀스를 만드는 문법 :

create sequence seq1
start with 1                  시작 값 : 1
maxvalue 100                  최댓값
increment by 1                증가치
cache 20;                    메모리 20개 미리 올려놓음       핵심 파라미터

select seq1.nextval from dual;

문제 1. 시퀀스를 다음과 같이 생성 하시오

 시퀀스 이름:  seq2
 증가치  :  1
 최대값 : 1000
 캐쉬 :  nocache   ----> 메모리에 미리 시퀀스 번호를 올려놓지 않겠다.

답

create sequence seq2
start with 1
maxvalue 1000
increment by 1
nocache;

select seq2.nextval from dual;

문제2. 다음과 같이 시퀀스를 생성하시오

시퀀스 이름:  seq3
 증가치  :  1
 최대값 : 10000
 캐쉬 :  100

create sequence seq3
start with 1
maxvalue 10000
increment by 1
cache 100;

select seq3.nextval from dual;

order : rac 환경에서 노드와 무관하게 시퀀스의 발번 순서를 보장 시키는 것
no order : rac 환경에서 시퀀스의 발번 순서를 보장하지 않는 것

order 인 경우

create sequence seq7
start with 1
maxvalue 100
increment by 1
cache 20
order ;

noorder 인 경우

약간 그런거 키오스크 번호 공유 안하는거

no order + no cache 이면 어캐 되는가 ?

순서는 보장하지 않으면서 디비에서 번호 뽑아서 감. 제일 안좋아

■ 실습

#1. SEQ1 시퀀스가 있으면 drop

SQL#1 > drop sequence seq1;

#2. order 옵션을 사용해서 SEQ1 시퀀스를 생성

SQL#1 > create sequence seq1
		start with 1
		maxvalue 100
		increment by 1
		cache 20
		order;

#3. 1번 노드에서 SEQ1 시퀀스의 번호를 2번 생성

 SQL#1 > select seq1.nextval from dual;
 SQL#1 > select seq1.nextval from dual;

#4. 2번 노드에서 SEQ1 시퀀스의 번호를 2번 생성

 SQL#2 > select seq1.nextval from dual;
 SQL#2 > select seq1.nextval from dual;

문제2. 이번에는 no order 옵션을 실험
다음과 같이 테스트 하시오

#1. SEQ1 시퀀스가 있으면 drop

SQL#1 > drop sequence seq1;

#2. order 옵션을 사용해서 SEQ1 시퀀스를 생성

SQL#1 > create sequence seq1
		start with 1
		maxvalue 100
		increment by 1
		cache 20
		noorder;

#3. 1번 노드에서 SEQ1 시퀀스의 번호를 2번 생성

 SQL#1 > select seq1.nextval from dual;
 SQL#1 > select seq1.nextval from dual;

#4. 2번 노드에서 SEQ1 시퀀스의 번호를 2번 생성

 SQL#2 > select seq1.nextval from dual;
 SQL#2 > select seq1.nextval from dual;

1~20 // 21~40
1번 노드에서는 20번 이후에 41번이 나온다.

▣ 예제 47. RAC 환경에서 발생하는 SQ enqueue 성능 문제 해결하는 방법

no cache 일 때

오라클에서 권장하는 시퀀스 파라미터 조합의 우선순위
(안좋은 것부터)

■ 실습 환경 구성

SQL#1 > drop user owi cascade;

SQL#1 > drop tablespace owi_tbs including contents and datafiles;

SQL#1 > alter system set db_recovery_file_dest_size=30G;

RMAN > delete backup;

RMAN > delete copy;

RMAN > backup database;

SQL#1 > @install

■ 실습1. 시퀀스 속성 nocache 성능 테스트

#1. owi 유져가 가지고 있는 시퀀스를 확인하고 속성을 조회하시오

SQL#1> connect  owi/owi

SQL#1> select  sequence_name,  cache_size,  order_flag
               from  user_sequences; 

SQL#1 > alter sequence seq_sq_enqueue nocache;

SQL#1 > select sequence_name, cache_size, order_flag
		from user_sequences;

#2. 터미널 창을 열어서 lock 관련한 대기 이벤트로 대기하는 세션을 조회합니다.

select sid, event
from gv$session_wait
where event like '%lock%'
or event like '%enqueue%';

#3. 시퀀스 관련 성능 부하를 일으킵니다.

SQL#1 > @exec

Event name to simulate: sq_enqueue

나머지 전부 엔터 침 (디폴트 값)

▣ 예제 48. RAC 환경에서 발생하는 SQ enqueue 성능 문제 해결하는 방법 2

cache + order 일 때

오라클에서 권장하는 시퀀스 파라미터 조합의 우선순위
(안좋은 것부터)

1번 노드와 2번 노드가 서로 통신하면서 몇번까지 번호를 부여했는지 서로 메세지를 교환한다.
시퀀스 사용이 모든 노드에서 빈번해지면 rac 성능 저하가 발생한다
이 때 발생하는 대기 이벤트가 dfs lock handle 이다.

#1. owi 유져의 seq_sq_enqueue 시퀀스의 시퀀스 속성을 확인 하시오

SQL#1 > connect owi/owi

SQL#1 > select sequence_name, cache_size, order_flag
   		from user_sequences;

#2. cache 사이즈는 10으로 하고 order flag 는 order 로 설정

SQL#1 > alter sequence seq_sq_enqueue cache 10;

SQL#1 > alter sequence seq_sq_enqueue order;

SQL#1 > select sequence_name, cache_size, order_flag
from user_sequences;

#3. 부하를 일으키기 전에 별도의 터미널 창을 열어서 대기 이벤트를 볼 준비를 하시오

select  sid, event
   from gv$session_wait
   where  event  not  in ( 'ASM background timer',
'DIAG idle wait',
'GCR sleep',
'PING',
'PX Deq: Execute Reply',
'PX Deq: Execution Msg',
'SQL*Net message from client',
'Space Manager: slave idle wait',
'Streams AQ: qmn coordinator idle wait',
'Streams AQ: qmn slave idle wait',
'Streams AQ: waiting for time management or cleanup tasks',
'VKTM Logical Idle Wait',
'class slave wait',
'gcs remote message',
'ges remote message',
'pmon timer',
'rdbms ipc message',
'smon timer',
'wait for unread message on broadcast channel')
      order  by event  asc;

1번 터미널

2번 터미널 대기 이벤트 확인

enq: SV - contention

SV enqueue 가 발생한다는 것은 sequence 의 옵션 설정 중 order flag 를 noorder 가 아니라 order 로 했을 때 발생하는 대기 이벤트이다.

1번 노드와 2번 노드가 서로 시퀀스 번호 관련해서 통신하면서 대기하고 있다는 뜻이다.

처방 : 시퀀스의 파라미터 속성을 no order 로 변경해서 해결한다.

문제1. 다시 한번 부하를 일으키는데 이번에는 부하 일으키기 전에 사진 한번 찍고 부하 일으킨 후에 사진 한번 찍고 ADDM 레포트를 떠서 오라클이 권장하는 처방전을 출력 하시오.

사진 찍고 - @exec - 사진찍음

이거 두개 비긴 엔딩 해서 addm 레포트 뜨기

사진 찍고
SQL#1 > exec dbms_workload_repository.create_snapshot;
부하주고
SQL#1 > @exec // sq_enqueue 그다음 다 엔터

다른 터미널 가서 @event 실행

사진찍고
SQL#1 > exec dbms_workload_repository.create_snapshot;

SQL#1> @?/rdbms/admin/addmrpt.sql

Action
Investigate the cause for high "enq: SV - contention" waits in Service
"SYS$USERS"

처방전: SV enqueue 가 발생하면서 database 가 느리다면 시퀀스의 속성을
order 에서 noorder 로 변경을 해주면 됩니다.

방법:

SQL#1> alter   sequence  seq_sq_enqueue   noorder ;

SQL#1>  select  sequence_name,  cache_size, order_flag
               from   user_sequences;

▣ 예제49. RAC 환경에서 발생하는 SQ enqueue 성능 문제 해결하는 방법3

( cache 사이즈가 작고 + noorder 일때 )

SQ enqueue 는 메모리에 캐쉬된 시퀀스 번호가 다 소진되어서 없어서 database 에서 새롭게 시퀀스 번호를 생성해서 메모리에 올리려고 할 때 확보해야하는 LOCK 이다.

SQ enqueue 를 확보한 세션만 시퀀스 번호를 생성해서 메모리에 올릴 수 있고 나머지는 전부 SQ enqueue 를 획득하지 못해서 waiting 하게 된다.

해결 방법 : cache 사이즈를 늘려준다.

■ 실습

#1. (터미널창1) seq_sq_enqueue 시퀀스의 속성을 확인합니다.

SQL#1 > connect owi/owi

SQL#1 > select sequence_name, cache_size, order_flag
from user_sequences;

#2. (터미널창1) seq_sq_enqueue 의 속성중 cache 사이즈를 2로 지정합니다.

SQL#1 > alter sequence seq_sq_enqueue cache 2;
SQL#1 > select sequence_name, cache_size, order_flag
from user_sequences;

#3. (터미널창2) 대기 이벤트를 모니터링하는 스크립트를 수행합니다.

SQL#1 > @event

#4. (터미널창1) 시퀀스 관련한 부하를 준다

SQL#1 > @exec

sq_enqueue

나머지 다 엔터

#5. 부하 후 (터미널창2) 대기 이벤트를 모니터링하는 스크립트를 수행합니다.

시퀀스의 캐쉬 사이즈가 너무 작아서 sq_enqueue 로 경합을 벌이고 있다.

문제 1. 사진을 한번 찍고 addm 레포트를 생성해서 해결 방법을 알아내시오 !

SQL#1 > @snap 
SQL#1 > @?/rdbms/admin/addmrpt.sql

문제2. ADDM 레포트의 처방대로 cache 사이즈를 늘리는데 일단 50으로 늘리시오

SQL#1 > connect owi/owi

SQL#1 > alter sequence SEQ_SQ_ENQUEUE cache 50;

SQL#1 > select sequence_name, cache_size, order_flag
		from user_sequences;

세션 50개 까지는 SQ_ENQUEUE 안일어난다.

문제3. 다시 부하를 아까와 똑같이 일으키고 SQ enqueue 가 뜨는지 모니터링 하시오 !

SQL#1 > snap
SQL#1 > @exec

sq_enqueue

나머지 다 엔터

두번째 터미널에서
SQL#1 > @event

   370 enq: SQ - contention 에서

   374 jobq slave wait 로 바뀌면

SQL#1 > @snap

설명 SQ enqueue 가 떴지만 아까보다 금방 사라짐

면접 질문 : 튜닝 후에 성능이 더 좋아졌다는 것을 어떻게 알 수 있나요 ?

답변 : awr 비교 레포트를 생성해서 튜닝 전과 후를 비교해 보았더니 SQ enqueue 에 대한 경합이 줄어든 것을 확인했습니다.

비교 레포트 생성하는 스크립트는 ?

SQL#1> @?/rdbms/admin/awrddrpt.sql

테스트 순서:    
 1. 사진 찍고
 2. 부하일으키고
 3. 사진 찍고
 4. 튜닝
 5. 사진찍고
 6. 부하일으키고
 7. 사진찍고

▣ 예제50. RAC 환경에서 많이 발생하는 HW enqueue 의 원인과 해결 방법

HW enqueue :
High Water Mark enqueue 의 약자

HWM 를 올리려면 이 enqueue 를 확보해야 올릴 수 있다.

High Water Mark 가 무엇 입니까 ?

답변 : 포멧된 디스크와 포멧되지 않은 디스크의 경계선입니다

데이터를 insert 하려면 포멧된 디스크가 있어야 합니다.
파란 부분 : 포멧된 디스크
HWM 윗 부분이 포멧되지 않은 디스크부분.

동시에 같은 테이블에 insert를 하는 세션이 다수가 있으면 그 중에 하나의 insert 문의 세션만 HW enqueue 를 확보해서 HWM 를 위로 올릴 수 있고 나머지 세션들은 모두 HW enqueue 로 waiting 하게 됩니다.

이런 HW enqueue 가 RAC 환경에서는 더 자주 발생합니다.

양쪽 노드에서 여러 세션들이 동시에 같은 테이블에 데이터를 입력하는 경우가 있기 때문에 하나의 인스턴스에서 보다
여러 노드일 때 발생할 확률이 높아진다.

insert 가 이루어지는 테이블이 속한 테이블 스페이스의 segment 관리 방법을 automatic segment space management 방식으로 설정해야 합니다 (기본 세팅)
bump_high_water_mark_count 파라미터의 숫자값을 늘립니다.

bump_high_water_mark_count : HWM 를 올릴 때 한번에 포멧할 수 있는 블럭의 수.

■ 실습

#1. t_log_buffer_space 테이블이 속한 테이블 스페이스의 segment 속성이 auto 인지 manual 인지 확인합니다.

segment space management 가 auto 이면 오라클이 알아서 HW enqueue 가 발생하지 않도록 bump_high_water_mark_count(수)를 자동 조절한다.

SQL#1 > select table_name, tablespace_name
		from user_tables
		where table_name='T_LOG_BUFFER_SPACE';
SQL#1 > select  tablespace_name,  segment_space_management
               from  dba_tablespaces
               where  tablespace_name='OWI_TBS';

#2. 테이블 스페이스를 하나 만드는데 segment space 관리를 manual 로 해서 생성 하시오

SQL#1 > create tablespace ts77
		datafile '+data' size 10m
		segment space management manual;

#3. T_LOG_BUFFER_SPACE 테이블을 ts77 테이블 스페이스로 이동 시킵니다.

SQL#1 > alter table T_LOG_BUFFER_SPACE
		move tablespace ts77;
SQL#1 > select  tablespace_name, table_name
               from  user_tables
               where lower(table_name)='t_log_buffer_space';