[친절한 SQL 1장] SQL 처리 과정과 I/O

1.1.2 SQL 최적화 

1. SQL 파싱

 -. 파싱 트리 생성 : SQL 문을 이루는 개별 구성요소를 분석해서 파싱 트리 생성

 -. Syntax 체크 : 문법적 오류가 없는지 확인

 -. Semantic 체크 : 의미상 오류가 없는지 확인 

2. SQL 최적화

 -. 데이터 딕셔너리(Data Dictionary)에 미리 수집해 둔 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상비용을 산정하고 최저 비용을 나타내는 실행계획을 선택한다.

3. 로우 소스 생성

 -. SQL 옵티마이저가 선택한 실행경로를 실제 생행 가능한 코드 또는 프로시저 형태로 포맷팅 하는 단계. 로우 소스 생성기(Row-Source Generator)가 그 역할을 맡는다.

 

※ 부록  2. AUTO Trace 1. set autotrace on  -. SQL을 실행하고 결과집합과 함께 예상 실행계획 및 실행통계를 출력한다. 2. set autotrace on explain  -. SQL을 실행하고 결과집합과 함께 예상 실행계획을 출력한다.  3. set autotrace on statistics  -. SQL을 실행하고 결과집합과 함께 실행통계를 출력한다.  4. set autotrace traceonly  -. SQL을 실행하지만 결과는 출력하지 않고, 예상 실행계획과 실행 통계만 출력한다.  5. set autotrace traceonly explain  -. SQL을 실행하지 않고, 예상 실행계획만 출력한다. 6. set autotrace traceonly statistics  -. SQL을 실행하지만 결과는 출력하지 않고, 실행통계만 출력한다.

1.1.5 옵티마이저 힌트

※주의사항

힌트 안에 인자를 나열할 땐, ','(콤마)를 사용할수 있지만, 힌트와 힌트 사이에 사용하면 안된다.

/*+ INDEX(A  A_X01) INDEX(B, B_X03)*/  -> 모두 유효 /*+ INDEX(C), FULL(D)*/ -> 첫 번째 힌트만 유효

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

SELECT /*+ FULL(SCOOT.EMP)*/  FROM EMP -> 무효

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

SELECT /*+ FULL(EMP)*/  -> 무효 FROM EMP E;

 

1.2.1 소프트 파싱 VS 하드 파싱

-. Library Cache :

• SQL 파싱, 최적화, 로우 소스 생성 과정을 거쳐 생성한 내부 프로시저를 반복 재사용할 수 있도록 캐싱해 두는 메모리 공간
• SGA(System Global Area) 구성요소

-. Library Cache에 있으면 소프트 파싱, 없으면 하드파싱(CPU를 많이 소비하는 작업)

 

1.3 데이터 저장 구조 및 I/O 메커니즘

1.3.1 SQL이 느린이유 (디스크 I/O 관점)

 -. 프로세스가 디스크에서 데이터를 읽을때 CPU를 OS에 반환하고 잠시 수면(waiting) 상태에서 I/O가 완료 되기 기다림

 -. 수많은 프로세스에서 동시다발적으로 디스크 I/O call이 일어나면, 드스크 경합이 심해지고 그만큼 대기시간도 늘어난다. 
(디스크 I/O가 SQL 성능을 좌우한다.)

 

1.3.2 데이터베이스 저장 구조

Tablespace - Segment - extent - block

※ ROWID = DBA(Database Address) + 로우번호(Block 내 순번)

 

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

 -. 논리적 블록 I/O : SQL을 처리하는 과정에서 발생한 총 블록 I/O

 -. 물리적 블록 I/O : 디스크에서 발생한 총 블록 I/O

 

 -. 버퍼캐시 히트율

BCHR = (캐시에서 곧바로 찾은 블록 수 / 총 읽은 블록 수) 100 BCHR = (1 - (물리적 I/O) / (논리적 I/O)) 100 온라인 트랜잭션을 주로 처리하는 APP이라면 99% 달성해야한다.

-. 물리적 I/O

물리적 IO = 논리적 I/O * (100 - BCHR)

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

 

1.3.6 Single Block I/O vs Multiblock I/O

 -. Single Block I/O : 주로 인덱스를 이용할때

 -. Multiblock I/O : Table Fullscan할때 

   -> 일반적으로 OS 레벨 I/O 단위가 1MB, 오라클 레벨 I/O 단위가 8KB이므로 128로 설정하면 최대한 담게 된다(8KB * 128 = 1MB)

 

1.3.8 캐시탐색 메커니즘

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

 

 ※ 메모리 공유자원에 대한 엑세스 직렬화 

-. 버퍼캐시는 SGA 구성요소로 캐싱된 버퍼블록은 모두 공유자원이다.  -. 자원을 공유하는 것 처럼 보여도 내부에선 한 프로세스씩 순차적으로 접근하도록 구현 되어있다.

 

 ※ 캐시버퍼 체인 래치

-. DBA를 해시 함수에 입력하고 거기서 반환된 값으로 스캔 할 해시 체인을 찾는다. 해시체인을 스캔하는 동안 다른 프로세스가 체인 구조를 변경하는 일이 생기면 안된다. 이를 막기 위해 체인래치가 존재한다.

 

※ 버퍼 Lock 이 필요한 이유

1. 읽고자 하는 블록을 찾았으면 캐시버퍼 체인래치 해제 2. 래치가 풀리기를 기다리는 다른 프로세스가 작업시작 3. 래치를 해제한 상태로 버퍼블록 데이터를 읽고 쓰는 도중에 후행 프로세스가 하필 같은 블록에 접근해서 데이터를 읽고 쓴다면 데이터 정합성에 문제가 발생한다. -. 이를 방지하기 위해 오라클은 캐시버퍼 체인 래치를 해제하기 전에 버퍼 헤더에 Lock를 설정함으로써 버퍼블록 자체에 대한 직렬화 문제를 해결했다.  (같은 로우는 로우Lock에 의해 보호될 텐데 버퍼 Lock이 왜 필요할까 싶겟지만, 로우 Lock을 설정하는 행위도 블록을 변경하는 작업이다. 로우 Lock 을 설정하는 순간 다른 프로세스가 해당 블록을 읽는다면 문제가 생긴다. 그뿐만 아니라 같은 블록에서 서로 다르 ㄴ로우를 동시에 읽고 쓰는 경우를 막기 위해서도 버퍼 Lock은 필요하다.)