by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

02. 핵심 개념




목적
  • 혼선 방지 목적의 용어 소개 (선택도, 카디널리티, 커서, 소프트/하드 파싱, 바인드 변수 피킹, 어댑티브 커서 공유)
  • SQL 구문의 생명 주기 설명
  • 코드와 데이터베이스 호출 계측 방법

2.1 선택도와 카디널리티

선택도
  • 전체 중 몇 개의 Row 반환 할지를 0 ~ 1 사이 값으로 표시
    • 120개 Row 중 18개 반환 시 선택도는 0.15(18/120)
    • 0 : 강함(높음,좋음) ~ 1 : 약함(낮음,나쁨)
카디널리티
  • 오퍼레이션에 의해 반환된 Row 수
  • Cardinality = Selectivity * Num_rows
  • 릴레이션(Relation)에서 튜플(Tuple)의 수
-- 선택도 : 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 커서란 무엇인가?

커서
  • 공유 SQL 영역과 관련된 Private SQL 영역을 참조하는 Handle.
  • 클라이언트 측 메모리에 위치, 서버 프로세스에 할당된 메모리 구조 참조, 공유 SQL 영역 참조
클라이언트 메모리   서버 프로세스 메모리   SGA 라이브러리 캐시
[핸들] [Private SQL 영역] [공유 SQL 영역]
   
  • 바인드 변수 값, 쿼리 수행 상태 정보
  • 특정 세션이 소유 (UGA 에 저장)
 
  • 부모 커서(SQL 구문의 텍스트) 와 자식 커서(실행 환경과 실행 계획) 구조로 SQL 구문이 수행되는 방법을 명시
  • 여러 세션에서 공유
  • 실무에서 커서는 Private SQL 영역 및 공유 SQL 영역과 구분 없이 사용

2.3 커서의 생명주기

번호 단계 설명
1 커서 열기(open cursor) 서버 프로세스 메모리, Private SQL 영역 각각 메모리 할당
2 커서 파싱(parse cursor)
  • SQL 파싱 결과 및 실행 계획을 공유 SQL 영역에 적재
  • Private SQL 영역은 공유 SQL 영역을 참조
3 출력 변수 정의 SQL 이 반환하는 값을 받을 변수 정의 (SELECT, RETURNING 절 포함 DML)
4 입력 변수 바인딩 바인드 변수 사용 SQL에 유효한 값 제공
5 커서 실행(execute cursor) SQL 실행
6 커거 페치(Fetch cursor)
  • 반환할 값이 있다면 값을 가져온다 (SELECT 문의 중요 단계)
  • 일부 페치 가능
7 커서 닫기(close cursor) 핸들과 관련된 자원과 private SQL 영역이 해제, 공유 SQL 영역 유지
  • 명시적/묵시적 커서 구현
    • 명시적 : 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 파싱의 작동 원리

오퍼레이션 단계 설명
1 VPD predicates 추가 참조 테이블이 VPD 적용 되어 있다면 WHERE 절 관련 내용 추가
2 문법적(syntax)/의미상(semantics) 오류 검사, 권한 확인 SQL 정상 여부, 참조 오브젝트 존재/권한 확인
3 공유 SQL 영역에 부모 커서 저장 기존 부모 커서 없다면, 라이브러리 캐시에 신규 부모 커서 저장
4 실행 계획 생성 옵티마이저가 실행 계획 생성
5 공유 SQL 영역에 자식 커서 저장 부모 커서와 연관된 공유 가능한 자식 커서 저장
구분
부모 v$sqlarea
자식 v$sql
  • 부모/자식 커서는 메모리 주소로 식별 혹은 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 관련 컬럼
      컬럼 의미
      is_bind_sensitive 바인드 변수 피킹 사용 여부, 어댑티브 커서 공유 사용 고려 여부
      is_bind_aware 어댑티브 커서 공유 사용 여부
      is_shareable 커서 공유 가능 여부
      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 블록 읽기와 쓰기

  • 버퍼 캐시 경유 구분
    I/O 오퍼레이션 설명
    논리적 읽기(logical read) 버퍼 캐시 혹은 PGA 내 블록 읽을 때, 블럭 읽기/쓰기 모두 발생
    버퍼 캐시 읽기(buffer cache reads) 버퍼 캐시에 없는 블록을 읽을 때 발생, 파일 내 블록을 버퍼 캐시에 저장
    DBWR 쓰기(DBWR writes) 버퍼 캐시에 변경된 블록(dirty 블록)을 데이터 파일에 저장
    다이렉트 읽기(direct reads) 서버 프로세스가 파일을 직접 읽어 PGA 에 저장
    다이렉트 쓰기(direct writes) 서버 프로세스가 데이터 파일에 직접 쓴다
  • 버퍼 캐시 관련 없이 구분
    I/O 오퍼레이션 설명
    물리적 읽기(physical reads) 버퍼 캐시 읽기와 다이렉트 읽기
    물리적 쓰기(physical writes) DBWR 쓰기와 다이렉트 쓰기
  • 스마트 스캔(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

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.