운영되는게 기적적인 블로그

NL조인으로 유도할 때 use_nl 힌트를 사용

해시 조인으로 유도할 때 use_hash 힌트 사용

힌트안에 인자를 나열한 순서는 조인 순서와는 무관하다. 조인 순서를 지정하고 싶을 때는 ordered 또는 leading힌트를 사용한다.

ordered 힌트는 FROM 절에 테이블을 나열한 순으로 조인하고자 할때 사용

leading 힌트는 인자에 나열한 순으로 조인할때 사용

FROM 절에 테이블을 나열한 순으로 조인하고자 할 때 force order 힌트 사용

NL 조인으로 유도할때 loop join 힌트 사용

Index() 힌트를 지정하면 해당 테이블은 인덱스를 통해 액세스한다. 인덱스명이나 컬럼명을 지정하지 않을땐 어떤 인덱스를 사용할지는 옵티마이저가 선택

NL 조인 특징

- 랜덤 액세스 위주의 조인 방식이다. 인덱스 구성이 아무리 완벽해도 대량 데이터를 조인할때는 힘들어잉

- 조인을 한 레코드씩 순차적으로 진행한다. 부분범위 처리가 가능하다면 조인할 대산 레코드가 아무리 많아도 빠른 응답속도를 낼수있다. 그러니 먼저 액세스되는 테이블 처리 범위에 의해 전체 일량이 결정됨

- 인덱스 구성 전략이 다른 조인 방식에 비해 특히 중요하다. 조인 컬럼에 대한 인덱스가 있으냐 없느냐, 있다면 컬럼이 어떻게 구성됐느냐에 따라 조인 효율이 크게 달라진다.

-> 즉 소량 데이터를 주로 처리하거나 부붐범위 처리가 가능한 온라인 트랜잭션 처리(OLTP) 시스템에 적합한 조인 방식

소트 머지 조인의 특징

조인을 위해 실시간으로 인덱스를 생성한다고 생각하자

양쪽 집합을 정렬한 다음에는 NL 조인과 같은 방식으로 진행하지만 PGA 영역에 저장한 데이터를 이용하기 때문에 빠르다고 앞선 문제에서 설명했다. 따라서 소트 부하만 감수한다면, 건건이 버퍼캐시를 경유하는 NL 조인보다 빠르다.

조인 컬럼에 대한 인덱스 유무에 영향을 받지않는다.

스캔 위주의 액세스 방식을 사용한다. 하지만 모든 처리가 스캔 방식으로 이루어지진 앙ㄴㅎ는다. 양쪽 소스 집합으로부터 조인 대상 레코드를 찾을때 인덱스를 이용한다면 랜덤 액세스가 일어난다.

해시 조인의 특징

조인 프로세싱 자체는 NL조인과 같지만, 건건이 Inner 집합을 버퍼캐시에서 탐색하지 않고 PGA에 미리 생성해둔 해시 테이블을 탐색하면서 조인한다는 점이 다르다. 해시 맵을 이용하므로 조인 컬럼에 인덱스가 없어서 상관없다.

해시 맵을 PGA에 생성해야 하므로 둘 중 어느 한쪽이 PGA에 담을 수 있을 정도로 충분히 작을때 효과적이며, 조인 조건 중 하나 이상이 '='조건일때만 사용할 수 있다.

스칼라 서브쿼리 캐싱 효과

스칼라 서브쿼리로 조인하면 오라클은 조인 횟수를 최소화하려고 입력 값과 출력 값을 내부 캐시에 저장해둔다. 조인할 때마다 일단 캐시에서 '입력 값'을 찾아보고, 찾으면 저장된 '출력 값'을 반환한다. 캐시에서 찾지 못할 때만 조인을 수행하며, 결과는 버리지 않고 캐시에 저장해둔다. 이런 캐싱 매커니즘은 조인 성능을 높이는데 큰 도움이 된다. 메인 쿼리 집합이 아무리 커도 조인할 데이터를 대부분 캐시에서 찾는다면, 조인 수행횟수를 최소화 할 수 있기 때문

_query_execution_cache_max_size 파라미터에 의해 사이즈를 결정

인덱스 ROWID는 테이블 레코드와 직접 연결된 구조가 아니다. 데이터파일상에서 테이블 레코드를 찾아가기 위한 논리적인 주소 정보다. 인덱스 ROWID에 포함된 데이터 블록 주소(= 데이터파일번호 + 블록번호)는 디스크 상의 블록 주소지만, 블록을 매번 데이터파일에서 읽는다면 성능은 이루 말할 수 없이 느리다. I/O 성능을 위한 버퍼캐시 활용이 필수인 이유다. ROWID가 가리키는 블록에서 버퍼캐시에서 먼저 찾아보고, 못 찾을때만 데이터파일에서 읽는다. 버퍼캐시에 적재한 후 읽는다.

캐시에서 블록을 읽을 때는 읽고자 하는 데이터 블록 주소를 해시 함수에 입력해서 해시 체인을 찾고 거기서 버퍼 헤더를 찾는다. 캐시에 적재할 때와 읽을 때 같은 해시 함수를 사용하므로 버퍼 헤더는 항상 같은 해시 체인에 연결된다. 반면 실제 데이터가 담긴 버퍼 블록은 매번 다른 위치에 캐싱되는데, 그 메모리 주소값을 버퍼 헤더가 가지고 있다.

버퍼캐시는 시스템 공유 메모리에 위치하므로 액세스를 직렬화하기 위한 Lock 메커니즘이 작동한다. 따라서 버퍼캐시에서 블록을 읽을 때마다 Latch와 Buffer Lock을 획득해야한다. 동시 액세스가 심할 때는 Latch와 Buffer Lock에 대한 경합까지 발생하므로 인덱스 ROWID를 이용한 테이블 액세스는 생각보다 고비용 구조다.

인덱스 ROWID는 테이블 레코드를 찾아가기 위한 위치 정보이다.

인덱스를 아무리 재생성해도 클러스터링 팩터가 좋아지지않는다. 인덱스 컬럼 순으로 정렬되도록 테이블을 재생성해야 좋아진다.

온라인 프로그램 튜닝 vs 배치 프로그램 튜닝

온라인 프로그램은 보통 소량 데이터를 읽고 갱신하므로 인덱스를 효과적으로 활용하는 것이 중요, 조인도 NL 방식, 인덱스를 이용해 소트 연산을 생략하여 부분범위 처리 방식으로 구현할 수 있다면, 온라인 환경에서도 대량 데이터를 조회할 때도 아주 빠른 응답 속도를 낼 수 있다.

반면, 대량 데이터를 읽고 갱싱하는 배치 프로그램은 항상 전체범위 처리 기준으로 튜닝해야한다. 처리대상 집합 중 일부를 빠르게 처리하는 것이 아니라 전체를 빠르게 처리하는 것을 목표로 삼아야 한다. 대량 데이터를 빠르게 처리하려면, 인덱스와 NL조인보다 Full Scan과 해시 조인이 유리한다. 그래서 부분 범위 처리가 전혀 효용성이 없다.

배치 I/O

인덱스 ROWID를 이용한 테이블 랜덤 액세스는 생각보다 고비용 구조다. 인덱스를 이용해 대량 데이터를 조회하면, 디스크 I/O 발생량이 증가하고 그만큼 성능이 나빠진다. 디스크 랜덤 I/O 성능을 높이려고 DBMS 업체들이 계속 노력을 기울이는 가운데, 오라클에서 최근 가장 눈에 띄는 개선은 배치 I/O 기능에서 찾을 수 있다.

읽는 블록마다 건건이 I/O Call 발생시키는 비효율을 줄이기위해 고안된게 배치 I/O임. 인덱스를 이용해 테이블을 액세스하다가 버퍼 캐시에서 블록을 찾지 못하면 일반적으로 디스크 블록을 바로 읽는데, 이 기능이 작동하면 테이블 블록에 대한 디스크 I/O Call을 미뤘다가 읽은 블록이 일정량 쌓이면 한꺼번에 처리한다.

배치 I/O 기능이 작동하면 인덱스를 이용해서 출력하는 데이터 정렬 순서가 매번 다를 수 있다는 사실. 테이블 블록을 모두 버퍼 캐시에서 찾을때는 (버퍼캐시 히트율 100%) 기존처럼 인덱스 키값 순으로 데이터가 출력되지만, 그렇지 앙ㄴㅎ을때 (버퍼캐시 히트율 < 100%) 즉, 실제 배치 I/O가 작동할 때는 데이터 출력 순서가 인덱스 정렬 순서와 다를 수 있다.

인덱스 튜닝의 두가지 핵심요소

1, 인덱스 스캔 과정에서 발생하는 비효율을 줄이는 것. 즉 인덱스를 스캔한 양에 비해 얻은 결과 건수가 적으면 비효율적

2, 테이블 액세스 횟수를 줄이는 것. 인덱스 스캔 후 테이블 레코드를 액세스할 때 랜덤 I/O 방식을 사용하므로 이를 랜덤 액세스 최소화 튜닝, 테이블을 액세스한 양에 비해 얻은 결과 건수가 적으면 비효율적이다.

인덱스 스캔 효율

테이블과 달리 인덱스에는 '같은 값'을 갖는 레코드들이 서로 군집해 있다. '같은 값'을 찾을 때 '=' 연산자를 사용하므로 인덱스 컬럼을 앞쪽부터 누락없이 '=' 연산자로 조회하면 조건절을 만족하는 레코드는 모두 모여있다. 따라서 인덱스 스캔 효율은 인덱스 컬럼을 조건절에 모두 등치 조건으로 사용할 때 가장 좋다. 인덱스 컬럼 중 일부가 조건절에 없거나 '=' 조건이 아니더라도, 그것이 뒤쪽 컬럼일 때는 비효율이 없다. 

인덱스 선행 컬럼중 어느 하나가 조건절에 없거나 '=' 조건이 아닌 연산자로 조회하면 조건절을 만족하는 레코드가 서로 흩어진 상태가 된다. 결국, 선행 컬럼이 모두 '=; 조건인 상태에서 첫번째 나타나는 범위검색 조건까지만 만족하는 인덱스 레코드는 모두 연속해서 모여있지만, 그 이하 조건까지 만족으로 레코드는 비교 연산자 종류에 상관없이 흩어진다.

즉, 인덱스 선행 컬럼이 조건절에 없거나 부등호, BETWEEN, LIKE 같은 범위검색 조건이면, 인덱스를 스캔하는 단계에서 비효율이 생긴다.

B*Tree 인덱스 구조

브랜치 블록의 각 로우는 하위 블록에 대한 주소값을 갖는다

브랜치 블록 각 로우의 키 값은 하위 블록이 갖는 값의 범위를 의미한다.

리프 블론의 각 로우는 테이블 로우에 대한 주소갑승 갖는다.

리프 블록 각 로우의 키 값과 테이블 로우의 키 값은 서로 일치한다.

리프 블록의 각 로우와 테이블 로우 간에는 1:1 관계다.

리프 블록끼리는 이중연결리스트 구조다.

인덱스 ROWID

오라클 초기 버전의 제한 RowID 포맷 : 데이터파일 번호 + 블록 번호 + 블록 내 로우번호

오라클 8버전 부터 확장 ROWID 포맷 : 오브젝트 번호 + 데이터파일 번호 + 블록 번호 + 블록 내 로우번호

어떤 포맷을 사용하는지는 오브젝트 유형에 따라 다르다.

WITH 절을 이용해 INDEX 힌트를 기술할때 

DECLARE @CUST_NO INT 방법이 있다.

FORCESCAN을 지정하면 고객번호로 검색(SEEK)하지 않고 Full Scan 하면서 필터링

WITH절에 INDEX(1)를 지정하면 고객번호로 클러스터형 인덱스를 검색

INDEX(0)을 지정하면 클러스터형 인덱스를 Scan하면서 고객번호를 필터링

클러스터형 인덱스가 없는 상황에서 WITH절에 INDEX(0)을 지정하면 테이블을 Full Scan하면서 조건절을 필터링하면서 INDEX(1)을 지정하면 구문 오류가 발생한다.

Index Range Scan 하려면, WHERE 절에 인덱스 선두컬럼에 대한 조건이 반드시 있어야 하지만, '=' 조건일 필요는 없다. 부등호, BETWEEN, LIKE 등 모두 가능하다.

Index Full Scan

인덱스 리프 블록을 처음부터 끝까지 모두 스캔하는 방식이며, 인덱스 선두 컬럼이 조건절에 없을 때 사용된다.

인덱스 선두 컬럼이 조건절에 없으면 Index Range Scan이 불가능하므로 테이블 Full Scan해야하는데, 컬럼이 많은 큰 테이블을 스캔하려면 블록 I/O가 많이 발생하므로 성능이 느리다. 그럴 때 컬럼이 적은 인덱스를 스캔하면 I/O 발생량을 줄일 수 있다. 단, 인덱스 필터 조건을 만족하는 데이터가 적어야 한다. 필터 조건을 만족하는 데이터가 많으면 테이블 랜덤 액세스도 그만큼 많이 발생하므로 테이블 전체 스캔보다 성능이 훨씬 느려진다. 필터 조건을 만족하는 데이터가 많더라도 결과집합중 앞쪽 일부만 스캔하고 멈춘다면, 즉 부분범위 처리를 활용할 수 있다면, Index Full Scan이 효과적일 수 있다. 인덱스 앞쪽에서 조건을 만족하는 데이터를 빨리 찾을 수 있기 때문이다.

Unique 인덱스 구성 컬럼을 모두 '=' 조건으로 검색하면 Index Unique Scan이 선택되지만, 전체가 아닌 일부 컬럼으로 검색할 때는 Range Scan이 선택된다.

인덱스 리프 블록들은 물리적으로는 여러 익스텐트에 흩어진 상태로 저장된다. 하지만, 정렬 순서에 따라 이중연결리스트 방식으로 서로 연결돼 있으므로 순서대로 스캔하면 결과집합의 순서를 보장한다.

Index Fast Full Scan은 인덱스 리프 블록끼리의 논리적인 연결 순서를 따르지 않고 Table Full Scan처럼 HWM아래 익스텐트 전체를 Multiblock I/O 방식으로 Full Scan하므로 결과 집합의 순서를 보장하지 않는다.

Index Fragmentation

Index Skew는 인덱스 엔트리가 왼쪽 또는 오른쪽에 치우치는 현상을 말한다. 예를 들어, 시계열적으로 증가하는 인덱스에서 과거 데이터를 일괄 삭제하고 나면 왼쪽 리프 블록들은 텅비지만 오른쪽 블록들은 꽉 찬 상태가 된다.

Index Sparse는 인덱스 블록 전반에 걸쳐 밀도가 떨어지는 현상을 말한다.

비트맵 인덱스

비트맵 인덱스는 성별처럼 Distinct Value 개수가 적을 때 저장효율이 매우 좋다. 그런 컬럼이라면 B*Tree 인덱스보다 훨씬 적은 용량을 차지하므로 인덱스가 여러 개 필요한 대용량 테이블에 유용하다. 주로 다양한 분석관점을 가진 팩트 테이블이 여기에 속한다.

비트맨 인덱스는 여러 인덱스를 동시에 사용할 수 있다. 비트맨 인덱스로 Bitwise 연산을 수행함으로써 테이블 액세스량을 크게 줄일 수 있다면 극적인 성능 향상을 가져다 준다. 따라서 다양한 조건절이 사용되는, 특히 정형화되지 않은 임의 질의(ad-hoc query)가 많은 대용량 DW/OLAP 환경에 적합하다.

다만, 비트맵 인덱스는 Lock에 의한 DML 부하가 심한 것이 단점이다. 레코드 하나만 변경되더라도 해당 비트맵 범위에 속한 모든 레코드에 lock이 걸린다. OLTP성 환경에 비트맨 인덱스를 쓸 수 없는 이유다.

함수기반 인덱스

조건절에서 인덱스 컬럼에 함수를 적용하면 정상적인 Index Range Scan이 불가능하다. 인덱스에는 가공하지 않은 값을 기준으로 정렬해서 저장했는데, 가공한 값으로 검색하면 수직적 탐색을 통해 스캔 시작점과 끝 지점을 찾을 수 없다.

그럴 때 인덱스에도 함수를 적용한 상태로 값을 저장하면 정상적인 Index Range Scan이 가능한데 이게 함수기반 인덱스이다.

리버스 키 인덱스

일련번호, 입력일시, 변경일시 등 오름차순 한 방향으로만 값이 증가하는 컬럼에 인덱스를 생성하면, 동시에 INSERT 하려는 다중 트랜잭션에 의해 맨 우측(마지막) 리프 블록에 경합이 발생할 수 있다. 그럴 때 인덱스 키 값을 역으로 변환해서 저장하면 신규로  INSERT하는 값들이 여러 리프 블록에 흩어지므로 경합도 자연스럽게 줄어든다.

REVERSE

IOT는 테이블을 인덱스 구조로 관리한다. 일반 힙 구조 테이블은 값을 무작위로 입력하지만, IOT는 저장한 키 값 순으로 정렬 상태를 유지, 키 값이외의 컬럼도ㅗ 모두 인덱스 리프 블록에 저장하므로 테이블 랜덤 액세스가 전혀 발생하지 않는다.

클러스터는 값이 같은 레코드를 한 블록(데이터가 많으면 연결된 여러 블록)에 모아서 저장하므로 인덱스를 이용한 테이블 랜덤 액세스를 줄이는데 도움을 준다.

테이블 파티션은 사용자가 지정한 기준에 따라 데이터를 세그먼트 단위로 모아서 저장한다. 따라서 특정 조건을 만족하는 데이터를 인덱스를 이용한 랜덤 액세스가 아닌 Full Scan 방식으로 찾을 수 있다.

인덱스를 정상적으로 Range Scan 할 수 없는 이유는 인덱스 스캔 시작점을 찾을 수 없기 때문이다. 인덱스에서 일정 범위를 스캔한다는 뜻으로 시작지점과 끝지점이 있어야한다.

인덱스 액세스 조건 컬럼을 조건절에서 가공할때 못쓴다. 인덱스에는 가공하지 않은 값이 저장돼 있는데, 가공한 값을 기준으로 검색하려면 스캔 시작점을 찾을 수 없다. 스캔 끝지점도 찾을 수 없다.

LIKE로 중간 값을 검색할 때도 마찬가지

오라클은 구성 컬럼이 모두 NULL인 레코드는 인덱스에 저장하지 않는다. 따라서 단일 컬럼에 생성한 인덱스에 대한 IS NULL 조건으로는 Index Range Scan이 불가능하다.

2개 이상 컬럼으로 구성된 결합 인덱스에 대해서는 IS NULL 조건에 대한 Index Range Scan이 가능

OR 조건에 대한 Index Range Scan

OR 조건은 기본적으로 Index Range Scan을 위한 액세스 조건으로 사용할 수 없다. OR 조건으로는 수직적 탐색을 통해 스캔 시작점을 찾을 수 없기 때문이다. 다만 CONCATENATION (옵티마이저에 의한 UNION ALL 분기)로 처리했을때 각각 수직 탐색에 의한 액세스 조건으로 사용할 인덱스가 있다면, Index Range Scan이 가능하다.

실행계획을 확인하는 법

오라클이 기본으로 제공하는 쿼리툴 SQL *PLUS에서

explain plan for

SELECT ....

PLAN_TABLE에서 저장된 정보를 읽어 분석하기 쉬운 형태로 포매팅하는 방법

dbms_xplan.display

SELECT * FROM table ( dbms_xplan.display (null, null, 'typical') );

SQL Server에서 예상 실행계획을 출력하는법

set showplan_text on

오라클 예상 실행계획에서 기본으로 (dbms_xplan.display 함수 세번째 인자에 'typical' 입력) 확인할 수 있는 정보는 Plan Hash Value, 오브젝트 액세스 방식(Operation), 오브젝트명, 예상 Cardinality(Rows), 예상 데이터 크기(Bytes), 예상 Cost, 에상 CPU Time, 조건절 정보 (Predicate Information)

dbms_xplan.display 세번째 인자에 alia, outline, advanced 등을 지정하면, Query Block 및 Object Alias 정보, Outline 정보, Column Projection 정보 등을 추가로 확인할 수 있음.

오라클 AutoTrace에서 확인할 수 있는 정보

- 예상 실행 계획

- 실제 디스크에서 읽은 블록수

- 실제 기록한 Redo 크기

AutoTrace

- set autotrace on : SQL을 실행하고 결과집합과 함께 예상 실행계획 및 실행통계를 출력

- set autotrace on explain : SQL을 실행하고 결과집합과 함께 예상 실행계획을 출력

- set autotrace on statistics : SQL을 실행하고 결과집합과 함께 실행통계를 출력

- set autotrace traceonly : SQL을 실행하지만 결과는 출력하지 않고, 예상 실행계획과 실행통계만 출력

- set autotrace traceonly explain : SQL을 실제로 실행하지않고, 예상 실행 계획만 출력

- set autotrace traceonly statistics : SQL을 실행하지만 결과는 출력하지않고 실행통계만 출력

statistics_level 파라미터를 all로 설정하면, SQL 트레이스가 메모리에 수집된다.

gather_plan_statistics는 파라미터가 아니라 옵티마이저 힌트다. -> SQL 트레이스 정보를 서버 파일이 아닌 SGA메모리에 기록한다.

V$SQL_PLAN)STATISTICS_ALL 뷰에서 직접 조회할 수 있지만. dbms_xplan.display_cursor 함수를 이용하면 분석하기 쉬운 포매팅

dbms_xplan.display_cursor 첫번째와 두번째 인자에는 SQL커서의 ID와 Child_NUMBER입력

TKProf 유틸리티를 사용하면 SQL 트레이스 파일을 분석해서 리포트 파일을 생성해 준ek

- tkprok ora10g_ora_14370_oraking.trc report.prf sys=no

트레이스 파일명, 결과 파일명 (확장자는 자유), SQL을 파싱하는 과정에서 내부적으로 수행되는 SQL문장을 제외

AutoTrace의 'recursive calls'는 하드파싱 과정에서 딕셔너리를 조회하거나 DB저장형 함수에 내장된 SQL을 수행할때 발생한 Call 횟수를 표시

monitor 힌트는 실시간 SQL 모니터링을 위해 사용하는 힌트. 리포트는 dbms_sqltune.report_sql_moniter 함수로 출력

DBMS_XPLAN.DISPLAY_CURESOR 정보

- Starts : 각 오퍼레이션 단계별 실행 횟수

- E-Rows : 옵티마이저가 예상한 Rows

- A-Rows : 각 오퍼레이션 단계에서 읽거나 갱신한 로우수 (rows)

- A-Times : 각 오퍼레이션 단계별 소요시간 (time)

- Buffers : 캐시에서 읽은 버퍼 블록 수  (cr(query), current)

- Reads : 디스크에서 읽은 블록수 (pr)

SQL 트레이스 확인하는 옵션

set statistics profile on : 각 쿼리가 일반 결과집합을 반환하고 그 뒤에는 쿼리 실행 프로필을 보여 주는 추가 결과집합을 반환한다. 출력에는 다양한 연산자에서 처리한 행수 및 연산자의 실행 횟수에 대한 정보도 포함

set statistics io on : Transact-SQL 문이 실행되고 나서 해당 문에서 만들어진 디스크 동작 양에 대한 정보 표시

set statistics time on : 각 Transact-SQL 문을 구문 분석, 컴파일 및 실행하는 데 사용한 시간을 밀리초 단위로 표시

프로세스가 공유 메모리의 버퍼캐시, 라이브러리 캐시에서 정보를 읽으려면 래치를 반드시 획득해야한다. 래치를 획득하는 과정에 경합이 발생하면 대기 이벤트가 나타나지만, 경합없이 바로 읽으면 대기 이벤트가 나타나지 않는다.

대기 이벤트는 프로세스가 CPU를 OS에 반환하고 수면(Sleep) 상태로 진입하는 원인을 기록하기 위해 개발

1. 프로세스가 필요로 하는 특정 리소스가 다른 프로세스에 의해 사용중일때

2. 다른 프로세스에게 작업을 요청하고 해당 작업이 완료되기를 기다릴때

3. 프로세스가 할일이 없을때

대기 이벤트 발생

대기 이벤트가 나타나는 대표적인 예 . SGA 공유 메모리에서 특정 자원을 액세스하려고 래치를 획득하는 과정에 다른 프로세스 경합이 발생하거나, 디스크로부터 블록 I/O을 요청하거나, 클라이언트로부터 다음 작업 요청이 오기를 기다리는 경우

Shared Pool에서 특정 오브젝트 정보 또는 SQL 커서를 위한 Free Chunk를 할당받으려 할때 shared pool 래치를 할당받아야한다. latch: shared pool 대기 이벤트는 shared pool 래치를 할당받는 과정에 발생하는 경합과 관련 있으며, 하드 파싱을 동시에 심하게 일으킬때 나타난다.

library cache lock과 library cache pin 대기이벤트는 주로 SQL 수행도중 DDL을 수행할때 발생

free buffer waits 대기 이벤트는 서버 프로세스가 버퍼 캐시에서 Free Buffer를 찾지 못해 DBWR에게 공간을 확보해 달라고 신호를 보낸후 대기할때 발생

log file sync 대기 이벤트는 커밋 명령을 전송받은 서버 프로세스가 LGWR에게 로그 버퍼를 고르 파일에 기록해 달라고 신호를 보낸 후 대기할때 나타난다.

대기 이벤트를 기반으로 세션 또는 시스템 전체에 발생하는 병목 현상과 그 원인을 찾아 문제를 해결하는 방법,과정을 대기 이벤트 기반 또는 응답 시간 분석 성능관리 방법론 이라고 한다.

OWI(Oracle Wait Interface)는 Response Time Analysis 방법론을 지원하려고 오라클이 제공하는 기능과 인터페이스를 통칭

AWR(Automatic Workload Repository)는 성능 관련 데이터를 주기적으로 수집함으로써 Ratio 기반 분석과 대기 이벤트 기반 분석을 모두 지원하는 오라클 성능 관리 표준도구

Response Time = Servie Time + Wait Time, = CPU Time + Queue Time

Service Time(Cpu Time)은 프로세스가 정상적으로 동작하며 일을 수행한 시간

Wait Time (Queue Time)은 Cpu를 OS에 반환하고 수면상태에서 대기한 시간

오라클은 전통적으로 사용하던 Ratio 기반 성능 분석 방법론에 응답 시간 분석 방법론을 더해 Statspack을 개발하였고 이를 확장 및 업드레이드한 것이 AWR이다

AWR 보고서를 활용해 성능 이슈를 해결하려면 peak 시간대 또는 장애 발생 시점을 전부해 가능한 짧은 스냅샷 구간을 선택해야한다. 

sar, topas, vmstat, osstat등 os 모니터링 도구를 이용해 CPU, 메모리, I/O 사용량 정보를 수집하고 이를 통해 peak 시간대를 파악

dba_hist_로 시작하는 각종 뷰를 이용해 사용자가 직접 AWR 보고서에 출력되는 항목 볼수 있음

보고서 요약에는

- 캐시 크기, 부하 프로파일, 인스턴스 효율성, 최상위 대기 이벤트, Shared Pool 통계, IO 프로파일, 메모리통계 포함 (SQL 통계는 포함되지않지만 보고서를 활용하는 주요 내용중 하나)

Soft Parse % : 실행계획이 라이브러리 캐시에서 찾아져 하드파싱을 일으키지 않고 SQL을 수행한 비율

(전체 Parse Call 횟수 - 하드파싱 횟수) / (전체 Parse Call 횟수) * 100

Execute to Parse % : Parse Call 없이 곧바로 SQL을 수행한 비율, 즉 커서를 애플리케이션에서 캐싱한 채 반복 수행한 비율

Parse CPU to Parse Elapsd % : 파싱 총 소요 시간 중 CPU TIME이 차지한 비율, 파싱에 소요된 시간 중 실제 일을 수행한 시간 비율, 이 값이 낮다면 파싱 도중 대기가 많이 발생함

오라클 10g부터 시스템에 문제가 생겼을때 ASH 도입 (Active Session History)

v$active_session_history 뷰를 통해 세션 히스토리 정보확인하고 과거 시점에 발생한 장애 및 성능 저하 원인까지 세션 레벨로 분석가능

AWR로 옮겨진 더 오래된 과거의 세션 히스토리는 dba_hist_active_sess_history에서 확인

Rat는 DB 구조 변경, 버전 업그레이드, 솔루션 도입, OS/하드웨어/스토리지 변경이 시스템에 미치는 영향을 사전에 파악하고 평가할 수 있게 도와줌

SPA는 데이터베이스 변경 작업이 구체적으로 SQL 실행계획에 어떤 영향을 미치는지 분석할 수 있게 도와주는 기능으로 오라클 RAT 옵션중 하나

v$session_wait 뷰를 통해 문제의 대기 이벤트를 가장 많이 발생시키는 세션 목록 확인

v$active_session_history 뷰를 통해 문제의 세션들이 어떤 SQL을 수행하고 있는 확인

v$sql 뷰를 통해 문제 SQL의 전체 문장과 수행 통계 (실행횟수, 평균 소요시간, 평균 블록 I/O)확인

오라클은 물리적인 OS 파일의 집합을 데이터베이스라고 부른다.

SGA 공유 메모리 영역과 이를 액세스하는 프로세스 집합을 합쳐서 인스턴스라고 부른다.

하나의 인스턴스가 하나의 데이터베이스를 액세스하지만, RAC (Real Application Cluster) 환경에서는 여러 인스턴스가 하나의 데이터베이스를 액세스할 수 있다. 하나의 인스턴스가 여러 데이터베이스를 액세스할 수는 없다.

백그라운드 프로세스

SMON (System Montior) : 장애가 발생한 시스템을 재기동할때 인스턴스 복구를 수행하고, 임시 세그먼트와 익스텐트를 모니터링한다.

PMON (Process Monitor) : 이상이 생긴 프로세스가 사용하던 리소스를 복구한다.

DBWn (Database Writers) : 버퍼캐시에 있는 Dirty 버퍼를 데이터파일에 기록한다.

LGWR (Log Writer) : 로그 버퍼 엔트리에 Redo 로그 파일을 기록한다.

CKPT (Checkpoint)  :이전에 Checkpoint가 일어났던 마지막 시점 이후의 데이터베이스 변경 사항을 데이터파일에 기록하도록 트리거링하고, 기록이 완료되면 현재 어디까지 기록했는지를 컨트롤 파일과 데이터파일 헤더에 저장한다.

Write Ahead Logging 방식을 사용하는 DBMS는 Redo 로그에 기록해 둔 버퍼 블록에 대한 변경사항 중 현재 어디까지를 데이터파일에 기록했는지 체크포인트 정보를 관리해야한다. 이는 버퍼캐시와 데이터파일이 동기화된 시점을 가리키며, 장애가 발생하면 마지막 체크포인트 이후 로그 데이터만 디스크에 기록하므로써 인스턴스를 복구할 수 있도록 하는 용도로 사용된다. 이 정보를 갱신하는 주기가 길수록 장애 발생 시 인스턴스 복구 기간도 길어진다.
Archiver (ARCn) : 꽉 찬 Redo 로그가 덮어 쓰여지기 전에 Archive 로그 디렉토리로 백업한다.

RECO (Recoverer) : 분산 트랜잭션 과정에 발생한 문제를 해결한다.

데이터 저장 구조

블록(페이지): 대부분 DBMS는 블록 단위 I/O 한다. 이는 하나의 레코드에서 하나의 컬럼만 읽더라도 그것이 속한 블록을 통째로 읽게 됨을 뜻한다.

익스텐트 : 공간을 확장하는 단위다. 테이블이나 인덱스에 데이터를 입력하다가 공간이 부족해지면 해당 오브젝트가 속한 테이블스페이스로부터 추가적인 공간을 할당받는데 이때 정해진 익스텐트 크기의 연속된 블록을 할당받는다.

세그먼트 : 데이터 저장 공간을 사용하는 오브젝트(테이블, 인덱스, 파티션, 클러스터, LOB)를 저장공간을 사용하지 않는 오브젝트(뷰, 시너님, 시퀀스, 함수, 프로시저, 트리거)와 구분해서 세그먼트라고 부른다. 저장공간을 사용한다는 것은 테이블 스페이스로부터 한 개 이상의 익스텐트를 할당받음을 뜻한다. 세그먼트는 익스텐트의 집합이라고 말할 수 있는데, 익스텐트 내 블록이 논리적으로 서로 인접한 반면, 익스텐트끼리 서로 인접하지는 않는다.

테이블스테이스 : 세그먼트를 담는 컨테이너로서, 여러 데이터파일로 구성된다. 각 세그먼트는 정확히 한 테이블스페이스에만 속하지만, 한 테이블스페이스에는 여러 세그먼트가 존재할 수 있다. 한 세그먼트는 여러 데이터파일에 걸쳐 저장된다. 한 테이블스페이스가 여러 데이터파일로 구성된다.

Undo, Redo

Undo를 사용하는 목적

1. Transaction Rollback

2. Transaction Recovery (Instance Recovery 시 rollback 단계)

3. Read Consistency

Redo 로그 목적

1. Database Recovery (=Media Recovery)

-> 물리적으로 디스크에 결함이 생기는 등 Media Fail 발생 시 데이터베이스를 복구하기 위해 사용되며 Archived Redo 로그를 이용하게 된다.

2. Cache Recovery (-> Instance Recovery 시 roll forward 단계)

-> 모든 데이터베이스 시스템이 I/O 성능을 위해 버퍼캐시를 사용하지만 버퍼캐시는 휘발성이다. 캐시에만 적용한 변경사항을 아직 데이터파일레 기록하지 않은 상태에서 정전 등이 발생해 인스턴스가 비정상적으로 종료되면, 이때까지 작업이 모두 날라간다. 이런 트랜잭션 데이터 유실을 대비하기 위해 Redo 로그를 남긴ㄴ다.

3. Fast Commit

-> 변경된 메모리 버퍼 블록을 데이터 파일에 기록하는 작업은 랜덤 액세스 방식으로 이루어지기 때문에 느리다. 하지만 로그는 Append 방식으로 기록하므로 빠르다. 따라서 트랜잭션에 의한 변경사항을 건건이 데이터파일에 기록하는것보단 우선 Append 방식으로 빠르게 로그 파일에 기록하고, 버퍼캐시 블록과 데이터파일 블록 간 동기화는 적절한 수단 (DBWR, Checkpoint를 이용해 나중에 일관 수행한다.(Batch)

* 동시에 많은 트랜잭션이 몰려 로그 스위치가 너무 자주 발생하게 되면 백업을 완료하지못한 Online Redo로그로 스위칭이 일어나면서 DB Hang이 일어날 수 있다. 그래서 적절한 크기와 개수의 Redo 파일을 할당해야한다.

Redo 매커니즘

Log Force at commit

-> 메모리상의 로그 버퍼는 휘발성이므로 트랜잭션의 영속성을 보장하려면 최소한 커밋 시점에는 로그를 메모리가 아닌 데이터파일에 안전하게 기록해야한다,

Fast Commit

-> 커밋 정보가 로그파일에 기록돼 있기만 하면 인스턴스 Crash가 발생하더라도 Redo로그를 이용해 언제든 복구가능하므로 사용자 프로세스는 안심하고 커밋할 수 있다.

Write Ahead Logging

-> 버퍼캐시의 Dirty 블록들을 데이터 파일에 기록하기 전에 먼저 LGWR가 해당 Redo 엔트리를 모두 Redo 로그 파일에 기록했음이 보장되어야 한다.

버퍼 블록의 상태

Free 버퍼 : 인스턴스 기동 후 아직 데이터가 읽히지 않아 비어있는 상태 (클린 버퍼)이거나 데이터가 담겼지만 데이터파일과 서로 동기화돼 있는 상태여서 언제든지 덮어 써도 무방한 버퍼 블록, 오라클이 데이터파일로부터 새로운 데이터 블록을 로딩하려면 먼저 Free 버퍼를 확보해야한다. Free 상태인 버퍼에 변경이 발생하면 그 순간 더티 버퍼로 바뀐다.

Dirty 버퍼 : 버퍼캐시에 적재된 이후 변경이 발생했지만. 아직 데이터파일에 기록하지 않아 동기화가 필요한 버퍼 블록을 말한다. 이 버퍼 블록들을 다른 데이터 블록을 위해 재사용하려면 데이터파일에 먼저 기록해야 하며, 그 순간 Free 버퍼로 상태가 바꾸니다.

Pinned 버퍼 : 일기 쓰기 작업을 위해 현재 액세스 되고 있는 버퍼 블록을 말한다.

SQL 언어 특징

구조적, 집합적, 선언적

SQL 처리 과정

1. SQL 파싱 (파싱트리 생성 -> Syntax 체크 -> Semantic 체크)

2. SQL 최적화 (옵티마이저)

3. 로우 소스 생성 (프로시저, 로우 소스 생성기)

SQL 최적화

1. 사용자로부터 전달받은 쿼리를 수행하는 데 후보군이 될만한 실행계획들을 도출

2. 데이터 딕셔너리에 미리 수집해둔 오브젝트 통계 및 시스템 통계정보를 이용해 실행계획 예상비용 산정

3. 최저 비용을 나타내는 실행계획 선택

옵티마이저 힌트

- 명령어로써 특별한 이유가 없는 한 그대로 실행된다.

무시되는 경우는

1. 문법적으로 맞지 않게 힌트를 기술

2. 잘못된 참조 사용 (없는 테이블, 별칭 사용, 없는 인덱스 명)

3. 논리적으로 불가능한 액세스 경로 (조인절에 등치조건없는데 해시조인으로 유도, Count해야하는 쿼리에 Null 허용하는 단일 컬럼으로 생성한 인덱스를 사용하도록 힌트 지정)

4. 의미적으로 맞지 않게 힌트를 기술 (서브쿼리에 unnest와 push_subq를 같이 기술)

5. 옵티마이저에 의해 내부적으로 쿼리 변환

6. 버그

옵티마이저 힌트를 사용하면 SQL 최적화 소요시간은 오히려 감소

옵티마이저 힌트 사용할때

1. 힌트안에 인자를 나열할때 ','를 사용할 수 있지만 힌트와 힌트 사이에 쓰면 안된다.

2. 테이블을 지정할 때 스키마명까지 명시하면 안된다.

3. FROM 절 테이블명 옆에 ALIAS를 지정했다면, 힌트에도 반드시 ALIAS를 사용해야한다.

블록 단위 I/O

오라클을 포함한 모든 DBMS에서 I/O 블록단위로 이루어진다. 즉 하나의 레코드를 읽더라도 레코드가 속한 블록 전체를 읽는다.

블록 단위 I/O는 버퍼캐시와 데이터파일 I/O 모두에 적용된다.

- 데이터파일에서 DB버퍼캐시로 블록을 적재할 때

- 데이터파일에서 블록을 직접 읽고 쓸때 (Direct Path I/O)

- 버퍼캐시에서 블록을 읽고 쓸때

- 버퍼캐시에서 변경된 블록을 데이터파일에 쓸때

* 데이터 딕셔너리에 저장된 테이블 및 컬럼 정보를 딕셔너리 캐시에 적재할 때는 로우 단위로 I/O한다. 딕셔너리 캐시를 로우캐시라고 부르는 이유다.

버퍼캐시 탐색 매커니즘

Direst Path I/O를 제외한 모든 블록 I/O는 메모리 버퍼캐시를 경유한다.

- 인덱스 루트 블록을 읽을 때

- 인덱스 루트 블록에서 얻는 주소 정보를 브랜치 블록을 읽을 때

- 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때

- 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때

- 테이블 블록을 Full Scan 할 때

버퍼캐시 히트율 (BCHR)

= (캐시에서 곧바로 찾은 블록 수 / 총 읽은 블록 수 ) x 100

= ( ( 논리적 I/O - 물리적 I/O) / 논리적 I/O) x 100

= ( 1 - (물리적 I/O) / (논리적 I/O) ) x 100

논리적 I/O query항목 + current항목

LRU 알고리즘

DBMS는 사용 빈도가 높은 데이터 블록들이 버퍼캐시에 오래 남아있도록 하기 위해 해당 알고리즘 사용

시퀀셜 엑세스 vs 랜덤 엑세스

시퀀셜 엑서스 : 논리적 또는 물리적으로 연결된 순서에 따라 차례로 블록을 읽어나가는 방식. 인덱스, 테이블을 스캔할 때 사용

랜덤 엑세스 : 논리적, 물리적 순서를 따르지 않고, 레코드 하나를 읽기 위해 한 블록씩 접근. 인덱스를 스캔하면 얻은 ROWID로 테이블 블록을 엑세스할때 사용

Single Block I/O vs Multiblock I/O

Single Block I/O : 인덱스를 이용할 때는 기본적으로 인덱스와 테이블 블록 모두 Single Block I/O 방식을 사용한다. 인덱스는 소량 데이터를 읽을 때 주로 사용

- 인덱스 루트 블록을 읽을때

- 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때

- 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때

- 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때

Multiblock I/O : 캐시에서 찾지 못한 특정 블록을 읽으려고 I/O Call할 때 디스크 상에 그 블록한 인접한 블록들들 한꺼번에 읽어 캐시에 미리 적재하는 기능. db_file_multiblock_read_count 파라미터에 의해 결정

인접한 블록이란 같은 익스텐트에 속한 블록을 의미하며 Multiblock I/O 방식으로 읽더라도 익스텐트 경계를 넘지 못한다. 예를 들어 한 익스텐트에 20개 블록이 담겨있고 Multiblock I/O 단위가 8일때 세 번째 I/O Call에서는 4개 블록만 얻게 된다. 이때 8개를 마저 채우기 위해 다음 익스텐트까지 읽지 않는다.

* 병렬 쿼리를 자주하면 CPU와 메모리 사용이 많고 잦은 체크포인트 수행으로 LGWR 작업량이 증가해 커밋 성능이 지연되는 등 온라인 트랜잭션 처리에 안좋은 영향미침