메뉴 건너뛰기

bysql.net

2. SQL 처리과정

2010.06.27 22:24

휘휘 조회 수:20929

[SQL 처리 과정 ]

seh2tsdM1zLJvRP8hxgOr6A.png


  • 소프트 파싱 (Soft Parsing) 
    • SQL 커서를 메모리에서 찾아 곧바로 실행단계로 넘어가는것
  • 하드 파싱(Hard Parsing) 
    • SQL커서가 메모리에 없을경우(라이브러리 캐시 Miss) 최적화 및 Row-Source 생성 단계후 실행




SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE
  NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')
  NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"),
  NVL(SUM(C2),:"SYS_B_1")
FROM
 (SELECT /*+ NO_PARALLEL("PLAN_TABLE") FULL("PLAN_TABLE")
  NO_PARALLEL_INDEX("PLAN_TABLE") */ :"SYS_B_2" AS C1, :"SYS_B_3" AS C2 FROM
  "SYS"."PLAN_TABLE$" "PLAN_TABLE") SAMPLESUB


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        6      0.00       0.01          0          0          0           0
Execute      6      0.01       0.02          0          0          0           0
Fetch        6      0.00       0.00          0         24          0           6
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       18      0.01       0.04          0         24          0           6

Misses in library cache during parse: 3
Misses in library cache during execute: 3
Optimizer mode: ALL_ROWS


6번의 Parse Call 중 3번의 하드 파싱 발생



(1) SQL 파싱

  • SQL 파서 
    • 사용자가 실행한 SQL을 가장 먼저 받아서 처리하는 엔진
  • 파싱 과정
    1. Syntax 채크
      • SQL 의 문법적 오류 체크 - 사용 불가능한 키워드, 올바르지 않은 순서, 키워드 누락등 채크
    2. Semantic 채크
      • 존재 하지 않거나 권한 없는 오브잭트 사용 유무, 존재 하지 않은 컬럼 참조 유무
    3. Shared Pool 캐싱확인
      • SQL ASCII텍스트를 해싱값으로 변환후 사용자, 옵티마이저값을 참조하여  Shared Pool 에서 기존 사용 커서 찾음
      • SQL 문이 없거나 새로운 SQL를 생성해야 한다면 최적화 수행을 위해 최적화 수행 (옵티마이져)

(2) SQL 최적화

  • 옵티마이져
    • 통계정보 ( 시스템, 오브젝트) 를 기준으로 다양한 ACCESS PATH를 비교한후 가장 효율적인 실행계획을 생성해주는 DBMS의 핵심 엔진

  • 옵티마이저의 3가지 서브 엔진
    1. Query Transformer
      • 동일한 결과를 보장하는 최적화하기 쉬운쿼리로 변환
    2. Plan Generator 
      • 쿼리 수행시 수행 가능한 실행계획들을 생성
    3. Estimator 
      • 선택도, 카디널리티, 비용등의 예상치 계산 (각 단계 수행시 필요한 I/O,CPU,메모리 사용등을 예측)
  • 최적화 수행 시간 단축을 위한 테크닉
    1. Adaptive search strategy(적응적 탐색전략)
      • 쿼리 수행시 예상되는 총 수행시간에 비해 쿠리 최적화에 걸리는 시간이 일정비율을 넘지않도록 하는것
    2. Multiple Initial orderings heuristic
      • 최적의 실행계획을 발견한 가능성이 높은 순서대로 비용을 평가

         2005년 6월 오라클 백서 , Query Optimization in Oracle Database 10g Release2
        



(3) Row-Source Generation

  • 촤적화 과정을 거친 실행계획을 실행가능한 코드 또는 프로시저 형태로 포멧팅 


실행계획


Execution Plan

--------------------------------------------------
0    SELECT STATEMENT Optimizer=ALL_ROWS
1    0    NESTED LOOPS
2    1        TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
3    2            INDEX (RANGE SCAN) OF 'DEPT_X01' (NON-UNIQUE)
4    1        TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
5    4            INDEX (RANGE SCAN) OF 'EMP_X01' (NON-UNIQUE)



내부수행코드


<<outer_loop>>
LOOP
    outer_rid := index_range_scan ('DEPT_X01',search_key);
    outer_rec:=table_access_by_index_rowid('DEPT',outer_rid);
    EXIT outer_loop WHEN outer_rec IS NULL;
    <<inner_loop>>
    LOOP
        inner_rid :=index_range_SCAN ('EMP_X01',outer_rec.deptino);
        inner_rec :=table_access_by_index_rowid ('EMP',inner_rid);
        EXIT inner_loop WHEN inner_rec IS NULL;
        add_into_resultset(outer-rec,inner_rec);
        selected_rows := selected_rows +1;
    END LOOP inner_loop;
END LOOP outer_loop;
DBMS_OUTPUT.PUT_LINE(selected_rows || 'rows selected.');

  • 데이타 처리의 대부분의 과정은 I/O에 집중되지만 
  • 하드 파싱이 발생하면 CPU 소비가 많아진다.
     ( Shared Pool 과 라이브러리 캐시에 대해 래치경합발생, 데이터 딕셔네리 죄회등의 작업이 수반됨)



참고: 오라클 옵티마이저의 원리(02winter_optimizer.pdf)





이 게시물을
목록
목록
쓰기
태그