SQL 처리과정과 I/O

SELECT * FROM CUSTOMER WHERE LOGIN = :1

1.1 SQL

Structured Query Language: 

구조적, 집합적, 선언적 질의 언어

 

원하는 결과집합을 만드는 과정은 절차적일 수 밖에 없다.

결국 프로시저가 필요한데 , 그런 프로시저를 만드는 것은 옵티마이저이다.

 

SQL최적화

 

1.SQL파싱

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

- Syntax 체크: 문법적 오류가 없는지 확인 -> 사용할 수 없는 키워드, 순서가 바르지 않거난 체크

- Semantic 체크: 의미상 오류가 없는지 확인, 예를 들어, 존재하지 않는 테이블 또는 칼럼을 사용했는지, 사용한 오브젝트에 대한 권한이 있는지 확인

 

2.SQL 최적화

옵티마이저: 미리 수집한 시스템 및 오브젝트 통계정보를 바탕으로 다양한 실행경로를 생성해서 비교한 후 가장 효율적인 하나를 선택

 

3. 로우 소스 제공

SQL 옵티마이저가 선택한 실행 경로를 코드 또는 프로시저 형태로 포맷팅하는 단계(로우 소스 생성기)

 

 

SQL옵티마이저

사용자가 원하는 작업을 가장 효율적으로 수행할 수 있는 최적의 데이터 액세스 경로를 선택해주는 DBMS의 핵심 엔진

 

옵티마이저 최적화 단계

1. 쿼리를 수행하는데 후보군이 될만한 실행계획 찾아냄

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

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

 

실행계획과 비용

SQL 옵티마이저가 생성한 처리절차를 사용자가 확인 할 수 있게 아래와 같이 트리 구조로 표현한 것이 실행계획

 

비용은 쿼리를 수행하는 동안 발생할 것으로 예상하는 I/O 횟수 또는 소요시간을 판단할 수 있다.

 

비용은 실행경로를 선택하기 위해 옵티마이저가 여러 통계정보를 활용해서 계산해 낸 값이므로

실측치가 아니므로 실제 수행할 때 생성하는 I/O또는 시간과 많은 차이가 난다.

 

옵티마이저 힌트

 

옵티마이저 힌트를 이용해 데이터 액세스 경로를 바꿀 수 있다.

SELECT /*+ INDEX(A 고객_PK) */
		고객명, 연락처, 주소, 가입일시
	FROM 고객 A
    WHERE 고객ID = '000000008';

 

주석 기호에 '+'를 붙이면 된다.

옵티마이저의 판단에 강제적인 힌트를 주어서 활용할 수 있다.

 

1.2 SQL 공유 및 재사용

 

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

 

SGA(System Global ARea): 서버 프로세스와 백그라운드 프로세스가 공통으로 액세스하는 데이터와 제어 구조를 캐싱하는 메모리 공간

 

쿼리 문이 전달되면 DBMS는 SQL을 파싱한 후 해당 SQL이 라이브러리 캐시에 존재하는지 확인

캐시에서 찾으면 바로 실행단계로 이동 -> 소프트 파싱

찾는 데 실패해 최적화 및 로우 소스 생성 단계까지 -> 하드 파싱

 

왜 SQL 최적화가 하드 파싱인가

예를 들어 6개의 테이블을 조인하는 쿼리문 하나에 조인 순서만 해도 720개이다. 

거기에 NL조인, 소트 머지 조인, 해시 조인 등 다양한 조인 방식을 고려해야 하고

인덱스 또한 Index Range Scan, Index Unique Scan, Index Full Scan, Index Fast Full Scan, INdex Skip Scan등 다양한 방식 제공

 

옵티마이저가 연산할 때 사용하는 정보

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

오브젝트 통계: 테이블 통계, 인덱스 통계, 컬럼 통계

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

옵티마이저 관련 파라미터

 

1.2 바인드 변수의 중요성

사용자 정의 함수/프로시저, 트리거, 패키지 등은 생성할 때부터 이름을 갖는다.

컴파일한 상태로 딕셔너리 저장되며, 사용자가 삭제하지 않는 한 영구적으로 보관

라이브러리 캐시에 적잼함으로 여러 사용자가 공유하면서 재사용

DBMS에서 수행되는 .SQL이 모두 완성된 SQL은 아니며, 일회성 SQL도 많기에 모두 저장하려면 많은 공간이 필요하기에 영구 저장을 선택하지 않는 이유다.

 

공유 가능 SQL

 

라이브러리 캐시에서 SQL을 찾기 위해 사용하는 키 값이 'SQL 문 그 자체'이므로 아래는 모두 다른 SQL이다.

결국 모두 실행할 대 각각 최적화를 진행하고 라이브러리 캐시에서 별도 공간을 사용한다.

SELECT *
FROM emp
WHERE empno = 7900;

select *
from EMP
where EMPNO = 7900;

 

프로시저를 여러 개 생성할 것이 아니라 아래처럼 로그인ID를 파라미터로 받는 프로시저 하나를 공유하면서 재사용하는 것이 마땅하다.

 

DBMS에 발생하는 부하는 대개 과도한 I/O가 원인인데, 이날은 I/O가 거의 발생하지 않음에도 불구하고 CPU 사용률은 급격히 올라가고, 라이브러리 캐시에  발생하는 여러 종류의 경합 떄문에 트랜잭션들이 처리가 잘 되지 않는다.

 

create procedure LOGIN(login_id in varchar2) { ... }

 

 

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

 

OS 또는 I/O 서브시스템이 I/O 처리하는 동안 프로세스는 잠을 자기에 잠을 재우지 않는 방법이 중요하고

즉 I/O에 대한 개선이 가장 대표적이고 절대 비중을 차지한다.