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

인덱스는 수직적 탐색과 수평적 탐색 두가지가 있다.

데이터베이스 테이블에서 데이터를 찾는 방법도

• 테이블 전체를 스캔한다.
• 인덱스를 이용한다.

두가지이다.

인덱스 튜닝

인덱스는 큰 테이블에서 소량 데이터를 검색할 때 사용한다.

온라인 트랜잭션 처리 (OLTP)시스템 (ex. 은행의 입출금 시스템, 항공권 예약 시스템, 온라인 쇼핑몰의 주문 처리, POS(Point of Sale) 시스템)은 소량 데이터를 주로 검색한다.

• 인덱스 스캔 과정에서 발생하는 비효율 줄이기
• 테이블 엑세스 횟수 줄이기 (SQL 튜닝은 랜덤 I/O와의 전쟁이다)

가 인덱스 튜닝의 핵심이다.

IOT, 클러스터, 파티션~테이블 Prefetch, Batch I/O 모두 랜덤 I/O를 줄이기위해 만들었다.

인덱스 구조

인덱스가 있으면 처음과 끝의 범위를 알수 있으므로 Range Scan이 가능하다. 인덱스는 B*Tree 구조로 정렬이 되어있다.

루트와 브랜츠 블록에 있는 각 레코드는 하위 블록에 대한 주소값을 가진다.

가장 왼쪽의 첫번째 레코드는 LMC라고 한다.

리프 블록에 저장된 각 레코드는 정렬되어있고 ROWID를 가진다.

ROWID = 데이터블록 주소 + 로우 번호

데이터 블록 주소 = 데이터 파일 번호 + 블록 번호

블록 번호 : 데이터 파일내에사 부여한 상대적 순번

로우 번호 : 블록 내 순번

수직적 탐색

루트부터 시작해 인덱스 스캔 시작지점을 찾는 과정

수평적 탐색

리프블록에서 데이터를 찾는 과정

양방향 연결 리스트 구조이다.

일반적으로 인덱스를 스캔한 후 테이블도 엑세스하므로 ROWID를 얻는다.

SQL이 느린 이유는 디스크 I/O 때문이다.

프로세스는 실행중인 프로그램이다.

생성 -> 준비, 실행, 대기 -> 종료를 거친다.

interrupt에 의해 수시로 실행 준비 상태로 전환했다가 다시 실행 상태로 전환된다.

디스크에서 데이터를 읽어야할땐 CPU에 OS를 반환하고 잠시 수면 상태(waiting)상태에서 I/O가 완료되기를 디가린다. 대기 큐에서 잠을 자야한다. 프로세스가 잠을 자고있으니 I/O가 많으면 성능이 느릴수밖에 없다.

데이터를 저장하려면 테이블 스페이스를 생성해야한다.

테이블 스페이스 -> 세그먼트 -> 익스텐트 -> 블록으로 구성된다.

• 세그먼트 : 테이블, 인덱스처럼 데이터 저장공간이 필요한 오브젝트, 여러 익스텐트로 구성된다. 
• 익스텐트 : 공간을 확장하는 단위, 공간이 부족해지면 테이블스페이스로 부터 익스텐트 추가로 할당. 연속된 블록의 집합
• 블록 : 데이터를 읽고 쓰는 단위

DBA : 데이터 블록은 디스크 상에서 몇 번 데이터파일의 몇 번째 블록인지를 나타내는 고유 주소값, 인덱스를 이용해 테이블 레코드를 읽을때 인덱스 ROWID 이용

ROWID : DBA + 로우 번호

테이블을 스캔할 때는 테이블 세그먼트 헤더에 저장된 익스텐드 맵을 이용한다. 익스텐드 맵을 통해 익스텐트의 첫번째 블록 DBA를 알 수 있다.

블록 단위 I/O

블록이 DMBS가 데이터를 읽고 쓰는 단위이다.

특정 레코드 하나를 읽고 싶어도 해당 블록을 통째로 읽는다. 오라클은 기본적으로 8KB 블록 사용 (즉 1KB읽는데 8KB읽음)

V$PARAMETER 뷰로 조회 가능

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

테이블, 인덱스 블록을 액세스하는 방식으로 두가지가 있다

• 시퀀셜 액세스 : 논리적, 물리적으로 연결된 순서에 따라 차례대로 블록을 읽음
  • 오라클은 세그먼트에 할당된 익스텐드 목록을 세그먼트 헤더에 맵으록 관리한다. 익스텐드 맵은 각 익스텐트의 첫번째 블록 주소 값을 갖는다. 읽어야할 익스텐트 목록을 맵에서 얻고 첫번째 블록 뒤에 연속해서 저장된 블록을 순서대로 읽으면 그것이 Full Table Scan이다.
  • 대용량 데이터를 처리할 때 사용됨
  • 주로 WHERE 절에 인덱스가 없는 조건으로 검색할 때 발생
  • 테이블의 모든 블록을 읽어야 하므로 I/O 비용이 많이 듬
• 랜덤 액세스 : 논리적, 물리적 순서를 따르지 않고 레코드를 읽기 위해 한 블록 씩 접근하는 방식
• Index Scan처럼 인덱스를 통해 특정 데이터를 직접 찾아가는 방식
• 인덱스의 ROWID를 통해 테이블의 특정 레코드에 바로 접근
• 주로 인덱스가 있는 칼럼을 WHERE 절에서 검색할 때 사용
• 소량의 데이터를 빠르게 검색할 때 효율적

논리적 I/O vs 물리적 I/O

DB 버퍼캐시도 SGA의 구성요소다. 데이터에서 읽은 데이터 블록을 캐싱해서 같은 블록에 대한 반복적인 I/O Call을 줄인다.

서버 프로세스와 데이터파일 사이에 버퍼캐시가 있으므로 먼저 버퍼캐시부터 탐색한다. 찾으면 I/O Call안해도 됨.

• 논리적 I/O :  SQL을 처리하는 과정에서 발생한 총 블록 I/O (전기적 신호)
• 물리적 I/O : 디스크에서 발생한 총 블록 I/O (물리적 신호), 메모리(논리적) I/O에 비해 상당히 느리다.

SQL을 수행하면서 읽은 총 블록 I/O가 논리적 I/O다.

DB버퍼캐시에서 블록을 찾지 못해 디스크에서 읽은 블록 I/O가 물리적 I/O다.

버퍼캐시 히트율 (BCHR)

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

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

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

즉 버퍼캐시 히트율은 전체 블록중 물리적 디스크  I/O를 수반하지 않고 바로 메모리에서 찾은 비율

즉 물리적  I/O가 아닌 논리적  I/O를 줄여야한다.

-> 논리적 I/O는 일정하므로 물리적 I/O는 BCHR에 의해 결정된다.

--> 논리적 I/O를 줄임으로써 물리적 I/O를 줄이는 것이 SQL튜닝이다.

Query + Current = 논리적 I/O

Disk = 물리적 I/O

-> (1 - (601.458)  / (1.351.677 + 12.367))) x 100 = 55.9

* BCHR이 높다고 효율적인 SQL은 아니다. 같은 블록을 비효율적으로 반복해서 읽으면 높아진다.

Single Block I/O vs MulitBlock I/O

캐시에서 찾지 못한 데이터 블록은 I/O Call을 통해 디스크에서 DB 버퍼캐시로 적재하고 읽는다

• Single Block I/O : 한 번에 한 블록씩 요청해서 메모리 적재 
  • 인덱스 루트 블록을 읽을때
  • 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을때
  • 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을때
  • 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을때
  • 소량 데이터를 읽을때 효율적
• MultiBlock I/O : 한 번에 여러 블록 적재
  • 테이블 전체를 스캔할때
  • 한꺼번에 데이터를 적재하므로 I/O Call이 줄어듬
  • 담은량은 db_multiblock_read_count 파라미터로 정함
  • 익스텐트 경계는 못넘음

Table Full Scan vs Index Range Scan

Table Full Scan : 테이블에 속한 블록 '전체'를 읽어서 사용자가 원하는 데이터를 찾는 방식.

• 시퀀셜 엑세스, Multiblock I/O 방식
• 캐시에서 못 찾으면 한번 I/O call해서 많은 량의 블록을 한꺼번에 I/O

Index Scan : 인덱스에서 '일정량'을 스캔하면서 얻은 ROWID로 테이블 레코드를 찾아가는 방식 (ROWId : 테이블 레코드가 디스크 상에서 어디 저장됐는지 가리키는 위치정보)

• 랜덤 엑세스, Single Block I/O
• 레코드 하나 찾을때마다 한번의 I/O
• 읽었던 블록을 반복해서 읽는 비효율 존재

캐시 탐색 매커니즘

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

• 인덱스 루트 블록을 읽을 때
• 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을때
• 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때
• 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때
• 테이블 블록을 Full Scan 할때

DBMS는 버퍼캐시를 해시 구조로 관리한다.

• 같은 입력 값은 항상 동일한 해시 체인에 관리됨
• 다른 입력값이 동일한 해시 체인에 연결될 수 있음
• 해시 체인 내에서 정렬이 보장되지 않음

버퍼 캐시는 SGA 구성요소로 공유자원이다. 이것을 프로세스가 동시에 접근하면 블록 정합성 문제가 생길 수 있다. 이럴때 해결하는 매커니즘이 '래치'이다.

서브 캐시마다 래치가 별도로 존재하는데, 버퍼캐시에는 캐시버퍼 체인 래치, 캐시버퍼 LRU 체인 레치가 있다.

캐시 I/O도 래치에 의한 경합으로 생각만큼 빠르지 않을 수 있다.

버퍼 블록자체에도 직렬화 매커니즘이 존재한다. = 버퍼 Lock -> 그니까 SQL 튜닝으로 쿼리 일량 (논리적 I/O)를 줄여야한다.

소프트 파싱 vs 하드 파싱

SGA : 시스템 글로벌 아리아, 서버 프로세스와 백그라운드 프로세스가 공통으로 액세스하는 데이터와 제어 구조를 캐싱하는 메모리 공간

라이브러리 캐시 : SQL 파싱, 최적화, 로우 소스 생성 과정을 거쳐 생성한 내부 프로시저를 재사용할 수 있도록 캐싱해두는 메모리 공간

SQL을 캐시에서 찾아 바로 실행 단계로 넘어가면 소프트파싱, 아니면 하드파싱

옵티마이저가 사용하는 정보

• 테이블, 컬럼, 인덱스 구조에 관한 기본 정보
• 오브젝트 통계 : 테이블 통계, 인덱스 통계, 컬럼 통계
• 시스템 통계 : CPU 속도, Single, Multiblock I/O 속도
• 옵티마이저 관련 파라미터

데이터 베이스에서 이루어지는 처리 과정은 I/O 작업에 집중되지만 하드 파싱은 CPU를 많이 소비함

바인드 변수

SQL은 전체 텍스트 그 자체가 SQL의 이름이다. 즉 작은 부분이라도 이름이 다르면 그냥 다른 SQL로 취급한다. 그냥 개복치다.

이런 SQL을 모두 저장하면 많은 공간, 속도가 느려진다.

DBMS에 발생하는 부하는 과도한 I/O가 원인이다.

SELECT * FROM EMPLOYEE WHERE ID = 'KIM'
SELECT * FROM EMPLOYEE WHERE ID = 'LEE'

SELECT * FROM EMPLOYEE WHERE ID = 'DSA'

SELECT * FROM EMPLOYEE WHERE ID = 'FEWw'

SELECT * FROM EMPLOYEE WHERE ID = 'QFWQ'

이런식이면 프로시저 하나씩 만들어서 라이브러리에 적재한다.

create procedure LOGIN_KIM() {} 이런식으로

이러면 라이브러리 캐시가 넘나 많아지고 당연히 갑자기 100만건 들어오면 걍 터질것이다.

-> 프로시저 하나를 공유하면서 재사용해야한다.

create procedure LOGIN (id in varchar2) {}

이렇게

그럼 SELECT * FROM EMPLOYEE WHERE ID = :1

이런 라이브러리 캐시가 하나만 발견된다.

100만건 조회해도 1번만 파싱하여 재사용하기에 효율적이다.

친절한 SQL 튜닝 1-1장의 내용을 요약한 것입니다.

SQL의 특징

• 구조적 : SQL은 미리 정의된 구조와 문법 규칙을 따름 (SELECT, FROM, WHERE 같은 예약어들이 정해진 순서와 형식으로 사용)
• 집합적 : SQL은 데이터를 개별 레코드가 아닌 집합 단위로 처리 (ex. 모든 고객의 평균 나이 처럼 전체 데이터셋에 대한 연산 수행)
• 선언적 : 무엇을 원하는지만 명시하면 됨, 어떻게 처리할지는 DBMS가 결정

SQL을 실행하여 원하는 결과집합을 만들기 위해서는 절차적인 과정이 필요하고 프로시저가 필요하다.

그런 프로시저를 만드는 DBMS 내부 엔진이 SQL 옵티마이저다.

SQL을 최적화한다는 것은 DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행 가능한 상태로 만드는 과정이다.

SQL 최적화

1. SQL 파싱
   • 파싱 트리 생성 : SQL문의 개별 구성요소 분석
   • Syntax 체크 : 문법적 오류 확인
   • Semantic 체크 : 테이블, 컬럼 존재유무, 권한 확인
2. SQL 최적화
   • 옵지마이저가 통계정보를 바탕으로 효율적인 경로 선택
3. 로우 소스 생성
   • 선택한 실행경로를 실행가능한 코드, 프로시저 형태로 포맷팅

SQL 옵티마이저

가장 효율적으로 작업을 수행할 수 있는 액세스 경로를 선택해주는 엔진

1. 후보군의 실행계획들을 찾아낸다.
2. 데이터 딕셔너리에 미리 수집해둔 통계정보들을 이용해 각 실행계획의 예상비용을 산정
3. 최적 비용을 나타내는 실행계획 선택

실행계획과 비용

옵티마이저가 생성한 처리절차를 사용자가 확인할 수 있게 트리구조로 표현한 것

어떤 테이블, 인덱스를 어떤 방식으로 스캔하는지 확인

EXPLAIN PLAN:
-------------------------------------------------------------------------------
| Id  | Operation                                | Name                       | Rows  | Cost (%CPU)   |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                                  |    20 |     4  (25)   |
|   1 |  NESTED LOOPS                     |                                  |    20 |     4  (25)   |
|   2 |   TABLE ACCESS BY INDEX   | EMPLOYEES           |    20 |     2  (50)   |
|   3 |    INDEX RANGE SCAN           | EMP_SALARY_IDX |    20 |     1  (0)   |
|   4 |   TABLE ACCESS BY INDEX   | DEPARTMENTS      |     1 |     1  (0)   |
|   5 |    INDEX UNIQUE SCAN         | DEPT_PK                  |     1 |     0  (0)   |
-------------------------------------------------------------------------------

• EMP_SALARY_IDX 인덱스를 사용해 필터링
• 필터링된 employees 테이블 접근
• 각 직원에 대해 departments 테이블의 PK 인덱스로 조인
• Nested Loops 조인 방식 선택 (소량 데이터에 효율적)

옵티마이저 힌트

SELECT /*+ FULL(employees) */ * 
FROM employees 
WHERE salary > 50000;

• 힌트안에 인자를 나열할땐 ,를 사용할 수 있지만, 힌트와 힌트 사이에 사용하면 안된다.
  • -- 올바른 사용
    SELECT /*+ INDEX(emp, emp_idx1, emp_idx2) FULL(dept) */ *
    FROM employees emp, departments dept;
    
    -- 잘못된 사용 (힌트 사이 콤마)
    SELECT /*+ INDEX(emp, emp_idx1), FULL(dept) */ *
    FROM employees emp, departments dept;
• 테이블을 지정할때 스키마명까지 명시하면안된다.
  • -- 올바른 사용
    SELECT /*+ INDEX(employees emp_idx) */ *
    FROM employees;
    
    -- 잘못된 사용
    SELECT /*+ INDEX(HR.employees emp_idx) */ *
    FROM HR.employees;
• ALIAS를 지정하면 힌트에도 ALIAS를 사용해야한다.
  • -- 올바른 사용
    SELECT /*+ USE_NL(e d) INDEX(e emp_idx) */
    FROM employees e
    JOIN departments d ON e.dept_id = d.dept_id;
    
    -- 잘못된 사용
    SELECT /*+ USE_NL(employees departments) INDEX(employees emp_idx) */
    FROM employees e
    JOIN departments d ON e.dept_id = d.dept_id;

오라클은 데이터파일과 컨트롤 파일에 가해지는 모든 변경사항을 하나의 Redo 로그엔트리로서 Redo 로그에 기록한다.

Redo 로그는 Online Redo, Archived(offline) Redo 로그로 구성된다.

Online Redo 로그는 Redo 로그 버퍼에 버퍼링된 로그 엔트리를 기록하는 파일로서, 최소 두 개 이상의 파일로 구성된다. 현재 사용 중인 Redo 로그 파일이 꽉 차면 다음 Redo 로그 파일로 로그 스위칭이 발생하며, 계속 Redo 로그를 써 나가다가 모든 Redo 로그 파일이 꽉 차면 다시 첫번째 Redo 로그 파일부터 재사용하는 라운드 로빈 방식을 사용한다.

Arcived Redo 로그는 Online Redo 로그가 재사용되기 전에 다른 위치로 백업해둔 파일을 말한다.

Redo의 3가지 목적

1. Database Recovery(Media Recovery) : Redo 로그는 물리적으로 디스크가 깨지는 등의 Media Fail 발생시 데이터베이스를 복구하기 위해 사용되며, 이땐 Archived Redo로그를 이용하게 된다

2. Cache Recovery(=Instance Recovery) : 모든 데이터베이스 시스템이 버퍼 캐시를 도입하는 것은 I/O 성능을 향상시키기 위함이지만, 버퍼 캐시는 휘발성이다. 따라서 캐시에 저장된 변경사항이 디스크 상의 데이터 블록에 아직 기록되지 않은 상태에서 정전등이 발생해 인스턴스가 비정상적으로 종료하면 그동안 작업 내용을 읽는다. 이러한 트랜잭션 데이터의 유실에 대비하기 위해 Redo 로그를 사용한다.

Instance Crash 발생 후 시스템을 재기동하면 우선 Onlin Redo 로그에 저장된 기록 사항들을 읽어들여 마지막 체크포인트 이후부터 사고 발생직전까지 수행되었던 트랜잭션을 재현한다. 그러면 버퍼 캐시에만 수정하고 데이터파일에는 반영되지 않았던 변경사항들이 복구되며, 이는 트랜잭션의 커밋여부를 불문하고 일단 버퍼캐시를 시스템이 셧다운 되기 이전 상태로 되돌린다. 

Cache Recovery가 완료되면 Undo 데이터를 이용해 시스템이 셧다운 되는 시점에 아직 커밋되지 않았던 트랜잭션들을 모두 롤백하는 Transaction Recovery가 진행된다. 이후 커밋되지 않은 기록사항들은 모두 제거되어 데이터파일에 커밋에 성공한 데이터만 남게되어 데이터베이스는 완전히 동기화된 상태가 된다.

3. Fast Commit : 변경된 메모리 버퍼 블록을 디스크 상의 데이터 블록에 기록하는 작업은 Random 액세스 방식으로 이루어지기 때문에 느리다. 로그는 Append 방식으로 기록하므로 상대적으로 매우 빠르다. 그래서 트랜잭션 발생 시 건건이 데이터 파일에 기록하기보다 우선 변경사항을 Append하는 방식으로 빠르게 로그 파일에 기록하고 메모리 데이터 블록과 데이터 파일 간 동기화는 적절한 수단을 이용해 나중에 배치 방식으로 일관 수행한다. 사용자의 갱신내용이 메모리상의 버퍼블록에만 기록된 채 아직 디스크에 기록되지 않았지만 Redo 로그를 믿고 빠르게 커밋을 완료한다는 의미로 Fast Commit이라고 한다. 적어도 커밋 정보가 로그에 기록돼 있기만 한다면 인스턴스 Crash가 발생해도 Redo 로그를 이용해 언제든 Recovery가 가능한 상태가 되어 오라클은 안심하고 커밋할수있다

Fast Commit은 DBMS의 공통적인 매커니즘이다. 근데 오라클만의 특징이 있는데, 'Delayed 블록 클린아웃'이다. 완전한 커밋을 위해서는 Lock을 해제하는 일까지 완료해야 하는데 다른 DBMS는 Lock 매니저를 통해 로우 Lock을 관리하기 때문에 커밋 기점에 빠르게 Lock 리소스를 해제할 수 있다. 반면 오라클은 Lock 매니저 없이 레코드의 속성으로서 로우 Lock을 구현하므로 Lock을 해제하려면 갱신했던 블록들을 일일히 찾아다녀야한다. 그래서 Redo 로그에 기록하는 것만으로는 도저히 커밋을 빠르게 처리할 수 없다. 그래서 Delayed 블록 클린아웃을 사용한다.

LGWR 프로세스에 의해 Redo 로그를 기록하는 시점

1. 3초마다 DBWR 프로세스로부터 신호를 받을때

2. 로그 버퍼의 1/3이 차거나 기록된 Redo 레코드량이 1MB 넘을때

3. 사용자가 커밋 또는 롤백 명령을 날릴때 (핵심)

DB 버퍼 캐시 내에서 버퍼 블록을 찾았으면 빨리 래치를 해제해야한다. 아니면 cache buffers chains 래치에 여러 개의 해시 체인이 달렸으므로 래치에 대한 경합 발생 가능성이 증가한다. 그럼 선행 프로세스가 아직 버퍼를 사용 중이라면 데이터 정합성이 문제다.

두 개 이상의 프로세스가 동시에 버퍼 내용을 읽고 쓰면 문제가 생길수 있다.

버퍼 Lock이란?

위의 문제를 막기위해 캐시된 버퍼 블록을 읽거나 변경하려는 프로세스는 먼저 버퍼 헤더로부터 버퍼 Lock을 획득해야한다. 획득 후 래치를 곧바로 해제한다. 버퍼 내용을 읽기만 할때는 Share 모드, 변경할때는 Exclusive 모드로 Lock을 설정한다. 액세스 직렬화하기 위한 매커니즘이므로 당연히 Exclusive Lock은 한 시점에 하나의 프로세스만 얻을 수 있다. select문이더라도 블록 클린아웃이 필요할때는 버퍼 내용을 변경하는 작업이므로 Exclusive 모드 Lock을 요구한다. 뭔소린지 모르겠다.

해시 체인 래치를 획득하고 목적한 버퍼를 찾았는데 다른 프로세스가 버퍼 Lock을 Exclusive 모드로 점유한 채 내용을 갱신 중이라면 래치를 쥔채 기다릴수없으니 버퍼 헤더에 있는 버퍼 Lock 대기자 목록에 자신을 등록하고 래치를 해제한다. Lock 대기자 목록에 등록돼있는 동안 buffer busy waits 대기가 발생한다. 버퍼 Lock이 해제되면 버퍼 Lock을 획득하고 작업을 시작한다.목적한 읽기쓰기가 완료되면 버퍼 헤더에서 버퍼 Lock을 해제해야 하는데, 이때도 버퍼 헤더를 액세스하려는 다른 프로세스와 충돌이 생기므로 해당 버퍼가 속한 체인 래치를 다시 한번 획득한다. 버퍼 Lock을 해제하고 래치를 해제해야 비로소 버퍼 블록 읽기가 완료된다.

버퍼 핸들이란?

버퍼 헤더에 Pin을 설정하고 사용하려는 오브젝트를 '버퍼 헤더'라고 부른다. 버퍼 핸들을 얻어 버퍼 헤더에 있는 소유자 목록에 연결시키는 방식으로 Pin을 설정한다. 버퍼 핸들도 공유된 리소스 이므로 버퍼 핸들을 얻으려면 또 다른 래치가 필요해지는데 이를 cache buffer handles 래치다. 버퍼를 Pin하는 오퍼레이션이 많을 수록 cache buffer handles 래치가 경함지점이 될것이므로 각 프로세스마다 _db_handles_cached 개수많큼 버퍼 핸들을 미리 할당해주며 기본값은 5개다. 각 세션은 이를 캐싱하고 있다가 버퍼 Pin 할 때마다 사용하며, 그 이상 버퍼 핸들이 필요할때만 cache buffer handles 래치를 얻고 추가로 버퍼 핸들을 할당받는다.

시스템 전체적으로 사용할 수 있는 총 버퍼 핸들 개수는 _db_handles 파라미터(=processes 파라미터 x _db_handles_cached)에 결정된다 

버퍼 Lock의 필요성

사용자 데이터를 변경할 때는 DML Lock을 통해 보호하도록 돼 있는데, 그것을 담는 블록에 또 다른 Lock을 획득해야하는 이유는?

-> 오라클이 하나의 레코드를 갱신하더라도 블록 단위로 I/O을 수행한다. 블록안에 저장된 10개의 레코드를 읽는 짧은 순간 동안 다른 프로세스에 의해 변경이 발생하면 잘못된 결과를 얻는다.

 그리고 값을 변경하기 전에 레코드에 로우 단위 Lock을 설정하는 일 자체도 레코드의 속성을 변경하는 작업이므로 두 개의 프로세스가 동시에 로우 단위 Lock을 설정하려고 시도한다면 문제된다. 블록 SCN을 변경하거나 ITL 슬록에 변경을 가하는 등 블록 헤더 내용을 변경하는 작업도 동시에 일어날 수 있는데. 이런 동시 액세스가 실제로 발생한다면 Lost Update 문제가 생겨 블록 자체의 정합성이 깨진다.

-> 블록 자체로의 진입을 직렬화해야한다.

 Pin된 버퍼 블록은 버퍼 캐시 전체를 비우려고 아래 시스템 명령어를 날리더라도 밀려나지 않는다.

버퍼 Pinning

버퍼를 읽고 나서 버퍼 Pin을 즉각 해제하지 않고 데이터베이스 Call이 진행되는 동안 유지하는 기능. 같은 블록을 반복적으로 읽을 때 버퍼 Pinning을 통해 래치 획득 과정을 생략한다면 논리적인 블록 읽기 횟수를 획기적으로 줄일 수 있다. 같은 블록을 재방문할 가능성이 큰 몇몇 오퍼레이션을 수행할 때만 사용

v$sysstat, v$sesstat, v$mystat 등을 조회하면 래치 획득 과정을 통해 블록을 액세스할때는 session logical reads항목이 증가하고, 래치 획득 과정 없이 버퍼 Pinning을 통해 블록을 곧바로 액세스할때는 buffer is pinned count 항목의 수치가 증가한다.

버퍼 Pinning은 하나의 데이터베이스 Call내에서만 유효하다. 즉 Call이 끝나고 사용자에게 결과를 반환하고 나면 Pin은 해제되어야한다. 따라서 첫 번째 Fetch Call에서 Pin된 블록은 두번째 Fetch Call에서 다시 래치 획득 과정을 거쳐 Pin 되야한다.

전통적으로 버퍼 Pinning이 적용되던 지점은 인덱스를 스캔하면서 테이블을 액세스할 때의 인덱스 리프 블록이다. Index Range Scan하면서 인덱스와 테이블 블록을 교차 방문할때 블록 I/O를 체크해보면 테이블 블록에 대한 I/O만 계속 증가하는 이유다.

빠른 데이터 입출력을 위해 SGA 공유 메모리를 이용한다.

정확히는 캐시 영역은 SGA 구성요소 중 하나인 DB 버퍼 캐시이다.

블록 단위 I/O

오라클에서 I/O은 블록 단위로 이루어져있다.

메모리 버퍼 캐시에서 버퍼 블록을 액세스할 때뿐만 아니라 데이터파일에 저장된 데이터 블록을 DB 버퍼 캐시로 적재하거나 캐시에서 변경된 블록을 다시 데이터파일에 저장할 때도 블록 단위로 처리한다.

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

인덱스를 경유한 테이블 엑세스 -> 한번에 한 블록씩 (Single block read)

Full Scan 시                           -> 성능 향상을 위해 한번에 여러블록 (Multiblock read)

DBWR 프로세스는 버퍼 캐시로부터 변경된 블록 (Dirty 버퍼 블록)을 주기적으로 데이터파일에 기록하는 작업을 수행 이때도 여러 블록 처리

* SQL 성능 최적화하는데 가장 중요한 것은 액세스하는 블록 개수, 옵티마이저의 판단에 가장 큰 영향도 액세스 블록 개수, (레코드 수 x)

버퍼 캐시 구조

SGA에서 가장 많이 사용하는 것이 해시 테이블, DB 버퍼 캐시도 해시 테이블 구조로 관리됨

DB 버퍼캐시내에서 데이터 블록을 해싱하기 위해 사용되는 키 값 -> 데이터 블록 주소 (DBA), 즉 해시함수에 데이터 블록 주소를 입력해 리턴받은 해시 값이 같은 블록들을 같은 해시 버킷에 연결리스트 구조로 연결 (각각의 연결 리스트를 해시 체인이라고 한다)

정확히는 버퍼 헤더만 해시 체인에 연결, 실제의 데이터 값이 필요해지면 버퍼 헤더에 있는 포인터를 이용해 버퍼 블록을 찾아간다.

캐시 버퍼 체인

각 해시 체인은 래치(Latch)에 의해 보호된다. 래치를 획득한 프로세스만이 래치에 의해 보호되는 자료구조로 진입 허용

* DB 버퍼 캐시는 공유 메모리 영역인 SGA 내에 있어서 여러 프로세스에 의해 동시 액세스가 일어날 수 있음. 그래서 같은 리소스에 대한 액세스를 반드시 직렬화(Serialization)해야함 이 Lock 알고리즘이 래치다.

두개 이상의 프로세스가 같은 해시 체인으로 진입해 새로운 블록을 연결하고 해제하는 작업을 동시에 진행하면 문제가 있다

-> 이를 방지하는 것이 cache buffers chains 래치

select count(*) from v$latchname; 하면 래치 개수를 알수있다.

하나의 체인에 하나의 버퍼만 달리는 것을 목표로 해야한다.

오라클 9i부터 읽기전용 작업일때는 캐시 버퍼 체인 래치를 Share 모드로 획득할 수 있다.

* 읽기 전용 작업이란 select문이 아니라 해시체인을 스캔하면서 필요한 블록을 찾는 작업

selct를 동시에 수행하면 cache buffers chains 래치 경합이 발견된다.

래치는 데이터 자체를 보호하는게 아니라 SGA에 공유돼 있는 자료구조를 보호하는 것이며 cache buffers chains 래치는 버퍼 캐시에 연결된 체인구조를 보호한다. 해시체인을 스캔하거나 블록을 추가 제거할때 래치가 요구된다.

버퍼 헤더에 Pin을 설정하기도 한다. (나중에 설명 중요)

캐시 버퍼 LRU 체인

버퍼헤더는 해시 체인뿐만아니라 LRU 체인에 의해서도 연결돼있다.

DB 버퍼 캐시는 읽은 데이터 블록을 캐싱해두는 메모리 공간이지만 모든 데이터를 캐싱할 순 없다.

-> 버퍼 캐시가 사용빈도가 높은 데이터 블록들 위주로 구성될 수 있도록 LRU 알고리즘으로 관리

--> 사용빈도 순으로 위치를 옮기다가 Free 버퍼가 필요해질때마다 빈도가 낮은 데이터를 밀어냄

- Dirty 리스트 : 캐시 내에서 변경됐지만 아직 디스크에 기록되지 않은 Dirty 버퍼블록들을 관리한다 , LRUW 리스트

- LRU 리스트 : 아직 Dirty 리스트로 옮겨지지 않은 나머지 버퍼 블록들 관리

--> 모든 버퍼 블록은 이 둘 중 하나에 반드시 속하지만 읽기 또는 쓰기 작업을 위해 액세스 되는 동안에는 리스트에서 잠시 풀려나왔다가 다시 원래의 리스트로 연결되거나, 최초 변경이 발생하였을 때 Dirty 리스트로 옮겨간다. 

LRU 리스트를 보호하기 위해 사용하는 래치는 cache buffeds lru chain 래치

Free 버퍼 : 인스턴스 가동후 아직 데이터가 읽히지 않아 비어있는 상태거나 데이터가 담겼지만 데이퍼파일과 서로 동기화되어있어 언제든지 엎어 써도 무방한 버퍼. 데이터 파일로부터 새로운 데이터 블록을 로딩하려면 먼저 Free 버퍼를 확보해야하고 Free 버퍼에서 변경이 발생하면 더티 버퍼로 바뀐다.
Dirty 버퍼 : 변경이 발생했지만 아직 디스크에 기록되지 않아 데이터 파일 블록과 동기화가 필요한 버퍼블록, 재사용됟려면 먼저 디스크에 기록되야한다. 기록되면 Free 버퍼로 바뀜
Pinned 버퍼 : 읽기 쓰기 작업을 위해 현재 액세스되고있는 버퍼 블록

오라클은 데이터베이스와 이를 액세스하는 프로세스 사이에 SGA(System(Shared) Global Area)라는 메모리 캐시 영역을 두고있다.

디스크를 경유한 입출력은 물리적으로 액세스 암이 움직이면서 헤드를 통해 데이터를 읽고쓰지만 메모리 캐시를 통한 입출력은 전기적 신호로 디스크 I/O보다 월등히 빠르다.

오른쪽의 개많은 프로세스들이 동시에 데이터를 액세스하기때문에 사용자 데이터를 보호하는 Lock은 물론, 공유 메모리 영역인 SGA상에 위치한 데이터 구조에 대한 액세스를 직렬화 하기 위한 LOCK 매커니즘 (=Latch)도 필요하다.

오라클은 블록 단위로 읽고, 저장할 때도 변경이 발생한 블록만 찾아 블록 단위로 저장한다.

오라클은 백그라운드에서 DBWR와 CKPT 프로세스가 캐시와 데이터파일 간 동기화를 주기적으로 수행해준다 (자동저장이라고 생각하자)

오라클에서는 디스크에 저장된 데이터 집합 (Datafile, Redo Log File, Control File 등)을 데이터베이스라고 부른다.

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

프로세스 집합을 서버 프로세스와 백그라운드 프로세스 집합으로 나눌수있다.

서버 프로세스는 전면에서 사용자가 던지는 명령을 처리하고 (프론트라고 생각 근데 왜 이름이 서버야 헷갈리게) 백그라운드 프로세스는 SQL파싱, 최적화, 실행, 결과집합 생성, 전송 등을 한다.(백엔드라고 생각)

못한 부분은 OS, I/O 서비시스템, 백그라운드 프로세스 등에 신호를 보내 일을 처리한다.

위 그림에서 제일 중요한건 리스너에 연결요청하는 순간 하나의 프로세스를 띄우고 (fork) PGA(Process Global Area) 메모리에 할당한다는 것. 근데 이게 엄청 비싼 작업이라 매번 연결요청하면 안되겠죠? 그래서 접속하는 애플리케이션을 구축할 때 반드시 커넥션 풀 기능이 필요하다.

커넥션 풀은 한번 커넥션 맺으면 작업을 완료해도 해제하지 않고 서버에 Pooling하다가 반복 재사용 (재사용성)

오라클은 하나의 데이터베이스에 접근하는 하나의 인스턴스가 생성되지만 RAC(Real Application Cluster) 환경에서는 하나의 데이터베이스를 액세스하는 다중 인스턴스로 구성된다. 또한 공유 캐시 방식을 지원한다. 글로벌 캐시 개념을 사용해서 로컬 캐시에 없는 데이터 블록을 이웃 노드에 전송받아 서비스한다.

심지어 아직 커밋하지 않은 Active 상태의 블록까지도 디스크를 경유하지 않고 Dirty 버퍼상태에서 네트워크를 통해 공유같으며 수행한다.

뭔 좋은건 다 때려박았다.

RAC이전에 OPS환경에서는 타 노드에 캐싱된 Dirty 버퍼를 읽고자할때 디스크로의 쓰기 작업이 선행되어야했고. 이처럼 디스크를 거치는 동기화 과정을 핑 Ping이라고 한다. 우리가 아는 그 핑맞음 ㅇㅇ

SQL 최적화 과정

옵티마이저가 SQL을 최적화할 때도 데이터베이스 사용자들이 보통 생각하는 것보다 훨씬 많은 일을 수행한다. 조인순서, 조인 방식, 스캔방식 등등 고려하면 그냥 뒤진다. 즉 옵티마이저는 엄청나게 많은 영산을 수행하며 그 과정에서 참여하는 정보는

- 테이블, 컬럼, 인덱스 구성에 관한 기본 정보

- 오브젝트 통계 : 테이블 통계, 인덱스 통계, (히스토그램을 포함한) 컬럼 통계

- 시스템 통계 : CPU 속도, Single Block I/O속도, Multiblock I/O 속도

- 옵티마이저 관련 파라미터

공유 가능 SQL

사용자 정의 함수/프로시저, 트리거, 패키지 등은 생성할 때부터 이름을 갖는다. 컴파일한 상태로 딕셔너리에 저장되며, 사용자가 삭제하지 않는 한 영구적으로 보관된다. 실행할 때 라이브러리 캐시에 적재함으로써 여러 사용자가 공유하면서 재사용한다.

근데 SQL은 이름이 없다. 전체 SQL 텍스트가 이름 역할을 한다. 딕셔너리에 저장하지도 않는다. 처음 실행할 때 최적화 과정을 거쳐 동적으로 생성한 내부 프로시저를 라이브러리 캐시에 적재함으로써 어러 사용자가 공유하면서 재사용한다. 캐시 공간이 부족하면 버려졌다가 다음에 다시 실행할 때 똑같은 최적화 과정을 거쳐 캐시에 적재된다. 의미적으로 100% 같은 SQL이더라도 텍스트 중 일부가 다르면, 각각 최적화를 진행하고 라이브러리 캐시에 별도 공간을 사용한다.

open_cursor 파라미터는 세션 당 Open 할 수 있는 커서 개수를 제한하는 파라미터다.

명시적 커서 : DECLARE문으로 SQL 문을 정의하고, 커서의 Open, Fetch, Close를 명시적으로 처리하는 개발 패턴

묵시적 커서 : DECLARE 문을 생략하고, 커서의 Open, Fetch, Close도 DBMs가 자동으로 처리하는 개발패턴

라이브러리에서 SQL을 찾는 작업을 생략하고 반복 수행하는 기능은 '애플리케이션 커서 캐싱'에 대한 설명이다.

Static SQL vs Dynamic SQL

스태틱 : String형 변수를 담지 않고 코드 사이에 직접 기술한 SQL문 Embedded SQL이라고 한다. PowerBuilder, PL/SQL, Pro*C, SQLJ가 있다.

다이나믹 : String형 변수를 담아서 기술하는 SQL문을 말한다. String 변수를 사용하므로 조건에 따라 SQL문을 동적으로 바꿀수도 있고 또는 런타임 시 사용자로부터 SQL의 일부 또는 전부를 입력받아서 실행할 수도 있다.

뭘 사용하든 DMBS입장에서는 전달받은 SQL문 그 자체만 인식한다.

CURSOR_SHARING 파라미터를 FORCE로 설정하면, SQL에 사용한 상수 값을 바인드 변수로 강제변환해 줌으로써 "상수 값만 다른 동일 SQL"을 반복해서 하드파싱하는 데 따른 부하를 줄여준다.

조건절 값을 일일이 바인드 변수로 처리하지 않아도 되므로 편리하지만 부작용이 당연히 있지 세상은 그리 만만하지 않단다.

- SQL을 실행할 때마다 상수 값을 바인드 변수로 변환하는 과정에 불필요한 CPU자원을 소모한다.

- 컬럼 히스토그램이 도움이 되는 상황에서도 상수 값을 강제 바인드 변수 처리함으로 인해 비효율적인 실행계획이 수립될 수 있고 이는 I/O 증가 및 성능 저하로 이어질 수 있다는 점이다.

쿼리 변환

쿼리 변환은 옵티마이더가 SQL을 분석해 의미적으로 동일하면서도 더 나은 성능이 기대되는 형태로 재작성하는 것을 말한다.

결과만 보장된다면 무조건 쿼리 변환 수행하는 것을 휴리스티 쿼리 변환이라고 한다.

예상 비용이 낮을 때만 변환된 쿼리를 사용하는 것을 비용기반 쿼리 변환이라고 한다.

서브쿼리 Unnesting

인라인 뷰, 중첩된 서브쿼리, 스칼라 서브쿼리가 서브쿼리다.

옵티마이저는 쿼리블록 단위로 최적화를 수행하므로 서브쿼리를 그대로 두면 최적화를 위해 선택할 수 있는 대안이 줄어든다. 예를 들어, 중첩된 서브쿼리를 Unnesting 하지 않으면 메인 쿼리를 기준으로 서브쿼리를 반복 실행하는 필터 방식으로 처리할 수밖에 없다. 이는 NL 조인과 같은 방식이므로 대용량 데이터를 처리할 때 매우 불리하다. 다만, 내부적으로 서브쿼리 캐싱 기능을 적용하므로 서브쿼리에서 리턴할 수 있는 값의 종류 수가 적을 때는 성능이 크게 나쁘지 않을 수 있다. 

중첩된 서브쿼리를 Unnesting하면 조인 순서를 자유롭게 결정할 수 있고, NL 조인 뿐 아니라 해시조인, 소트 머지 조인으로도 처리할 수 있어 성능 최적화에 큰 도움이 된다.

뷰 Merging과 조인 조건 Pushdown

- ;;

OR Expansion

OR Expansion은 OR 조건을 분해해서 UNION ALL 형태로 변환해주는 기능이다. 이 기능이 작동하도록 유도하는 힌트는 use_concat이며, 반대로 이 기능을 방지하는 힌트는 no_expand다. 이 힌트를 사용하면 브랜치별 액세스 경로를 더 정교하게 제어할 수 있다.

공통 표현식 제거

같은 조건식이 여러 곳에서 반복 사용될 경우, 해당 조건식이 각 로우당 한 번씩만 평가되도록 변환하는 것

비용기반 옵티마이저

비용기반(Cost-Based) 옵티마이저 (CBO)는 사용자 쿼리를 위해 후보군이 될만한 실행계획들을 도출하고, 데이터 딕셔너리에 미리 수집해둔 통계정보를 이용해 각 실행계획의 예상비용을 산정하고, 그중 가장 낮은 비용의 실행계획 하나를 선택하는 옵티마이저다.

CBO가 사용하는 통계정보

- 데이터양

- 컬럼 값의 수

- 컬럼 값의 분포

- 인덱스 높이,

- 클러스터링 팩터

규칙기반 옵티마이저

과거에는 각 액세스 경로에 대한 우선순위 규칙에 따라 실행계획을 만드는 옵티마이저를 사용했었다. (Rule-Base) 아까 CBO였으니까RBO겠지

데이터 특성을 나타내는 통계정보를 전혀 활용하지 않고 단순한 규칙에만 의존하기 때문에 대량 데이터를 처리하는데 부적합하다.

인덱스 구조, 연산자, 조건절 형태가 순위를 결정하는 주요소이다.

RBO가 사용하는 규칙 우선순위 및 규칙이다.

Single Row by Rowid -> Single Row by Cluster Join -> Single Row by Hash Cluster Key with Unique or Primary Key -> Single Row by Unique or Primary Key -> Clustered Join -> Hash Cluster Key -> Indexed Cluster Key -> Composite Index -> Single-Column Indexes -> Bounded Range Search on Indexed Columns -> Unbounded Range Search on Indexed Columns -> Sort Merge Join -> MAX or MIN of Indexed Column -> ORDER BY Indexed Column -> Full Table Scan 아 겁나 많네

옵티마이저 서브 엔진

- Query Transformer : 사용자로부터 전달받은 SQL을 그대로 최적화하지 않고 우선 최적화에 유리한 형태로 변환을 시도한다.

- Estimator : 쿼리 오퍼레이션 각 단계의 선택도 (Selectivity), 카디널리티 (Cardinality), 비용 (Cost)를 계산하고, 궁극적으로는 실행계획 전체에 대한 총 비용을 계산해 낸다.

- Plan Generator : 하나의 쿼리를 수행하는데 있어서, 후보군이 될만한 실행계획들을 생성해낸다.

통계정보

옵티마이저가 사용하는 통계정보로는 크게 오브젝트 통계와 시스템 통계가 있다.

오브젝트 통계로는

1. 테이블 통계 -> 레코드 수, 블록 수, 평균 행 길이

2. 인덱스 통계 -> 인덱스 높이, 리프 블록 개수, 클러스터링 팩터

3. 컬럼 통계 -> 중복을 제거한 컬럼 값의 수, 최소값, 최대값, Null 값 개수, 히스토그램

또한

CPU 속도, Single Block I/O 속도, Multiblock I/O 속도, 평균적인 Multiblock I/O 개수 등을 관리한다.

오라클 11g에서 도입된 Adaptive Direct Path Read 기능이 Direct Path Read를 사용할지 여부를 결정할 때 고려하는 항목 중 하나가 버퍼캐시 크기이다. 이는 실행계획을 수립할 때가 아니라 런타임시 고려사항이다.

최초 응답속도 최적화가 효과적인 애플리케이션 아키텍쳐는 주로 2-Tier 환경의 클라이언트/서버 구조다. 이 애플리케이션 구조의 특징은 전체 결과집합이 아무리 많아도 사용자가 스크롤을 통해 일부만 Fetch 하다가 멈출 수 있다는 점이다. 결과집합을 끝까지 Fetch 하거나 다른 쿼리를 수행하기 전까지 SQL 커서는 오픈된 상태를 유지한다.

반면, OLTP성 애플리케이션이라더라도 3-Tier 구조는 클라이언트와 서버 간 연결을 지속하지 않는 환경이므로 오픈 커서를 계속 유지할 수 없어 일반적으로 페이징 처리 기법을 사용한다. 이를 위해 rownum으로 결과집합을 10~20건으로 제한하는 쿼리를 사용한다. 대량 데이터 중 일부만 Fetch하다가 멈추는 것이 아니라 집합 자체를 소량으로 정의해서 모두 Fetch 한다면, 전체 처리속도 최적화가 더 적절한 설정이다.

결론적으로, OLTP성 애플리케이션이더라도 아키텍처에 따라 최적화 목표는 다를 수 있다 그렇군

옵티마이저 행동에 영향을 미쳐버리는 요소 (쿼리 성능에 영향을 미치는 요소) 아주 미쳐버릴거같다.

- 옵티마이저 팩터 : 인덱스, IOT, 클러스터링, 파티셔닝 등 오브젝트 구성

- DBMS 제약 설정 : PK, FK, Check, Not Null

- 통계정보

- 옵티마이저 힌트

- 옵티마이저 관련 파라미터

옵티마이저의 한계 ㅉㅉ

옵티마이저가 항상 최적의 실행계획을 생성하지 못하는 데는 아래와 같은 이유가 있다.

- 부족한 옵티마이징 팩터 : 인덱스, IOT, 클러스터링, 파티셔닝 등 오브젝트 구성

- 부정확한 통계 : 정보 수집 및 보관 비용 측면의 한계 (샘플링 비율, 수집 주기 등)

- 결합 선택도 산정의 어려움

- 바인드 변수 사용시, 히스토그램 사용에 제약 : 균등분포 가정

- 비현실적인 가정과 규칙에 의존

- 최적화 시간에 허용된 시간 제약

온라인 옵티마이저는 정해진 시간 내에 빠르게 최적화를 수행해야하기 때문에 정보를 충분히 활용하지 못한다. 오라클의 경우 튜닝 모드에서 오프라인 옵티마이저(=자동 튜닝 옵티마이저)를 구동하면, 시간 제약없이 다이나믹 샘플링을 포함한 다양한 정보와 기법을 활용하므로 훨씬 더 완벽한 실행계획을 생성한다

라이브러리 캐시 공간의 크기는 옵티마이저가 생성하는 실행계획에는 영향을 주지않는다. 공간이 부족하면 SQL 실행계획이 캐시에서 자주 밀려나므로 파싱과 최적화를 자주 수행함으로 인한 부하가 늘어날뿐

선택도와 카디널리티

선택도 : 전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율을 말한다. 연산자 종류에 따라 선택도 구하는 방식이 다른데, 가장 단순한 '=' 조건으로 검색하는 경우만 살펴보면

선택도 = 1 / NDV (넘버 오브 디스틴트 밸류스 컬럼 값 종류 개수)

카디널리티 : 전체 레코드 중에서 조건절에 의해 선택되는 레코드 개수

카디널리티 : 총 로우 수 x 선택도 = 총 로우수 / NDV

컬럼 통계

오라클에서 수집하는 컬럼 통계 항목들

- 중복을 제거한 컬럼 값의 수 (num_distinct)

- 최소값 (low_value)

- 최대값 (high_value)

- 밀도 (density)

- 평균 컬럼 길이 (avg_col_len)

- NULL 값을 가진 레코드의 수 (num_nulls)

시스템 통계

애플리케이션 및 하드웨어 성능 특성을 측정한 겂

- CPU 속도

- 평균적인 Single Block I/O 속도

- 평균적인 Multiblock I/O 속도

- 평균적인 Multiblcok I/O 개수 

- I/O 서브시스템의 최대 처리량 (Throughput)

- 병렬 Slave 평균적인 처리량 (Throughput)

I/O 비용 모델 vs CPU 비용 모델

I/O 비용 모델의 비용은 예상되는 디스크 I/O Call 횟수를 의마한다.

CPU 비용 모델에서는 예상 I/O 시간과 예상 CPU 사용시간을 구한 후 Single Block I/O 시간으로 나눈 값을 비용 값으로 사용한다. 즉 비용을 Single Block I/O에 소요되는 시간과의 상대적인 시간 비용으로 표현한 것이다.

히스토그램 유형 (와 이런거까지 알아야되네)

- 도수분포 (Frequency) : 값별로 빈도수 저장

- 높이균형 (Height-Balanced) : 각 버킷의 높이가 동일하도록 데이터 분포 관리

- 상위도수분포 (Top-Frequency) : 많은 레코드를 가진 상위 n개 값의 빈도수 저장

- 하이브리드(Hybrid) : 도수분포와 높이균형 히스토그램의 특성을 결합

인덱스를 이용한 테이블 액세스 비용

비용 = 브랜치 레벨

          + (리프 블록 수 x 유효 인덱스 선택도)

          + (클러스터링 팩터 x 유효 테이블 선택도)

유효 인덱스 선택도는 인덱스 총 레코드 중에서 조건절을 만족할 것으로 예상되는 레코드 비율

유효 테이블 선택도는 전체 인덱스 레코드중에서 인덱스 스캔을 원료하고서 테이블을 방문할 것으로 예상되는 레코드 비율

손꾸락 아파죽겠다