SQL 처리 과정

1. SQL 처리 과정을 요약하면 아래와 같다.

• SQL 문을 실행
• SQL Parsing
• SQL 이 메모리(Libeary cache) 에 있는지 확인
  --> 있으면 : 바로 실행
  --> 없으면 : 최적화 > Row 소스 생성 > 실행

SQL> alter session set events '10046 trace name context forever, level 12';
SQL> exit

[TEST10]clt585av13:/lim/admin/TEST10/udump> tkprof test10_ora_18030.trc
output = lc_test

TKPROF: Release 10.2.0.4.0 - Production on Sat May 12 02:28:52 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


[TEST10]clt585av13:/lim/admin/TEST10/udump> vi lc_test


********************************************************************************
SQL> select * from TAX_TAB where rownum <3;

select *
from
 TAX_TAB where rownum <3


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          1          3          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          1          3          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 124

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net more data to client                     1        0.00          0.00
  db file sequential read                         1        0.00          0.00



********************************************************************************

• Misses in library cache during parse - 라이브러리 캐시에서 찾지 못한(miss) 횟수
• Parse call - sql parse 횟수
  --> SQL 첫 수행시만 Libeary cache 에서 정보를 찾지 못해 SQL 을 캐싱
  --> 이후 파스 시에는 Libeary cache 에 있는 SQL Object 재 사용

2. SQL 실행단계 상세 과정

• Parse 시 거치는 단계

1) SQL Parser

• 사용자가 전달 한 SQL 을 가장 먼저 받아서 처리하는 엔진
• SQL 구성 요소 분석, 파싱하여 파싱 트리 생성
• Syntax 체크 - SQL 문법 오류 없는지 (SELECT TO_DATE('12/05/12', 'YYYY/MM/DD') FROM DUAL;)
• Semantic 체크 - SQL 의 의미상 오류 없는지 (권한 없는 요청, 존재하지 않는 테이블 참조)
• SQL 이 Shared Pool에 캐싱되어 있는지 확인
• SQL 이 있으면 기존 SQL 커서 재 사용 하여 바로 실행 단계로 이동(Soft Parse)
  SQL 문장이 없거나,
  SQL 문장이 LC 안에 일치하더라도 Parshing Schema 가 다르거나,
  ex) SCOTT.EMP Table 과 HR.EMP Table 이 공존 할 경우
  Select * from EMP; 를 SCOTT 이 수행했는지 HR 이 수행 했는지에 따라 달라짐
  옵티마이저 관련 설정(Parameter 등) 이 다르면
  새로운 SQL 커서 생성을 위해 SQL Parser 는 옵티마이저에게 다음 수행 단계를 넘긴다

2) Otimizer

• 시스템 통계, Object 통계정보 기반으로 수행 경로 비교하여 가장 효율적 실행 계획 선택 해 주는 엔진
• 3가지 SUB 엔진을 가지고 있다

Query Transformer 트랜스포머

SQL 최적화 하기 쉬운 형태로 변환

Plan Generator

실행계획 후보군 생성

Estimator

수행 각 단계의 선택도, 카디널리티, 비용 고려하여 수행 시 드는 총 비용 계산
오브젝트 통계 정보, 시스템 성능 통계, 메모리, CPU, Single block read time, Multiblock read time 등등 이용

• 짧은 시간 안에 모든 경우의 수를 만들어 내고 비용 계산 하는 것은 힘들다
  h6 적응적 탐색 전략(Adaptive Search Stratigy) 사용
  최적화 시간/총 수행 예상 시간 이 일정 비율 넘지 안토록
  최적의 수행 계획이 될 가능성이 있는 것부터 순서대로 비용 계산

3) Row Source Generation

• 최적화 거친 개념적인 실행계획을 실재 수행 가능한 코드 프로시져 형태로 변환

3. 결론은...

• 위 과정을 모두 거쳐야 하므로 하드 파싱은 비용 소모가 큰 작업이다 - 특히 CPU
• Shared Pool 및 Library cache 에 발생하는 래치도 CPU소모를 높인다
• Hard Parse 수행 시 마다 데이터 딕셔너리 조회도 많이 한다
• --> 한번 파스해서 만들어 놓은 커서는 재 사용 할 수록 이익이다