6장

▣ 예제 35. RAC 환경에서의 튜닝 요소

CPU 시간 : 컴퓨터가 일하는 시간 서비스시간 sql 튜닝하면 해결 됨.
대기 시간 : 안하고 기다리는 시간 오라클 not service

대기 시간 때문에 응답시간이 느려짐.

설명: database 를 튜닝 한다는것은 ? 응답 시간을 줄이는 것입니다.

     reponse  time =  cpu  time   +  wait  time 
     ( 응답 시간 )      (서비스 시간)    (대기 시간)

cpu time 이 높은것은 SQL튜닝으로 어느 정도 해결할 수 있지만
wait time 이 높은것은 instance 튜닝 또는 rac 튜닝을 해야합니다.

instance 튜닝 : 파라미터를 수정해서 성능 개선을 해 나가는것 입니다.

응답시간이 느리다는 것은 서비스 시간이 느리기 보다는 대기한 시간이 느린게 더 영향이 큽니다.

튜닝의 목표 : wait time(대기 시간) 을 줄이는것

• RAC 환경에서 튜닝 요소 3가지 ?

  1. instance recovery 튜닝
  2. interconnect traffic 튜닝
  3. global 동기화 작업에 대한 경합 튜닝

정리: rac 환경에서 db 튜닝을 한다는것은 SQL 튜닝만 해서는 해결 안되는 다른 여러 문제들을 해결하는것 입니다.

▣ 예제36. RAC 환경에서 instance recovery 튜닝

★ instance recovery 란 ?
갑자기 db 가 비정상적으로 shutdown 이 되었을때 다시 startup 할 때 복구하는 작업

현업에서 instance recovery 가 일어나는 경우 ?

 db 에 과도한 DML 작업을 수행해서 db 가 먹통이 되어버렸습니다. 
 dba 가 어쩔 수 없이 db 를 shutdown  abort 해야합니다. 
 다시 startup 하면 복구하느라고 시간을 다 잡아먹습니다. 
 이 복구하는 시간이 instance recovery 시간 입니다. 

 그래서 이 먹통이 되는 복구 시간을 빠르게  끝내는게 instance recovery 튜닝입니다.

그림 설명
1번 노드에 과도한 DML 작업이 들어와서 1번 인스턴스가 먹통이 되면
어쩔 수 없이 dba 가 shutdown abort 로 내리게 되는데 이때
2번 노드에서 1번 인스턴스에 대한 instance recovery 가 발생합니다.
그러면서 1번 노드도 접속이 안되게 되고 2번 노드도 instance recovery 작업이 cpu 를 100% 쓰면서 느려지게 됩니다.

그래서 이를 대비하는 일이 바로 instance recovery 튜닝 입니다.

■ 실습

#1. 1번 노드는 sys 유져로 접속합니다.
#2. 2번 노드는 alert logfile 을 모니터링합니다.
#3. 1번 노드에서 shutdown abort 를 하고 2번 노드에서 instance recovery 하는지
살펴봅니다.

▣ 예제37. RAC 의 instance recovery 파라미터 튜닝하기

RAC 환경에서 instance recovery 튜닝을 하기 위해서 반드시 알아야할 파라미터 2개

1. 히든 파라미터 _fast_start_instance_recovery_target

 GRD 가 재구성되는 시간을 결정하는 파라미터 

 GRD(Global  Resource  Directory) :  내가 검색하고자하는 최신 데이터가 
                                                어느 노드에 있는지 위치정보가 있는 저장소

설명: 1번 인스턴스가 비정상적으로 종료되면 2번 인스턴스에서 1번 인스턴스가
가지고 있던 GRD 정보가 갱신됩니다. 그 시간을 결정하는 파라미터가
_fast_start_instance_recovery_target 입니다.

2. fast_start_mttr_target : instance recovery ( rollforward + rollback ) 에 걸리는 시간기본값은 자동입니다. 오라클이 알아서 이 시간을 결정합니다. 그런데 수동으로 이 시간을 셋팅할 수 있습니다. 10분이라고 셋팅하면 오라클에 복구하는데 걸리는 시간을 10분으로 셋팅합니다. 그리고 10분을 맞춰주기 위해서 평상시 dml 이 많으면 바로바로 메모리의 내용을 disk 로 반영합니다.
   현장에서 히든 파라미터는 잘 안건들이고 주로 fast_start_mttr_target 를 회사 사정에 맞춰서 세팅해 놓는다.

■ 실습

#1. 인스턴스 리커버리에 걸리는 시간을 10분으로 지정합니다.

show parameter fast_start_mttr_target

alter system set fast_start_mttr_target=600 scope=both sid='*';

show parameter fast_start_mttr_target

#2. GRD 를 재구성하는 시간은 오라클에서 변경하라고 권장하지 않으므로 확인만 합니다.

col "Parameter" format a40
col "Session Value" format a15
col "Instance Value" Format a15

select a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and a.ksppinm like '%¶m_name%';

결과가 출력되지는 않습니다.

문제1. RAC 환경에서 instance recovery 에 걸리는 시간을 15분으로 지정하시오 !

alter system set fast_start_mttr_target=900 scope=both sid='*';

※ 잦은 instance recovery 의 긴 복구 시간으로 문제가 될 때 위와 같이 설정하면 복구 시간을 미리 예상할 수 있어서 유용합니다.

▣ 예제38. RAC 튜닝 중 interconnect traffic 튜닝

interconnect 의 부하로 인해서 성능이 느려지는 원인이 발생하지 않도록 튜닝

1. 인터 커넥트 하드웨어를 비싼걸로 사용한다.

• 1gb 비트 이더넷 : 초당 125mb 전송속도 보장
• 10gb 비트 이더넷
• 100gb 비트 이더넷(인피니 밴드) <--- 요즘 rac 19c 구현하는 곳은 대부분 사용

2. innterconnect ip 가 private ip 로 셋팅되어있는게 맞는지 확인해야합니다.

   가끔 public ip 로 셋팅해놔서 느려지는 경우가 종종 있습니다.

• RAC 에서 사용하는 IP 주소 4가지 ?

1. public ip : 외부에서 rac 로 접속할 때 사용하는 ip
2. private ip : interconnect 를 통해 통신할 때 사용하는 ip
3. virtural ip : failover 를 할 때 사용하는 ip
4. scan ip : 스캔 리스너가 사용하는 ip

■ 실습

#1. interconnet ip 가 private ip 가 맞는지 확인하기

$ oifcfg getif

eth0 10.0.2.0 global public
eth1 192.168.56.0 global cluster_interconnect

$ oifcfg iflist -p -n

eth0 10.0.2.0 PRIVATE 255.255.255.0
eth1 192.168.56.0 PRIVATE 255.255.255.0
eth1 169.254.0.0 UNKNOWN 255.255.0.0

#2. 디버깅을 이용해서 interconnect ip 가 private ip 가 맞는지 확인하는 방법

$ cat  /etc/hosts 

### Public
10.0.2.15  rac1
10.0.2.16  rac2
### Private
192.168.56.111  rac1-priv
192.168.56.112  rac2-priv
### Virtual
10.0.2.111  rac1-vip
10.0.2.112  rac2-vip
### SCAN
10.0.2.120  rac-scan

SQL#1> oradebug setmypid

SQL#1> oradebug ipc

SQL#1> oradebug tracefile_name

SQL#1 > oradebug tracefile_name
/u01/app/oracle/diag/rdbms/racdb/racdb1/trace/racdb1_ora_29483.trc

$ vi /u01/app/oracle/diag/rdbms/racdb/racdb1/trace/racdb1_ora_5241.trc

SKGXPGPID Internet address 169.254.215.245 UDP port number 24545, mask 16

11gR2 부터는 인터커넥트 아이피가 /etc/hosts 의 private ip와 다르게 나옵니다.
11gR2 부터 제공하는 HAIP 를 사용하고 있다고 보면됩니다.

169.254.215.245 가 interconnect 아이피가 맞는지를 확인해보면됩니다.

select  inst_id, name, ip_address, is_public
  from   gv$cluster_interconnects
  order  by  inst_id  asc;

설명: interconnect 아이피가 public ip 가 아니다라는 것을 확인을 했습니다.

#3. MTU 사이즈를 확인
MTU ? 하드웨어적으로 한번에 네트워크를 통해서 전송할 수 있는 패킷의 최대크기 이 값이 1500 이거나 그 이상으로 되어있는지 확인해야 합니다.

$ netstat -i

#4. LMS 프로세서의 친절도(NICE) 를 확인

LMS 프로세서 : 노드 간의 데이터 전송하는 프로세서

친절도(NICE) : CPU 를 다른 프로세서에게 양보하는 친절도 (-20 ~ 20 까지 세팅 되어 있음)

[oracle@racdb1 ~]$ ps -ef | grep lms

root 로 접속해서 친절도를 -20 으로 변경

#renice -20 -p 5123

#top -p 5123

점심시간 문제 ora_lms1_racdb1 의 프로세서의 친절도를 -20 으로 변경하시오 !

정리 : interconnect traffic 튜닝의 4가지 요소는 ?

1. 인터 커넥트의 하드 웨어가 인피니밴드로 되어있는지 확인
2. 인터커넥트 아이피 주소가 private ip 또는 HAIP 로 되어있는지 확인
3. MTU 사이즈 1500 으로 되어 있는지
4. LMS 프로세서의 친절도를 낮추게끔 설정

▣ 예제 39. global 동기화 작업에 대한 경합 튜닝의 큰 개요

PDF 참조

1. rac 개념 그림

노드 사이의 인터커넥트에 스위치가 있는데 이 스위치의 역활을
하나의 노드가 죽었을때 다른 살아있는 노드로 세션들을 failover 시키는
물리적 하드웨어 장치입니다.

2. RAC 구성 요소

스토리지가 3가지중에 하나로 구성할 수 있는데 현재 19c RAC 를 사용하는
많은 회사들이 ASM 을 스토리지로 사용하는 회사들이 많아졌습니다.

3. RAC 의 글로벌 동기화를 위한 서비스 3가지

1. GCS :  노드간의 데이터 전송하는 서비스 , LMS 프로세서가 담당
2. GES :  노드간의 락을 관리하는 서비스, LMD 프로세서가 담당
3. CGS :  노드간의 클러스터 그룹 서비스, LMON 프로세서가 담당

이 서비스들로 인해서 우리가 RAC 노드중에 어느 노드로 접속하던지 간에 항상 최신 데이터를 볼 수 있는것이고 내가 접속한 노드가 다운되면 나도 모르게 살아있는 노드로 다시 접속하게 되게 됩니다.

■ 실습

#1. LMS 프로세서가 노드마다 몇개씩 떠있는지 확인합니다.

$ ps -ef | grep lms

#2. LMD 프로세서가 노드마다 몇개씩 떠있는지 확인합니다.

$ ps -ef | grep lmd

#3. LMON 프로세서가 노드마다 몇개씩 떠있는지 확인합니다.
$ ps -ef |grep lmon

#4. LMS 프로세서의 개수를 정하는 파라미터를 확인하시오

SQL#1 > show parameter gcs

인터커넥트로 활발하게 데이터와 메세지가 전달되는 상황의 서버이고
그래서 rac 가 느리다면 이 파라미터를 늘려줘야 합니다.

#5. gcs_server_processes 의 개수를 3개로 늘려서 LMS 프로세서의 개수를 3개로 만드시오

SQL#1> alter system set gcs_server_processes=3 scope=spfile sid='*';

SQL#1> shutdown immediate
SQL#2> shudown immediate

SQL#1> startup
SQL#2> startup

[oracle@racdb1 ~]$ ps -ef |grep lms

문제 1. LMS 프로세서의 개수를 다시 2개가 되게 하시오

SQL#1> alter system set gcs_server_processes=2 scope=spfile sid='*';

SQL#1> shutdown immediate
SQL#2> shudown immediate

SQL#1> startup
SQL#2> startup

이 gcs_server_processes 의 갯수는 서버의 사양에 따라 자동으로 결정됩니다.
일부러 lms 프로세서의 갯수를 늘려야한다면 이 파라미터를 수정하면 됩니다.
이게 RAC 튜닝입니다.

11g 버전 기준으로는 36개까지 LMS 프로세서의 개수를 늘릴 수 있습니다.

글로벌 락을 관리하는 LMD 프로세서는 노드 당 1개씩만 유지할 수 있습니다.

▣ 예제40. RAC 의 핵심 원리인 cache fusion 에 대한 설명

면접 질문 : RAC 를 배우셨던데 rac 의 cache fusion 에 대해서 설명해주실 수 있으신가요?

cache fusion : request node, master node, holder node 가 interconnect 를 통해 block 또는 메세지를 교환하는 메커니즘

★ cache fusion 을 이해하기 위해 먼저 알아야할 용어 ?

1. request node : 마스터 노드에게 블럭 전송을 요청하는 노드

2. master node : 요청하는 데이터에 대한 위치정보가 있는 GRD 가 있는 노드

3. holder node : 요청하는 데이터에 대한 실제 데이터 블럭을 가지고 있는 노드

4. null mode : 락을 거는 건데 내가 확보한 빈 메모리 공간을 누구도 덮어쓰지
   못하게 거는 락

5. shared mode : 비어있는 상태는 아니고 데이터가 있는 상태인데 그 누구도
   덮어쓰지 못하게 거는 락

6. exclusive mode : 데이터가 update 가 된 상태의 버퍼에 거는 락입니다.

cache fusion 의 예제 6가지

cache fusion 이란 내가 어느 노드로 접속하든 관계 없이 항상 최신 데이터를 볼 수 있게 해주는 rac 메커니즘.

1. initial read with no transfer
2. read to read with transfer
3. read to write with transfer
4. write to read with transfer (commit 이후)
5. write to read with transfer (commit 이전)
6. write to write with transfer

■ 실습 1. initial read with no transfer

1. 실습 환경을 먼저 조성합니다.

   SQL#1> drop table rac_test;

   SQL#1> create table rac_test
   ( id number );

   SQL#1> insert into rac_test values(1);

   SQL#1> commit;
   

   SQL#1> select dbms_rowid.rowid_relative_fno(rowid) as fno,
   dbms_rowid.rowid_block_number(rowid) as blkno
   from rac_test;
   

     FNO      BLKNO
---------- ----------
        1      94513  <--- rac_test 테이블의 파일 번호와 블럭번호 알아내는 명령어 

SQL#1> select b.lock_element_addr, b.status, e.mode_held, e.local
from v$bh b, v$gc_element e
where b.file#=1 and b.block#=94513
and b.lock_element_addr=e.gc_element_addr;

   status    mode_hold    local 
   xcur   <---------------------------   exclusive 모드 
   scur   <----------------------------   shared  모드  
   no row select  <------------------------ null 모드  

SQL#1> alter system flush buffer_cache;
SQL#2> alter system flush buffer_cache;

1. initial read with no transfer

2. SQL#1> @rac_test.sql

   no rows selected <-- null mode

3. SQL#1> select * from rac_test;

4. SQL#1> @rac_test.sql

설명: 1번 노드가 공유 디스크에서 데이터를 직접 읽어들이고 shared mode 로 락을 보유하고 있는 상태입니다.

2. read to read with transfer

SQL#1> @rac_test.sql

LOCK_ELEMENT_ADD STATUS      MODE_HELD      LOCAL
---------------- ---------- ---------- ----------
000000008FFA1028 scur                1          1

SQL#2> select * from rac_test;

SQL#2> @rac_test

LOCK_ELEMENT_ADD STATUS      MODE_HELD      LOCAL
---------------- ---------- ---------- ----------
000000008EF97E18 scur                1          1

SQL#1> @rac_test

LOCK_ELEMENT_ADD STATUS      MODE_HELD      LOCAL
---------------- ---------- ---------- ----------
000000008FFA1028 scur                1          1

문제. 다음 캐쉬퓨젼 메커니즘 3번의 경우를 실험하시오 !

3. read to write with transfer

SQL#1> alter system flush buffer_cache;

SQL#1> @rac_test

no row select

SQL#2> select * from rac_test;

SQL#2> @rac_test

LOCK_ELEMENT_ADD STATUS      MODE_HELD      LOCAL
---------------- ---------- ---------- ----------
000000008FFA1028 scur                1          1

SQL#1> update rac_test
set id = 10;

SQL#1> @rac_test

SQL#2> @rac_test 하면

no rows selected 나옴

설명: 1번 노드에서 update 를 수행했기 때문에 rac_test 블럭에 대해서 독점적인(exclusive)
락이 획득됩니다. 이 독점적인 락은 모든 노드에 딱 1개에만 존재합니다.
그리고 나머지 노드는 null 모드로 다운그레이드 되어집니다.

※ 1번 노드가 commit 을 안했기 때문에 2번 노드가 계속해서 rac_test 테이블을 조회해도 null 모드입니다.

SQL#2> select * from rac_test;

        ID
----------
         1

SQL#2> @rac_test

no rows selected <--- null 모드란 애기는 계속해서 select 할 때마다 전송받아야된다는 것입니다.

※ RAC 환경에서는 특히 commit 을 빼먹으면 안됩니다.
계속 노드간의 데이터 전송이 빈번해지면 rac 성능이 느려집니다.

4. write to read with transfer (commit 이후)

SQL#2> select * from rac_test;

SQL#2> @rac_test.sql

SQL#2> select * from rac_test;

SQL#2> @rac_test.sql

설명: 11g 이후 부터는 _FAIRNESS_THRESHOLD 가 2 이므로 2번만 요청하면 바로 2번 shared mode 가 됩니다.

select a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and a.ksppinm like '%¶m_name%'

SQL#2> @h.sql

왜 ????????
1번 노드가 exclusive 로 메모리를 잡았다는 것은 값비싼 작업
달라고하면 바로 안준다...
형평성을 위해서 한번 더 요청하면 주겠다
shared 모드로 내려오고 업그레이드 된다

5. write to read with transfer (commit 이전)

설명: 1번 노드가 rac_test 데이터를 update 를 하고 commit 을 안했습니다. 그런데 2번 노드가 계속해서 rac_test 데이터를 select 를 합니다. 그러면 2번 노드는 rac_test 테이블의 변경 이전 데이터를 봐야합니다. 그래서 만약 dbwr 가 cr buffer 를 data file 의 undo tablespace 에 안내려셨다면 cr buffer 가 전송이 되어지고 cr buffer 에 있는 이전 데이터를 2번 노드가 select 를 합니다. 그런데 만약 dbwr 가 cr buffer 를 undo tablespace 에 내려썼으면 cr buffer 를 2번 노드에 전송하고 직접 undo tablespace에서 데이터를 읽어서 cr buffer 에 적용하고 select 를 합니다.

※ RAC 에서는 자주 commit 를 수행하는게 바람직 합니다.

오늘의 마지막 문제

write to read with transfer (commit 이전) 을 실험 하시오

2번 노드에서 아무리 rac_test 테이블을 select 해도 계속 null 모드 인 것을 화면 캡쳐해서 올리세요

SQL#1> alter system flush buffer_cache;
SQL#2> alter system flush buffer_cache;

SQL#1> update rac_test
set id = 10;

SQL#1> @rac_test.sql

SQL#2> select from rac_test;
SQL#2> @rac_test.sql
SQL#2> select from rac_test;
SQL#2> @rac_test.sql
SQL#2> select * from rac_test;
SQL#2> @rac_test.sql

설명: 1번 노드가 commit 을 안했기 때문에 2번 노드는 계속해서 rac_test 테이블을
조회해도 null mode 인 것입니다.

※ RAC 환경에서는 특히 commit 을 빼먹으면 안됩니다.
null 모드라는 것은 자기 노드에 데이터를 보유하지 못하고 계속 요청해서
받아야한다는 의미입니다.