(작업중)1. SQL 수행 구조 및 처리 과정과 I/O

옵티마이저

• SQL이란 구조적, 집합적, 선언적 질의 언어 → 결과 집합을 만드는 과정은 절차적

→ 즉, 프로시저가 필요하다.(프로시저란 특정 작업을 수행하는 서브루틴(함수)를 의미)

→ SQL을 DBMS 내에서 프로시저로 만들어 낸다. 이 역할은 DBMS 내부 엔진인 SQL 옵티마이저가 담당한다.

• 즉, DBMS 내부에서 프로시저를 작성하고 컴파일하여 실행 가능한 상태로 만드는 전 과정을 ‘SQL 최적화’라고 부른다.

SQL 최적화 과정

1. SQL 파싱
   1. 파싱 트리 생성 : SQL문의 개별 요소를 분석하여 파싱 트리 생성
   2. Syntax 체크 : 문법적 오류 점검
   3. Semantic 체크 : 의미상 오류 점검(미존재 테이블 또는 컬럼의 사용, 오브젝트 권한 점검 등)
2. SQL 최적화
   1. 옵티마이저가 다양한 수집 정보를 이용하여 실행경로를 생성하여 비교 후 효율적인 하나를 선택 → 데이터베이스의 성능을 좌우하는 기능
      1. 옵티마이저가 사용 하는 정보
         • 테이블, 컬럼, 인덱스 구조에 관한 정보
         • 오브젝트 통계 : 테이블 통계, 인덱스 통계, 컬럼 통계 등
         • 시스템 통계 : CPU 속도, Single Block I/O 속도, Multiblock I/O 속도 등
         • 옵티마이저 관련 파라미터
   2. 최적화 과정 : 실행계획 찾기 → Data Dictionary에 수집된 정보, 시스템 통계 정보를 바탕으로 실행계획의 예상비용 산정 → 최저 비용 실행계획 선택
   3. 비용(cost)은 쿼리를 수행하는 동안 발생할 것으로 예상되는 I/O 횟수 또는 소요시간을 표현한 것 → 예상이므로 정확하지는 않을 수 있다.
3. 로우 소스 생성
   1. 선택된 실행경로를 실제 실행 가능한 코드 또는 프로시저 형태로 Row-Source Generator이 포맷팅한다.

옵티마이저 힌트

• 힌트를 통해 데이터 엑세스 경로 조정

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

• 주석 기호에 +를 붙이면 된다.
• 주의사항
  • 힌트 안에 인자 나열 시 힌트와 힌트 사이에 콤마 사용 x
  • 테이블명 지정 시 스키마명 명시 x
  • FROM 절에 ALIAS 지정 시 힌트에도 반드시 ALIAS 사용

SELECT /*+ LEADING(A) USE_NL(B) INDEX(A (주문일자)) INDEX(B 고객_PK) */
       A.주문번호, A.주문금액, B.고객명, B.연락처, B.주소
  FROM 주문 A, 고객 B
 WHERE A.주문일자 = #{ORD_DT}
   AND A.고객ID = B.고객ID
/*
힌트 해설
LEADING(A): 가장 먼저 접근할 테이블나타냄
USE_NL(B): 고객(B)에 대해 Nested Loop Join을 사용하도록 힌트를 제공
INDEX(A (주문일자)): 주문(A)에서 주문일자 컬럼에 대해 인덱스를 사용하도록 힌트를 제공
INDEX(B 고객_PK): 고객(B)에서 고객의 PK에 대한 인덱스를 사용하도록 힌트를 제공
*/

• 위 처럼 힌트를 상세하게 쓰면 옵티마이저의 자유도를 제한할 수 있다.

자주 사용하는 힌트

1. 최적화 목표

• ALL_LOWS : 전체 처리속도 최적화
• FIRST_ROWS(N) : 최초 N건 응답속도 최적화

2.액세스 방식

• FULL : 테이블 풀스캔으로 접근 실행
• INDEX : 인덱스 접근 실행
• INDEX_DESC : 인덱스를 ORDER BY DESC 역순으로 실행
• INDEX_FFS : INDEX FAST FULL SCAN
• INDEX_SS : INDEX SKIP SCAN

3. 조인순서

• ORDERED : FROM절에 나열된 테이블 순서대로 조인
• LEADING : 내가 힌트절에 열거한 테이블 순서대로 조인
• SWAP_JOIN_INPUTS : 해시조인의 경우, BUILD INPUT를 명시적으로 선택

4. 조인방식

• USE_NL :NL(NESTED LOOP - 중첩루프) 방식 조인 유도
• USE_MERGE : 소트머지 조인으로 유도
• USE_HASH : 해시조인으로 유도
• NL_SJ : NL SEMI조인으로 유도
• MERGE_SJ : 소트머지 세미조인으로 유도
• HASH_SJ : 해시 세미조인으로 유도

5. 서브쿼리팩토링

• MATERIALIZE : WITH문으로 정의한 집합을 물리적으로 생성하도록 유도
• INLINE : WITH문으로 정의한 집합을 물리적으로 생성하지 않고 INLINE 처리하도록 유도

6.쿼리변환

• MEERGE : 뷰 머징 유도
• NO_MERGE : 뷰 머징 방지
• UNNEST : 서브쿼리 UNNESTING 유도
• NO_UNNEST : 서브쿼리 UNNESTING 방지
• PUSH_PRED : 조인조건 PUSHDOWN 유도
• NO_PUSH_PRED : 조인조건 PUSHDOWN 방지
• USE_CONCAT : OR 또는 IN-LIST조건을 OR-EXPANSION으로 유도
• NO_EXPAND : OR 또는 IN-LIST 조건에 대한 OR-EXPANSION방지

7.병렬처리

• PARALLEL : 테이블 스캔, DML 병렬방식으로 처리하도록 할 때 사용
• PARALLEL_INDEX : 인덱스 스캔을 병렬방식으로 처리하도록 유도
• PQ_DISTRIBUTE : 병렬수행시 데이터 분배방식 결정

8. 그외 기타

• APPEND: DIRECT PATH INSERT유도
• DRIVING_SITE : DB LINK REMOTE쿼리에 대한 최적화 및 실행 주체 지정 (LOCAL 또는 REMOTE)
• PUSH_SUBQ : 서브쿼리를 가급적 빨리 필터링하도록 유도
• NO_PUSH_SUBQ : 서브쿼리를 가급적 늦게 필터링 하도록 유도

SQL 공유 및 재사용

• SQL 파싱 - 최적화 - 로우 소스 생성 과정을 거쳐 생성된 내부 프로시저를 반복 재사용할 수 있도록 캐싱해 두는 메모리 공간을 라이브러리 캐시(libarary cache)라고 한다. SGA(System Global Area)의 구성 요소이다
• System Global Area (SGA) : 시스템 메모리(RAM)의 일부를 형성하고 하나의 오라클 데이터베이스 인스턴스에 속하는 프로세스들 모두에 의해 공유되는 시스템 메모리(RAM)의 일부 영역. 서버 프로세스와 백그라운드 프로세스가 공통으로 엑세스하는 데이터와 제어 구조를 캐싱하는 메모리 영역(서버 프로세스는 클라이언트의 요청 처리, 백그라운드 프로세스는 모니터링, 로깅 등의 보조 역할 프로세스)

소프트 파싱 vs 하드 파싱

1. 소프트 파싱 : SQL을 라이브러리 캐시에서 찾아 곧바로 실행 단계로 넘어가는 것
2. 하드 파싱 : SQL을 캐시에서 찾는 데 실패해 최적화 및 로우 소스 생성 단계 모두 거치는 것

 

SQL 재사용에 있어서 바인드 변수의 중요성

• DBMS는 SQL을 일일히 저장하지 않는다 → 라이브러리 캐시에서 SQL을 찾기 위해 SQL문 그 자체를 찾는다.

SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'ABC'
SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'TOTO'
SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'DADADA'
SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'JAVAMAN'

• 위 SQL문은 DBMS가 실행할 때마다 내부 프로시저를 만들어 캐시에 적재하여 비효율이 발생한다.
• 이 때 파리미터 Driven 방식으로 SQL을 작성하는 방법을 사용하면 효율을 증대시킬 수 있다.

SELECT * FROM CUSTOMER WHERE LOGIN_ID = :1 -- <- 바인드 변수

• 위 SQL에 대한 하드 파싱은 한 번만 일어나고 캐싱된 SQL을 여러 사용자가 공유하여 사용할 수 있다.