PURPOSE
 부정형의 비교를 긍정형 비교로 바꾸어서 인덱스 사용을 유도하는 방법에 대해서 알아본다.

KEY IDEA
 부정형의 비교에는 논리적으로 인덱스를 사용할 수 없다. 하지만 약간의 IDEA를 첨부한다면
부정형의 비교를 긍정형의 비교로 바꾸어서 인덱스의 사용을 유도할 수 있다.
(KEY WORD : INDEX 활용, 인덱스, 부정형 비교, NOT IN, NOT EXISTS, <> )

DESCRIPTION
   다음의 SQL을 보자.
     SELECT ‘Not found’  FROM EMP WHERE EMPNO <> ‘1234’

• 대개의 Application에서는 사용자가 처리한 데이터의 타당성을 검증하기 위해 이 값의
  존재 유무를 확인하는 경우가 빈번하게 발생한다.  
  이럴 경우 위의 예처럼 부정형의 문장을 사용하는 경우가 자주 있다.
  
• 하지만 아래와 같이 ‘NOT EXISTS’를 이용해서 서브쿼리(SUB-QUERY)내의 SQL을 긍정형으로 바꾸면 인덱스를 사용할 수 있다.
  
   SELECT ‘NOT FOUND’ FROM DUAL
      WHERE NOT EXISTS ( SELECT ‘X’ FROM EMP WHERE EMPNO = ‘1234’ )
  
  
• 그러나 ‘EXISTS’를 사용하는 것이 항상 유리한 것은 아니다. 다음의 3개의 SQL을 보자.
  
  [SQL1]
    SELECT * FROM TAB1
       WHERE YYYYMM = ‘199910’
           AND NOT EXISTS ( SELECT * FROM TAB2
                                     WHERE COL2 = COL1
                                         AND YYYYMM = ‘199910’ )
  [SQL2]
    SELECT * FROM TAB1
        WHERE YYYYMM =’199910’
            AND COL1 NOT IN (SELECT COL2 FROM TAB2
                                         WHERE YYYYMM = ‘199910’ )
  [SQL3]
     SELECT * FROM TAB1
         WHERE (YYYYMM, COL1) IN ( SELECT ‘199910’, COL1 FROM TAB1
                                                     WHERE YYYYMM = ‘199910’
                                                 MINUS
                                                 SELECT ‘199910’, COL2 FROM TAB2
                                                      WHERE YYYYMM = ‘199910’ )
  
  
• TAB1 테이블의 ‘YYYYMM’, ‘COL1’이 각각 인덱스로 생성되어 있고
  TAB2의 ‘YYYYMM’, ‘COL2’가 각각 인덱스로 생성되어 있다.
   
• [SQL1] 은 ‘TAB1’의 ‘YYYYMM’ 인덱스만을 사용하여 테이블의 로우를 엑세스하고
  각 로우마다 TAB2 테이블을 엑세스하는 서브쿼리가 수행되어 TAB2 에 존재하지 않는
  로우만 추출하게 된다. 이 SQL은 ‘199910’조건에 해당하는 모든 로우에 대해 서브
  쿼리가 랜덤엑세스를 수행한다. 왜냐하면 서브쿼리내에 메인쿼리의 컬럼인 'COL1'이
  존재하기 때문이다.
   
• [SQL2] 는 서브쿼리 내에 메인쿼리 컬럼을 없애기 위해 작성하였지만 동일한 결과를
  초래한다. 그 이유는 'NOT IN'을 사용한 서브쿼리는 항상 나중에 수행되거나
  필터링(Filtering) 조인방식으로 수행되기 때문이다.
   
• [SQL3]은 각 테이블에 ‘YYYYMM + COL1’, ‘YYYYMM + COL2’의 결합인덱스가 존재한
  다면 먼저 서브쿼리에서 두 개의 테이블을 ‘MINUS’하여 결과를 추출하고 그 결과를
  이용해 메인쿼리를 엑세스하게 할 수 있다. 이 경우에는 서브쿼리가 먼저 수행된다.
  인덱스만으로도 처리가 가능하기 때문에 테이블을 엑세스하지 않고 양쪽 테이블의
  인덱스들만 범위스캔(Range Scan)하여 ‘SORT-MERGE’방식으로 서브쿼리가 처리 된다.
   
• 위의 경우에서는 결과적으로 [SQL3]가 가장 유리한 처리방법이라 하겠다.
  

참고 : http://kdonghwa.tistory.com/58?srchid=BR1http%3A%2F%2Fkdonghwa.tistory.com%2F58

* 인덱스를 타지 않는 SQL
1. 인덱스 컬럼 절의 변형
2. 내부적인 데이터 변환
3. NULL 조건의 사용
4. 부정형 조건의 사용
5. LIKE 연산자 사용
6. 최적기가 판단

1 - 인덱스 컬럼 절의 변형
SQL> select ename from emp where sal * 2.1 > 950 --인덱스 사용불가
SQL> select ename from emp where sal > 950 /2.1  --인덱스 사용가능
SQL> select ename from emp where to_char(hiredate,'DDMMYY') = '250884' --인덱스 사용불가
SQL> select ename from emp where hiredate = to_date('250884','DDMMYY') --인덱스 사용가능

-> 인덱스 컬럼에 변형을 가하면은 사용할수 없습니다. 단 변형 가능하더라도 쓰고 싶다면은
    말리지는 않겠지만 create index .... on emp to_char(hiredate,'DDMMYY') 이렇게 하시면 됩니다.

2 - 내부적인 데이터 변환
SQL> select * from emp where hiredate ='14-JAN-85' --인덱스 사용불가
SQL> select * from emp hiredate = to_date('71-10-22','YY/DD/DD') --인덱스 사용가능
SQL> select * from emp where empno = '7936' --인덱스 사용불가
SQL> select * from emp where empno = to_number('7936') --인덱스 사용가능

-> 내부적인 데이터변환에서 가장 많이 실수하는 부분은 문자값 데이터타입을 갖는 컬럼에
    '값' -> 값 이렇게 하시는분이 많습니다. 딱맞는 데이터타입을 주세요 ^_^

3 - NULL 조건의 사용
SQL> select ename from emp where comm is null --인덱스 사용불가
SQL> select ename from emp where comm is not null --인덱스 사용불가
SQL> select ename from emp where ename > '' --인덱스 사용가능
SQL> select ename from emp where comm >= 0 --인덱스 사용가능

-> NULL조건으로 검색한다는 가정하에는 거의 인덱스 풀 스캔이 일어나겠죠. 적절히 사용합씨다.

4 - 부정형 조건의 사용
SQL> select ename from emp where deptno != 30 --인덱스 사용불가
SQL> select ename from emp where deptno < 30 and deptno > 30 --인덱스 사용가능

-> 논리적으로 부정형을 이용하여 인덱스를 사용하겠다는것은 말이 안되죠...
    이 쿼리문도 적절히 사용합씨다.

5 - Like 연산자 사용
SQL> select * from emp where ename like 'S%' --인덱스 사용가능
SQL> select * from emp where ename like '%S%' --인덱스 사용불가

-> %S% 부분을 꼭 쓰고싶다면은 이렇게 하세요 앞에부분을 다 넣는거죠. AS%, BS%...
    요즘 홈피를 보면 본문 찾기는 거의 없어져가고 있죠. 엔코아 경우 주제어를 검색을..

6 - 최적기가 판단

-> RBO경우 무조건 타죠 있으면은 이눔은 워낙 법을 좋아해서리..CBO는 통계값을 기준으로
    하기 때문에 DBA가 잘 해야겠죠. 그럼 우리가 판단하게 할라면은 HINT를 써서 이눔들을
    인도해야죠... 오늘도 전도를 ^____^


마지막으로 인덱스를 이용한 sort 회피 방법에 대해서 알아보겠습니다.
SQL> select empno, ename, job, comm from emp order by empno --sort 정렬
SQL> select empno, ename, job, comm from emp where empno >= 0 --sort 정렬제거

-> 인덱스는 값이 정렬되어 들어가있는 구조이기 때문에 처음부터 작은값 맨 끝값은
    최대값이 있겠죠. 잘 사용하면 최고의 튜닝이 되겠죠.

출처 : http://www.bysql.net/index.php?document_srl=18171

1. 10046 Event가 갖지 못한 DBMS_XPLAN.DISPLAY, DBMS_XPLAN.DISPLAY_CURSOR의 장점

2. DBMS_XPLAN

3. DBMS_XPLAN.DISPLAY_CURSOR

• Function Parameter
• FUNCTION DISPLAY_CURSOR(SQL_ID VARCHAR2 DEFAULT NULL,
                          CURSOR_CHILD_NO INTEGER DEFAULT 0,
                          FORMAT VARCHAR2 DEFAULT 'TYPICAL')
• 파라미터 설명
  

파라미터	설명
SQL_ID	실제 수행된 SQL의 SQL_ID 값을 입력하면 해당 SQL의 실행계획을 볼 수 있다.
	SQL_ID 값은 V$SQL, V$SQLAREA에서 확인할 수 있다.
	SQL_ID 값을 명시하지 않으면 해당 세션의 마지막에 실행된 문장을 의미한다.
CHILD_NUMBER	해당 SQL_ID의 CHILD NUMBER 값을 지정한다.
	CURSOR_CHILD_NO 값을 명시하지 않으면 해당 SQL_ID의 첫번째 CURSOR_CHILD_NO 값을 가져온다.
FORMAT	저장된 PLAN을 어떤 Format으로 보여줄 지 결정하는 파라미터

• FORMAT 옵션
  

  구분	FORMAT	내용
  예측 내용	BASIC	가장 기본적인 정보만 보여준다.
  	TYPICAL	FORMAT의 DEFAULT 값인 TYPICAL은 SQL 투닝에 필요한 NORMAL한 정보를 보여 준다. SQL 튜닝에 가장 유용하게 사용되는 PREDICATE INFORMATION이 제공된다.
  	ALL	TYPICAL  FORMAT에 QUERY BLOCK NAME과 COLUMN PROJECTION INFORMATION이 추가로 제공된다.
  	OUTLINE	TYPICAL FORMAT에 추가적으로 HIDDEN HINT인 OUTLINE GLOBAL HINT를 제공한다.
  	ADVANCE	ALL FORMAT에 OUTLINE FORMAT를 합친 정보를 제공한다.
  실측 내용	ALLSTATS	실제 엑세스한 로우수와 수행시간, CP, PR, PW 정보를 보여준다.
  		수행횟수에 따라 누적된 값을 보여준다.
  	ALLATATS LAST	실제 엑스스한 로우수와 수행시간, CP, PR, PW 정보를 보여준다.
  		마지막에 수행된 값을 보여준다.
  	ADVANCED ALLSTATS LSAT	DBMS_XPLAN.DISPLAY_CURSOR에서 지원하는 모든 FORMAT의 정보를 보여준다.

4. DBMS_XPLAN.DISPLAY_CURSOR 사용방법

• PLAN 정보 기록
• 'GATHER_PLAN_STATISTICS' HINT 사용

  SELECT /*+GATHER_PLAN_STATISTICS*/                  *   FROM (SELECT E.*           FROM EMPLOYEE E          WHERE E.DEPARTMENT_ID = 50          ORDER BY E.EMPLOYEE_ID)  WHERE ROWNUM <= 100

• STSTISTICS_LEVEL를 ALL로 설정
• '_ROWWOURCE_EXECUTION_STATISTICS' 파라미터를 TRUE로 설정\
• PLAN 정보 조회

  SELECT *    FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ADVANCED ALLSTATS LAST'))

  
  

5. Plan 상의 항목들에 대한 설명

1) Basics 항목

• Id : 각 Operationd의 ID임. *가 달려있는 경우는 Predicate Informatio에 Access 및 Filter에 관한 정보를 표시함
• Operation : 각각 실행되는 JOB
• Name : Operationdl 엑세스 하는 Table 및 Index

2) Query Optimizer Estimations 항목(예상치)

• E-Rows : 각 Operation이 끝났을 때 return 되는 건수.
• E-Bytes : 각 Operation이 Temporany Space를 사용한 양
• Cost(%CPU) : 각 Operation의 Cost. 괄호 안의 내용은 CPU Cost의 백분율임. 이 값은 Child Operation의 Cost를 합친 누적치.
• E-Time : 수행시간

3) Runtime Statistics 항목

• Starts : 각 Operation을 반복 수행한 건수
• A-Rows : 각 Operation이 Return 한 건수
• A-Time : 실제 실행시간. 0.01초까지 나타남(HH:MM:SS.FF).  Child Operation의 A-Time을 합친 누적치

4) I/O Statistics

• Buffers : 각 Operation이 memory에서 읽은 Block 수.
• Reads : 각 Operation이 Disk에서 Read한 Block 수.
• Writes : 각 Operation이 Disk에서 White한 Block 수.

5) Memory Utilization Statistics(hash 작업이나 sort 작업 시 사용한 메모리 통계)

• OMen : Optimal Execution에 필요한 Memory
• SQL 실행 메모리가 최적의 크기를 가졌을때의 메모리. 여기서 메모리가 최적의 크기를 갖는다는 것은 예를 들어, disk에 write하지 않고 sort 작업을 수행하는 경우를 의미한다.
• 1Mem : One-pass Execution에 필요한 Momory
• SQL 실행 메모리가 1 pass의 크기를 가졌을 때의 메모리. 여기서 메모리가 1 pass의 크기를 갖는다는 의미는, 예를 들어 sort의 경우 disk에 임시 결과를 한번은 저장하고 결과를 merge해서 sort 작업을 마치는 경우를 의미한다.
• O/1/M : 각 Operation이 실행한 Optmal/One-pass/Multipass 횟수가 순서대로 표시됨
              O 일 경우 메모리공간(hash_area_size)이 부족하지 않아 temp 영역(disk)을 
              사용하지 않고 처리 되었다는 의미임. 
• multipass 횟수 : SQL 실행 메모리가 2 pass 이상의 크기를 가졌던 횟수.
• Used-mem : 마지막 실행 시 사용한 PGA -Memory
• Used-Tmp : 마지막 실행 시 메모리가 부족하여 Temporary Space를 대신 사용할 때 나타남. 보이는 값에 1024를 곱해야 함.
• Max-Tmp : 메모리가 부족하여 Temporary Space를 사용할 때 최대 Temp 사용량. Used-Tmp와 다른 점은 마지막 수행시가 아니라 SQL을 여러 번 수행했을 경우에 항상 최대값만 보인다는 것. 보이는 값에 1024를 곱해야 함.

6) 쿼리블록 정보 : Plan 상의 Id별로 쿼리블럭 및 Alias 정보를 출력.,

7) Outline Date : 오라클이 내부적으로 사용한 힌트.

8) Predicate Information : Plan 상의 Id 별로 인덱스 액세스, Filter, 조인정보 등을 표출

9) Column Projection Information : Plan 상의 Id 별로 Select 되는 컬럼의 정보.

6. 쿼리 변형이 없는 단순 쿼리 튜닝의 경우는 최대한 단순화.

• Format : 'allstats last -rows +predicate'로 설정
• 예측 Row 수(E-row) 생략. 실행통계와 Predicate Information만 출력

7.쿼리 변형이 발생하거나 복잡한 쿼리 튜닝시 쿼리블럭과 힌트정보를 추가로 출력

• Format : 'allstats last -rows +alias +outline +predicate'로 설정

• Query Block Name / Object Alias 정보와 Outline Data 정보가 추가로 출력
• +alias : 쿼리블록 추가
• +outline : 오라클리 내부적으로 사용한 힌트정보를 출력

Current Page: http://www.bysql.net/index.php?document_srl=14752   <<==출처

http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams157.htm 

PGA는 각각의 프로세스에서 개별적으로 사용되는 메모리 영역을 말한다. 이 PGA는 크게 아래와 같이 4개로 나누어져 있다. 

9i부터 PGA 메모리 영역의 크기를 auto와 manual 두가지 방법으로 관리한다.
AUTO으로 관리한다면 PGA_AGGREGATE_TARGET 파라미터를 설정해야 하고 manual 으로 한다면  *_AREA_SIZE  파라미터 값을 설정해야 한다.

AUTO ( PGA_AGGREGATE_TARGET  )

PGA_AGGREGATE_TARGET는 PGA 메모리 관리를 Auto 로 했을 때 사용되는 파라미터이다. 

주의 

 
AUTO 로 관리될 경우 PGA_AGGREGATE_TARGET 에 설정 크기내의 가용 메모리 내에서 PGA 크기가 자동으로 조정된다.
말이 좀 어려운데  PGA_AGGREGATE_TARGET는 생성되는 모든 PGA가 사용 할 수 있는 총 메모리 크기이다. 그러므로 예를 들면 PGA_AGGREGATE_TARGET =10M 일때 현재 1mb를 사용중인 Process 가 2개가 있다면 현재 가용 메모리 양은 8MB 가 된다.

만약 크기를 정하지 않으면 기본값은 10Mb 와 SGA 크기의 20%중 큰 값으로 설정된다.

 예를들어 만약  PGA_AGGREGATE_TARGET 이 50MB 이고 현재 PGA크기가 5MB 인 서버프로세스가 10MB 작업공간이 필요한 정렬작업을 수행시 자동으로 10MB의 메모리를 할당받게 되어 메모리 정렬이 일어나게 된다.


MANUAL ( SORT_AREA_SIZE ) 

 이전 버전에서는 SORT_AREA_SIZE 를 설정하여 각 서버 프로세스별로 동일한 크기의 PGA 를 할당하여 사용했었다. 이 방법은 관리가 간단한 반면 메모리 사용이 비효율적일 수 있다. 
 각 PGA는 SORT_AREA_SIZE 크기 만큼의 정렬공간을 할당받는다. 예를 들어 SORT_AREA_SIZE가 5M일 때 10MB 의 공간이 필요한 정렬 작업이라면 디스크I/O가 발생하게 된다. 


주의 사항
 분명 WORKAREA_SIZE_POLICY=AUTO 방법으로 사용하는 것이 융통성이나 효율면에서 이점이 있는것이 분명하다. 하지만 주의 할 점은  PGA_AGGREGATE_TARGET=100M 인데 어떤 서버 프로세스의 PGA에서 100MB 할당 받아버리면 다른 사용자가 접속할 수 없게 된다. 그러므로 이를 위해 잘 파악하고 사용해야 한다.




현재 PGA 메모리 사용량과 최대 메모리 사용량 조회

-

-

위의 결과에서 highlight 된 부분이 Server Process 이다. 

각 컬럼에 대해 설명하자면 
PGA_USED_MEM : 프로세스가 현재 사용하는 PGA 크기.
PGA_ALLOC_MEM : 프로세스에 할당된 PGA 크기
- 다른 프로세스에서 필요로 할 경우  PGA_USED_MEM  를 제외한 나머지 공간을 반환한다. 즉  PGA_ALLOC_MEM  만큼 할당되어 있지만 현재는  PGA_USED_MEM  만 사용중이고 남은 메모리는 반환되지 않았음을 의미)
PGA_MAX_MEM : 프로세스가 가장 많이 사용했을 때의 크기

PGA(Program Global Area) 관리

PGA는 오라클 서버 프로세스에 의해 생성되며 DB에 접속하는 모든 사용자에게 할당되는 오라클 메모리 영역인데 아래에서 이 영역을 관리하는 방법에 대해 알아 보겠습니다.

우선 PGA를 구성하는 영역에 대해 알아보면… Sort Area(Order Bu, Group By의 경우에 사용), Session Information(서버 프로세스에 의해 추출된 결과값을 클라이언트의 사용자 프로세스에 전달하기 위해 필요한 사용자 프로세스의 정보 저장), Cursor State(SQL의 파싱 정보 저장), Stack Space(Bind 변수를 저장)로 구성되는데 이를 관리하기 위해서는

다음과 같은 파라미터의 설정이 필요 합니다.

WORKAREA_SIZE_POLICY : manual 또는 auto의 값이 들어올 수 있는데 auto인 경우 PGA_AGGREGATE_TARGET 파라미터를 이용하여 PGA 크기를 설정하겠다는 의미이며 manual인 경우 SORT_AREA_SIZE 설정을 통해 소트 영역을 설정하겠다는 의미 입니다.

SORT_AREA_SIZE : 정렬을 위한 공간을 설정

PGA_AGGREGATE_TARGET : 모든 사용자 세션이 사용할 총 PGA 크기를 지정. 즉 이 크기만큼 모든 사용자 세션이 사용할 수 있으며 이는 오라클서버에서 자동으로 관리 됩니다.

이전 오라클 버전에서는 SORT_AREA_SIZE 크기를 설정하여 세션별로 지정된 크기의 PGA를 할당하였는데 9i 및 10g에서는 세션별로 정해진 PGA의 크기가 아닌 전체 세션에 할당될 총 PGA SIZE를 PGA_AGGREGATE_TARGET을 통해 설정하게 되어 있는것이죠…

DB에 한 사용자가 접속하였고 이때 SORT_AREA_SIZE는 10M 였다고 가정할 때 사용자가 50M되는 테이블을 정렬한다고 할 때 WORKAREA_SIZE_POLICY가 manual인 경우라면 SORT_AREA_SIZE 10M를 넘어서는 경우이므로 TEMP Tablespace를 사용하여 정렬을 하게 될 겁니다(SORT가 메모리에서 일어나면 좋지만 공간이 부족하여 디스크에서 일어난다면 성능에 문제가 있을 수 있습니다). 이 경우엔 5번정도 DISK IO가 일어나므로 성능에 영향을 줄 수가 있겠죠… 그런데 WORKAREA_SIZE_POLICY를 auto로 설정하고 PGA_AGGREGATE_TARGET 파라미터를 최소 50M 이상 준다면 DISK IO 없이 충분히 메모리 SORT가 가능할 겁니다. 물론 여러 사용자가 있으니 PGA_AGGREGATE_TARGET의 크기는 충분히 고려되어야 할겁니다.

평상 시 사용되는 PGA의 크기는 v$session을 통해 조회할 수 있는데 간단히 보시면  PGA_USE_MEM을 통해 프로세스에서 현재 사용되는 PGA 크기를 알수 있으며 PGA_ALLOC_MEM 컬럼을 통해 프로세스에 할당된 PGA 크기, PGA_MAX_MEM 컬럼을 통해 프로세스가 사용한 최대 메모리 사용량을 알 수 있습니다.

감사합니다.
[출처] [Oracle강좌] PGA(Program Global Area) 관리 |작성자 오라클러

Latch: cache buffers chains

[편집] Basic Info

버퍼 캐시를 사용하기 위해 해시 체인을 탐색하거나 변경하려는 프로세스는 반드시 해당 체인을 관리하는 cache buffers chains 래치를 획득해야 한다. cache buffers chains 래치를 획득하는 과정에서 경합이 발생하면 latch: cache buffers chains 이벤트를 대기하게 된다.

오라클 9i 이후부터는 읽기 전용의 목적으로 체인을 탐색하는 경우에는 cache buffers chains 래치를 Shared 모드로 공유할 수 있어 경합을 줄이는데 도움이 된다. Shared 모드의 cache buffers chains 래치에 대해 한가지 주의할 점이 있다. 만일 cache buffers chains 래치를 공유할 수 있다면 이론적으로는 동시 Select에 의한 cache buffers chains 래치 경합은 전혀 발생하지 않아야 한다. 하지만, 실제로 테스트를 해보면 동시 Select인 경우에도 래치 경합은 여전히 발생한다. 그 이유는 Buffer Lock과 관련이 있다. 읽기작업을 위해서 Shared 모드로 래치를 획득한 경우, 실제 버퍼를 읽는 과정에서 Buffer Lock을 Shared 모드로 획득해야 하는데 이 과정에서 버퍼 헤더의 정보를 일부 변경해야 한다. 따라서 Buffer Lock을 획득하는 동안에는 래치를 Exclusive 모드로 변경해야 하고Buffer Lock을 해제하는 동안에도 래치를 Exclusive하게 획득해야 한다. 이 과정에서 경합이 발생하고 이로 인해 latch: cache buffers chains 이벤트를 대기하게 된다.

cache buffers chains 래치 경합이 발생하는 대표적인 경우는 다음과 같다.

• 비효율적인 SQL
• 핫블록(Hot Block)

[편집] 비효율적인 SQL

비효율적인 SQL문장이 cache buffers chains 래치 경합의 가장 중요한 원인이다. 동시에 여러 프로세스가 넓은 범위의 인덱스나 넓은 범위의 테이블에 대해 스캔을 수행할 경우 cache buffers chains 래치 경합이 광범위하게 발생할 수 있다.

cache buffers chains 래치 경합이 발생한 경우 경합의 원인을 정확하게 밝히는 것이 중요하다. 래치 경합이 핫블록에 의해 발생하는지 비효율적인 SQL문장에 의해 발생하는지를 판단하는 가장 중요한 근거는 SQL문장 자체이다. SQL문장 자체가 비효율적으로 작성되어 있는 것으로 판단할 수 있는 근거가 확실하다면 SQL문장을 튜닝함으로써 문제를 해결할 수 있다.

만일, SQL문장에 대한 정보가 없다면 간접적으로 핫블록에 의한 문제인지 비효율적인 SQL문장에 의한 문제인지 판단할 수 있는 방법이 있다. V$LATCH_CHILDREN 뷰에서 자식 cache buffers chains 래치에 해당하는 CHILD# 과 GETS, SLEEPS 값을 비교하여 특정 자식 래치에 사용하는 회수와 경합이 집중되는지 판단하는 것이다. 다음 명령문을 이용해서 SLEEPS 회수가 높은 자식 래치를 얻는다.

SQL>
select * from
(select child#, gets, sleeps from v$latch_children 
          where name = 'cache buffers chains'
          order by sleeps desc
) where rownum <= 20

만일 특정 자식 래치의 GETS, SLEEPS 값이 다른 자식 래치에 비해서 비정상적으로 높다면 해당 래치가 관장하는 체인에 핫블록이 있는 것으로 추측할 수 있다. 위의 테스트를 수행한 후의 결과는 다음과 같은데 특정 래치에 대한 편중 현상이 보이지 않으므로 핫블록에 의한 문제는 없다고 판단할 수 있다.

    CHILD#       GETS     SLEEPS
---------- ---------- ----------
       329      81160         78
       940      79773         74
       232      62792         69
       791      99123         68
       905      99185         68
       408      80687         65
       259     101793        62
       611      82187         62
       466      99870         60
       839      79744         60
       ...

핫블록 여부를 판단하는 또 다른 방법은 V$SESSION_WAIT 뷰로부터 래치의 주소를 얻어서 비교하는 것이다. cache buffers chains 래치의 경우 V$SESSION_WAIT.P1RAW가 자식 래치의 주소에 해당한다. 만일 V$SESSION_WAIT 뷰로부터 얻은 래치의 주소가 과다하게 중복해서 나타난다면 해당 래치에 대한 편중이 생긴다는 의미이며, 이 경우 핫블록에 의한 경합으로 해석할 수 있다.

[편집] 핫블록(Hot Block)

SQL 문장이 적절히 튜닝되었음에도 불구하고 cache buffers chains 래치의 경합이 해결이 되지 않는 경우가 있다. SQL 문의 작동방식이 소수의 특정 블록을 계속해서 스캔하는 형태로 작성되었다면, 여러 세션이 동시에 이 SQL 문을 수행하는 경우 핫블록에 의한 cache buffers chains 래치 경합이 발생한다.

V$LATCH_CHILDREN 뷰를 통해 특정 자식 래치 사용이 편중되어 있는지 확인해 보면 핫블록에 의한 래치 경합인지를 간접적으로 판단할 수 있다. 또는, V$SESSION_WAIT 뷰의 P1RAW 컬럼값을 캡쳐해서 반복적으로 관찰되는 값을 래치 주소로 이용해도 된다.

SQL> 
select * from
(select addr, child#, gets, sleeps from v$latch_children 
        where name = 'cache buffers chains'
order by sleeps desc
) where rownum <= 20
;

ADDR                 CHILD#       GETS     SLEEPS
---------------- ---------- ---------- ----------
C0000000CDFF24F0        569   10500275      11298  <-- 래치 사용 집중
C0000000CE3ADDF0        827    5250508       8085
C0000000CDF18A98        178    5250192       4781
C0000000CDEDB6E8         68       3786             17
C0000000CE3CBEE0        881       2121              8
C0000000CE359430        675       1768               1
C0000000CDEB6230           1        235               0
C0000000CDEB6B18           2        171               0
C0000000CDEB7400           3        390               0
C0000000CDEB7CE8          4        192               0
C0000000CDEB85D0          5        151               0
...

위의 결과에서 보면 child# 569, 827, 178 세 개의 자식 래치가 집중적으로 사용되고 있으며 이에 의해 래치 경합이 발생한 것을 확인할 수 있다. X$BH 뷰를 이용하면 정확하게 어떤 블록들이 핫블록인지 확인할 수 있다.

SQL> 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 
    ('C0000000CDFF24F0', 'C0000000CE3ADDF0', 'C0000000CDF18A98')
order by hladdr, obj;

HLADDR                         OBJ    OBJECT_NAME    DBARFIL     DBABLK   TCH
------------------------ ---------- ------------------ ------------- ------------- ------
C0000000CDF18A98         57          OBJAUTH$               1         43911       1
C0000000CDF18A98         73           IDL_UB1$               1         27025       1
C0000000CDF18A98        181 C_TOID_VERSION#          1         26792       1
C0000000CDF18A98        181 C_TOID_VERSION#          1         14244       1
…
C0000000CDF18A98      55236  CBC_TEST_IDX             4          45919   130
…
C0000000CDFF24F0      55236   CBC_TEST_IDX             4         45918    130
C0000000CE3ADDF0            2                  IND$             1           7933        1
C0000000CE3ADDF0            2                  IND$             1          60455       9
C0000000CE3ADDF0          18                  OBJ$             1          29623       1
...
C0000000CE3ADDF0     55236   CBC_TEST_IDX            4              100    130

X$BH 뷰로부터 1) 사용자 객체(Table, Index)에 해당하며, 2) 접촉 회수(Touch Count)가 높은 블록을 기준으로 핫블록을 추출할 수 있다. 위의 조회결과를 보면 CBC_TEST_IDX 인덱스의 45918, 45919, 100 블록에서 대부분의 경합이 발생하는 것을 확인할 수 있다.

[편집] Parameter & Wait Time

[편집] Wait Parameters

latch free 대기이벤트와 동일하다.

[편집] Wait Time

latch free 대기이벤트와 동일하다.

[편집] Check Point & Solution

[편집] 비효율적인 SQL 문을 튜닝한다.

비효율적인 SQL 문장을 튜닝해서 Logical Reads를 줄이면 자연스럽게 버퍼 캐시에 대한 액세스가 줄어들고 그만큼 cache buffers chains 래치 경합도 감소한다.

[편집] 핫블록을 분산한다.

핫블록에 의한 cache buffers chains 래치 경합인 경우에는 핫블록을 분산함으로써 경합을 감소시킬 수 있다. 핫블록을 분산시키는 방법은 다음과 같다.

• PCTFREE를 높게 주거나 작은 크기의 블록을 사용함으로써 블록 경합을 줄인다. PCTFREE를 높게 주는 것과 작은 크기의 블록을 사용하는 것은 하나의 블록에 포함되는 로우수를 줄임으로써 블록 경합을 피한다는 점에서 기본적으로 동일한 방법이다. 이 방법은 확실히 블록 경합을 줄이는 효과가 있지만 그 만큼 관리해야 할 블록수가 늘어남으로써 다른 사이드 이펙트를 초래할 수 있다. 가령 블록수가 늘어남으로써 동일한 쿼리가 훨씬 더 많은 블록을 스캔해야 하기 때문에 성능 저하 현상이 생기게 된다. 즉 핫블록에 의한 래치 경합은 줄어들지만, 늘어난 스캔 회수만큼 다시 래치 경합이 증가할 수도 있다. 따라서 테스트에 의한 검증없이 적용하는 경우 일반적인 가이드와는 전혀 다른 결과를 낳을 수도 있다. 경합 해소를 위한 일반적인 가이드를 그대로 따르는 것은 때로는 위험할 수 있기 때문에 반드시 실제 데이터를 이용해 테스트를 수행하는 것이 바람직하다.
• 파티셔닝(Partitioning) 기법을 사용해서 로우가 물리적으로 다른 블록으로 들어가게끔 한다. 이 기법을 사용하면 문제가 되는 로우들이 자연스럽게 물리적으로 다른 블록에 분산시킴으로써 래치 경합을 피할 수 있다. 하지만, 테이블에 대해서 이 방법을 적용할 경우에는 인덱스의 클러스터링 팩터(Clustering Factor)를 악화시킬 수 있으며, 이로 인해 인덱스범위스캔에 의한 데이터 스캔 속도가 저하될 수도 있다. 따라서 파티셔닝을 적용할 때도 사이드 이펙트(Side-effect)를 충분히 고려해야 한다.
• 문제가 되는 블록의 로우들에 대해서만 삭제 후 재삽입 작업을 한다. 이 방법은 테이블에 대해서만 가능하다. 문제가 되는 블록들과 해당 블록에 포함된 로우 들의 ROWID를 정확하게 알 수 있다면 해당 로우를 삭제한 후 재삽입해서 각 로우가 다른 블록에 흩어지게 할 수 있다. 블록 덤프와 DBMS_ROWID 패키지를 이용하면 핫블록에 속한 ROWID를 알 수 있다. 이 방법은 테이블 속성을 변경하지 않는 가장 이상적인 방법이라고 할 수 있다. 하지만 핫블록이 고정되어 있지 않고 SQL문의 조건(Where …)에 따라 매번 바뀐다면 적용할 수 없다. 또한 인덱스에 대해서는 이 방법을 사용할 수 없다.

테이블에서의 cache buffers chains 래치 경합은 해결하기가 비교적 쉬운 편이다. 로우를 분산시킬 수 있는 방법이 매우 다양하기 때문이다. 하지만, 인덱스에서의 경합 문제는 상당히 까다롭다. 정렬된 상태로 저장된다는 인덱스의 고유 특성 때문에 임의의 블록으로 분산시키는 것이 불가능한 경우가 있기 때문이다. 이 경우에는 PCTFREE를 높게 주거나 작은 크기의 블록을 사용하는 방식을 사용하는 것 외에는 뾰족한 대책이 없다. 하지만 앞서 설명한 것처럼, 이 경우에 블록의 개수가 늘어나게 되고 이로 인해 오히려 래치 경합이 줄어들지 않는 경우도 있으므로 적용 시에 유의해야 한다.

[편집] Event Tip

[편집] 버퍼 캐시 구조

오라클은 물리적인 I/O를 최소화하기 위해 최근에 사용된 블록에 대한 정보를 메모리의 일정 영역에 보관한다. 이 메모리 영역을 버퍼 캐시라고 부른다. 버퍼 캐시는 Shared Pool, 리두 버퍼(Redo Buffer)와 함께 SGA를 이루는 가장 중요한 메모리 영역 중 하나다. 아래 명령을 이용해 현재 인스턴스의 버퍼 캐시(Buffer cache)의 크기를 알 수 있다.

SQL> show sga

Total System Global Area  314572800 bytes
Fixed Size                   788692 bytes
Variable Size             144963372 bytes
Database Buffers          163577856 bytes
Redo Buffers                5242880 bytes

Database Buffers에 해당하는 값이 현재 인스턴스의 버퍼 캐시의 크기이다.

오라클은 버퍼 캐시를 효과적으로 관리하기 위해 해시 체인(Hash chain) 구조를 사용한다. 해시 체인은 Shared Pool 내에 존재하며 오라클의 전형적인 메모리 구조 관리기법인 버킷(Bucket) -> 체인(Chain) -> 헤더(Header) 의 구조를 사용한다. 이 구조는 아래 그림에 표현되어 있다.

그림 오라클 8i이상에서의 버퍼 캐시 구조도

해시 체인 구조의 시작점은 해시 테이블(Hash table)이다. 해시 테이블은 여러 개의 해시 버킷(Hash bucket)으로 이루어져 있다. 하나의 해시 버킷은 해시함수 결과와 매칭된다. 오라클은 블록의 주소(DBA: Data Block Address. File#와 Block#으로 이루어져 있음)와 블록 클래스에 대해 간단한 해시 함수를 적용한 결과를 이용해 해시 버킷을 찾아간다. 해시 버킷에는 같은 해시값을 갖는 버퍼 헤더(Buffer Header)들이 체인 형태로 걸려 있다. 버퍼 헤더는 버퍼(Buffer)에 대한 메타정보를 가지고 있으며, 버퍼 메모리 영역의 실제 버퍼에 대한 포인터 값을 가지고 있다. 해시 체인 구조는 Shared Pool 영역에 존재하며, 실제 버퍼에 대한 정보들은 버퍼 캐시 영역에 존재한다는 사실을 명심하자.

해시 체인 구조는 cache buffers chains 래치를 이용해 보호된다. 특정 블록을 스캔하고자 하는 프로세스는 반드시 해당 블록이 위치한 해시 체인을 관리하는 cache buffers chains 래치를 획득해야 한다. 기본적으로 한번에 하나의 프로세스만이 하나의 cache buffers chains 래치를 획득할 수 있으며 하나의 cache buffers chains 래치가 여러 개의 해시 체인을 관리한다. 따라서, 동시에 많은 수의 프로세스가 버퍼 캐시를 탐색할 경우 cache buffers chains 래치를 획득하는 과정에서 경합이 발생하며, 이 과정에서 latch: cache buffers chains 이벤트를 대기한다. 오라클 9i 부터는 읽기 전용의 작업에 한해서 cache buffers chains 래치를 Shared 모드로 획득한다. 따라서 동시에 읽기 작업을 수행하는 프로세스간에는 cache buffers chains 래치를 공유할 수 있다. 하지만 버퍼에 대해 Buffer Lock을 획득하거나 해제할 때 cache buffers chains 래치를 Exclusive하게 획득해야 하기 때문에 읽기 작업만 수행하는 경우에도 여전히 cache buffers chains 래치 경합은 발생한다.

다음과 같은 명령문으로 cache buffers chains 래치의 개수를 구할 수 있다.

SQL> select count(*) from v$latch_children where name = 
        'cache buffers chains';
  COUNT(*)
  ----------
       1024

또는 _DB_BLOCK_HASH_LATCHES 히든 파라미터값을 조회해도 같은 결과를 얻을 수 있다. 해시 버킷의 수는 _DB_BLOCK_HASH_BUCKETS 히든 파라미터값을 이용해 조회 가능하다.

[편집] Working Set

오라클은 버퍼 캐시를 효율적으로 사용하기 위해 두 종류의 LRU(Least Recently Used) 리스트를 사용한다. LRU 리스트는 가장 최근에 사용되거나 미사용된 버퍼들의 리스트로 프리(Free. 미사용) 버퍼, 사용 중이거나 사용된 버퍼, 아직 LRUW 리스트(Dirty List)로 옮겨지지 않은 더티(Dirty. 변경된) 버퍼 등을 포함한다. 일부 문서에서는 LRU 리스트를 대체 리스트(Replacement List)라고 부른다. LRUW 리스트는 아직 디스크에 기록되지 않은 변경된(Dirty한) 버퍼들의 리스트를 관리한다. 버퍼 캐시의 모든 버퍼들은 반드시 LRU 리스트 또는 LRUW 리스트 둘 중의 하나에 속한다. LRUW 리스트는 더티 리스트(Dirty List), 또는 기록 리스트(Write List)라고도 부른다.

오라클은 리스트 스캔의 효율성을 위해 LRU 리스트나 LRUW 리스트를 다시 메인 리스트(Main List)와 보조 리스트(Auxiliary List)로 나누어 관리한다. 이를 정리하면 다음과 같다.

LRU 리스트(대체 리스트)

• 메인 리스트 : 사용된 버퍼들의 리스트. 핫 영역과 콜드 영역으로 구분 관리된다.
• 보조 리스트 : 프리 버퍼들의 리스트. 더 정확하게 표현하면, 미 사용된 버퍼들이나, DBWR에 의해 기록된 버퍼들의 리스트

LRUW 리스트(기록 리스트)

• 메인 리스트 : 변경된 버퍼들의 리스트
• 보조 리스트 : 현재 DBWR에 의해 기록중인 버퍼들의 리스트

오라클은 프리 버퍼 탐색 시, 우선 LRU 리스트의 보조 리스트에서 프리 버퍼를 찾는다. 보조 리스트의 버퍼가 모두 사용된 경우에는, 메인 리스트의 콜드 영역에서 프리 버퍼를 찾는다. 인스턴스가 최초로 구동된 때는 모든 버퍼들은 보조 리스트에서 관리된다. 또한 변경된 버퍼들이 DBWR에 의해 기록된 후에는 다시 프리 버퍼로 바뀌며, LRU 리스트의 보조 리스트에 추가된다.

LRU 리스트 와 LRUW 리스트는 항상 짝(Pair)으로 존재하며, 이 짝을 Working Set이라고 부른다(즉 Working Set = LRU + LRUW). 오라클은 복수 개의 Working Set을 사용한다. 하나의 Working Set을 하나의 cache buffers lru chain 래치가 관리한다. LRU 리스트나 LRUW 리스트를 탐색하고자 하는 프로세스는 반드시 cache buffers lru chain 래치를 획득해야 한다. 따라서 동시에 많은 프로세스가 LRU 리스트나 LRUW 리스트를 탐색하고자 할 경우에 cache buffers lru chain 래치를 획득하기 위해 경쟁하게 되며 이 과정에서 latch: cache buffers lru chain 이벤트를 대기한다.

_DB_BLOCK_LRU_LATCHES 히든 파라미터의 값을 조회하거나, 다음 명령문을 이용해 cache buffers lru chain 래치의 최대 개수를 구할 수 있다.

SQL> select count(*) from v$latch_children where name = 'cache buffers lru chain'; 
 COUNT(*)
 ----------
         16

하지만 위의 래치를 다 사용하는 것은 아니다. 오라클에는 다양한 종류의 버퍼 풀이 존재하며 각 버퍼 풀들이 이들 래치를 골고루 사용한다. 첫째, 버퍼는 크게 Default 버퍼 풀, Keep 버퍼 풀, Recycle 버퍼 풀로 나누어 진다. 둘째, Default 버퍼 풀은 다시 블록 크기 별로 표준블록사이즈, 2K, 4K, 8K, 16K, 32K 버퍼 풀로 나누어 진다. 개개의 버퍼 풀은 각각 독립적인 cache buffers lru chain 래치를 사용한다. 따라서 래치의 최소 개수는 8개가 된다. 다음 명령문을 사용하면 어떤 래치가 어떤 종류의 버퍼에 대해 사용 중인지를 확인할 수 있다.

SQL> 
-- x$kcbwds=Working Set, x$kcbwbpd=Buffer pool, v$latch_children=Latch
select d.blk_size, c.child#, p.bp_name, c.gets, c.sleeps
from x$kcbwds d, v$latch_children c, x$kcbwbpd p
where
 d.set_latch = c.addr
 and d.set_id between p.bp_lo_sid and p.bp_hi_sid
order by c.child#
;

  BLK_SIZE     CHILD# BP_NAME                    GETS     SLEEPS
---------- ---------- -------------------- ---------- ----------
      8192          1 KEEP                            42         0
      8192          2 KEEP                            42         0
      8192          3 RECYCLE                      42         0
      8192          4 RECYCLE                      42         0
      8192          5 DEFAULT                   2337         0     <-- 실제 사용중
      8192          6 DEFAULT                   2322         0     <-- 실제 사용중
      2048          7 DEFAULT                      33          0
      2048          8 DEFAULT                      33          0
      4096          9 DEFAULT                      32          0
      4096         10 DEFAULT                     32          0
      8192         11 DEFAULT                     32          0
      8192         12 DEFAULT                     32          0
     16384         13 DEFAULT                    32          0
     16384         14 DEFAULT                    32          0
     32768         15 DEFAULT                    32          0
     32768         16 DEFAULT                    32          0

위의 결과를 해석하면 Keep 버퍼 풀에 대해 2개, Recycle 버퍼 풀에 대해 2개, 그리고 Default 버퍼 풀에 대해 블록 크기 별로 각각 2개씩 래치를 사용하는 것을 알 수 있다. 만일 Default 버퍼 풀에 8K 표준 크기의 버퍼 풀만 사용한다면 2개의 lru 래치 만을 사용하게 될 것이다. 최대 16개의 래치 개수는 CPU 개수로부터 유래된 것이다. 오라클은 DBWR의 개수가 4보다 작으면 4 * CPU_COUNT 만큼 lru 래치를 생성하고, DBWR의 개수가 4이상이면 DB_WRITER_PROCESSES * CPU_COUNT 만큼 lru 래치를 생성한다. 필자의 시스템에서는 CPU 개수가 네 개이므로 16개의 래치가 생성되었고 그 중 실질적으로 사용되고 있는 것은 8K 버퍼 풀에 할당된 두 개의 래치임을 알 수 있다. 단, 앞서 언급한 것처럼 버퍼 풀의 최소 개수가 8개이기 때문에 lru 래치의 최소 개수도 8개임에 유의하자.

[편집] 버퍼 캐시 LRU 리스트의 관리

서버 프로세스가 스캔하는 모든 버퍼들이 LRU 리스트에 등록되기 때문에 LRU 리스트를 효율적으로 관리하는 것이 매우 중요하다. 특히 불필요하게 많은 량의 블록을 스캔하는 프로세스에 의해 중요한 버퍼들이 버퍼 캐시에서 밀려나는 것을 최소화할 수 있어야 한다. 오라클은 8i 이후의 버전부터 LRU 리스트를 효율적으로 관리하기 위해 Touch count에 기반한 LRU 알고리즘을 사용한다. 이 알고리즘은 LRU 리스트의 메인 리스트를 관리하는데 사용된다. Touch count 기반의 LRU 알고리즘을 그림으로 표현하면 아래 그림과 같다.

Touch count 기반의 LRU 알고리즘은 다음과 같은 방식으로 작동한다.

1. LRU 리스트의 메인 리스트는 크게 핫 영역(Hot Region)과 콜드 영역(Cold Region)으로 나누어진다. 자주 사용되는 블록은 핫 영역에 머무르며, 사용빈도가 낮은 블록은 콜드 영역에 머무른다. 오라클은 개별 버퍼마다 Touch count(접촉 회수)를 관리하며, 프로세스에 의해 스캔이 이루어질 때마다 Touch count를 1씩 증가시킨다.
2. 프리 버퍼를 찾을 때는 우선 LRU 리스트의 보조 리스트에서 미사용된 버퍼를 찾는다. 만일 보조 리스트가 비어 있다면, 메인 리스트의 콜드 영역의 꼬리에서부터 프리 버퍼를 찾는다. 메인 리스트의 꼬리에 있으면서 Touch count가 1이하인 버퍼가 프리 버퍼로 사용된다. 프리 버퍼를 찾는 과정에서 Touch count가 2 이상인 블록을 만나면 핫 영역의 머리(Head of Hot Region)로 옮기고 해당 버퍼의 Touch count를 0으로 초기화시킨다. 핫 영역으로 옮기는 기준이 되는 값은 _DB_AGING_HOT_CRITERIA 히든 파라미터이며 기본값이 2이다.
3. 싱글 블록 I/O에 의해 읽혀진 블록은 Mid-point에 삽입되며 Touch count는 1의 값을 지닌다. Mid-point가 가리키는 위치는 콜드 영역의 머리(Head of Cold Region)이다. 싱글 블록 I/O에 읽혀진 블록은 콜드 영역의 머리에 위치함으로써 버퍼 캐시에 머무를 확률이 높아진다.
4. 멀티 블록 I/O에 의해 읽혀진 블록들은 Mid-point에 삽입된 후 콜드 영역의 제일 뒤(Tail of Cold Region)으로 옮겨진다. 풀테이블스캔(FTS)이나 인덱스풀스캔으로 읽힌 블록들은 콜드 영역의 꼬리에 위치함으로써 버퍼 캐시에 머무를 확률이 낮아진다.
5. Keep 버퍼 풀과 Recycle 버퍼 풀은 Default 풀과는 달리 영역의 구분이 불필요하므로 핫 영역을 가지지 않는다. Recycle 버퍼 풀은 핫 영역을 가지지 않는다는 점을 제외하면 Default 버퍼 풀과 완전히 동일한 방식으로 작동한다. 하지만 Keep 버퍼 풀의 경우에는 FTS로 읽히는 작은 크기의 테이블을 메모리에 상주시키기 위해 고안된 공간이기 때문에 멀티 블록 I/O로 읽은 블록들을 싱글 블록 I/O로 읽은 블록과 동일하게 콜드 영역의 제일 앞에 위치시키도록 구현되었다.

[편집] 버퍼 탐색 과정

오라클은 해시 체인과 LRU, LRUW 리스트를 적절히 사용해서 사용자가 요청한 블록을 버퍼 캐시로 위치시킨다. 그 절차 및 기법을 시간 순으로 정리하면 다음과 같다.

1. 사용자가 요청한 블록의 DBA와 클래스에 대해 해시 함수를 이용해서 해시 값을 생성하고 해시 값에 해당하는 해시 버킷을 찾는다.
2. 해시 버킷을 보호하는 cache buffers chains 래치를 획득한다. 읽기 작업이라면 Shared 모드로, 변경 작업이라면 Exclusive 모드로 래치를 획득한다. 만일 이 과정에서 경합이 발생하면 latch: cache buffers chains 이벤트를 대기한다. 해시 버킷에 딸려있는 체인을 탐색해서 블록에 해당하는 버퍼 헤더가 존재하는지 확인한다. 버퍼 헤더가 이미 존재하고 해당 블록이 버퍼 캐시에 올라와 있는 상태라면 해당 버퍼에 대해 Buffer Lock을 Shared 모드나 Exclusive 모드로 획득하고 원하는 작업을 수행한다. 일반적으로 Buffer Lock을 획득하는 과정에서 경합이 발생하면 buffer busy waits 이벤트를 대기하게 된다. DBWR에 의해 기록중인 버퍼에 대해 Buffer Lock을 획득하는 과정에서 경합이 발생하는 경우에는 write complete waits 이벤트를 대기한다. cache buffers chains 래치를 획득한 후 해시 체인을 탐색하고 버퍼를 사용하기 위해 Buffer Lock을 획득한 후 버퍼를 읽는 일련의 작업을 “Logical Reads”라고 부른다. Logical Reads가 발생한 블록 수만큼 session logical reads 통계 값이 증가한다. 만일 Logical Reads 작업이 일관된 모드의 읽기(Consistent read) 작업이라면 consistent gets 통계 값이 증가하고, 현재 모드의 읽기(Current read) 작업이라면 db block gets 통계 값이 증가한다. 따라서 session logical reads 통계 값은 consistent gets 통계 값과 db block gets 통계 값의 합과 일치한다.
3. 버퍼 캐시에 블록이 존재하지 않으면 우선 Working Set을 관리하는 cache buffers lru chain 래치를 획득한다. 이 과정에서 경합이 발생하면 latch: cache buffers lru chain 이벤트를 대기한다. 래치를 획득한 후 LRU 리스트의 보조 리스트에서 프리 버퍼를 찾는다. 보조 리스트가 비었다면, 메인 리스트에서 가장 덜 사용된 순서로 프리 버퍼를 찾는다. 이 과정에서 더티 버퍼가 발견되면 LRUW 리스트로 이동시킨다. 프리 버퍼를 찾게 되면 해당 버퍼에 대해 Buffer Lock을 Exclusive하게 획득하고 데이터 파일로부터 블록을 해당 버퍼로 읽어 들인다. 이때 Buffer Lock을 획득하는 과정에서 경합이 발생하면 read by other session 이벤트를 대기한다. 데이터 파일로부터 물리적으로 블록을 읽어 들이는 일련의 작업을 Physical Reads”라고 부른다. Physical Reads가 발생한 블록 수만큼 physical reads 통계 값이 증가한다. physical reads 통계 값은 direct path I/O 작업에서도 증가하기 때문에, 버퍼 캐시를 경유한 정확한 Physical Reads 값은 physical reads 통계 값에서 physical reads direct, physical reads direct(lob) 통계 값을 빼면 된다.
4. LRU 리스트에서 프리 버퍼를 찾을 때 _DB_BLOCK_SCAN_MAX_PCT(기본값은 40) 파라미터의 값만큼 스캔을 하고도 프리 버퍼를 찾지 못하면 서버 프로세스는 LRU 리스트의 스캔을 멈춘다. 서버 프로세스는 DBWR에게 더티 버퍼를 파일에 기록하고 프리 버퍼를 확보할 것을 요청한다. DBWR에 의해 프리 버퍼가 확보될 때까지 서버 프로세스는 free buffer waits 이벤트를 대기한다. 요청을 받은 DBWR은 DBWR make free request 통계 값을 증가시키고, cache buffers lru chain 래치를 획득한 후 LRUW 리스트를 콜드 영역의 꼬리에서부터 탐색한다. 디스크에 기록할 버퍼를 찾게 되면 Buffer Lock을 획득한 후 버퍼를 디스크에 기록한다. 디스크에 기록된 버퍼는 프리 버퍼로 변경되고 LRU 리스트로 옮겨진다. DBWR이 LRUW 리스트를 탐색할 때마다 DBWR lru scans 통계 값과 DBWR buffers scanned 통계 값이 증가한다.

[편집] Analysis Case

[편집] 1. 인덱스 튜닝에 의한 latch: cache buffers chains 대기 감소

latch: cache buffers chains 이벤트 발생 시나리오는 다음과 같다.

• t_cache_buffers_chains_latch(type,name,id) 테이블에는 16만건의 데이터가 입력되어 있으며, 이중 type=4, name=’KIM’을 만족하는 데이터는 15만건이다.
• idx_cache_buffers_chains_latch 인덱스는 type, name 두개의 컬럼으로 구성되어 있다.
• 10개의 세션이 동시에 (type=4, name=’KIM’, id=4)을 만족하는 데이터의 개수를 구하는 SQL을 idx_cache_buffers_chains_latch 인덱스를 사용하여 수행된다.
• 세션들은 수행중 내내 latch: cache buffers chains 이벤트를 대기한다.

위의 시나리오를 그림으로 표현하면 아래 그림 1과 같다. 그림 latch: cache buffers chains 이벤트 발생 시나리오

동시에 여러 프로세스가 위의 쿼리를 실행하는 과정을 모니터링한 결과는 아래 표와 같다. latch: cache buffers chains 이벤트 대기가 가장 심각하게 발생함을 알 수 있다.

표 모니터링 결과

RealTime Client의 Active Session List는 아래 그림 2와 같다. 대부분의 액티브 세션들이 latch: cache buffers chains 이벤트를 대기하고 있는 것을 확인할 수 있다.

그림 RealTime Client – Active Session List

위와 같은 같은 상황에서 latch: cache buffers chains 이벤트 대기가 발생하는 이유는 다음과 같다.

• idx_cache_buffers_chains 인덱스를 사용하여 데이터를 검색하면 16만건 중 15만건의 데이터가 조건을 만족하므로 이 15만건에 대해 다시 테이블 액세스가 발생하게 되어 결과적으로 넓은 범위의 블록에 대한 랜덤 엑세스(Random Access)가 반복적으로 발생하게 된다.
• Cache Buffer 는 검색성능을 향상시키기 위해 해시체인(Hash Chains)으로 관리되어지는데 이 체인을 검색하기위해서는 반드시 래치를 획득하여야만 한다. 하지만 10개의 세션이 동시에 래치를 획득하기 위해 경합이 발생하게 되므로 latch: cache buffers chains 이벤트에 대한 대기가 불가피하게 된다.

SQL의 검색조건중 id컬럼은 선택도가 양호하므로 이것을 인덱스 사용시 이용할 수 있도록 한다.

• 기존의 idx_cache_buffers_chains에 id컬럼을 추가한다.
• 15만회에 걸쳐 테이블검색을 하던 것을 1회검색으로 원하는 결과를 얻게 되므로 경합은 현저히 줄어들게 된다.

그림 latch: cache buffers chains 이벤트 대기 개선 시나리오

-- 기존의 idx_cache_buffers_chains 인덱스 삭제
DROP INDEX idx_cache_buffers_chains;

-- 인덱스에 id컬럼을 추가하여 새로 생성
CREATE INDEX idx_cache_buffers_chains 
ON t_cache_buffers_chains_latch(type,name,id);

성능 개선 후의 모니터링 결과는 아래 표와 같다. 표 2에서 latch: cache buffers chains 이벤트의 대기 시간이 1133(cs)로, 성능 개선 전인 3061(cs)이었던 것에 비하면 3배 정도 개선효과가 있는것을 확인할 수 있다.

표 자체 모니터링 결과

[편집] 2. 과도한 Logical Read로 인한 cache buffer chain latch 발생 분석 사례

동시사용자가 많은OLTP 및 WEB환경에서, 부적절한 인덱스 사용으로 인한 과도한 I/O발생은 성능상의 심각한 문제를 야기하는 경우가 많다. Oracle DBMS의 성능진단/분석 툴인 MaxGauge(맥스게이지)를 활용하여, 부적절한 인덱스 사용으로 인한 과도한 I/O발생이 야기시키는 성능저하 문제의 원인을 규명해 보고자 한다.

[편집] 성능저하구간의 확인

성능문제가 발생한 인스턴스에서 수집된 가동이력 로그로부터 일간 추이그래프를 확인해 보면,「CPU 사용률」은 높지만 execute count가 적은 DB Management시간대(01시30분~05시00분)를 제외하고 「CPU 사용률], [Active session count], [Wait]가 동일한 추이로 변화하고 있음을 감지할 수 있다.

■ CPU사용률의 추이그래프

■ Active Session수의 추이그래프

■ Wait Events의 추이그래프（Wait Time or Waits）

■ Execution Count의 추이그래프

[편집] Wait Events의 검출 및 분석

Active Session의 급증으로 인한 성능저하（Performance Slow-Down）의 원인을 규명하기 위해, 문제시점(10시17분)의 Wait Events의 발생내용을 확인해 본다. ｢Value｣ 탭에서 동 시점의 Top Wait Event를 확인한 결과, Idle Event(= SQL*Net message from client)를 제외한 Top Wait Event는 latch free임이 확인 된다.

Active Session의 급증에 대한 Latch free 대기이벤트의 연관성을 규정하기 위해, 대기이벤트와의 발생패턴을 비교해 본 결과, Active Session의 발생 추이와 상당히 유사하고, 문제시점에 발생한 Wait Events(Wait Time)의 약 53.4%(전체 109.89초 중에서, 58.69초를 점유 함）를 차지하고 있는 점에서, Active Session의 급증은 latch free 대기이벤트의 급격한 발생과 연관이 있음을 추측할 수 있다.

실제로, 같은 시점의 상세데이터를 표시하는 ｢세션 Grid｣ 화면에서도, latch free가 Top Wait Event이며, 그 중에서도 latch free(cache buffer chain) 대기 이벤트가 많이 발생해 있음을 확인할 수 있다.

[편집] Wait Event(Latch Free)발생원인의 조사

latch free대기이벤트의 발생원인에는 여러 가지가 있으나, 일반적으로 latch free (cache buffer chain) 대기이벤트가 발생한 경우에는 ｢Hot block｣이 그 원인일 경우가 많다. Hot block은 table full scan보다는 index range scan에서 빈번하게 발생하며, 해결책으로는 SQL 튜닝을 통하여 Index 검색 범위를 줄여야 하며 SQL 튜닝이 불가능한 경우에는 블록 사이즈를 줄이거나 pct free 값을 증가시켜 블록당 row수를 줄여야 한다.

[편집] 세션 및 SQL의 분석을 통한 문제원인의 규명

latch free (cache buffer chain)의 대기가 많았던 21시30분~ 21시40분 사이에서, latch free (cache buffer chain)를 유발한 SQL이 동일한 패턴의 SQL로 확인된다.

[편집] 결론

latch free(cache buffer chain)대기이벤트의 다발에 의한 Active session의 급증 →

SQL 수행 시 비효율적인 Index Scan으로 인한 성능 저하 발생 →

Table Access 없이 Index Scan으로 수행되도록 해당 SQL의 조건절 칼럼을 Index에 모두 포함시켜 재생성 하여 해결

[편집] 3. 잘못된 인덱스 스캔으로 인한 Latch 경합

성능문제가 발생한 인스턴스에서 수집된 가동이력로그 및 실시간 모니터링을 통해 Active Session의 추이를 확인해본다. 문제 구간의 Active Session은 Cache Buffers Chains Latch 이벤트를 대기하고 있고, Active Session List의 Wait 항목에서 Latch의 주소값(ADDR)은 16321267696임을 알 수 있다.

Latch 경합이 발생한 Latch의 Address를 X$bh 뷰와 조인하여 어떤 오브젝트의 블록인지 확인한다.

-- X$bh 뷰와 조인하여 블록을 확인하는 SQL문 -- 
select a.hladdr, a.file#, a.dbablk, a.tch, a.obj, b.object_name, b.object_type
from   sys.xm$bh a, dba_objects b
where  (a.obj = b.object_id or a.obj = b.data_object_id)
and    a.hladdr = '00000003CCD2C7F0'
order by 4 desc

이 중, Touch Count(TCH)가 높은 블록을 확인한다.

같은 Latch Addr 로 조회 시 여러 개의 object 결과값이 나오는 이유는 하나의 래치가 여러 개의 체인을 관리하기 때문이다. 문제의 인스턴스의 Cache Buffers Chains Latch는 1024 개이고, _DB_BLOCK_HASH_BUCKETS 파라미터는(해시 버킷의 수) 254083 개이다. 즉, 문제의 인스턴스에서 1개의 CBC 래치는 약 249 개의 체인을 관리하고 있다.

ABC$123 이란 INDEX가 가장 TCH가 높으므로, Cache Buffers Chains Latch 경합의 주요 발생 블록임을 유추할 수 있다.

dba_indexes 뷰를 통해 해당 인덱스의 TABLE을 확인하고, 해당 테이블의 인덱스 구성을 확인한다.

select index_name, table_name
from dba_indexes
where index_name = 'ABC$123';

ABC$123 인덱스는 ABC 테이블에 있으며, ABC 테이블은 ABC_NUM + ABC_TIM 으로 구성된 UNIQUE 인덱스와 ABCTYPE으로 구성된 인덱스를 가지고 있음. 래치 경합으로 문제가 된 인덱스는 DMLTYPE로 구성된 ABC$123 인덱스이다.

경합이 발생한 세션의 SQL문의 트레이스 결과를 확인해본다.

-- SQL문 --
UPDATE ABC 
SET    version$$=version$$+1 ,
          type$$='I' 
WHERE  ABC_NUM =:b2 
AND    ABC_TIM" =:b1 
AND    ABC_TYPE='D'

SQL문의 조건에 PK Index의 구성 컬럼이 있음에도 불구하고, 범위가 넓은 인덱스를 선택해 SQL문이 실행되었음을 알 수 있다. (query 부분을 통해, 수행 시 불필요하게 174427 블록을 액세스함을 알 수 있다.)

이 경우, 힌트를 적용하여 PK Index를 경유하게 되면, Cache Buffers Chains Latch 경합을 해소할 수 있다.

[편집] 4. 인덱스 컬럼 변형에 의한 Latch 대기현상

시스템의 CPU 사용율이 Logical Reads와 Physical Reads와 유사한 추이를 나타내고 있다. SQL의 수행횟수를 나타내는 Execute Count는 평균적으로 500회 정도를 유지하고 있으며, SQL의 수행횟수가 많아서 CPU를 많이 사용한 것은 아닌 것으로 추측된다.

이 구간의 Active Session 추이를 확인해 보면, Latch Free 이벤트와 추이가 유사함을 알 수 있고, Cache Buffers Chains Latch를 대기하는 세션들을 발견할 수 있다.

Cache Buffers Chains Latch는 SQL문을 수행 시에 넓은 처리범위로 인하여 발생한다. 또한, db file sequencial reads 이벤트의 발생 추이도 유사하므로, SQL문이 인덱스 스캔을 통해 수행하고 있으나 비효율적인 인덱스를 통해 데이터를 Access하고 있으며 이로 인해 처리범위가 과다하게 나타난 것으로 예상된다.

이 사례는 세션이 수행하고 있는 SQL문에서 distinct key의 종류가 많아서 분포도가 좋은 컬럼이 변형되어 그 컬럼의 인덱스를 경유하지 못하고 비효율적인 컬럼의 인덱스를 통해 데이터를 Access한 경우이다.

예를들면, 다음과 같다.

SELECT emp_id, emp_name, salary, emp_date, dept_id
FROM   emp
WHERE  emp_date >=:1
AND    emp_date <=:2
AND    TO_NUMBER( emp_id ) =:3
AND    emp_name like :4
AND    job_id =:5

와 같은 SQL문을 수행하는데, emp_id 컬럼에 TO_NUMBER라는 함수를 사용하여 해당 인덱스를 이용하지 못하고 emp_name의 컬럼의 인덱스를 이용해서 실행이 된 경우로 이해할 수 있다.

이 경우, 다음과 같이 변경하여야 한다.

SELECT emp_id, emp_name, salary, emp_date, dept_id
FROM   emp
WHERE  emp_date >=:1
AND    emp_date <=:2
AND    emp_id =to_char(:3)
AND    emp_name like :4
AND    job_id =:5

이와 같이, Latch: Cache buffers chains 가 발생하는 경우, 과다한 처리범위를 발생하는 SQL문을 추출하여야 하고, 처리범위를 줄이기 위해 Tuning이 필요하다.

Oracle Wait Event 모니터링

Oracle_Wait_Event_모니터링.doc

글: 안진철 (jcahn@warevalley.com)

現 ㈜웨어밸리 책임 컨설턴트
前 LG-EDS 기술 연구부문 DB 팀
- 솔루션 컨설팅 경력 6년

연재 순서

[1] Oracle Wait Event 모니터링 (2003년 1월 28일)
[2] Enqueue와 Latch (2003년 2월 12일)
[3] Shared Pool 관련 Wait Event (2003년 2월 26일)
[4] buffer cache 관련 Wait Event (2003년 3월 12일)
[5] redo log 관련 Wait Event (2003년 3월 26일)
[6] Top SQL 튜닝 (2003년 4월 9일)

[1] Oracle Wait Event 모니터링

흔히 DBA를 3D업종이라고 부르는 이유 가운데 하나는 몸은 고달픈데 반해 그 성과가 별로 티가 나지 않는다는 사실 때문일 것이다. 실제로, DBA가 수행해야 하는 일상적인 관리 업무들은 몸은 다소 피곤하게 만들지 몰라도 어느 정도 경험이 쌓이면 그리 부담을 주는 일은 아니다. 우리가 한단계 업그레이드된 전문가로서 인정 받는 DBA가 되기 위해서는 장애상황 혹은 유사 장애 상황에서 DB 모니터링 작업을 수행하고 분석할 수 있어야 한다. 시스템이 갑자기 느려지고 업무가 마비되는 상황에 맞닥뜨렸을 때 문제의 원인이 무엇인지를 집어낼 수 있는 능력이 있어야 하며 최소한 오라클의 문제인지 아닌지를 판단할 수는 있어야 몸으로 야간작업이나 때우는 DBA가 아니라 조직에 없어서는 안될 전문가로서의 나의 존재가치를 인정 받을 수 있을 것이다.

이 글에서는 오라클 Wait Event에 대하여 간단히 알아보고 일시적인 성능저하 상황에서 Wait Event를 모니터링하고 그 원인을 찾아가는 방법에 대하여 다루어 보고자 한다. 짧은 지면 위에 다룰 수 있는 내용도 제한되어 있고 글쓴이의 지식 또한 일천하지만 오라클 전문가가 되기 위해 같은 길을 가고 있는 동료로서 가진 지식 몇 가지 공유한다는 취지로 이 글을 쓴다.

오라클의 Wait Event 정보는 V$SYSTEM_EVENT, V$SESSION_EVENT, V$SESSION_WAIT 등이 있는데, 이 가운데 V$SESSION_WAIT는 각 세션이 현재 Waiting 하고 있는 Event나 마지막으로 Wait한 Event 정보를 보관하고 있으며, V$SYSTEM_EVENT와 V$SESSION_EVENT는 시스템이 Startup된 이후 각각 시스템 전체, 혹은 세션별로 발생한 Wait Event 정보를 누적하여 기록하고 있다.

오라클의 Wait Event는 성격에 따라 Network교신이나 IO를 위해 대기하는 일상적인 Wait와 특정 자원에 대해 여러 프로세스가 동시에 액세스하고자 할 때 발생하는 Wait, 별달리 할 일이 없어 대기하고 있는 Idle Wait 등 세가지 유형으로 구분할 수 있는데 그 유형에 따라 해석방법도 달라진다. 일단, Idle Wait는 일반적인 관심의 대상에서 제외되며 IO나 Network 관련 Wait는 작업량이 증가하면 같이 증가하는 Wait이므로 전체 서비스 시간(CPU time)과 비교하여 상대적으로 평가해야 하며 총 Wait time보다는 평균 Wait Time에 관심을 두고 분석을 해야 할 것이다. 시스템 자원에 대한 Wait는 데이터베이스 서버 튜닝시 가장 주된 관심 대상이 되며 이들 Wait에 대해서는 평균 Wait Time뿐만 아니라 총 Wait Time에도 관심을 가지고 분석해야 할 것이다. 유형별로 대표적인 Wait Event를 살펴본다면 아래와 같다.

[주요 Wait Event]

업무시간대에 시스템이 갑자기 느려졌다면서 오라클 서버에 문제가 없는지 문의가 들어오면 글쓴이는 우선 아래의 SQL을 수행시켜본다.

SQL의 구체적인 내용이야 필요한 정보와 개인적 취향에 따라 달라지겠지만, 중요한 것은 일단 V$SESSION_WAIT 뷰로부터 실시간 Wait Event 정보를 얻어낸다는 것이다. 위 SQL을 수행했을 때 나타나는 결과가 없다면 일단 오라클 측면에서 업무성능을 심각하게 마비시키는 Waiting이 발생하고 있지 않다고 봐도 큰 무리가 없을 것이다.

일반적인 상태에서는 주로 'db file sequential read'나 'db file scattered read' 가 나타날 텐데, 이러한 Wait Event는 보통 짧은 시간 동안 지속되며 대상 자원(블록)을 바꿔가며 Wait가 반복되는 형태로 나타날 것이다. 이는 작업 처리량이 많을 때 일상적으로 발생하는 IO관련 Wait Event이므로 해당 세션에서 IO를 제법 많이 유발하고 있다는 정도로 이해하고 넘어가면 될 것이다. 물론, Wait의 지속시간이 길거나 지나치게 빈번히 나타나는 SQL에 대해서는 비효율적인 실행계획을 수립하고 있지 않은지 검토해서 튜닝해 주어야 한다.

성능저하의 원인이 오라클 쪽에 있는 경우에는 특정 자원에 대한 Waiting이 상당히 오랫동안 지속되어 현재까지 Waiting이 진행 중인 세션들(STATUS가 'Wai-ting' (wait_time=0)이며 'W_time(sec)' (seconds_in_wait) 값이 상당히 큰 세션)이 존재할 가능성이 높다. 오라클의 내부적인 작업들은 매우 짧은 기간에 처리되어야 하므로, Idle event(where절에서 not in으로 처리한 부분, 버전에 따라 달라질 수 있다.) 이외의 특정 Wait Event가 눈에 띌 정도로 검출된다는 것은 오라클 내부적으로는 훨씬 더 많은 Waiting이 발생하고 있다고 생각해야 한다. 바로 이런 세션들이 문제의 범인들이며 이제부터 DBA는 이들 Wait Event에 대한 원인을 파악하여 조치하는 작업을 해주어야 한다. 각각의 Wait Event에 따라 원인을 추적하고 조치하는 방법은 달라질 것이다.

다음 호에서는, 자주 경험하는 몇가지 대표적인 Wait Event들에 대하여 SGA 영역별로 구분하여 좀 더 자세히 살펴보고, 그에 앞서 Lock 또는 Latch Event의 이해를 위해 필요한 Enqueue와 Latch의 개념을 간단히 알아보도록 하겠다.

[2] Enqueue와 Latch 개념 이해하기

DBMS의 가장 주된 기능 중에 하나는 동일 자원에 대한 동시 액세스를 관리하는 것이며, 이를 위해 오라클이 사용하는 대표적인 제어 구조가 Enqueue와 Latch이다.
Enqueue와 Latch는 모두 특정 자원에 대한 접근을 serialize하는 것이 목적이라는 점에서는 같은 Lock의 일종이지만 관리방식이나 용도에서 차이가 있다. Enqueue는 이름에서 보듯 Queue를 통해 관리된다. 대상 자원에 대한 Owner, Waiter, Converter Queue를 관리하면서 먼저 요청한 순서대로 Lock을 획득하도록 하는 구조이며, Exclusive 모드 뿐 아니라 다양한 수준의 공유를 허용한다. 대표적인 것이 테이블 데이터를 Update할 때 사용되는 TM, TX enqueue이다.

반면에, Latch는 Enqueue에 비해 훨씬 단순한 구조로서 매우 짧은 시간 내에 획득되고 해제된다. Queue를 통해 관리되지 않으므로 먼저 Request한 프로세스가 먼저 latch를 획득한다는 보장이 없으며, 대부분의 경우 Exclusive모드로만 획득된다. Latch는 주로 SGA의 특정 메모리 구조체에 대한 액세스(library cache latch, cache buffers chains latch) 혹은 메모리 할당 시 (shared pool latch) 사용되거나 오라클의 중요한 코드가 동시에 수행되지 않도록 하기 위한 용도로(redo writing latch) 사용된다. Latch는 Enqueue보다는 하위 level에서 Locking 자체의 부하를 최소화하며 작동하는 제어 메커니즘이라고 할 수 있으며, 실제로 Enqueue 역시 내부적으로는 Latch (enqueues, enqueue hash chains latch )에 의해 운영된다는 점을 생각하면 둘 사이의 차이를 쉽게 이해할 수 있을 것이다.

■ Enqueue

Enqueue 정보는 내부적으로 Enqueue Resource 배열과 Enqueue Lock 배열에 저장된다. 특정 자원에 대한 Lock이 요청되면 대상을 하나의 Resource로 정의하여 할당하고 그 Resource에 대해 관련 Lock 정보를 Owner, Waiter, Converter가운데 하나로서 Link시키는 방식으로 운영되며, 이러한 정보는 V$RESOURCE와 V$LOCK 뷰를 통해 조회해 볼 수 있다. V$RESOURCE와 V$LOCK은 1:M 관계로 하나의 Resource에 대하여 여러 건의 Lock 레코드가 Owner (LMODE>0, REQUEST=0), Waiter (LMODE=0 ,REQUEST>0), Converter (LMODE>0, REQUEST>0) 중 하나로서 대응된다.
Enqueue Wait이 발생하는 것은 다른 세션이 이미 나보다 먼저 해당 자원에 대한 Lock을 잡고 있으므로 인해 내가 원하는 모드로 Lock을 할당 받을 수 없기 때문이다. 자신이 필요로 하는 Lock의 획득에 실패한 세션은 Owner가 작업을 완료하고 자신을 깨워줄 때까지(세마포어를 포스트해줄 때까지) Waiter 혹은 Converter Queue에서 대기하게 되며, 기다려도 소식이 없으면 3초 간격으로 timeout에 의해 일어나 혹시 Deadlock 상황이 아닌지 점검해 본 후 다시 Sleep에 빠져들기를 반복하게 된다. 튜닝관련 자료를 보다 보면 가끔 Enqueue에 대한 Wait이 많은 경우에 Enqueue_resource나 Enqueue_lock 파라미터를 증가시켜 주어야 한다는 가이드를 보게 되는 경우가 있는데 이 파라미터들은 Enqueue resource와 lock 배열의 크기를 늘려줄 뿐 특정 Enqueue 자원에 대한 동시 경합을 해소시키는 것과는 상관이 없다. Enqueue Wait를 해소하기 위한 구체적인 방법은 Enqueue type에 따라 달라지지만 결국은 Enqueue를 불필요하게 요청하는 경우가 없는지를 살펴 Enqueue에 대한 요청을 최소화하고 Enqueue를 점유하는 시간을 최대한 단축시키는 것이다. TX Enqueue에 대한 Wait은 대상 자원에 대한 Lock을 소유하고 있는 세션과 그 세션이 수행 중인 SQL을 찾아 트랜잭션이 장시간 지속되고 있는 이유가 무엇인지 애플리케이션 측면에서 조사해야 하며, SQ enqueue는 Sequence 값 할당 시 발생하는 경합이므로 cache값을 늘려줌으로써 완화시킨다거나 ST Enqueue의 경합이 존재할 경우에는 Locally managed tablespace를 사용하거나 Initial, Next 등의 extent 크기를 적당한 값으로 조정하여 실시간 공간할당을 감소시켜주는 등의 방법들이 Enqueue Wait에 대처하는 대표적인 사례이다. 지난 호에서 소개한 Session Waiter 스크립트는 Enqueue Wait 이벤트에 대해서 Enqueue type과 모드를 함께 표시하여 주도록 하고 있으며, 참고로 Enqueue type별 누적 Wait현황을 확인하고자 하면 아래 SQL을 수행하면 된다.


select q.ksqsttyp type,
           q.ksqstget gets,
           q.ksqstwat waits,
            round(q.ksqstwat/q.ksqstget,3) waitratio
       from sys.x$ksqst q
where q.inst_id = userenv('Instance')
      and q.ksqstget > 0
order by waits desc
/


■ Latch

오라클 운영 시에 하위레벨에서 내부적으로 처리되는 다양한 조작들이 latch의 관할 하에 수행되는데 V$LATCHNAME을 조회해보면 (9i 기준으로) 239 종류나 되는 Latch가 존재하는 것을 확인할 수 있다. 이 가운데 우리가 자주 접하게 되는 latch는 다음과 같은 정도이며 각 Latch의 기능은 관련 SGA별 Wait를 다룰 때 간단하게나마 소개하도록 하겠다.

▷ Willing to wait 모드와 No-wait 모드

Latch 획득 방식은 No-wait과 Willing to wait 의 두 가지 모드로 구분할 수 있다. Willing to wait 모드는 Latch의 획득에 실패하면 좀더 시간을 끌면서 해당 Latch를 잡을 때까지 재시도를 해보는 방식을 말한다. 일차적으로는 CPU를 놓지 않고 정해진 횟수만큼 Spinning을 한 후 재시도를 해보다가 그래도 실패하면 CPU를 놓고 Sleep하다가 timeout되어 재시도하는 작업을 반복하면서 Latch의 획득을 노력하게 된다. Latch가 sleep에 들어가게 되면 'latch free' wait event 대기가 시작된다. sleep의 지속시간은 sleep 횟수가 늘어갈수록 점점 길어지게 되는데, 따라서 V$LATCH의 Gets와 Sleeps의 비율과 함께 Sleep1~sleep4 항목에서 몇차 Sleep까지 발생했는지 여부도 각 Latch Wait의 심각성을 판단하는 요소 가운데 하나가 된다.

No-wait 모드는 Willing to wait과는 달리 더 이상 미련을 두지 않고 해당 Latch에 대한 획득을 포기하는 것이다. No-wait 모드가 사용되는 경우는 두 가지가 있는데, 하나는 동일한 기능을 하는 Latch가 여러 개 존재하여 그 중에 하나만 획득하면 충분하여서 특정 Latch에 미련을 가질 필요가 없는 경우이다. 물론, 이 때에도 같은 기능의 모든 Latch에 대한 시도가 실패로 끝날 경우에는 Willing to wait 모드로 요청을 할 것이다. No-wait 모드가 사용되는 다른 한가지 경우는 dead lock을 피하기 위해서 이다. 오라클은 기본적으로 latch dead lock 상황을 피하기 위하여 모든 Latch에 level을 부여하여 정해진 순서를 따라서만 Latch를 획득하도록 하고 있는데, 필요에 의해 이 규칙을 어기고 Latch를 획득하고자 할 경우 일단 No-wait 모드로 시도를 해보는 것이다. 다행히 Latch를 잡으면 좋은 것이고 비록 latch를 잡을 수 없더라도 무한정 기다림으로써 dead lock 상태에 빠지는 일은 피할 수 있는 것이다. No-wait 모드의 Latch작업에서는 당연히 Latch 관련 wait이 발생하지 않으며, redo copy latch를 제외하고는 Willing to wait 모드로 Latch를 획득하는 경우가 훨씬 많다.

▷ Parent latch와 Child latch

Latch 가운데에는 동일 기능을 하는 Child latch들의 set으로 운영되는 Latch도 있으며 하나의 Latch로만 운영되는 Latch도 있다. 전자의 대표적인 예로는 cache buffers chains (버퍼캐쉬 블록 들을 같은 이름의 다수의 Latch가 나누어 담당)가 있으며, 후자의 예로는 shared pool latch (shared pool내에서 메모리 할당을 위해 획득해야 하는 Latch로 시스템에 하나만 존재)가 있다. 이와 같은 Latch 관련 통계 정보는 Parent latch와 Child latch의 개념으로 관리가 되는데 Latch set에서 개별 Child latch에 대한 통계정보는 V$LATCH_CHILDREN 뷰를 통해 조회할 수 있으며, 단일 Latch 혹은 Latch set의 마스터 Latch (parent)에 대한 통계정보는 V$LATCH_PARENT 뷰를 통해 조회할 수 있다.

지금까지 한 회 분량을 할애하여 Enqueue와 Latch에 대해 요약해본 이유는, 많은 Waiting이 SGA내의 공유자원 (Block, Cursor 등)에 대한 경합으로 인해 발생하며 이러한 경합은 다시 해당 자원에 대한 동시 액세스를 제어하는 Enqueue와 Latch에 대한 경합으로 흔히 드러나게 되므로 오라클의 Wait Event를 모니터링하기 위해서는 Enqueue와 Latch의 구조와 작동원리에 대해 이해하는 것이 필수적이기 때문이다.

[3] Shared Pool 관련 Wait

■Share pool과 성능문제

오라클이 공유 메모리(SGA)를 사용하는 가장 큰 이유는 기본적으로 메모리 사용을 최소화하면서 처리성능은 최대화하기 위한 것이다. 한번 액세스된 블록을 Database buffer cache에 캐쉬 함으로써 비용이 큰 Disk I/O를 최소화하는 것처럼, 한번 처리된 SQL의 실행 정보를 Shared Pool에 공유함으로써 파싱 작업을 위한 CPU, 메모리 자원의 사용을 최소화하고 SQL 수행속도를 증가시킬 수 있다. Shared Pool에는 SQL이나 PL/SQL을 수행하기 위한 각종 정보 - SQL구문 및 실행계획, PL/SQL 소스, 테이블, 뷰 등의 각종 오브젝트와 오브젝트 상호간의 의존관계, 권한관계 등 - 가 저장되어 있다. 지면 관계상 이 글에서 Shared Pool의 관리 메커니즘을 상세히 기술할 수는 없지만 몇 가지 내재적인 특징으로 인해 Shared Pool은 오라클의 메모리 영역 가운데에서도 가장 성능문제의 요소가 많은 곳이면서도 효과적인 튜닝이 수월치 않은 영역이기도 하다.

무엇보다, Shared Pool에서 가장 문제가 되는 것은 메모리의 조각화(Fragmentation)이다. Shared Pool에서 라이브러리 캐쉬 오브젝트를 위해 할당되는 메모리 단위를 chunk라고 부르는데 chunk의 크기는 수 바이트에서 수 K바이트에 이르기까지 필요에 의해 다양하게 할당된다. 새로운 chunk의 할당이 필요하게 되면, 프로세스는 이미 존재하는 chunk로부터 필요한 만큼의 크기만을 떼어내어 사용하므로 시간이 흐를수록 점차 메모리가 조각화 되는 것을 피할 수 없다. ( 이는, Pctincrease가 0가 아닌 테이블스페이스에서 익스텐트의 할당과 해제가 반복됨에 따라 공간의 조각화가 심해지는 것을 떠올리면 이해가 쉬울 것이다. ). 어느 정도 정형화된 패턴의 애플리케이션이 수행되는 환경이 아니라, 공유가 불가능한 다양한 형태의 SQL(대표적으로 Literal SQL)이 빈번히 요청되는 환경이라면 Shared Pool 메모리 조각화에 따른 문제는 더욱 심각해진다.

또한, Shared Pool은 일반적인 메모리 캐쉬와는 달리 메모리에 저장되었던 정보를 잠시 기록해둘 대응되는 디스크 공간이 없으므로 한번 flush된 라이브러리 캐쉬 오브젝트를 reload하기 위해서는 해당 정보를 재생성 해야만 한다. 이 과정에서 관련 오브젝트 정보의 검색 및 참조, locking, 메모리 할당 등의 작업을 위해 많은 비용이 들기 때문에 결국 Shared Pool 관련 튜닝의 최대 과제는 SQL 공유를 최대화하여 새로운 파싱 요청과 메모리 요청을 최소화하는 것이라고 할 수 있다. 헌데, 이는 애플리케이션의 설계와 연계되는 영역으로서 이미 개발이 완료된 운영서버에서는 변경작업이 여의치 않은 것이 현실이다. 앞서, Shared Pool이 DBA로서 튜닝이 수월치 않은 영역이라고 표현한 이유 가운데 하나가 여기에 있다.

■ Shared Pool 관련 오해 바로잡기

Shared Pool과 관련하여 판단이 쉽지 않은 부분 가운데 하나가 과연 shared_pool_size를 얼마나 할당할 것인가 하는 것이다. 오라클은 Shared Pool 메모리를 최대한 효율적으로 활용하기 위하여 다양한 기법을 동원하고 있는데, 이러한 메모리 관리 메커니즘에 대해 정확히 알지 못하여 Shared Pool 크기를 지나치게 크게 할당함으로써 오히려 문제를 악화시키는 경우도 드물지 않다. 이러한 오해를 바로잡기 위해 Shared Pool의 메모리 할당과정을 간단하게나마 살펴보도록 하겠다.

새로운 메모리 Chunk가 할당되는 과정을 살펴보면, 우선 프로세스는 Free List를 검색하여 자신이 필요로 하는 크기의 Free Chunk를 찾고, 그러한 Free Chunk가 없으면 원하는 크기보다 한단계 큰 Free Chunk를 찾아서 필요한 크기만큼 분할하여 사용하게 된다. 만약 Free List에서 충분한 크기의 Free Chunk를 찾을 수 없다면, 이미 사용되었으나 현재는 사용되고 있지 않는(unpinned) Chunk들의 LRU List를 검색하여 오래된 것부터 8개씩 flush시켜 Free Chunk로 만든 후 자신이 필요한 크기를 할당하여 사용하게 된다. 만약 이 과정에서 현재 사용중인(pinned) Chunk가 대부분이거나, 너무 메모리 조각화가 많이 일어나서 기존 Chunk를 Flush시킨 후 인접한 Free Chunk들을 병합해보아도 원하는 크기의 Free Chunk를 얻어낼 수 없다면 오라클은 ORA-4031 에러를 발생시키는데, 그 이전에 한가지 최후의 비밀무기가 더 숨어 있다. 바로 Spare Free 메모리라는 것인데 오라클은 인스턴스 기동 후 처음에는 전체 Shared Pool의 50% 가량은 Free List에 올려놓지 않고 아예 숨겨두었다가 앞서와 같이 도저히 피할 수 없는 순간이 되면 조금씩 해제 시켜 사용하도록 한다. 그야말로 메모리의 조각화를 최소화하기 위한 오라클의 눈물 나는 노력이라고 할 수 있을 것이다. 물론 이 영역까지 다 소모한 후에 flush를 통해서도 필요한 Chunk를 확보할 수 없는 상황이 되면 결국 ORA-4031 에러가 발생할 것이다.

많은 이들이 Shared Pool의 남아있는 Free memory의 크기가 작으면 shared_pool_size를 증가시켜주어야 한다고 믿고 있는데 이는 잘못된 것이다. Shared Pool은 정보의 재사용을 위해 운영하는 것이므로 SQL 실행이 끝났다고 해서 해당 Chunk를 Free List로 반납하지 않는다. 즉, Free Memory가 남아있는 한 계속 소모 시키는 방식으로 사용되므로 오랜 시간동안 운영되어온 시스템에서 Shared Pool의 Free Memory가 매우 적게 남아 있는 것은 그 자체로는 문제가 되지 않으며, 오히려 피크타임이 지난 후에도 많은 양의 Free Memory가 남아있다면 이는 Spare Free 메모리도 다 소모하지 않은 상태로서 불필요하게 많은 메모리가 할당되어 낭비되고 있음을 의미한다. 더구나, Shared Pool 크기가 지나치게 크면 Free Memory를 다 사용할 때까지의 기간이 연장되는 효과는 얻을 수 있겠지만, 시간이 지날수록 Memory의 조각화가 더욱 심해지고 Free List의 길이가 길어져 Free Chunk의 검색과 할당에 걸리는 시간이 지연되므로 오히려 성능이 악화되는 결과를 초래할 것이다.

또한, 메모리 조각화에 따른 영향을 줄이기 위해 오라클은 5000 bytes가 넘는 큰 사이즈의 Chunk만을 위해 전체 Shared Pool의 5% 정도를 따로 관리하는 방법을 사용하고 있는데, 경험적으로 보면 이 공간은 거의 사용되지 않고 버려지고 있는 경우가 많다. 이는 V$SHARED_POOL_RESERVED 뷰의 USED_SPACE 값을 확인해 보면 알 수 있으며, 5000 bytes 이상의 large chunk가 거의 요구되지 않는 환경에서는 오히려 이 크기를 줄여주는 것이 나을 것이다.


■Shared Pool 관련 wait

Shared Pool과 관련하여 흔히 발생하는 Wait은 라이브러리 캐쉬 오브젝트에 대한 동시 액세스와 메모리 할당에 따른 관련 Lock 또는 Latch에 대한 경합이 대부분이며, 구체적인 이름은 다음과 같다. (Latch free 이벤트시 괄호 안의 관련 latch 이름은 v$session_wait의 p2값과 v$latchname의 latch#를 조인하여 얻어낼 수 있다. 1회 SQL 참조)

Library cache lock, library cache pin, library load lock은 각각 특정 라이브러리 캐쉬 오브젝트에 대한 검색이나 변경 및 실행 또는 로드 시에 대상 오브젝트에 대해 할당되며, 이러한 Locking 작업은 library cache latch와 library cache load lock latch의 관할 하에 처리된다. Shared pool latch는 Free List나 LRU List를 검색하거나 메모리를 할당하는 작업에 사용되며, row cache lock과 row cache objects latch는 Data dictionary cache 오브젝트에 대한 동시 액세스를 제어하는데 사용된다.

Latch의 개수는 시스템 전체적으로 하나 또는 제한된 개수가 존재하는 것이고 Lock은 대상 오브젝트 각각 대해 할당되는 것이므로, 엄밀하게 말해서 Lock에 대한 경합은 직접적으로는 특정 라이브러리 캐쉬 오브젝트에 대한 동시 액세스로 인해 유발되는 것인 반면에, Latch에 대한 경합은 시스템 전체적으로 관련 오퍼레이션(즉, SQL 파싱) 자체가 지나치게 많이 발생하거나, 짧은 시간 내에 처리되지 못함으로 인해 유발되는 것이라고 구분해볼 수 있다. 그러나, 결국 이 모든 경합은 근본적으로 Shared Pool의 조각화(Fragmentation)에 따른 문제가 주된 원인이며 다시 이러한 조각화는 요청되는 SQL들이 공유되지 못하고 지속적으로 새롭게 파싱되고 메모리가 할당됨으로 인해 발생하는 것이다. 따라서, 이러한 문제를 해결하는 가장 효과적인 방법은 Literal SQL을 바인드 변수를 사용하도록 수정하거나, SQL작성 표준을 마련하고, HOLD_CURSOR/ RELEASE_CURSOR, SESSION_CACHED_CURSORS, CURSOR_SPACE_FOR_TIME, CURSOR_SHARING 등의 파라미터를 활용하는 등의 방법을 통해 SQL의 공유도를 높여주는 것이며, 또한 자주 사용되는 PL/SQL에 대해서는 DBMS_SHARED_POOL 패키지를 사용하여 메모리에서 Flush되지 않도록 보존하는 등의 조치를 취해주면 도움이 될 것이다. SQL의 수정이 어려운 환경이거나 시스템에 요청되는 SQL의 절대량이 확보된 메모리 공간에 비해 많은 상황이라면 주기적으로 피크타임을 피해 Shared Pool을 직접 Flush(alter system flush shared_pool 명령을 사용한다.) 시켜주는 것도 권장할 만한 관리 방법이다. 많은 이들이 우려하는 바와는 달리 Shared Pool을 직접 flush 시키는 것이 심각한 성능상 문제를 야기하지는 않으며 특히 중요한 패키지나 SQL cursor, Sequence 등이 keep되어 있는 경우라면 더욱 그러하다.

가끔 버그를 포함한 특수한 상황에서 특정 라이브러리 캐쉬 오브젝트에 대한 lock이 장시간 해제되지 못하고 있는 경우도 있는데 이때는 X$KGLLK 뷰를 조회하면 library cache lock에 대한 holder/waiter를 확인하여 조치할 수 있다. 또한, Row cache lock에 대한 경합은 Locally managed tablespace를 도입하거나, DML이 빈번한 테이블에 대한 인덱스의 개수를 줄여주는 등의 조치를 통해 완화될 수 있을 것이다.

부연하자면, Shared Pool과 관련된 Wait는 특정 오브젝트 자원에 대한 경합에 의해 발생하기 보다는 애플리케이션의 설계, 보다 단순화시켜 표현하면 Literal SQL에 의한 메모리 조각화에 의해 발생하는 경우가 많다. 따라서, Shared Pool관련 Wait가 많이 발생하여 오라클이 그로 인한 성능상의 문제를 드러낼 때 눈에 띄는 하나의 주범을 찾아내려는 노력은 별 효과를 거두지 못하는 경우가 많으며, 그러한 시점에 DBA가 즉각적으로 취할 수 있는 조치로는 직접 Shared Pool을 Flush 시키는 정도가 있을 것이다. 결국, 평소에 꾸준한 모니터링을 통해 Shared Pool의 적절한 크기와 관련 파라미터 값을 찾아가는 것, 그리고 무엇보다 애플리케이션 측면에서 튜닝 및 수정 작업을 진행함으로써 성능문제를 사전에 예방하는 것이 최선이다.

[3] Buffer Cache 관련 Wait

■ Buffer Cache 구조

Buffer Cache의 기본적인 기능은 여러 프로세스에 의해 공통으로 자주 액세스 되는 데이터베이스 블록을 메모리에 캐쉬하여 물리적인 디스크 IO를 최소화함으로써 더 빠른 액세스 속도를 제공하기 위한 것이다. 복잡한 설명은 생략하고, Buffer Cache 의 기본구조를 이해하기 위한 몇 가지 핵심 용어들을 간단히 정리해 보도록 하겠다.

▷ Buffer header

모든 버퍼 블록들은 각자의 buffer header를 통해 액세스되고 관리된다. 즉, 메모리에 캐쉬된 특정 데이터 블록에 대한 액세스는 먼저 해쉬 알고리즘을 통해 cache chain 상의 buffer header를 찾고 해당 buffer header에 기록된 데이터 블록의 메모리상 주소를 찾아가 원하는 정보를 읽는 방식으로 이루어진다. Buffer header에 기록되는 주요정보는 다음과 같으며 Buffer header의 내용은 V$bh 뷰를 통하여 조회해볼 수 있다.

     - 메모리상에서의 해당 버퍼블록의 주소
     - 해당 버퍼 블록(실제로는 버퍼헤더)가 포함되어 있는 hash chain
     - LRU, LRUW, CKPTQ와 같은 리스트상에서의 해당 버퍼블록의 위치
     - 해당 버퍼블록에 대한 User, Waiter와 상태를 나타내는 각종 Flag

▷ Hash Buckets/ Hash Chains

Buffer Cache의 모든 블록은 해쉬 알고리즘을 통해 관리된다. 곧, 데이터 블록의 DBA, Class 값으로 Hash Function을 적용하여 해당 블록이 속하는 hash buckets을 할당하며, 동일한 hash buckets에 할당되는 데이터 블록의 버퍼헤더들은 linked list형태로 hash chain을 이루게 된다. Hash buckets/hash chains는 특정 데이터 블록을 찾아가기 위한 수단을 제공한다. 각각의 hash buckets에는 자신에 속한 hash chain을 보호하기 위한 latch(cache buffers chains)가 할당된다.

▷ LRU

LRU는 두개의 리스트, 즉 LRUW와 LRU 리스트의 쌍으로 구성된다. LRUW(LRU Write list)는 dirty list와 같은 말이며, 수정되어 디스크에 반영되어야 할 블록들의 리스트이다. LRU(Least recently used list)는 LRUW에 올라가지 않은 나머지 버퍼 블록들이 등록되어 있다. Buffer cache 상의 버퍼블록은 반드시 LRU나 LRUW 둘 중의 하나에 등록되며, 두 리스트에 동시에 포함되는 경우는 없다. LRU는 Free Buffer를 찾기 위한 수단을 제공한다. 경합을 피하기 위해 버퍼캐쉬 블록들을 여러 개의 LRU쌍으로 나누어 관리할 수 있으며, 각 LRU리스트를 보호하기 위해 Latch(Cache buffers lru chain)가 하나씩 할당된다.

■ Buffer Cache 운영규칙

▷ 메모리상의 특정 버퍼블록을 찾아가거나, 특정 블록이 메모리에 캐쉬 되어 있는지를 확인하기 위해서 오라클은 hash bucket/hash chain 구조를 사용한다.

▷새로운 데이터블록을 디스크로부터 메모리로 읽어 들이기 위한 free buffer를 확보하기 위해 오라클은 LRU 리스트를 사용한다.

▷ 버퍼블록은 LRU나 LRUW 둘 가운데 하나에 등록된다.

▷ 하나의 블록에 대해 시간대가 다른 여러 개의 복사본이 존재할 수 있으며, 그 가운데 오직 CURRENT 버퍼만이 변경될 수 있다.

▷하나의 버퍼블록은 한번에 오직 하나의 프로세스에 의해서만 변경될 수 있다.

■ Buffer Cache 관련 Waits

버퍼캐쉬와 관련되어 흔히 발생하는 대표적인 Wait 이벤트는 다음과 같다.

▷ buffer busy waits

여러 세션이 동시에 같은 블록을 읽으려고 하거나 여러 세션이 같은 블록에 대한 변경작업이 완료되기를 기다리고 있는 경우에 발생하며, 특정 블록에 대한 경합을 해소하기 위한 조치는 블록의 유형에 따라 달라진다. Data block에 대한 경합이 많은 경우는 Pct free나 Pct used 값을 사용하여 블록 당 로우수를 줄이거나, 특정 블록에 로우 입력이 몰리는 구조의 인덱스(right-hand-index)일 경우는 reverse key index의 사용을 검토하는 등의 방법이 있으며, segment header의 경합이 많은 경우는 freelist 수를 늘리거나 Extent의 크기를 증가시키는 등의 방법이 있고, undo header나 undo block에 대한 경합은 롤백세그먼트의 개수나 크기를 증가시키는 것이 전형적인 조치 방법이다. v$waitstat과 x$kcbfwait을 이용하며 Class 또는 file별로 wait 발생상황을 판단할 수 있다.

▷ free buffer waits/write complete waits

DBWR가 dirty buffer를 write하는 동안 서버 프로세스가 대기하고 있는 경우 발생한다. 곧, 너무나 많은 dirty buffer가 생겨나거나 DBWR의 쓰기 속도가 충분히 튜닝 되지 못한 경우에 발생한다. 점검 포인트는 물리적 디스크의 속성(stripe size, layour, cache size) 최적화, Raw device의 활용, Async IO나 multi-DBWR(db_writer_processes) 활용여부 등이다.

위와 같은 버퍼 블록에 대한 경합 역시 비효율적인 실행계획을 통해 수행되는 애플리케이션에 의하여 불필요하게 많은 블록이 메모리로 올라오는 것이 원인일 경우가 많으므로 경합이 빈번한 블록이 속하는 테이블/인덱스 명을 찾아낼 수 있다면 관련 SQL을 찾아내어 보다 효과적인 튜닝작업이 이루어질 수 있을 것이다. v$session_wait의 p1,p2 컬럼에 각각 file#, block#값을 표시하여 주므로 이 값을 이용하여 아래의 SQL문으로 현재 어떤 오브젝트에 대하여 해당 wait가 발생하고 있는지를 추적할 수 있다. ( 1회에 소개한 SQL문에서는 Additional Info 값을 참조. )

     select segment_name, segment_type
     from dba_extents
     where file_id = :file#
     and :block# between block_id and block_id + blocks -1

▷ cache buffers chains latch

SGA내에 캐쉬된 데이터블록을 검색할 때 사용된다. 버퍼캐쉬는 블록들의 chain을 이루고 있으므로 각각의 chain은 이 Latch의 child들에 의해 보호된다. 이 Latch에 대한 경합은 특정 블록에 대한 대량의 동시 액세스가 발생할 때 유발된다. 애플리케이션을 검토해 보아야 한다.
Ø cache buffers lru chain latch
버퍼캐쉬의 버퍼를 LRU 정책에 따라 이동시켜야 할 필요가 있는 경우 프로세스는 이 Latch 획득하게 된다. 이 Latch에 대한 경합은 Multiple buffer pool을 사용하거나 DB_BLOCK_LRU_LATCHES 를 증가시켜 LRU Latch의 개수를 늘려서 해소할 수 있다. SQL문을 튜닝하면 해당 프로세스에 의해 액세스 될 블록의 수가 줄어들 것이므로 당연히 효과를 거둘 수 있다.

위와 같이 버퍼캐쉬를 관리하는 Latch에 대한 경합은 경합이 집중되는 특정 Child Latch에 의해 관리되는 버퍼블록을 찾아 해당 블록이 속한 세그먼트 정보를 알아낸다면 보다 효과적인 조치가 가능할 것인데, latch free wait일 경우 v$session_wait의 p1raw 값이 해당 Latch address를 의미한다. 이 값을 x$bh의 hladdr 값과 조인하면 관련 오브젝트 이름을 추적해볼 수 있다.

     select file#, dbarfil, dbablk, obj, o.name
     from x$bh bh, obj$ o
     where bh.hladdr = :latch_address
     and bh.obj = o.obj#;

[5] Redo buffer 관련 Wait

■ Redo buffer 구조

오라클 리두 구조의 핵심은 모든 트랜잭션 정보를 OS 파일에 기록해 둠으로써 시스템 장애가 발생해도 트랜잭션 단위의 일관성을 잃지 않고 데이터베이스를 복구할 수 있도록 하겠다는 것이다. 리두버퍼(redo buffer)는 이처럼 데이터베이스에 가해진 모든 변경내역을 파일에 기록 하기 위해 잠시 사용되는 메모리 영역이며 리두버퍼에 기록된 리두 정보는 다시 리두로그 파일에 기록되어짐으로써 향후 시스템 복구 작업이 필요할 때에 사용하게 된다. 오라클의 리두 구조를 이해하기 위한 핵심적인 개념을 간단히 정리해보면 다음과 같다.

데이터베이스에 대한 변경내역은 블록단위로 저장된다. 물론 변경되는 모든 블록의 복사본을 통째로 저장하는 것은 아니고 블록별로 어떠한 오퍼레이션을 수행하는가, 그리고 그러한 블록별 오퍼레이션을 어떠한 순서로 수행하는가를 기록한다. 이러한 블록별 단위액션을 change vector라고 부르며 change vector가 순차적으로 모여 하나의 의미 있는 redo record가 된다. 리두로그는 시스템내의 모든 프로세스들에 의해 생성되는 redo record를 SCN 순서대로 저장해놓은 것이다. 이때 리두로그에 기록되는 내용에는 테이블이나 인덱스 등의 데이터 블록 뿐만 아니라 UNDO 블록 또는 UNDO 세그먼트 헤더블록에 대한 변경내용을 포함하는 모든 버퍼캐쉬 블록에 대한 변경내역이 대상이 된다.

리두 정보는 항상 실제 변경작업보다 먼저 보관되어야 어떤 상황에서도 복구가 가능해진다. 따라서 트랜잭션을 수행하는(데이터베이스 블록에 변경을 가하는) 프로세스는 우선 자신의 메모리 영역 내에서 수행하고자 하는 작업에 대한 리두 레코드를 만들며, 이를 먼저 로그버퍼에 기록하고 난 후에 실제 버퍼블록에도 리두 레코드에 담긴 내용을 따라 적용하게 된다. 또한 같은 이유로 오라클은 변경된 버퍼캐쉬 블록을 디스크에 기록하기 전에 먼저 관련된 로그버퍼를 로그파일에 기록하는 작업을 처리하게 된다. 따라서, 리두 버퍼 또는 리두 파일 (아카이브 파일을 포함해서)에 대한 쓰기 작업에 병목이 생기면 시스템에 대한 모든 작업 수행이 대기 상태로 빠지게 될 것이다.

트랜잭션 커밋을 요청한 프로세스는 우선 해당 트랜잭션에 대한 로그버퍼가 리두로그 파일에 기록되는 작업이 완료된 후에야 커밋 완료 메세지를 받을 수 있다. 그렇게 함으로써 버퍼캐쉬 변경내역을 모두 디스크에 반영하지 않고도 시스템의 비정상 종료시 리두파일에 저장된 리두 레코드로부터 커밋 트랜잭션을 보존할 수 있게 된다.

■리두 버퍼관련 Wait 이벤트

일반적으로는 로그버퍼 관련해서 심각한 Waiting이 발생하는 경우는 드물지만, 가끔 볼 수 있는 리두 관련 Wait 이벤트로는 다음과 같은 것들이 있다.

▷ Log file parallel write

LGWR가 OS에 리두 버퍼를 로그파일에 기록하도록 요청해 둔 상태에서 대기하고 있는 이벤트이다. 이 경우에는 DML 작업시 nologging 옵션 등을 사용하여 시스템에서 발생하는 리두 레코드의 절대량을 줄이거나 하드웨어적으로 DISK IO를 개선시켜주는 것이 방안이다.

▷Log buffer space

프로세스가 로그버퍼를 할당하기 위해 대기하는 이벤트인데 LGWR가 로그버퍼를 비우는 것보다 더 빠른 속도로 프로세스들이 리두 레코드를 생성하고 있다는 것을 의미한다. 로그버퍼의 크기를 늘려주거나, DISK IO의 속도를 개선시켜 주어야 할 것이다. 로그버퍼는 로그파일에 대응되는 블록이 맵핑이 된 후에 사용될 수 있으므로 로그 스위치 발생시에도 log buffer space 이벤트에 대한 대기가 발생할 수 있다. 로그 스위치가 너무 잦다면 리두 로그 파일의 크기를 증가시켜주는 것이 좋다.

▷ Log file sync

프로세스가 커밋이나 롤백을 수행할 경우 우선 LGWR에게 해당 트랜잭션까지의 로그버퍼를 Write하도록 요청하게 되는데 이때 사용자 프로세스는 LGWR가 쓰기 작업을 완료할 때까지 log file sync 이벤트를 대기하게 된다. 버전 8i 이전에서는 DBWR가 쓰기 작업을 수행하다가 아직 관련 로그버퍼가 파일에 쓰여지지 않을 경우에도 LGWR에 쓰기를 요청하고 log file sync 이벤트에 대기하였으나 8i 이상에서는 log file sync에 대기하는 대신 deferred write queue에 등록한다. 따라서 버전 8i 이상에서 log file sync 이벤트는 사용자 프로세스에 의해 요청되는 커밋, 롤백 처리 시에 발생하며 결국, 시스템 전체적으로 커밋, 롤백이 지나치게 자주 수행되거나 상대적으로 LGWR의 쓰기 속도가 느린 것이 원인일 것이다. 또는, 로그 버퍼가 너무 커서 LGWR가 백그라운드로 flush 시켜주기 전( 보통 3초 간격 및 1/3 이상의 로그버퍼가 찬 경우)에 커밋에 의한 쓰기 요청이 이루어지므로 커밋 시점에 써야 할 양이 많아 대기시간이 길어지는 경우도 있는데 이 경우엔 리두 버퍼의 크기를 오히려 줄여주어야 할 것이다. 또는, LGWR wait for redo copy 이벤트가 많이 나타난다면 redo copy latch가 너무 많아 LGWR이 사용자 프로세스가 버퍼 쓰기 작업을 마칠 때까지 기다리는 일이 잦은 경우를 뜻하며 이 경우엔 _LOG_SIMULTANEOUS_COPIES 파라미터를 사용하여 copy latch의 수를 줄여주는 조치가 필요할 것이다.

시스템에 따라서 언급한 외의 다양한 이벤트 대기와 원인이 존재할 수 있고, 더구나 버전에 따라 redo copy latch와 redo allocation latch를 포함한 리두 운영 방식상 상이한 부분이 많이 존재하여 그에 따른 추가적인 튜닝요소가 있으나 이 글에서는 지면 관계상 8i를 기준으로 간략히 정리해 보았다.

[6] Top SQL 튜닝하기 (맺음)

■ Top SQL 튜닝의 필요성

지난 회까지 실시간 Wait Event 모니터링과 이벤트별 원인분석에 대해서 간단히 살펴보았다. 일시적 성능장애 시 재빨리 원인을 찾아내는 것도 중요하지만 보다 바람직한 것은 이러한 성능문제를 사전에 최대한 예방하는 것임은 두말할 필요도 없다. 오라클 성능문제를 다루는데 있어 강조하고 싶은 한가지는 시스템 자원의 배분을 변경하거나 증가를 고려하기 전에, 불필요한 작업을 최소화함으로써 자원요구 횟수와 자원점유 시간을 줄여주는 노력이 선행되어야 한다는 점이다. Wait Event에 대한 모니터링과 분석이 DBMS의 자원에 대한 경합과 관련된 성능문제를 파악하는데 유용한 방법임에 틀림없지만, 이 같은 정보는 데이터베이스 혹은 그 하위 레벨의 구조적 비효율성을 드러내어줄 뿐 애플리케이션 레벨의 문제를 직접적으로 알려주지는 않는다. 간단히 말하자면, DBMS 튜닝을 위해 정성을 쏟기 이전에 애플리케이션 튜닝에 더 많은 투자를 하라는 것이다. 이런 관점에서 DBA가 비교적 손쉽게 수행할 수 있는 것이 오라클 메모리로부터 악성 SQL을 추출하여 튜닝하는 Top SQL 튜닝이다.

■Top SQL 추출기준

사용자로부터 요청되어 오라클 내에서 처리되는 모든 SQL은 오라클의 공유 메모리 영역 가운데 shared pool내에 캐쉬 되어 지며 이렇게 캐쉬 되어 있는 SQL과 관련 통계정보는 V$SQL 또는 V$SQLAREA 뷰를 통해서 조회할 수 있다. 이때, Top SQL을 추출하는데 중요하게 사용되는 항목은 buffer_gets, disk_reads, executions, rows_processed 등이며 일반적으로 아래와 같은 기준으로 Top SQL을 추출한다.

▷ Buffer gets 수치가 높은 SQL

Buffer gets은 해당 SQL이 처리되는 과정에서 액세스한 메모리 블록의 수(Logical IO)를 의미한다. 물론 이 값이 높다고 해서 무조건 악성 SQL임을 의미하는 것은 아니다. 즉, 이러한 SQL들 중에는 실제로 요구되는 작업량이 많아서 액세스 블록수가 많은 SQL도 있을 것이며 불필요한 처리를 수행하느라 액세스 블록수가 많은 SQL도 있을 것이다. 어느 경우이든 이 SQL들이 현재 오라클 서버에 부하를 많이 유발하고 있는 SQL들이라는 것만은 분명하며 사소한 비효율적 요소에 의해서도 서버에 큰 영향을 미칠 잠재적인 가능성이 있는 SQL들이므로 일차적으로 점검해 볼 필요가 있다.

악성 SQL여부를 판단하기 위한 Buffer gets의 수치에 절대적인 기준은 없으며 시스템의 데이터 규모와 트랜잭션량에 따라 다르다. buffer gets값을 기준으로 역순으로 정렬한 후 패턴을 살펴 적절한 추출기준을 선택하는 것이 좋을 것이다. 만일, 상위 몇 개의 SQL들과 나머지 SQL들 간의 buffer gets의 편차가 매우 크게 나타난다면 상위 몇 개의 SQL에 대해서만 튜닝을 수행해 주어도 큰 효과를 볼 수 있을 것이다. 일반적으로 시스템에서 수행되는 SQL 가운데 심각한 부하를 야기하는 SQL은 소수에 불과한 경우가 많으며 뒤에 기술될 다른 조건들과 조합하여 최대 Top 50건 정도를 추출하여 효율성을 검증하고 튜닝을 통해 개선하는 작업을 수행하여 주면 충분하다.

▷Buffer gets/Execution 수치가 높은 SQL

SQL의 단위 수행당 buffer gets 수치를 의미한다. 단위 수행당 buffer gets 값이 높다는 것은 해당 SQL의 처리가 비효율적일 가능성이 높음을 의미한다. 액세스 블록수가 비정상적으로 많다는 것은 rows_processed 값과 비교하여 상대적으로 평가되어야 할 부분이다. 실제로 반환하는 로우수가 매우 많은 배치성 SQL이거나 혹은 반환되는 로우수가 1건이라도 Group Function이 사용된 Summary성 SQL이라면 처리과정에서 많은 수의 블록을 액세스하는 것은 불가피하며 이 자체가 문제가 될 수는 없기 때문이다.

▷Execution 수치가 높은 SQL

Executions는 해당 SQL이 수행된 횟수를 의미한다. 수행횟수가 잦은 SQL은 buffer gets가 높을 경우가 많다. 일반적으로 십만 ~ 백만 회 이상 빈번하게 수행되는 SQL이라면 buffer gets/executions 값이 2자리 수 이내의 값을 나타내어야 정상이며 단위 수행당 속도는 0.1초 이내로 매우 빨라야 한다. 따라서, 이러한 SQL의 경우 SQL단위로 보면 튜닝의 효과를 체감하기도 어렵고 필요성을 느끼지 못할 수도 있으나 튜닝을 통해 아주 적은 차이라도 개선을 가져올 수 있다면 시스템 전체적인 관점에서는 매우 큰 효과를 가져다 줄 수 있다는 점이 중요하다. 하루에 백만번 수행되는 SQL에 대하여 0.01초를 개선한다면 시스템 시간으로 하루에 일만초를 절약한 셈이 될 것이다. 이러한 SQL에 대해서는 현재 빠르게 수행되고 있다고 해도 더 빠르게 처리할 여지가 없는지 점검하고 가능한 모든 방안을 동원하여 개선시키도록 노력해야 한다.

▷disk_reads 수치가 높은 SQL

disk_reads는 SQL이 처리되는 과정에서 물리적인 IO를 통해 액세스한 블록의 수를 의미한다. 물리적 IO의 발생여부는 원하는 블록이 메모리에 캐쉬되어 있는지 여부에 따라 달라지므로 수행되는 횟수와 수행되는 시간대의 데이터베이스 캐쉬 상황에 따라 유동적이라고 할 수 있다. 그러나, buffer gets의 값과 비교하여 disk_reads의 비율이 높은 SQL은 Full Scan을 수행하는 SQL일 가능성이 큰데 그 이유는 Full Scan을 통해 액세스되는 블록들은 기본적으로 DB buffer Cache의 LRU 알고리즘에 의해 관리되지 않으므로 작업 후에 곧바로 메모리로부터 밀려나 버릴 가능성이 높기 때문이다. 반면에 인덱스를 통하여 액세스하는 경우, 일상적으로 액세스되는 테이블에 대해서는 인덱스의 root block과 branch block은 항상 메모리에 캐쉬 되어 있을 확률이 높으므로 물리적 IO를 유발하는 비율이 낮을 수 밖에 없다.

■Top SQL 추출기준

글을 맺기 전에 마지막으로 언급하고 싶은 것 하나는, 문제가 발생했을 때 문제의 원인이 bug로 인한 것일 가능성을 항상 염두에 두어야 헛된 고생을 덜한다는 것이다. 오라클도 사람이 만든 프로그램이므로 버그가 없을 수 없으나 다행히 오라클의 버그 및 패치 관리는 매우 훌륭한 편이다. 오라클 메타링크를 활용하여 유사한 문제가 보고된 적은 없는지 관련 버그에 대한 정보는 없는지 살펴보아야 하며, 평소에 정기적으로 패치 및 버전 관리를 해주는 것이 바람직하다.

오라클 시스템을 운영하다 보면 현실에서는 다양한 문제가 복잡하게 얽혀 나타나므로 명백한 원인을 파악하기가 쉽지 않을 때가 많지만, 운영 시 자신의 시스템에서 자주 발생하는 Wait 패턴 또한 분명히 존재하므로 굵은 가지들부터 하나씩 이해하고 해결해 나가다 보면 오라클이 우리가 보인 애정에 보답해줄 날이 올 것이라 믿는다. 지면 관계상 OPS(RAC) 관련 Wait을 비롯한 기타 Wait 이벤트에 대해 다루지 못한 점, 그리고 각 Wait 이벤트별로 좀더 친절한 설명과 사례를 제시하지 못한 점이 아쉬움으로 남지만, 누구든 아주 작은 것 하나라도 이 글을 통해 새로이 얻을 수 있었다면 그 이상 바랄 것은 없다.

Chapter 9 Tuning Buffer Cache

1.Oracle Database Architecture

1)Buffer Cache: Default, Keep, Recycle , nK buffer (개별적 할당가능)

                  - Using Parameter : \show parameter cache_size 로 조회 가능.

2)DBWn : 0~19 (20\개 프로세스 -> 개수 조절가능.

3)System, Temp , Sysaux, Datafile, Und

4)LRU 기반의 정책
             5) cache fusion(RAC 환경에서 인스턴스 failover)

6)증분checkpoint 메커니즘(lgwr부터 수행 scn번호 증분) 

7)Advisors:자동메모리관리(asmm)를 사용안할시 활성화.

8)대량 i/o작업시 os 공간에 private pool할당 ex)append hint 절 혹은 direct paht 설정 direct=Y

2..Buffer Hash Table for Lookups

1)  buffer cache를 효과적으로 관리하기 위해  hash chain 구조를 사용

2)  CBC latch

A  Cache된 data block을 찾을 때 획득해야 하는 latch

        B  Buffer를 탐색/변경하려는 p/s는 반드시 해당 체인을 관리하는 CBC latch를 획득해

                                   함

3)  hash bucket table

A.       header들 관리

B.       함수를 통해 그룹화된 버퍼 헤더 목록 관리 테이블

3.buffer chain

             1) 하나의 해시 버킷에 연결된 버퍼 헤더 집합

             2) 실제 양방향이다.

             3) 동일한 해쉬값을 갖는 헤더 블록들은 하나의 해쉬 버켓에 의해 관리

4) 헤쉬버켓의 버퍼 헤더들은 double linked list 형태로 연결되어 하나의 hash chain으로 관리

4.. buffer heather 들은 shared pool에 저장되어 있다.

                  1) 버퍼헤더는 포인터 값

                  2) 버퍼의 메타정보를 갖고 실제 버퍼 캐시와 연결된

                  3)bufferheader에 대한 정보는 x$bh

5..Working set

 1)buffer cache의 모든 buffer들은 반드시 LRU/LRUW list에 속한다

 2)instance 가동시 buffer pool에 최소 8개 할당

                           select set_id, dbwr_num from x$kcbwds order by set_id;

                           0000.. DBw0 이면

                           2개면 양분되어 0 1 0 1이 된다.

           3)LRU list + LRUW list(dirty list)

           4)working set 수 = _db_block_lru_latch로 결정

                           > select count(*) from v$latch_children

                           where name like 'cache buffers lru chain%';

#LRU list

main : 사용된 buffer들이 대한 list(used list). touch count 알고리즘을 쓰기 때문에 hot영역과 cold 영역으로 나뉜다.

sub : free buffer list. 먼저 확인하는 부분. 확인이 안되면 main으로 올라가서 lru end에 저장

# LRUW list(dirty list)

             main : 변경된 buffer list.

             sub : 현재 dbwn에 의해 기록중인 buffer list

<오라클 block은 어떻게 hash bucket에 적재되는가?>

racle은 buffer cache에 적재된 block을 검색하기 위해 DBA(Data Block Address)와 class number에 대해 간단한  hash 함수를 적용한 결과를 이용해 해쉬 bucket을 적재 /검색한다.

db_block_scan_max_pct(기본값은 40)

6. Symptoms

1)latch에 의한 경합이 문제

              2)Latch:cache buffer chains (CBC latch)    

                동일 table이나 index를 여러 세션이 동시에 스캔 수행시 발생

                SQL 문 있는지 확인시 가장 먼저 획득해야 하는 것.

7.Latch:cache buffer LRU chains

1)서로 다른 table이나 index들을 여러 세션이 동시에 처음 읽거나 데이터의 변경이 빈번할 때 발생

2)버퍼 캐시에 원하는 블록이 존재하지 않아 working set scan시 cache buffers LRU chain latch의 획득해야 함

               주요 원인은 비효율적인 SQL의 과도한 free buffer : hot block

8.Buffer busy waits

             1)buffer busy wait: 많은 세션이 특정블럭에서 경합이 자주 발생할때.

                  2)해결방법 : block size를 줄여 경합을 줄이거나 revers index를 고려해볼만도함.

                  3)free buffer wait: free buffer 를 찾지 못한경우(lru list에서 40% 탐색후 시점)

                 4)해결방법: buffer cache size 늘린다.

9.Read waits

1)Index Scan : DB File Sequential Read

2)FTS(Multi Block Access) : DB File Parallel Read, DB File Scattered Read

3)Read Wait Time이 길 경우

Disk에 과도한 요청을 하는 SQL을 튜닝한다

디스크의 용량을 추가한다.

10. Free buffer waits

1)Solution

Buffer Cache 사이즈를 적당히 조절한다

Cache 옵션을 사용한다

10g부터는 Small Table에서 FTS를 하는 빈도가 상당히 높다. 

à       Reload의 원인 -> Cache 옵션 지정

Keep, Recycle Pool을 사용한다

빠른 쓰기를 위해서 DBWn를 증가시킨다

Private I/O(OS Memory) Pool 사용

11. Cache hit ratio

메모리 상에 내가 요청한 데이터를 찾을 수 없기 때문에 떨어진다.(큰 데이터가 많이 있거나.... ) ->  keep / cache option 사용으로 해결

A low cache hit ratio, below 80%

해결방안 : size문제나 경합 문제를 찾아서 해결

12. Cache Buffer Chains Latch Contention

1)cache buffer chains latch에 대한 경합이 발생하는 경우

               원인 : 1> hot block    2> 잘못 작성된 SQL

               비효율적인 SQL문, execution 당buffer get이 높은 SQL문.

                 Logical reads가 높은 SQL문(large index range scan), full table scan or hot block(hot buffer)에 의한 경우

2)HOT block(hot buffer) 발생하는 경우

                 다수의 세션이 동일한 cache buffer chains 래피에 의해 보호되고 있는 하나 이상의 블록들을 반복적으로 접근할 대 발생

               db_file_scattered_read는 full scan발생

               db_file_sequential_read는 single block 에 대한 read가 많이 발생

               긴 해쉬 체인들(long hash chains)

               DBWR의 작업략이 많을 때 발생

               cache가 너무 작을 때 발생

3)해결 방법 및 고려 사항

               -Statpack이나 awrrpt를 통해서 상태 확인

             -cache buffers chains latch wait event를 일으키는 SQL 튜닝

             -execution 당 buffer get이 높은 SQL 튜닝

             -table full scan보다 index range scan이 빈번하게 발생하는 경우 index 의 검색 --range를 축소할 수 있도록 유도

             -table을 EXPORt하고, pctfree수치를 매우 높게 설정 후 데이터를 IMPORT

             -table의 block당 record의 수를 최소화

13.Finding Hot Segments

1)Characteristics of cache buffer chains latch contention:

              – Many accesses to one or more block under the same latch

              – Worse with larger block sizes

                CBC latch가 많이 발생하면

2)To find hot segments:

             SQL> SELECT * FROM ( SELECT owner, object_name,

                     object_type, statistic_name, sum(value)

                     FROM V$SEGMENT_STATISTICS

                 GROUP BY owner, object_name, object_type, statistic_name

                   ORDER BY SUM(value) DESC)

                    WHERE ROWNUM < 10;

                 query까지 조회 가능 : 비효율적인 것은 무엇인가…

14. Buffer Busy Waits

1) Application-level contention for buffers in the buffer cache

2)Identify buffer busy waits contention: block 별 wait 정보들을 조사할 때

SELECT object_name, value

FROM V$SEGMENT_STATISTICS

WHERE statistic_name 'buffer busy waits' AND

value > 20000;

SELECT class, count

FROM V$WAITSTAT

WHERE count>0

ORDER BY count DESC;

15.Calculating the Buffer Cache Hit Ratio

SELECT name, value

FROM v$sysstat

WHERE name IN ('db block gets from cache',

'consistent gets from cache',

'physical reads cache');

                 --dual에 놓고 계산--

PHYSICAL READS = 'physical reads cache')

LOGICAL READS = ('consistent gets from cache' +

'db block gets from cache')

HIT RATIO = 1 - PHYSICAL READS/LOGICAL READS

             select (1-(14081/642657))*100 from dual;

16. Buffer Cache Hit Ratio Is Not Everything

1) A badly tuned database can still have a hit ratio of 99% or better.

2) Hit ratio is only one part in determining tuning performance.

             SQL 구문, wait event와 함께 보아야한다.

3) Hit ratio does not determine whether a database is optimally tuned.

4)      Use the wait interface to examine what is causing a bottleneck:

(아래 세가지를 같이 보아야 한다.)

             – V$SESSION_WAIT

             – V$SESSION_EVENT

             – V$SYSTEM_EVENT

17.Interpreting Buffer Cache Hit Ratio

1)Hit ratio is affected by data access methods:

             – Full table scans (index full scan도)

             – Repeated scans of the same tables : 인위적 상승

             – Large table with random access : 100만건 있으면 첫번째 걸릴 수도있고 마지막에 걸리수도 있다.

             – Data or application design

2)Investigate increasing the cache size if:

             먼저 조사해야 할 부분

             – Hit ratio is low

   – Application is tuned to avoid full table scans : index 같은 것들을 더 추가하던가 하여야 한다.

18.Read Waits

1) List of wait events performing disk reads into the buffer cache:

             – db file sequential read (index)

비효율적인 SQL문장이나 비효율적인 index 스캔이 자주 수행ㅎ되는 경우 불필요한 물리적 I/O로 인해 db file sequential read대기가 증가할 수 있다.

선택도(selectivity)가 좋지 않은 index의 사용은 db file sequential read 대기의 주범이다.

부적절한 index의 사용은 i/O분만 아니라 버퍼 캐시의 경합을 유발할 수 있다.

SQL 문장에 최적화되고, index를 효율적으로 사용하는 것만으로 대부분의 문제를 미연에 방지할 수 있다.

             – db file parallel read (multi block)

             – db file scattered read (multi block) : 가장 일반적

                           db file scattered read 대기가 주로 발생하는 sql 문을 추출.

만일 불필요하게 FTS or index fast_full scan을 수행하고 있다면 SQL문 tuning하여 좀 더 합리적인 index를 생성해주면 문제는 해결

                           넓은 범위의 데이터를 읽을 때는 FTS가 훨씬 유리한 경우가 많다.

무리하게 index를 생성시켜 주는 것이 아니라, 해당 SQL문의 특성을 고려하여 FTS or index range scan이 유리한지에 대한 판단이 필요.

2)If wait time for reads is high:

– Tune the SQL statement that issues most disk reads by sorting V$SQL by DISK_READS and BUFFER_GETS.

                           V$SQL : 물리적 논리적 읽기를 저장한 view

                          – Grow buffer cache if needed.

                          – Reduce writes due to checkpointing.

                          – Add more disk capacity.

                           분산 효과를 높일 수 있다.

19.memory상에 free buffer가 없을 경우

1)buffer cache가 너무 작거나 dirty block들을 disk에 기록하는 작업이 빠르지 못한 경우. DBW의 갯수를 늘릴 수 있다.

2)ree buffer waits 대기이벤트는 버퍼캐쉬 내부에 데이터블록을 읽어 들이거나, CR이미지를 생성하기 위한 free buffer을 찾지 못할 때 발생

이것은 버퍼캐쉬가 너무 작건, 더티 블록들을 디스크로 기록하는 작업이 충분히 빠르지 못하다는 것을 의미힘

LRU list에서 free buffer를 찾을 때 _db_block_scan_max_pct(기본값 40) 값만큼 스캔을 하고도 free buffer를 찾지 못하면 스캔을 멈추고 DBWR에게 dirty block을 disk에 기록하고 free buffer를 확보할 것을 요청한다. 쓰기가 완료되면 해당 buffer를 사용한다.

             [발생 원인]

             1. 비효율적인 SQL문

                           v$sql.disk_reads를 수행하는 SQL문 조회.

해당 SQL문들은 full table scan, index fast full scan 또는 선택도가 좋지 않은 index를 사용하여 table을 접근한 것

             2. 불충분한 DBWR p/s 수

하나의 DBWR p/s만 존재한다면, 모든 working set에 대한 service를 하나의 DBWR p/s가 수행.

다수의 DBWR p/s가 존재한다면, working set을 균등하게 분배하여 더 많은 DBWR p/s는 더욱 효율적으로 working set을 service할 수 있고 더 높은 처리량을 보장

                           > select set_id, dbwr_num from x$kcbwds order by set_id.;

             3. 느린 I/O sub system

DBWR에서 db file parallel write 대기시간이 길게 나타난다면 I/O system에 문제가 있다고 판단

Direct I/O를 사용하는 경우 CPU 개수가 충분하다면, DB_writer_processes 값을 조정해서 DBWR의 개수를 증가시키는 것을 병행할 수 있다.

20.Solutions

1) Properly size the buffer cache. : ASMM 사용 권장

             2) Cache objects.

                           table의 option 중

                           cache - 상주시켜라

                                        FTS : M(hot)

                                        index : M(hot)

                           nocache

                                        FTS : cold 영역(L)으로

                                        index : hot영역으로(M)

             3) Use the keep and recycle pools.

               hit ratio 높이기

             4)Increase the writing speed of DBWn.

             5)Use private I/O pool.

                           direct path 방식을 적절히 사용

                           대용량일 경우                  

====================================================================

select total_waits

from v$system_event

where event='free buffer watis';

사용 가능한 buffer 대기수가 높으면 DBWn 수를 증가. I/O slave 구성 고려.