1. SQL 처리 과정과 I/O [1/3]

1.1 SQL 파싱과 최적화

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

SQL은 Structured Query Language 의 줄임말이다. 그대로 구조적 질의 언어라 한다.

• SQL is designed for a specific purpose : to query data contained in a relational database.
• SQL is set-based, declarative query language, not an imperative language such as C or BASIC.

오라클 PL/SQL, SQL Server T-SQL 처럼 절차적인 프로그래밍 기능을 구현 할 수 있는 확장 언어도 제공하지만, SQL은 기본적으로 구조적이고, 집합적이며, 선언적인 질의 언어다.

 

그러나 그 결과 집합을 만드는 과정은 절차적일 수 밖에 없다. 즉, ※프로시저가 필요하다.

 DB에 대한 일련의 작업을 정리한 절차를 관계형 데이터베이스 관리 시스템에 저장한 것

넓은 의미로 어떠한 업무를 수행하기 위한절차를 뜻한다.

쿼리문을 하나의 메서드 형식으로 만들고 어떤 동작을 일괄적으로 처리하는 용도로 쓰인다. 

 

 

* 인용

[IT지식] 프로시저(PROCEDURE)란?

 

그 프로시저를 만들어 내는 DBMS 내부 엔진이 바로 SQL 옵티마이저이다. 프로그래밍을 대신해 주는 셈이다.

 

 

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

 

1.1.2 SQL 최적화

SQL 실행 전 최적화 과정을 세분화 하면 아래와 같다.

1. SQL 파싱
   • 사용자로부터 SQL을 전달받으면 가장 먼저 SQL Parser가 파싱을 진행한다. 
   • 파싱 트리 생성 : SQL문을 개별 구성요소로 분석해서 파싱 트리를 생성
   • Syntax 체크 : 문법적인 오류가 없는지 확인.
   • Semantic 체크 : 의미상 오류가 없는지 확인.
2. SQL 최적화
   이 역할을 옵티마이저(Optimizer)가 맡는다. 
   • 옵티마이저는 미리 수집한 시스템 및 오브젝트 통계정보를 바탕으로 실행경로를 생성해서 비교한 후 가장 효율적인 1개를 선택한다. Database의 성능을 결정하는 가장 핵심적인 엔진.
3. 로우 소스 생성
   • SQL 옵티마이저가 선택한 실행경로를 실제 실행 가능한 코드나 프로시저 형태로 포맷팅하는 단계.
   • 로우 소스 생성기(Row-Source Generator)가 그 역할을 맡는다.

 

1.1.3 SQL 옵티마이저

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

옵티마이저는 백그라운드 프로세스로 이해하기 쉽지만, 별도 프로세스가 아닌 서버 프로세스가 가진 기능일 뿐이다. SQL 파서나 로우 소스 생성기로 마찬가지이다.

 

옵티마이저의 최적화 단계를 요약하면 아래와 같다.

1. 사용자로부터 전달받은 쿼리를 수행하는 데 후보군이 될만한 실행 계획들을 찾아낸다.
2. 데이터 딕셔너리에 미리 수집해둔 오브젝트 통계 및 시스템 통계정보를 통해 각 실행 계획의 예상 비용을 산정한다.
3. 최저 비용을 나타내는 실행 계획을 선택한다.

 

1.1.4 실행 계획과 비용

SQL 옵티 마이저를 생각할 때 자동차 네비게이션을 생각하면 여러모로 흡사하다. 경로를 검색하고 미리 확인하며, 경우에 따라서 검색모드를 변경하거나 경유지를 추가하는 등 유저가 원하는 경로를 설정할 수 있다.

 

DBMS에도 SQL 실행 경로 미리보기 기능이 있다. '실행 계획'이 그것이다.

옵티마이저가 생성한 처리 절차를 사용자가 확인할 수 있도록 트리 구조를 활용해 표현한 것이 실행 계획이다.

SELECT STATEMENT Optimizer=ALL_ROWS (Cost=209 Card=5 Bytes=175)
	TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=5 Bytes=85)
		NESTED LOOPS (Cost=209 Card=5 Bytes=175)
			TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=207 Card=1 Bytes=18)
				INDEX (RANGE SCAN) OF 'DEPT_LOC_IDX' (NON-UNIQUE) (Cost=7 Card=1)
			INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE) (Cost=1 Card=5)

 

이 미리보기를 통해 작성한 SQL이 테이블을 스캔하는지 인덱스를 스캔하는지, 어떤 인덱스를 활용하는지, 예상과 다르게 처리되는지 등을 확인하고 원한다면 변경할 수 있다.

 

1.1.5 옵티마이저 힌트

네비게이션이 보편적으로 좋은 선택을 하지만 항상 그렇진 않 듯. SQL 옵티마이저도 완벽하지는 않다.

단순 통계 정보에는 담을 수 없는 데이터나 특정 업무 특성을 활용해 개발자가 더욱 나은 액세스 경로를 찾아낼 수 있다.

그럴 땐 옵티마이저 힌트를 통해 데이터 엑세스 경로를 바꿀 수 있다.

힌트 사용법은 주석 기호에 +를 붙이는 식으로 사용한다.

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

 

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

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

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

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

• FROM 절에서 ALIAS를 지정했다면 힌트에도 반드시 사용해야한다. ALIAS를 사용했는데 힌트에 테이블 명을 입력하면 무효 처리 된다.