2.1 선택도와 카디널리티

-- 선택도 : 1 (카디널리티 = 전체 Row 수)
SQL> SELECT * FROM t;
...
1000 rows selected.

-- 선택도 : 0.2601
SQL> SELECT * from t WHERE n1 BETWEEN 6000 AND 7000;
...
2601 rows selected.

-- 선택도 : 0
SQL> SELECT * FROM t where n1 = 19;

no rows selected.

-- 선택도 계산 불가 : 액세스 오퍼레이션의 반환 Row 수 필요
SQL> SELECT sum(n2) FROM t WHERE n1 BETWEEN 6000 AND 7000;

   SUM(N2)
----------
     70846

1 row selected.

-- 선택도 : 0.2601 (2601/10000)
SQL> SELECT count(*) FROM t WHERE n1 BETWEEN 6000 AND 7000;

  COUNT(*)
----------
      2601

1 row selected.

• 오퍼레이션의 선택도를 알고 있으면, 가장 효율적인 액세스 패스 결정 가능

2.2 커서란 무엇인가?

• 실무에서 커서는 Private SQL 영역 및 공유 SQL 영역과 구분 없이 사용

2.3 커서의 생명주기

• 명시적/묵시적 커서 구현
  • 명시적 : DBMS_SQL 패키지 사용, 세밀한 제어 가능

DECLARE
  l_ename emp.ename%TYPE := 'SCOTT';
  l_empno emp.empno%TYPE;
  l_cursor INTEGER;
  l_retval INTEGER;
BEGIN
  l_cursor := dbms_sql.open_cursor;
  dbms_sql.parse(l_cursor, 'SELECT empno FROM emp WHERE ename = :ename', 1);
  dbms_sql.define_column(l_cursor, 1, l_empno);
  dbms_sql.bind_variable(l_cursor, ':ename', l_ename);
  l_retval := dbms_sql.execute(l_cursor);
  IF dbms_sql.fetch_rows(l_cursor) > 0 
  THEN  
    dbms_sql.column_value(l_cursor, 1, l_empno);
    dbms_output.put_line(l_empno);
  END IF;
  dbms_sql.close_cursor(l_cursor);
END;

• • 묵시적 : 커서 제어를 PL/SQL 컴파일러에 위임 (대부분 Good)

DECLARE
  l_ename emp.ename%TYPE := 'SCOTT';
  l_empno emp.empno%TYPE;
BEGIN
  SELECT empno INTO l_empno
  FROM emp
  WHERE ename = l_ename;
  dbms_output.put_line(l_empno);
END;
/

2.4 파싱의 작동 원리

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

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

2.4.1 공유 가능한 커서

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

• 부모 커서 공유 : 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

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

2.4.2 바인드 변수

• 바인드 변수 영향
  • 개발 시 코딩이 더/덜 요구 됨 (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 절 조건이 범위 조건에 기반인 경우 ( > )
  • 옵티마이저가 히스토그램 사용 하는 경우

2.5 블록 읽기와 쓰기

• 버퍼 캐시 경유 구분

• 버퍼 캐시 관련 없이 구분

• 스마트 스캔(smart scan) - Exadata 전용의 다른 구조로 데이터 전달 (direct path reads 에서 발생)
  • 불필요한 데이터가 Exadata 스토리지 서버에서 DB 인스턴스로 이동 방지
  • Exadata 스토리지 서버가 불필요한 데이터 읽기 방지
  • CPU 의존 오퍼레이션을 스토리지 서버로 오프로드 처리

2.6 계측

• 모든 애플리케이션은 계측 되어야 함
  • 호출이나 라인 단위 정보는 코드 프로파일러에서 제공
  • 계측 코드는 업무 관련 오퍼레이션 및 구성요소(계층) 간 상호 작용에 집중
  • 주요 단계 마다 측정을 위해 코드의 중요한 위치에 계측 코드를 추가 해야 함

2.6.1 애플리케이션 코드

• 이미 존재하는 로깅 프레임워크를 활용하여 계측 코드 구현
• 아파치 로깅 서비스 프로젝트 : log4j

2.6.2 데이터베이스 호출

• 사용자와 애플리케이션에 대한 정보를 DB 엔진에 전달 필요
  • DB는 세션을 통해 수행되는 SQL이 어느 어플리케이션의 어느 부분인지 알 수 없음

• DB 세션에 동적으로 정보 설정
  • 클라이언트 식별자(client_identifier) : 클라이언트 식별하는 문자열 (64B), DB LINK 원격 세션에도 전달 됨
  • 클라이언트 정보(client_info) : 클라이언트 기술하는 문자열 (64B)
  • 모듈명(module) : 모듈 기술하는 문자열 (48B)
  • 액션명(action) : 액션 기술하는 문자열 (32B)

• 조회 방법
  • sys_context('userenv', 'client_identifier'), v$session.client_identifier
  • sys_context('userenv', 'client_info'), v$session.client_info
  • sys_context('userenv', 'module'), v$session.module
  • sys_context('userenv', 'action'), v$session.action

• 설정 방법
  • PL/SQL

BEGIN
  dbms_sesesion.set_identifier(client_id => 'helicon.antognini.ch');
  dbms_application.set_client_info(client_info => 'Linux x86_64');
  dbms_application.set_module(module_name => 'session_info.sql', action_name => 'test session information');
END;

• OCI : OCIAttrSet 함수
  • OCI_ATTR_CLIENT_IDENTIFIER
  • OCI_ATTR_CLIENT_INFO
  • OCI_ATTR_MODULE
  • OCI_ATTR_ACTION
• JDBC
  • JDBC 드라이버 12.1 버전 부터 제공
• ODP.NET : OracleConnection 클래스 의 프로퍼티
  • ClientId
  • ClientInfo
  • ModuleName
  • ActionName
• PHP : OCI8 확장 함수
  • oci_set_client_identifier
  • oci_set_client_info
  • oci_set_module_name
  • oci_set_action