SQL 공유와 재사용

SQL 문이 파싱, 최적화, Row Source 생성, 실행을 거치는 과정과 하드 파싱, 소프트 파싱의 차이, 리터럴 SQL 대신 바인드 변수를 써서 SQL을 공유하는 실습

SQL 처리파싱하드 파싱소프트 파싱라이브러리 캐시바인드 변수SQL_IDRow Source 생성

#핵심 요약

SQL 처리는 파싱, 최적화, Row Source 생성, 실행으로 이루어짐

파싱 때 같은 SQL이 공유 풀에 이미 있으면 재사용(소프트 파싱)하고, 없으면 새로 만듦(하드 파싱)
소프트 파싱은 최적화와 Row Source 생성을 건너뜀

조건값을 문자열로 붙인 리터럴 SQL을 반복하면 매번 다른 SQL로 보여 하드 파싱이 늘어남
바인드 변수(:x)를 쓰면 같은 SQL로 공유됨

Row Source 생성은 옵티마이저가 고른 실행계획을 실제로 실행할 수 있는 형태로 바꾸는 단계임

#개념과 동작 원리

단계	공식 문서 설명
문법 검사	SQL 문법이 맞는지 봄
의미 검사	문장의 객체와 컬럼이 존재하는지 등 의미가 맞는지 봄
공유 풀 검사	SQL마다 해시 값(SQL ID, `V$SQL.SQL_ID`)을 만들고 공유 SQL 영역에서 같은 해시 값의 파싱된 문장을 찾음
하드 파싱	재사용할 코드가 없어 새 실행 버전을 만듦(library cache miss) DDL은 항상 하드 파싱함
소프트 파싱	하드 파싱이 아닌 모든 파싱(library cache hit) 최적화와 Row Source 생성을 건너뛰고 실행으로 감
Row Source 생성	옵티마이저의 최적 실행계획을 받아 반복 실행 가능한 계획(Row Source 트리)을 만듦

SQL ID는 같은 Oracle 버전 안에서 결정적이라 같은 문장은 같은 SQL ID를 가짐

하드 파싱 때 필요한 객체 정의가 바뀌지 않도록 래치(latch)라는 직렬화 장치를 씀
래치 경합은 실행 시간을 늘리고 동시성을 떨어뜨림

설명(Row Source 생성): 옵티마이저는 "T_X01 인덱스를 쓰는 게 최적"처럼 계획만 세우고, Row Source 생성기가 그것을 행 단위로 처리하는 실행 가능한 트리 구조 코드로 바꾸며, 실행 엔진이 그 코드로 데이터를 읽음
실행계획에 보이는 TABLE ACCESS BY INDEX ROWID 아래 INDEX RANGE SCAN 같은 구조가 그 결과물임

#필수 명령어 또는 SQL

#리터럴 SQL로 하드 파싱 유도

SQL*Plus (실습 계정)조회만 함

DECLARE
  TYPE rc IS REF CURSOR;
  l_rc    rc;
  l_dummy all_objects.object_name%TYPE;
  l_start NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
BEGIN
  FOR i IN 1.. 1000 LOOP
    OPEN l_rc FOR
      'select object_name from all_objects where object_id = ' || i;
    FETCH l_rc INTO l_dummy;
    CLOSE l_rc;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(ROUND((DBMS_UTILITY.GET_TIME - l_start) / 100, 2) || 'seconds');
END;
/

결과 해석: object_id = 1, object_id = 2처럼 문장 텍스트가 1000가지로 달라 각각 다른 SQL로 파싱됨
실습에서는 이어서 라이브러리 캐시에 올라간 SQL을 조회했음

#바인드 변수로 바꾸기

SQL*Plus (실습 계정)조회만 함

DECLARE
  TYPE rc IS REF CURSOR;
  l_rc    rc;
  l_dummy all_objects.object_name%TYPE;
  l_start NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
BEGIN
  FOR i IN 1.. 1000 LOOP
    OPEN l_rc FOR
      'select object_name from all_objects where object_id = :x' USING i;
    FETCH l_rc INTO l_dummy;
    CLOSE l_rc;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(ROUND((DBMS_UTILITY.GET_TIME - l_start) / 100, 2) || 'seconds..');
END;
/

결과 해석: 문장 텍스트가 하나뿐이므로 첫 실행 뒤로는 공유 SQL을 재사용함
두 블록의 경과 시간을 비교하는 것이 실습의 목적임

공유 풀의 SQL은 V$SQL에서 조회함
예:

SQL*Plus (V$SQL 조회 권한)조회만 함

SELECT sql_id, executions, parse_calls, sql_text
  FROM v$sql
 WHERE sql_text LIKE 'select object_name from all_objects where object_id%';

결과 해석:

이 조회 문장은 기재 없음

#사용 조건과 주의사항

바인드 변수를 쓰면 실제 값 분포를 옵티마이저가 반영하기 어려울 수 있음(옵티마이저와 비용)