0327 RAC

■ 6. write to write with transfer

SQL#1 > @flush
SQL#2 > @flush

SQL#1 > update rac_test set id=9000;

SQL#1 > commit;

SQL#2 > update rac_test set id=7500;

SQL#1 > @rac_test
SQL#2 > @rac_test

▣ 예제41. RAC 환경에서 대기 이벤트

대기 이벤트 : 오라클이 성능이 느리면 왜 느린지 원인을 알려주는 신호

대기 이벤트 종류 확인하기

select name from v$event_name;

11g 에는 1367개에 해당하는 대기 이벤트가 있다

우리 회사 database 가 왜 느린지 확인하는 방법

select sid, event
from v$session_wait
order by event asc;

rdbms ipc message <--- 건강하다는 뜻입니다.

■ 실습

#1. putty 창을 2개를 열고 각각 scott 유져로 접속해서 다음과 같이
update 를 합니다.

반드시 다른 putty 창을 열고 SYS 유져에서 아래의 쿼리를 조회합니다.

select sid, event
from gv$session_wait
where event like '%enq%'
order by event asc;

   248 enq: TX - row lock contention
   en                queue 
   enterance     행렬(메모리) 
   TX  transaction

select decode(status,'INACTIVE',username || ' ' || sid || ',' || serial#,'lock') as Holder,
       decode(status,'ACTIVE',  username || ' ' || sid || ',' || serial#,'lock') as waiter, sid, serial#, status
    from( select level as le, NVL(s.username,'(oracle)') AS username,
    s.osuser,   
    s.sid,
    s.serial#,
    s.lockwait,
    s.module,
    s.machine,
    s.status,
    s.program,
    to_char(s.logon_TIME, 'DD-MON-YYYY HH24:MI:SS') as logon_time
       from gv$session s
      where level>1
                or EXISTS( select 1
    from gv$session
    where blocking_session = s.sid)
      CONNECT by PRIOR s.sid = s.blocking_session
  START WITH s.blocking_session is null);

#2. 위의 상황을 해결하시오

SYS > alter system kill session '129,67' immediate;

@enq

다시 해보기

SYS> @enq

SYS > alter system kill session '366,613,@1' immediate;

※ rac 환경에서는 single instance 와는 다르게 @인스턴스 번호를 써줘야합니다.

이렇게해서 tx enqueue 로 인한 성능 문제를 해결했습니다.

※ dba 를 위한 팁 !

dba 가 성능 문제를 해결하는 순서 !

1. v$session_wait 를 조회해서 지금 어떤 이유로 오라클이 느린지
   주로 많이 나오는 대기 이벤트를 보면서 파악합니다.

2. 대기 이벤트에 대한 해결방법을 오라클 문서에서 조치방법을
   찾습니다.

3. 조치하고 문제를 해결합니다.

▣ 예제 42.

▣ 예제 43. gc buffer busy 대기 이벤트를 일으키고 해결하기

buffer busy wait 대기 이벤트
여러 세션들에 의해서 동시에 하나의 버퍼의 데이터를 동시에 갱신하려고 할 때 buffer lock 를 못잡아서 발생하는 대기 이벤트

tx enqueue : 같은 행을 갱신하려고 할 때 발생하는 대기 이벤트

buffer busy wait

동시에 같은 블락 안에 있는 행을 갱신할 때 (수강신청을 예로)
버퍼 락을 잡은 사람만 락 함 - 누구도 갱신 못하게 막아버림 - 이 선 좌
현업에서 자주 발생함.
첫번째가 업데이트를 끝내면 두번째 프로세서가 업데이트 하고 나머지는 기다림
순차적으로 풀려 나감.

■ 실습

0. 2번 인스턴스를 shutdown immediate 로 내립니다.

1. owi 로 접속해서 수동으로 사진을 찍는다.

$ sqlplus owi/owi

SQL> exec dbms_workload_repository.create_snapshot;

설명 : 인스턴스의 성능 정보를 AWR repository 에 수집합니다.

2. owi 유져에서 아까 일으켰던 buffer busy wait 대기 이벤트를 일으킵니다

   SQL#1> @exec
   

select sid, event
 from gv$session_wait
where  event like '%gc buffer busy%' 
or  event  like '%buffer busy wait%';

한번 더 반복

3. 쉬는 시간 끝날때 사진을 다시 찍습니다.

SQL> exec dbms_workload_repository.create_snapshot;

4. awr report 를 생성합니다.

SQL> @?/rdbms/admin/awrrpt.sql

현재 상황에 대한 성능 보고서를 출력합니다

AWR 레포트는 특정 기간에 DB 에 발생한 성능 이슈를 확인하는 레포트 입니다
그래서 전체적으로 어떤 대기 이벤트가 많이 일어났으며 악성 SQL 은 어떤것인지 파악할 수 있습니다.
해결방법은 나오지 않아서 DBA 가 직접 해결방법을 알 고 있어야합니다.
그런데 아래의 addm 레포트는 해결방법을 알려줍니다.

5. addm report 를 생성합니다. (처방전이 출력됩니다.)

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

Recommendation 1: Schema Changes
Estimated benefit is .98 active sessions, 16.43% of total activity.

---

Action
Consider rebuilding the TABLE "OWI.T_BUFFER_BUSY_WAITS" with object ID
87475 using a higher value for PCTFREE.

해결 방법에 대한 설명

PCTFREE parameter : block parameter
PCTFREE 늘려서 해결했습니다 !

pctfree 는 update 를 위한 공간입니다. 그래서 이 공간에는 insert 할 때 데이터가 입력되지 않습니다. 블럭안에 데이터가 더 크게 변경이 될 때 이 pct free 영역에 update 를 합니다.

그런데 ADDM 레포트의 해결 방법을 보면 PCT FREE 를 늘려야 buffer busy wait 을 덜 발생하게 할 수 있다고 나와있습니다.

1. pct free 를 안늘렸을 때

block 의 buffer lock 은 한개 !!!! - 만 업데이트 가능 !!!
buffer lock을 잡은 첫번째 업데이트 문만 업데이트가 되고
buffer busy wait으로 대기하게 됨.

2. pct free 를 늘렸을 때

pct free가 커서 블럭마다 데이터가 한건씩 입력된 상태 입니다. 그렇게 되면 블럭마다 하나 씩 있는 버퍼락을 4개의 세션이 모두 잡을 수 있게 되었으므로 buffer busy wait 으로 대기하는 세션이 사라지게 됩니다. 단점이 있다면 공간을 많이 차지 합니다.

pct free 를 늘리고 블락이 많아짐 -buffer busy wait 해결
단점 : 업데이트를 위한 비어있는 공간이 너무 많아짐 비용이 많이 들겠져 ?

■ 실습

#1. 문제가 되고 있는 테이블을 백업

create table t_buffer_busy_waits_backup
as select * from t_buffer_busy_waits;

#2. 문제가 되고 있는 테이블의 데이터를 truncate

truncate table t_buffer_busy_waits;

#3. 문제가 되고 있는 테이블의 pctfree 를 늘림

select table_name, pct_free
from user_tables
where lower(table_name)='t_buffer_busy_waits';

10% 의 여유 공간이 있다 !

alter table t_buffer_busy_waits pctfree 99;

#4. 백업 받은 데이터를 문제가 되고 있는 테이블에 입력
t_buffer_busy_waits_backup

insert into t_buffer_busy_waits
select * from t_buffer_busy_waits_backup;

#5. 사진을 찍는다

#6. buffer busy wait 대기 이벤트를 일으킨다

@exec

▣ 예제 44. gc buffer busy 대기 이벤트 일으키고 해결하기

gc buffer busy : buffer busy wait 이라는 대기 이벤트가 rac 환경에서 global 하게 발생하는 경우에 나타나는 대기 이벤트.

설명 : 1번 노드에서 2번 노드로 전송이 되고 있는 데이터를 update 하는 경우에 gc buffer busy 대기 이벤트가 발생합니다. 이 경우는 1번 노드와 2번 노드의 각각 접속 세션들이 같은 블럭의 데이터를 update 하려고 시도 하는 경우 입니다.

■ 실습 1. OWI 패키지 초기화 하기.

#1. sys 유져로 접속해서 owi 유져를 drop 하기

SYS > drop user owi cascade;

#2. owi_tbs tablespace 를 drop 하기

SYS > drop tablespace owi_tbs including contents and datafiles;

■ 실습 2. gc buffer busy 대기 이벤트를 일으키는 실습

#1. 2번 노드를 올립니다

SQL#2 > statrtup

#2. owi 패키지를 sys 유져에서 설치 합니다.

@install

#3. gc buffer busy 대기 이벤트를 일으킨다

@exec

#4. 사진을 수동으로 찍습니다

@snap

#5. awr 레포트를 생성

@?/rdbms/admin/awrrpt.sql

#6. addm 레포트를 생성

@?/rdbms/admin/addmrpt.sql