파싱의 작동 원리

• 부모/자식 커서는 메모리 주소로 식별 혹은 SQL_ID + CHILD_NUMBER 컬럼 으로 식별
  • 부모 커서가 폐기 되는 케이스 존재, 이 때 ADDRESS 칼럼도 필요

• 소프트 파싱 : 1, 2 오퍼레이션 만 수행 (공유 가능한 커서가 이미 존재), 라이브러리 캐시 내 공유 커서 재사용, 가능한 피해야 함
• 하드 파싱 : 파싱 단계 모두 수행, 가능한 피해야 함 (CPU 매우 많이 소모, 공유 풀 메모리 소모, 공유 풀 접근 직렬화로 래치 경합 발생)

공유 가능한 커서

• 공유 메모리 영역 저장 목적 : 커서를 재사용함으로써 하드 파싱 회피

부모 커서 공유

• SQL 텍스트가 완전히 동일하면 공유 (CURSOR SHARING 설정에 따라 예외 있음)

-- v$sqlarea 에 부모 커서 3개 생성
SQL> SELECT * FROM t WHERE n = 1234;
SQL> select * from t where n = 1234;
SQL> SELECT  *  FROM  t  WHERE n=1234;
SQL> SELECT * FROM t WHERE n = 1234;

자식 커서 공유

• 실행 계획 및 그와 관련된 환경이 동일 하면 공유

-- v$sqlarea 에 하나의 부모 커서 생성, v$sql 에 자식 커서 2개 생성 (optimizer_mode 다름)
SQL> ALTER SESSION SET optimizer_mode = all_rows;
SQL> SELECT count(*) FROM t;
...
SQL> ALTER SESSION SET optimizer_mode = first_rows_1;
SQL> SELECT count(*) FROM t;

• v$sql_shared_cursor 뷰에서 어떤 불일치(mismatch)로 인해 자식 커서가 새로 생겼는데 확인 가능
  • 12.1 버전 기준 64개 사유
  • 11.2.0.2 부터 reason 컬럼 추가 (불일치 사유 및 추가 정보를 텍스트 형식으로 제공)

• 실행 환경에 의해 결과 값이 달라지는 사례 (자식 커서 각각 생성)

SQL> ALTER SESSION SET nls_sort = binary;
SQL> SELECT * FROM t ORDER BY pad;

  N PAD
--- ---
  1 1
  2 =
  3 Z
  4 z

SQL> ALTER SESSION SET nls_sort = xgerman;
SQL> SELECT * FROM t ORDER BY pad;

  N PAD
--- ---
  2 =
  4 z
  3 Z
  1 1

• 실제로는 자식 커서 보다는 부모 커서로 인해 하드파싱이 생김 (바인드 변수 대신 리터럴 사용)

바인드 변수

바인드 변수 영향

• 개발 시 코딩이 더/덜 요구 됨 (Java.JDBC:더, PLSQL:덜)
• SQL Injection 위험 감소
• 성능 관점에서 장/단점 존재

장점

• 라이브러리 캐시 내 커서 공유로 하드 파싱 오버헤드 감소

-- 커서 공유
SQL> VARIABLE n NUMBER
SQL> VARIABLE v VARCHAR2(32)
SQL> EXECUTE :n := 1; :v := 'Helicon';
SQL> INSERT INTO t (n, v) VALUES (:n, :v);

-- 자식 커서 생성 (변수 길이 변경 : v$sql_shared_cursor.bind_length_upgradeable = 'Y')
SQL> VARIABLE v VARCHAR2(33)
SQL> EXECUTE :n := 1; :v := 'Helicon';
SQL> INSERT INTO t (n, v) VALUES (:n, :v);

Bind variable graduation

• VARCHAR2
  • 1 ~ 32 바이트
  • 33 ~ 128 바이트
  • 129 ~ 2000 바이트
  • 2001 바이트 이상
• NUMBER
  • 22 바이트
• 동작 방식
  • 작은 그룹 → 큰 그룹 : 자식 커서 생성
  • 큰 그룹 → 작은 그룹 : 자식 커서 공유

단점

• 최적 판단을 위한 중요한 정보(리터럴 값)를 옵티마이저에 못 알려줌
  • 특히 범위 비교 조건절(BETWEEN, >, <), 히스토그램 사용 시

• 데모 (id 컬럼에 1 ~ 1000 값을 가진 테이블 t)

-- TABLE ACCESS FULL 선택 (전체의 99%)
SQL> SELECT count(pad) FROM t WHERE id < 990;

-- INDEX RANGE SCAN 선택 (전체의 1%)
SQL> SELECT count(pad) FROM t WHERE id < 10;

• Bind variable peeking : 9i 도입 기능, 최초 값에 따라 실행 계획 생성

SQL> VARIABLE id NUMBER

-- TABLE ACCESS FULL 선택
SQL> EXECUTE :id := 999;
SQL> SELECT count(pad) FROM t WHERE id < :id;

-- 여전히 TABLE ACCESS FULL 선택
SQL> EXECUTE :id := 10;
SQL> SELECT count(pad) FROM t WHERE id < :id;

-- INDEX RANGE SCAN 선택 (새로운 커서 생성을 위해 소문자 적용)
SQL> EXECUTE :id := 10;
SQL> select count(pad) from t where id < :id;

-- 여전히 INDEX RANGE SCAN 선택
SQL> EXECUTE :id := 999;
SQL> select count(pad) from t where id < :id;

• Adaptive cursor sharing (Bind-aware cursor sharing) : 공유 커서 재활용 시 비효율 여부 식별

• v$sql 관련 컬럼

SQL> EXECUTE :id := 10;
SQL> SELECT count(pad) FROM t WHERE id < :id;

-- CHILD_NUMBER : 0
-- IS_BIND_SENSITIVE : Y
-- IS_BIND_AWARE : N
-- IS_SHAREABLE : Y

SQL> EXECUTE :id := 990;
SQL> SELECT count(pad) FROM t WHERE id < :id;
SQL> EXECUTE :id := 10;
SQL> SELECT count(pad) FROM t WHERE id < :id;

-- CHILD_NUMBER : 0 / INDEX RANGE SCAN
-- IS_BIND_SENSITIVE : Y
-- IS_BIND_AWARE : N
-- IS_SHAREABLE : N

-- CHILD_NUMBER : 1 / TABLE ACCESS FULL
-- IS_BIND_SENSITIVE : Y
-- IS_BIND_AWARE : Y
-- IS_SHAREABLE : Y

-- CHILD_NUMBER : 2 / INDEX RANGE SCAN
-- IS_BIND_SENSITIVE : Y
-- IS_BIND_AWARE : Y
-- IS_SHAREABLE : Y

관련 동적 성능 뷰

• v$sql_cs_statistics : 바인드 변수 피킹 여부, 자식 커서 실행 통계 (executions, rows_processed, buffer_gets)
• v$sql_cs_selectivity : 자식 커서 선택도 범위 정보, 바인드 변수 피킹에 의해 추정된 선택도에 적합한 자식 커서 선택
• v$sql_cs_histogram : 자식 커서 별 버킷 관리(0:효율적, 1:비효율적, 2:매우비효율적), 실제 실행 후 해당 버킷 값 증가

어댑티브 커서 공유 안쓸 때

• 바인드 변수가 14개 초과 시
• 선택도를 측정할 수 없을 때 (묵시적 형 변환 등)
• 대상 오브젝트에 통계 정보가 없을 때

어댑티브 커서 공유 제약

• 커서 공유 기능 이용 까지 여러번 비효율 실행을 겪게 됨
  • 커서는 Bind aware 가 아닌 커서로 생성 됨
  • 커서의 Bind aware 속성은 영구적이지 않음
• 힌트 : bind_aware

모범 사례

일반

• 적은 양의 데이터를 처리하는 SQL (자주 사용되는 SQL) 은 바인드 변수 권장 (하드파싱 시간)
• 많은 양의 데이터를 처리하는 SQL 은 비권장 (옵티마이저에 많은 정보 전달)

바인드 변수 비권장

• 값이 사용 가능 범위를 벗어나는지 옵티마이저가 확인해야 하는 경우
• WHERE 절 조건이 범위 조건에 기반인 경우 ( > )
• 옵티마이저가 히스토그램 사용 하는 경우