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

04. 라이브러리 캐시 최적화 원리




라이브러리 캐시 최적화 원리

01 SQL과 옵티마이저 ( 그림 4-3, Page 245 )

  • SQL : 4세대 언어 ( 옵티마이저 내장된 DBMS )
  • 사용자 -> SQL -> 옵티마이저 -> 실행계획 -> 프로시저
    실행계획
    
    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)
    
    

옵티마이저의 최적화 수행단계를 요약 ( Page.246 )

  • 1. 사용자가 던전 쿼리수행을 위해 후보군이 될만한 실행계획들을 찾아낸다.
  • 2. 데이터 딕셔너리에 미리 수집해 놓은( 다아니믹 셈플링은 논외로 함 ) 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상비용을 산정한다.
  • 3. 각 실행계획의 비용을 비교해서 최소비용을 갖는 하나를 선택한다.

02 SQL 처리 과정 ( 그림 4-5, Page 247 )

  • 소프트 파싱 : 파싱 단계에서 SQL 커서를 메모리에서 찾아서 곧바로 실행 단계로..
  • 하드 파싱 : 찾는데 실패해 최적화 및 Row-Source 생성 단계를 거치는 것

(1) SQL 파싱

  • 1. SQL 문장을 이루는 개별 구성요소를 분석하고 파싱해서 파싱 트리를 만든다. ( Syntax 체크 )
  • 2. Semanatic 체크를 통해 의미상 오류가 없는지 체크. ( 권한, 컬럼 )
  • 3. 해싱 알고리즘을 이용해 해당 SQL 커서가 Shared Pool에 캐싱돼 있는지를 확인한다.
    • SQL ASCII 텍스트에 대한 숫자 값을 계산 하고, 해쉬 값으로 변환
  • 4. 파싱을 요청한 사용자가 다르거나 옵티마이저 관련 파라미터 설정이 다르다면 새로운 SQL 커서를 생성해야 한다.
    • SCOTT.EMP OR HR.EMP

(2) SQL 최적화

  • Query Transformer : 최적화하기 쉬운 행태로 변환을 시도 ( 결과 동일 보장 )
  • Plan Generator : 하나의 쿼리를 수행하는 데 있어, 후보군이 될만한 실행계획들을 생성해 내는 역할을 한다.
  • Estimator : 쿼리 오퍼레이션 각 단계의 선택도, 카디널리티, 비용를 계산하고, 궁극적으로는
    실행계획 전체에 대한 총 비용을 계산해 낸다. 이는 어디까지나 예상치
    각 단계를 수행하는데 필요한 I/O, CPU, 메모리 사용량 등을 예측하기 위해 데이터 베이스 오브젝트 통계정보와
    하드웨어적인 시스템 성능 통계 정보를 이용한다.

Adaptive search strategy

  • 쿼리 수행 시 예상되는 총 수행시간에 비해 쿼리 최적화에 걸리는 시간이 일정비율을 넘지 않도록 적응적 탐색 전략

Multiple Initial orderings heuristic

  • 조인 순서를 무순위로 평가하는게 아니라 최적의 실행계획을 발결한 가능이 높은 순서대로 비용을 평가하는 것을 말한다.

(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)
    
    -- Row-Source
    <<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;
      <<iner_loop>>
      LOOP
        inner_rid := index_range_scan('EMP_X01', outer_rec.deptno);
        iner_rec := table_acces_by_index_rowid('EMP', inner_rid);
        EXIT inner_loop WHEN inner_rec, inner_rec);
        selected_rows := selected_rows + 1;
      END LOOP inner_loop;
    END LOOP outer_loop;
    DBMS_OUTPUT.PUT_LINE(selected_rows || ' rows selected.');
    
    

03 라이브러리 캐시 구조

  • Shared Pool에 위치하면, SQL 공유 커서 및 데이터베이스 오브젝트에 대한 정보를 관리
  • 실행가능 LCO ( Transient Objects ) : SQL 커서, 컴파일을 커친 프로시저, 함수, 패키지, 트리거 등
  • 오브젝트 LCO ( Stored Objects ) : 테이블, 인덱스, 클러스터, 뷰, 트리거, 사용자 정의 함수/프로시저
    • 라이브러리 캐시 중복 저장 이유 : 데이터 딕셔너리 캐시에서 읽어 LCO간 의존성를 관리하는 데에 목적이 있다.
      • LCO 각각에는 자신을 참조하는 다른 실행 가능한 LCO 목록을 갖는다.
        라이브러리 캐시 오브젝트
        SYSTEM @ > SELECT NAMESPACE,GETS,PINS, RELOADS, INVALIDATIONS FROM V$LIBRARYCACHE;
        
        NAMESPACE                                                              GETS       PINS    RELOADS INVALIDATIONS
        ---------------------------------------------------------------- ---------- ---------- ---------- -------------
        SQL AREA                                                           78288103  733114514     233266     177501
        TABLE/PROCEDURE                                                    33174336  108610624     412483      14370
        BODY                                                                2095642   17718280       1951        222
        TRIGGER                                                            20840876   20849612        216          4
        INDEX                                                                 55524      45666       4709          0
        CLUSTER                                                               85546      86708         12          0
        LOB                                                                      17          0          0          0
        DIRECTORY                                                              3834       3970          0          0
        QUEUE                                                                   217     123685         25          0
        JAVA SOURCE                                                             973        973          0          0
        JAVA RESOURCE                                                           973        973          0          0
        APP CONTEXT                                                              85        170          0          0
        RULESET                                                                 121        598          2          0
        SUBSCRIPTION                                                             17         17          0          0
        JAVA DATA                                                               949        949          0          0
        TRANSFORMATION                                                            1          1          0          0
        RULE                                                                     52         52          0          0
        TEMPORARY TABLE                                                        8036       8036       5713          0
        TEMPORARY INDEX                                                        3032       3032        182          0
        EDITION                                                             9918889   19809390          0          0
        DBLINK                                                             25846484          0          0          0
        OBJECT ID                                                            221319          0          0          0
        SCHEMA                                                             10320566          0          0          8
        DBINSTANCE                                                                1          0          0          0
        SQL AREA STATS                                                      7155118    7155288          6          0
        ACCOUNT_STATUS                                                     29671789          0          0          0
        SQL AREA BUILD                                                      7178493          0          0          0
        
        27 개의 행이 선택되었습니다.
        

Shared Pool

  • LRU 알고리즘
  • 9i 이전부터는 1의 shared pool latch 관리, 9i부터 Sub Pool로 래치도 7개까지
  • 순간적으로 과도한 하드 파싱 부하를 일으킨다면 shared pool 래치에 대한 경합 현상이 나타날 수 있다.
    SYSTEM @ > SELECT CHILD#, GETS, MISSES, SLEEPS, IMMEDIATE_GETS, IMMEDIATE_MISSES
      2    FROM V$LATCH_CHILDREN
      3  WHERE NAME = 'shared pool'
      4  order by 1;
    
        CHILD#       GETS     MISSES     SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
    ---------- ---------- ---------- ---------- -------------- ----------------
             1  252831947     434433      27617          91532              154
             2  244664337     435765      29035          90496              170
             3  252170145     437037      28759          86524              170
             4  351294992     424751      27971          69115              127
             5  257473630     443297      29409          85666              139
             6         52          0          0              0                0
             7         52          0          0              0                0
    
    7 개의 행이 선택되었습니다.
    

latch 대기 이벤트 ( 그림 4-6, Page 255 )

  • shared pool latch : 특정 오브젝트 정보 또는 SQL 커서를 위한 Free Chunk를 할당 할 때 필요한 래치 ( 소프트/하드파싱 과다 발생 )
  • cache buffer chains 래치 : DB 버퍼 캐시에서 체인에 연결된 리스트 구조를 보호하기 위해
  • latch: library cache : 라이브퍼리 캐시 체인을 탐색하고 변경 경합 ( 소프트/하드파싱 과다 발생 )
  • library cache pin : SQL 수행 도중 DDL 발생
  • library cache lock : SQL 수행 도중 DDL 발생

라이브러리 캐시 최적화 ( 개발자 측면 )

  • 1. 커서를 공유할 수 잇는 형태로 SQL을 작성한다. : 바인드 변수
  • 2. 세션 커서 캐싱 기능을 이용해 라이브러리 캐시에서 SQL 찾는 비용을 줄인다.
  • 3. 애플리케이셔 커서 캐싱을 이용해 Parse Call 발생량을 줄인다.

04 커서 공유

(1) 커서란?

  • 공유 커서 : 라이브러리 캐시에 공유돼 있는 Shared SQL Area
  • 세션 커서 : Private SQL Area에 저장된 커서
  • 애플리케이션 커서 : 세션 커서를 가리키는 핸들

공유 커서( Shared cursor )

  • Shared SQL Area

세션 커서( session cursor )

  • Private SQL Area : Persistent Area, Runtime Area 으로 구분
    • Shared SQL Area를 읽어 커서를 실행하는 데 필요한 정보들을 Private SQL Area에 담고, 공유 커서를 가리키는 포인터 유지 및 커서의 상태 정보도 관리 ( 커서 오픈 )
    • PGA에 저장된 커서 정보를 또한 '커서'라고 부른다.

애플리케이션 커서( application cursor ) ( 그림 4-7, Page.259 )

(2) 커서 공유

  • parse_calls : 라이브러리 캐시에서 SQL 커서를 찾으려는 요청 횟수
  • loads : 하드파싱을 거친 SQL 실행계획을 라이브러리 캐시에 적재한 횟수
  • executions : SQL을 수행한 횟수
  • invalidations : 커서가 무효화된 횟수
    커서 공유 테스트
    
    SYSTEM @ > SELECT * FROM V$VERSION;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    PL/SQL Release 11.2.0.4.0 - Production
    CORE    11.2.0.4.0      Production
    TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
    NLSRTL Version 11.2.0.4.0 - Production
    
    SYSTEM @ > CREATE TABLE SYSTEM.JHLEE68_LCO_TEST_20160408 AS
      2  SELECT LEVEL NO, CHR( LEVEL ) NAME
      3    FROM DUAL
      4  CONNECT BY LEVEL <= 100;
    
    테이블이 생성되었습니다.
    
    SYSTEM @ XXXXX> SELECT SQL_ID, PARSE_CALLS, LOADS, EXECUTIONS, INVALIDATIONS
      2              , DECODE( SIGN(INVALIDATIONS), 1, (LOADS-INVALIDATIONS), 0 ) RELOADS
      3         FROM V$SQL
      4         WHERE SQL_TEXT LIKE '%CURSOR_TEST3%'
      5           AND SQL_TEXT NOT LIKE '%V$SQL%';
    
    선택된 레코드가 없습니다.
    
    SYSTEM @ XXXXX> SELECT /* CURSOR_TEST3 */ * FROM JHLEE68_LCO_TEST_20160408 WHERE NO = 1;
    
        NO NAME       ETC                                                                                          ETC2
    ------ ---------- ---------------------------------------------------------------------------------------------------- ---------------
         1                                                                                                        Y
    
    SYSTEM @ XXXXX> SELECT SQL_ID, PARSE_CALLS, LOADS, EXECUTIONS, INVALIDATIONS
      2              , DECODE( SIGN(INVALIDATIONS), 1, (LOADS-INVALIDATIONS), 0 ) RELOADS
      3         FROM V$SQL
      4         WHERE SQL_TEXT LIKE '%CURSOR_TEST3%'
      5           AND SQL_TEXT NOT LIKE '%V$SQL%';
    
    SQL_ID        PARSE_CALLS      LOADS EXECUTIONS INVALIDATIONS    RELOADS
    ------------- ----------- ---------- ---------- ------------- ----------
    95smyj0461xrw           1          1          1             0          0
    
    SYSTEM @ XXXXX> save LCO_CH3
    file LCO_CH3.sql(이)가 생성되었습니다
    SYSTEM @ XXXXX> SELECT /* CURSOR_TEST3 */ * FROM JHLEE68_LCO_TEST_20160408 WHERE NO = 1;
    
        NO NAME       ETC                                                                                          ETC2
    ------ ---------- ---------------------------------------------------------------------------------------------------- ---------------
         1                                                                                                        Y
    
    SYSTEM @ XXXXX> @LCO_CH3
    
    SQL_ID        PARSE_CALLS      LOADS EXECUTIONS INVALIDATIONS    RELOADS
    ------------- ----------- ---------- ---------- ------------- ----------
    95smyj0461xrw           2          1          2             0          0
    
    SYSTEM @ XXXXX> SELECT /* CURSOR_TEST3 */ * FROM JHLEE68_LCO_TEST_20160408 WHERE NO = 1;
    
        NO NAME       ETC                                                                                          ETC2
    ------ ---------- ---------------------------------------------------------------------------------------------------- ---------------
         1                                                                                                        Y
    
    SYSTEM @ XXXXX> @LCO_CH3
    
    SQL_ID        PARSE_CALLS      LOADS EXECUTIONS INVALIDATIONS    RELOADS
    ------------- ----------- ---------- ---------- ------------- ----------
    95smyj0461xrw           3          1          3             0          0
    
    SYSTEM @ XXXXX> COL SQL_TEXT FOR A100
    SYSTEM @ XXXXX> SELECT SQL_TEXT, INVALIDATIONS
      2        FROM V$SQLAREA
      3      WHERE SQL_ID = '95smyj0461xrw';
    
    SQL_TEXT                                                                                             INVALIDATIONS
    ---------------------------------------------------------------------------------------------------- -------------
    SELECT /* CURSOR_TEST3 */ * FROM JHLEE68_LCO_TEST_20160408 WHERE NO = 1                                  0
    
    SYSTEM @ XXXXX> BEGIN
      2     DBMS_STATS.GATHER_TABLE_STATS(
      3       OWNNAME => 'SYSTEM'
      4     , TABNAME => 'JHLEE68_LCO_TEST_20160408'
      5     , CASCADE => TRUE
      6     , ESTIMATE_PERCENT =>'1'
      7     , DEGREE => '1'
      8     , METHOD_OPT=>'FOR ALL COLUMNS SIZE 1'
      9     , NO_INVALIDATE=>DBMS_STATS.AUTO_INVALIDATE ); -- DBMS_STATS.AUTO_INVALIDATE
     10  END;
     11  /
    
    PL/SQL 처리가 정상적으로 완료되었습니다.
    
    SYSTEM @ XXXXX> @LCO_CH3
    
    SQL_ID        PARSE_CALLS      LOADS EXECUTIONS INVALIDATIONS    RELOADS
    ------------- ----------- ---------- ---------- ------------- ----------
    95smyj0461xrw           3          1          3             0          0
    
    SYSTEM @ XXXXX> COL SQL_TEXT FOR A100
    SYSTEM @ XXXXX> SELECT SQL_TEXT, INVALIDATIONS
      2        FROM V$SQLAREA
      3      WHERE SQL_ID = '95smyj0461xrw';
    
    SQL_TEXT                                                                                             INVALIDATIONS
    ---------------------------------------------------------------------------------------------------- -------------
    SELECT /* CURSOR_TEST3 */ * FROM JHLEE68_LCO_TEST_20160408 WHERE NO = 1                                  0
    
    SYSTEM @ XXXXX>  BEGIN
      2        DBMS_STATS.GATHER_TABLE_STATS(
      3          OWNNAME => 'SYSTEM'
      4        , TABNAME => 'JHLEE68_LCO_TEST_20160408'
      5        , CASCADE => TRUE
      6        , ESTIMATE_PERCENT =>'1'
      7        , DEGREE => '1'
      8        , METHOD_OPT=>'FOR ALL COLUMNS SIZE 1'
      9        , NO_INVALIDATE=> FALSE ); --
     10     END;
     11     /
    
    PL/SQL 처리가 정상적으로 완료되었습니다.
    
    SYSTEM @ XXXXX> @LCO_CH3
    
    SQL_ID        PARSE_CALLS      LOADS EXECUTIONS INVALIDATIONS    RELOADS
    ------------- ----------- ---------- ---------- ------------- ----------
    95smyj0461xrw           3          1          3             1          0
    
    SYSTEM @ XXXXX>
    SYSTEM @ XXXXX> COL SQL_TEXT FOR A100
    SYSTEM @ XXXXX> SELECT SQL_TEXT, INVALIDATIONS
      2        FROM V$SQLAREA
      3      WHERE SQL_ID = '95smyj0461xrw';
    
    SQL_TEXT                                                                                             INVALIDATIONS
    ---------------------------------------------------------------------------------------------------- -------------
    SELECT /* CURSOR_TEST3 */ * FROM JHLEE68_LCO_TEST_20160408 WHERE NO = 1                                  1
    
    SYSTEM @ XXXXX> SELECT /* CURSOR_TEST3 */ * FROM JHLEE68_LCO_TEST_20160408 WHERE NO = 1;
    
        NO NAME       ETC                                                                                          ETC2
    ------ ---------- ---------------------------------------------------------------------------------------------------- ---------------
         1                                                                                                        Y
    
    SYSTEM @ XXXXX> @LCO_CH3
    
    SQL_ID        PARSE_CALLS      LOADS EXECUTIONS INVALIDATIONS    RELOADS
    ------------- ----------- ---------- ---------- ------------- ----------
    95smyj0461xrw           1          2          1             1          1
    
    SYSTEM @ XXXXX>
    SYSTEM @ XXXXX> COL SQL_TEXT FOR A100
    SYSTEM @ XXXXX> SELECT SQL_TEXT, INVALIDATIONS
      2        FROM V$SQLAREA
      3      WHERE SQL_ID = '95smyj0461xrw';
    
    SQL_TEXT                                                                                             INVALIDATIONS
    ---------------------------------------------------------------------------------------------------- -------------
    SELECT /* CURSOR_TEST3 */ * FROM JHLEE68_LCO_TEST_20160408 WHERE NO = 1                                  1
    
    SYSTEM @ XXXXX> select SYS.dbms_metadata.get_ddl( 'TABLE', 'JHLEE68_LCO_TEST_20160408' ) from dual;
    
    SYS.DBMS_METADATA.GET_DDL('TABLE','JHLEE68_LCO_TEST_20160408')
    --------------------------------------------------------------------------------
    
      CREATE TABLE "SYSTEM"."JHLEE68_LCO_TEST_20160408"
       (    "NO" NUMBER,
            "NAME" VARCHAR2(5),
            "ETC" VARCHAR2(100),
            "ETC2" VARCHAR2(100) DEFAULT 'Y' NOT NULL ENABLE
       ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
     NOCOMPRESS LOGGING
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "SYSTEM"
    
    
    SYSTEM @ XXXXX> -- 늘리기
    SYSTEM @ XXXXX> ALTER TABLE JHLEE68_LCO_TEST_20160408 MODIFY NAME VARCHAR2(10);
    
    테이블이 변경되었습니다.
    
    SYSTEM @ XXXXX> @LCO_CH3
    
    SQL_ID        PARSE_CALLS      LOADS EXECUTIONS INVALIDATIONS    RELOADS
    ------------- ----------- ---------- ---------- ------------- ----------
    95smyj0461xrw           1          2          1             2          0
    
    SYSTEM @ XXXXX>
    SYSTEM @ XXXXX> COL SQL_TEXT FOR A100
    SYSTEM @ XXXXX> SELECT SQL_TEXT, INVALIDATIONS
      2        FROM V$SQLAREA
      3      WHERE SQL_ID = '95smyj0461xrw';
    
    SQL_TEXT                                                                                             INVALIDATIONS
    ---------------------------------------------------------------------------------------------------- -------------
    SELECT /* CURSOR_TEST3 */ * FROM JHLEE68_LCO_TEST_20160408 WHERE NO = 1                                  2
    
    SYSTEM @ XXXXX> SELECT /* CURSOR_TEST3 */ * FROM JHLEE68_LCO_TEST_20160408 WHERE NO = 1;
    
        NO NAME       ETC                                                                                          ETC2
    ------ ---------- ---------------------------------------------------------------------------------------------------- ---------------
         1                                                                                                        Y
    
    SYSTEM @ XXXXX> @LCO_CH3
    
    SQL_ID        PARSE_CALLS      LOADS EXECUTIONS INVALIDATIONS    RELOADS
    ------------- ----------- ---------- ---------- ------------- ----------
    95smyj0461xrw           1          3          1             2          1
    
    SYSTEM @ XXXXX>
    SYSTEM @ XXXXX> COL SQL_TEXT FOR A100
    SYSTEM @ XXXXX> SELECT SQL_TEXT, INVALIDATIONS
      2        FROM V$SQLAREA
      3      WHERE SQL_ID = '95smyj0461xrw';
    
    SQL_TEXT                                                                                             INVALIDATIONS
    ---------------------------------------------------------------------------------------------------- -------------
    SELECT /* CURSOR_TEST3 */ * FROM JHLEE68_LCO_TEST_20160408 WHERE NO = 1                                  2
    
    SYSTEM @ XXXXX>
    
    

(3) Child 커서를 공유하지 못하는 경우

  • 1. SQL에서 참조하는 오브젝트명이 같지만 SQL을 실행한 사용자에 따른 다른 오브젝트 가리킬 때
  • 2. 참조 오브젝트가 변경돼 커서가 무효화되면 이후 그 커서를 처음 사용하려는 세션에 의해 다시 하드 파싱돼야 하는데, 특정
    세션이 아직 기존 커서를 사용 중(Pin) 일 때
  • 3. 옵티마이저 모드를 비롯해 옵티마이저 관련 파리미터가 다를 때
  • 4. 입력된 바인드 값의 길이가 크게 다를 때
  • 5. NLS 파라미터를 다르게 설정했을 때
  • 6. SQL 트레이스를 활성화했을 때
    
    -- 다른 테이블 경우
    SYS @ XXXXX> conn system/XXXXX@XXXXX
    연결되었습니다.
    SYSTEM @ XXXXX>
    SYSTEM @ XXXXX>
    SYSTEM @ XXXXX>  CREATE TABLE SYSTEM.JHLEE68_LCO_TEST_20160415 AS
      2   SELECT LEVEL NO, CHR( LEVEL ) NAME
      3     FROM DUAL
      4  CONNECT BY LEVEL <= 100;
    
    테이블이 생성되었습니다.
    
    SYSTEM @ XXXXX> SELECT /* CHILE_TEST4 */ * FROM JHLEE68_LCO_TEST_20160415 WHERE ROWNUM <= 1;
    
        NO NAME
    ------ ----------
         1 
    
    SYSTEM @ XXXXX> SELECT SQL_TEXT, SQL_ID
      2        FROM V$SQL
      3      WHERE 1 = 1 --
      4        AND SQL_TEXT LIKE '%CHILE_TEST4%';
    
    SQL_TEXT                                                                                             SQL_ID
    ---------------------------------------------------------------------------------------------------- -------------
    SELECT SQL_TEXT, SQL_ID       FROM V$SQL     WHERE 1 = 1 --       AND SQL_TEXT LIKE '%CHILE_TEST4%'  bdbdqz1r8t95q
    SELECT /* CHILE_TEST4 */ * FROM JHLEE68_LCO_TEST_20160415 WHERE ROWNUM <= 1                          awruqkrw87sy8
    
    SYSTEM @ XXXXX>
    SYSTEM @ XXXXX> SELECT sql_id, child_number, optimizer_mode, address, hash_value, parsing_user_id
      2        FROM V$SQL
      3       WHERE SQL_TEXT LIKE '%CHILE_TEST4%'
      4         AND SQL_TEXT NOT LIKE '%V$SQL%'
      5         AND SQL_ID = 'awruqkrw87sy8';
    
    SQL_ID        CHILD_NUMBER OPTIMIZER_ ADDRESS          HASH_VALUE PARSING_USER_ID
    ------------- ------------ ---------- ---------------- ---------- ---------------
    awruqkrw87sy8            0 FIRST_ROWS 07000100F874B648 4169393096               5
    
    SYSTEM @ XXXXX>
    SYSTEM @ XXXXX>
    SYSTEM @ XXXXX> conn sys/XXXXX@XXXXX as sysdba
    연결되었습니다.
    SYS @ XXXXX>  CREATE TABLE SYS.JHLEE68_LCO_TEST_20160415 AS
      2   SELECT LEVEL NO, CHR( LEVEL ) NAME
      3     FROM DUAL
      4  CONNECT BY LEVEL <= 100;
    
    테이블이 생성되었습니다.
    
    SYS @ XXXXX> SELECT /* CHILE_TEST4 */ * FROM JHLEE68_LCO_TEST_20160415 WHERE ROWNUM <= 1;
    
        NO NAME
    ------ ----------
         1 
    
    SYS @ XXXXX> SELECT sql_id, child_number, optimizer_mode, address, hash_value, parsing_user_id
      2        FROM V$SQL
      3       WHERE SQL_TEXT LIKE '%CHILE_TEST4%'
      4         AND SQL_TEXT NOT LIKE '%V$SQL%';
    
    SQL_ID        CHILD_NUMBER OPTIMIZER_ ADDRESS          HASH_VALUE PARSING_USER_ID
    ------------- ------------ ---------- ---------------- ---------- ---------------
    awruqkrw87sy8            0 FIRST_ROWS 07000100F874B648 4169393096               5
    awruqkrw87sy8            1 FIRST_ROWS 07000100F874B648 4169393096               0
    
    SYS @ XXXXX>  SELECT sql_id, version_count, optimizer_mode, address, hash_value
      2            FROM V$SQLAREA
      3           WHERE SQL_TEXT LIKE '%CHILE_TEST4%'
      4             AND SQL_TEXT NOT LIKE '%V$SQL%'
      5  ;
    
    SQL_ID        VERSION_COUNT OPTIMIZER_ ADDRESS          HASH_VALUE
    ------------- ------------- ---------- ---------------- ----------
    awruqkrw87sy8             2 FIRST_ROWS 07000100F874B648 4169393096
    
    SYS @ XXXXX>
    
    
    -- 같은 테이블일 경우
    SYS @ xxxxxx> conn system/xxxxxx@xxxxxx
    연결되었습니다.
    SYSTEM @ xxxxxx>
    SYSTEM @ xxxxxx>
    SYSTEM @ xxxxxx>
    SYSTEM @ xxxxxx>
    SYSTEM @ xxxxxx>
    SYSTEM @ xxxxxx> SELECT /* CHILE_TEST5 */ * FROM SYS.JHLEE68_LCO_TEST_20160415 WHERE ROWNUM <= 1;
    
        NO NAME
    ------ ----------
         1 
    
    SYSTEM @ xxxxxx> SELECT sql_id, child_number, optimizer_mode, address, hash_value, parsing_user_id
      2            FROM V$SQL
      3           WHERE SQL_TEXT LIKE '%CHILE_TEST5%'
      4             AND SQL_TEXT NOT LIKE '%V$SQL%';
    
    SQL_ID        CHILD_NUMBER OPTIMIZER_ ADDRESS          HASH_VALUE PARSING_USER_ID
    ------------- ------------ ---------- ---------------- ---------- ---------------
    493havd3rxym4            0 FIRST_ROWS 070001010CB34EB0 1199503972               5
    
    SYSTEM @ xxxxxx>
     @ > conn sys/xxxxxx@xxxxxx as sysdba
    연결되었습니다.
    SYS @ xxxxxx> SELECT /* CHILE_TEST5 */ * FROM SYS.JHLEE68_LCO_TEST_20160415 WHERE ROWNUM <= 1;
    
        NO NAME
    ------ ----------
         1 
    
    SYS @ xxxxxx> SELECT sql_id, child_number, optimizer_mode, address, hash_value, parsing_user_id
      2            FROM V$SQL
      3           WHERE SQL_TEXT LIKE '%CHILE_TEST5%'
      4             AND SQL_TEXT NOT LIKE '%V$SQL%';
    
    SQL_ID        CHILD_NUMBER OPTIMIZER_ ADDRESS          HASH_VALUE PARSING_USER_ID
    ------------- ------------ ---------- ---------------- ---------- ---------------
    493havd3rxym4            0 FIRST_ROWS 070001010CB34EB0 1199503972               5
    
    SYS @ xxxxxx>   SELECT sql_id, version_count, optimizer_mode, address, hash_value
      2            FROM V$SQLAREA
      3           WHERE SQL_TEXT LIKE '%CHILE_TEST5%'
      4             AND SQL_TEXT NOT LIKE '%V$SQL%';
    
    SQL_ID        VERSION_COUNT OPTIMIZER_ ADDRESS          HASH_VALUE
    ------------- ------------- ---------- ---------------- ----------
    493havd3rxym4             1 FIRST_ROWS 070001010CB34EB0 1199503972
    
    SYS @ xxxxxx>
    
    -- 같은 테이블 시노님 경우
    SYSTEM @ XXXXX> conn system/XXXXX@XXXXX;
    연결되었습니다.
    
    SYSTEM @ XXXXX> SELECT /* CHILE_TEST3 */ * FROM DUAL;
    
    D
    -
    X
    
    SYSTEM @ XXXXX> SELECT SQL_TEXT, SQL_ID
      2    FROM V$SQL
      3  WHERE 1 = 1 --
      4    AND SQL_TEXT LIKE '%CHILE_TEST3%';
    
    SQL_TEXT                                                                                             SQL_ID
    ---------------------------------------------------------------------------------------------------- -------------
    SELECT SQL_TEXT   FROM V$SQL WHERE 1 = 1 --   AND SQL_TEXT LIKE '%CHILE_TEST3%'                      d9n0awq1f43wc
    SELECT SQL_TEXT, SQL_ID   FROM V$SQL WHERE 1 = 1 --   AND SQL_TEXT LIKE '%CHILE_TEST3%'              50bycrda19sg5
    SELECT /* CHILE_TEST3 */ * FROM DUAL                                                                 f14d3f8uwbg3j
    
    SYSTEM @ XXXXX> SELECT sql_id, child_number, optimizer_mode, address, hash_value, parsing_user_id
      2    FROM V$SQL
      3   WHERE SQL_TEXT LIKE '%CHILE_TEST3%'
      4     AND SQL_TEXT NOT LIKE '%V$SQL%'
      5     AND SQL_ID = 'f14d3f8uwbg3j';
    
    SQL_ID        CHILD_NUMBER OPTIMIZER_ ADDRESS          HASH_VALUE PARSING_USER_ID
    ------------- ------------ ---------- ---------------- ---------- ---------------
    f14d3f8uwbg3j            0 FIRST_ROWS 07000101046A3758  902151281               5
    
    SYSTEM @ XXXXX>
    SYSTEM @ XXXXX> conn sys/XXXXX@XXXXX as sysdba
    연결되었습니다.
    SYS @ XXXXX>
    SYS @ XXXXX> SELECT /* CHILE_TEST3 */ * FROM DUAL;
    
    D
    -
    X
    
    SYS @ XXXXX> SELECT sql_id, child_number, optimizer_mode, address, hash_value, parsing_user_id
      2    FROM V$SQL
      3   WHERE SQL_TEXT LIKE '%CHILE_TEST3%'
      4     AND SQL_TEXT NOT LIKE '%V$SQL%'
      5     AND SQL_ID = 'f14d3f8uwbg3j';
    
    SQL_ID        CHILD_NUMBER OPTIMIZER_ ADDRESS          HASH_VALUE PARSING_USER_ID
    ------------- ------------ ---------- ---------------- ---------- ---------------
    f14d3f8uwbg3j            0 FIRST_ROWS 07000101046A3758  902151281               5
    
    SYS @ XXXXX>
    SYS @ XXXXX> SELECT sql_id, version_count, optimizer_mode, address, hash_value
      2    FROM V$SQLAREA
      3   WHERE SQL_TEXT LIKE '%CHILE_TEST3%'
      4     AND SQL_TEXT NOT LIKE '%V$SQL%'
      5     AND SQL_ID = 'f14d3f8uwbg3j'  ;
    
    SQL_ID        VERSION_COUNT OPTIMIZER_ ADDRESS          HASH_VALUE
    ------------- ------------- ---------- ---------------- ----------
    f14d3f8uwbg3j             1 FIRST_ROWS 07000101046A3758  902151281
    
    SYS @ XXXXX>    SELECT USERNAME
      2       FROM DBA_USERS
      3      WHERE USER_ID = 5
      4  ;
    
    USERNAME
    ------------------------------
    SYSTEM
    
    SYS @ XXXXX>
    

V$SQL_SHARED_CURSOR ( Page.267 )

  • optimizer_mode_mismatch : 옵티마이저 틀린 설정 모드로 발생
  • bind_mismatch : 바인드 값이 32, 128, 2000 바이트를 넘을 때마다 새로운 Child 커서가 생성됨

(4) Parent 커서를 공유하지 못하는 경우

  • 1. 공백 문자 또는 줄바꿈
  • 2. 대소문자 구분
  • 3. 테이블 Owner 명시
  • 4. 주석( Comment )
  • 5. 옵티마이져 힌트 사용
  • 6. 조건절 비교 값 ( 리터널 변수 )

05 바인드 변수의 중요성 ( Page.273 )

  • 바인드 변수 사용시 : LOADS = 1, PARSE_CALLS = 실행수, EXECUTIONS = 실행수, FETCHES = FETCHES
  • 리터널 변수 사용시 : LOADS = 실행수, PARSE_CALLS = 실행수, EXECUTIONS = 실행수, FETCHES = FETCHES
    • cursor_sharing : 응급처방으로 사용 ( 잘사용 하지 않는 기능으로 본문에서 제외 )

06 바인드 변수의 부작용과 해법

  • SQL을 최적화하는 시점에 조건절 컬럼의 데이터 분포도를 활용하지 못하는 문제점 ( 평균 분포도 사용 )
    • 특히, 등치 조건이 아닌 부등호나 Between 같은 범위 기반 검색 조건일 때는 고정된 규칙을 사용하므로 더 부정학환 예측 ( 1 ~ 4 : 5%, 5 ~ 8 : 0.25% )
      • 1 : 번호 > :NO
      • 2 : 번호 < :NO
      • 3 : 번호 >= :NO
      • 4 : 번호 <= :NO
      • 5 : 번호 BETWEEN :NO1 AND :NO2
      • 6 : 번호 > :NO1 AND 번호 <= :NO2
      • 7 : 번호 >= :NO1 AND 번호 < :NO2
      • 8 : 번호 > :NO1 AND 번호 < :NO2
  • 카디널리티 = 선택도 * 전체 레코드 수
    SYS @ xxxxxx> create table jhlee68_card_test_20160415
      2  as
      3  select level no from dual connect by level <= 1000;
    
    테이블이 생성되었습니다.
    
    SYS @ xxxxxx> analyze table jhlee68_card_test_20160415 compute statistics for table for all columns;
    
    테이블이 분석되었습니다.
    
    SYS @ xxxxxx> explain plan for select * from jhlee68_card_test_20160415 where no <= :no;
    
    해석되었습니다.
    
    SYS @ xxxxxx>
    SYS @ xxxxxx>
    SYS @ xxxxxx> select * from table( dbms_xplan.display( null, null, 'basic rows'));
    
    ----------------------------------------------------------------
    | Id  | Operation         | Name                       | Rows  |
    ----------------------------------------------------------------
    |   0 | SELECT STATEMENT  |                            |    10 |
    |   1 |  TABLE ACCESS FULL| JHLEE68_CARD_TEST_20160415 |    10 |
    ----------------------------------------------------------------
    
    8 개의 행이 선택되었습니다.
    
    SYS @ xxxxxx> explain plan for select * from jhlee68_card_test_20160415 where no between :no1 and :no2;
    
    해석되었습니다.
    
    SYS @ xxxxxx> select * from table( dbms_xplan.display( null, null, 'basic rows'));
    
    -----------------------------------------------------------------
    | Id  | Operation          | Name                       | Rows  |
    -----------------------------------------------------------------
    |   0 | SELECT STATEMENT   |                            |     3 |
    |   1 |  FILTER            |                            |       |
    |   2 |   TABLE ACCESS FULL| JHLEE68_CARD_TEST_20160415 |     3 |
    -----------------------------------------------------------------
    
    9 개의 행이 선택되었습니다.
    
    -- 리터널 변수
    SYS @ xxxxxx> explain plan for select * from jhlee68_card_test_20160415 where no <= 100;
    
    해석되었습니다.
    
    SYS @ xxxxxx> select * from table( dbms_xplan.display( null, null, 'basic rows'));
    
    ----------------------------------------------------------------
    | Id  | Operation         | Name                       | Rows  |
    ----------------------------------------------------------------
    |   0 | SELECT STATEMENT  |                            |    11 |
    |   1 |  TABLE ACCESS FULL| JHLEE68_CARD_TEST_20160415 |    11 |
    ----------------------------------------------------------------
    
    8 개의 행이 선택되었습니다.
    
    SYS @ xxxxxx>  explain plan for select * from jhlee68_card_test_20160415 where no between 500 and 600;
    
    해석되었습니다.
    
    SYS @ xxxxxx>  select * from table( dbms_xplan.display( null, null, 'basic rows'));
    
    ----------------------------------------------------------------
    | Id  | Operation         | Name                       | Rows  |
    ----------------------------------------------------------------
    |   0 | SELECT STATEMENT  |                            |    11 |
    |   1 |  TABLE ACCESS FULL| JHLEE68_CARD_TEST_20160415 |    11 |
    ----------------------------------------------------------------
    
    8 개의 행이 선택되었습니다.
    
    SYS @ xxxxxx>
    
    
  • 바인드 변수 사용시 : 컬럼 히스토그램 X, 파티션 레벨 통계 정보 X ( 테이블 레벨 통계 정보 사용 )

(1) 바인드 변수 Peeking ( 그림 4-8, Page.281 )

  • SQL SERVER : Parameter Sniffing 이라고 함.
  • Oracle 9i
    • DBMS_STAT 패키지의 기본 설정이 컬럼 히스그램 생성 X
  • Oracle 9i
    • DBMS_STAT 패키지의 기본 설정이 컬럼 히스그램 생성 O
  • Explain Plan : Peeking 기능이 활성화 상태에서도 실행계획은 미적용
  • 현재 대부분 운영 시스템에서는 아래처럼 이 기능을 비활성화시킨 상태 ( alter system set "_optim_peek_user_binds" = FALSE; )

(2) 적응적 커서 공유

  • 바인드 변수 Peeking 부작용 개선 : 11g
  • 1. 서울시 : 컬럼 히스토그램 확인 후 선택도가 높아 FTS 선택 이후 서울시가 입력되면 1번 커서를 반복 재상용 ( Child : 0 )
  • 2. 제주도 : 컬럼 히스토그램 확인 후 선택도가 낮아 인덱스 선택 2번 커서라고 하자 ( Child : 1 )
  • 3. 경기도 : 컬럼 히스토그램 확인 후 서울시 만큼 선택도가 비슷 FTS 선택 1번 커서 재사용 ( Child : 0 )
  • 4. 강원도 : 컬럼 히스토그램 확인 후 제주도 만큼 선택도가 비슷 FTS 선택 2번 커서 재사용 ( Child : 1 )
  • 수행 통계 뷰
    • v$sql_cs_statistics
    • v$sql_cs_histogram
    • v$sql_cs_selectivity

Bind Sensitive 커서

  • 옵티마지어가 바인드 변수 값에 다라 실행계획을 달리 가져갈 필요가 있다고
    판단되는 SQL 커서에 대해서 이 기능이 활성화 된다.
    이런 커서를 "Bind Sensitive 커서"라고 부른다. ( V$SQL.IS_BIND_SENSITIVE = 'Y' )
  • Bind Sensitve 모드에서는 아직 바인드 값에 따라 실행 계획을 변경하지 않으며, 우선 Bind Aware 모드로의 전환이 필요하다. ( V$SQL.IS_BIND_AWARE = 'Y', V$SQL.IS_SHAREABLE = 'N' )
    • 오라클은 Bind Sensitive 커서에 대해 내부적으로 별도의 히스토그램과 수행 통계를 관리하며,
      특정 값으로 실행했을 때 이전에 비해 많은 일량을 처리한 것으로 판단되는 순간 해당 커서를 Bind Aware 모드로 전환
      • 기존 커서는 사용이 중지되고, 새로운 커서가 생성되기 시작
      • 커서가 바인드 값 별로 다로 만들어지는 것은 아니며, 선택도가 비슷한 것끼리는 같은 커서를 공유.
  • 단점 : 처음 한번은 많은 일량으로 실행 되어야지만, 차후 새로운 실행계획을 수립의 근거가 됨.

(3) 입력 값에 따라 SQL 분리


SELECT /*+ FULL( a ) */ *
  FROM 아파트매물 a
 WHERE :CITY IN ( '서울시','경기도' )
   AND  도시 = :CITY
UNION ALL
SELECT /*+ INDEX( a IDX01 ) */ *
  FROM 아파트매물 a
 WHERE :CITY NOT IN ( '서울시','경기도' )
   AND  도시 = :CITY

(3) 입력 값에 따라 SQL 분리 주의 사항

  • OLTP 시스템에서 union all을 이용해 SQL을 지나치게 길게 작성하면 오히려 라이브러리 캐시 효율을 떨어 뜨리게 된다.
    • 하드 파싱 시점에 UNION ALL의 갯수 만큼 최적화 및 Shared Pool에서 많은 공간을 차지
    • Parse 단계 : 과도한 CPU 사용 ( 파싱트리, Syntax, Semantic )
    • 네트워크 : 메시지 전송량 증가
      
      SELECT /*+ FULL( EMP ) */ * FROM EMP
       WHERE :deptno = '30'
         AND DEPTNO = :deptno
      UNION ALL
      SELECT /*+ FULL( EMP ) */ * FROM EMP
       WHERE :deptno <> '30'
         AND DEPTNO = :deptno
         
      Rows   Row Source Operation
      -----  --------------------------------------------
          0  STATEMENT
          3    UNION-ALL (cr=4 pr=0 pw=0 time=151 us)
          0     FILTER (cr=0 pr=0 pw=0 time=9 us)
          0       TABLE ACCESS FULL EMP (cr=0 pr=0 pw=0 time=0 us)
          3     FILTER (cr=0 pr=0 pw=0 time=85 us)
          3       TABLE ACCESS BY INDEX ROWID EMP (cr=0 pr=0 pw=0 time=47 us)
          3         INDEX RANGE SCAN EMP_DEPTNO_IDX (cr=2 pr=0 pw=0 time=49 us)    
      
      -- 개발단 로직에서 분할
      IF :CITY IN ( '서울시', '경기도' ) THEN
        SELECT /*+ FULL( A ) */ *
          FROM 아파트매물 A
         WHERE 도시 = :CITY;
      ELSE
        SELECT /*+ INDEX( A IDX01 ) */ *
          FROM 아파트매물 A
         WHERE 도시 = :CITY;
      END IF;
      

(4) 예외적으로, Literal 상수값 사용

  • 1. 조건절 컬럼의 값 종류가 소수일 때
  • 2. 호출 빈도가 미비 할때 ( DW, OLAT )

07 세션 커서 캐싱 ( Page.290 그림 4-9, 그림 4-10 )

  • 소프트 파싱 부하 감소 : SQL 구문을 분석해서 해시 값 계산, library cache 래치를 획득, 커서 탐색
    • PGA 영역에 커서 포인터를 캐시함.
    • session_cached_cursors 세션 커서를 캐싱할지를 지정하는파라미터
      • Parse Call이 발생할 때마다 라이브러리 캐시를 탐색 전에 세션 커서 캐시를 먼저 확인( 캐시 사용 )
      • 커서를 닫는 순간 해당 커서의 Parse Call 횟수를 확인 3보다 크거나 같으면 세션 커서를 세션 커서 캐시 이동 ( 캐시 등록 )
      • LRU 알고리즘을 사용
    • 한계 : 탐색을 위한 래치 획득은 피할 수 있지만, 찾는 커서에 Pin을 설정하고 나중에 다시 해제하는 과정에서 발생
      할 수 있는 충돌을 막아야 하므로 library Cache 래치를 완전히 회피 하지는 못한다.
  • v$sql.users_opening : 공유 커서를 참조하고 있는 세션 커서의 수를 보여준다.
    수행을 마쳐 커서를 닫았더라도 참조를 버리지 않은 채 세션 커서 캐시로 옮겨진다면 여기에 집계
  • v$sql.users_executions : 커서가 열려있는 세션 커서의 수를 보여준다.
    DML일 때는 수행을 마칠 때 커서가 자동으로 닫히지만 SELECT문은 EOF에 도달했을 때 커서가 닫힌다.
    따라서 토드나 오렌지 가튼 쿼리 툴에서 대량의 데이터를 ARRAY 단위로 Fetch하는 도중에 이 값을
    조회해 보면 1이상의 값을 보인다. 데이터를 끝까지 Fetch하고 나면 이 값은 1만큼 줄어든다.
    참고로, SQL을 수행 중일 때는 공유 커서에 대한 참조를 유지한 상태이어야 하므로 이 값은
    user_opening 보다 항상 작거나 같다.
    SYSTEM @ xxxx> create table jhlee68_server_test( x number );
    
    테이블이 생성되었습니다.
    
    SYSTEM @ xxxx> col name for a30
    SYSTEM @ xxxx> SELECT A.NAME, B.VALUE
      2    FROM v$statname a, v$mystat b
      3   WHERE A.NAME IN ( 'session cursor cache hits', 'parse count (total)')
      4     AND B.STATISTIC# = A.STATISTIC#;
    
    NAME                                VALUE
    ------------------------------ ----------
    session cursor cache hits             180
    parse count (total)                   180
    
    SYSTEM @ xxxx> alter session set session_cached_cursors = 0;
    
    세션이 변경되었습니다.
    
    SYSTEM @ xxxx> declare
      2    i number;
      3  begin
      4    for i in 1..10000
      5    loop
      6      execute immediate 'insert into jhlee68_server_test values( ' ||mod( i,100 ) ||')';
      7    end loop;
      8    commit;
      9  end;
     10  /
    
    PL/SQL 처리가 정상적으로 완료되었습니다.
    
    SYSTEM @ xxxx> col name for a30
    SYSTEM @ xxxx> SELECT A.NAME, B.VALUE
      2    FROM v$statname a, v$mystat b
      3   WHERE A.NAME IN ( 'session cursor cache hits', 'parse count (total)')
      4     AND B.STATISTIC# = A.STATISTIC#   ;
    
    NAME                                VALUE
    ------------------------------ ----------
    session cursor cache hits             180
    parse count (total)                 10195
    
    SYSTEM @ xxxx> alter session set session_cached_cursors = 100;
    
    세션이 변경되었습니다.
    
    SYSTEM @ xxxx> declare
      2    i number;
      3  begin
      4    for i in 1..10000
      5    loop
      6      execute immediate 'insert into jhlee68_server_test values( ' ||mod( i,100 ) ||')';
      7    end loop;
      8    commit;
      9  end;
     10  /
    
    PL/SQL 처리가 정상적으로 완료되었습니다.
    
    SYSTEM @ xxxx> col name for a30
    SYSTEM @ xxxx> SELECT A.NAME, B.VALUE
      2    FROM v$statname a, v$mystat b
      3   WHERE A.NAME IN ( 'session cursor cache hits', 'parse count (total)')
      4     AND B.STATISTIC# = A.STATISTIC#
      5  ;
    
    NAME                                VALUE
    ------------------------------ ----------
    session cursor cache hits             180
    parse count (total)                 20200
    
    SYSTEM @ xxxx>   select sql_fulltext
      2      from v$sql
      3     where sql_text like 'insert into jhlee68_server_test%'
      4      and rownum <= 10
      5  ;
    
    SQL_FULLTEXT
    --------------------------------------------------------------------------------
    insert into jhlee68_server_test values( 23)
    insert into jhlee68_server_test values( 25)
    insert into jhlee68_server_test values( 30)
    insert into jhlee68_server_test values( 51)
    insert into jhlee68_server_test values( 60)
    insert into jhlee68_server_test values( 50)
    insert into jhlee68_server_test values( 67)
    insert into jhlee68_server_test values( 61)
    insert into jhlee68_server_test values( 98)
    insert into jhlee68_server_test values( 83)
    
    10 개의 행이 선택되었습니다.
    
    SYSTEM @ xxxx> select a.value "session cursor chche hits"
      2        , b.value "total parse call count"
      3        , round(a.value/b.value*100,2) "session cursor cache hits"
      4  from v$sysstat a, v$sysstat b
      5  where a.name = 'session cursor cache hits'
      6    and b.name = 'parse count (total)';
    
    session cursor chche hits total parse call count session cursor cache hits
    ------------------------- ---------------------- -------------------------
                    127161883              214443641                      59.3
    
    SYSTEM @ xxxx>   disconn
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, Real Application Clusters and Real Application Testing options에서 분리되었습니다.
     @ >
     @ > conn system/xxxx@xxxx
    연결되었습니다.
    SYSTEM @ xxxx>
    SYSTEM @ xxxx> col name for a30
    SYSTEM @ xxxx> SELECT A.NAME, B.VALUE
      2    FROM v$statname a, v$mystat b
      3   WHERE A.NAME IN ( 'session cursor cache hits', 'parse count (total)')
      4     AND B.STATISTIC# = A.STATISTIC#;
    
    NAME                                VALUE
    ------------------------------ ----------
    session cursor cache hits               1
    parse count (total)                    11
    
    SYSTEM @ xxxx> alter session set session_cached_cursors = 0;
    
    세션이 변경되었습니다.
    
    SYSTEM @ xxxx> declare
      2    var number;
      3    sql_stmt varchar2( 1000 );
      4  begin
      5    for i in 1..10000
      6    loop
      7        var := mod( i,100 );
      8        sql_stmt := 'insert into jhlee68_server_test values( :1 )';
      9      execute immediate sql_stmt using var;
     10    end loop;
     11    commit;
     12  end;
     13  /
    
    PL/SQL 처리가 정상적으로 완료되었습니다.
    
    SYSTEM @ xxxx> col name for a30
    SYSTEM @ xxxx> SELECT A.NAME, B.VALUE
      2    FROM v$statname a, v$mystat b
      3   WHERE A.NAME IN ( 'session cursor cache hits', 'parse count (total)')
      4     AND B.STATISTIC# = A.STATISTIC#
      5     ;
    
    NAME                                VALUE
    ------------------------------ ----------
    session cursor cache hits               1
    parse count (total)                 10022
    
    SYSTEM @ xxxx> alter session set session_cached_cursors = 100;
    
    세션이 변경되었습니다.
    
    SYSTEM @ xxxx> declare
      2    var number;
      3    sql_stmt varchar2( 1000 );
      4  begin
      5    for i in 1..10000
      6    loop
      7        var := mod( i,100 );
      8        sql_stmt := 'insert into jhlee68_server_test values( :1 )';
      9      execute immediate sql_stmt using var;
     10    end loop;
     11    commit;
     12  end;
     13  /
    
    PL/SQL 처리가 정상적으로 완료되었습니다.
    
    SYSTEM @ xxxx> col name for a30
    SYSTEM @ xxxx> SELECT A.NAME, B.VALUE
      2    FROM v$statname a, v$mystat b
      3   WHERE A.NAME IN ( 'session cursor cache hits', 'parse count (total)')
      4     AND B.STATISTIC# = A.STATISTIC#
      5     ;
    
    NAME                                VALUE
    ------------------------------ ----------
    session cursor cache hits           10000
    parse count (total)                 10027
    
    SYSTEM @ xxxx> select a.value "session cursor chche hits"
      2        , b.value "total parse call count"
      3        , round(a.value/b.value*100,2) "session cursor cache hits"
      4  from v$sysstat a, v$sysstat b
      5  where a.name = 'session cursor cache hits'
      6    and b.name = 'parse count (total)';
    
    session cursor chche hits total parse call count session cursor cache hits
    ------------------------- ---------------------- -------------------------
                    127114092              214465659                     59.27
    
    SYSTEM @ xxxx> select * from v$version;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    PL/SQL Release 11.2.0.4.0 - Production
    CORE    11.2.0.4.0      Production
    TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
    NLSRTL Version 11.2.0.4.0 - Production
    
    SYSTEM @ xxxx>
    
    
    
  • Dynamic SQL 사용 시, 커서 캐싱( 10g 이후 ) ( Page.304 참조 )
    • 10g 이후부터 같은 SQL문을 연속적으로 실행하면, Dynamic SQL이더라도
      바로 직전 커서를 캐싱한다.( 하드 파싱으로 테스를 함 @,.@ )

08 애플리케이션 커서 캐싱 ( Page.297 참조 )

  • 서버 커서 캐싱 한계인 공유 커서 힙 Pin, PGA 공간 할당을 배제 하는 기능
    
    
    -- Pro*C
    for(;;){
      EXEC ORACLE OPTION( HOLD_CURSOR=YES ); -- 어플리케이션 커서와 세션 커서의 관계를 다룬다.
      EXEC ORACLE OPTION( RELEASE_CURSOR=NO ); -- 공유 커서와 세션 커서 관계를 다룬다.
      EXEC SQL INSERT ....;
      EXEC ORACLE OPTION( RELEASE_CURSOR=YES );
    }
    
    

애플리케이션 커서 캐싱 - JAVA

  • 1. 묵지적 캐싱 옵션을 사용
  • 2. Statement를 닫지 않고 재사용하면 된다. ( ? )

(1) 바인드 변수를 사용하지 않을 때


public class ParseCall
{
  public static void NoBinding ( Connection onn, int count ) throws Exception{
    PaeparedStatement stmt;
    ResultSet rs;
    
    for ( int i = 1; i <= count; i++ ){
    
      stmt = conn.prepareStatement(
        "select /* no_binding */ " + i + "," + ",'test', a.* " +
        "from emp a where e.ename like 'W%'");
        
        rs=stmt.executeQuery();
        
        rs.close();
        stmt.close();
    
    }
    
  }
}

  • 매번 하드 파싱 발생

h3. (2) 바인드 변수를 사용하지만, 커서를 캐싱하지 않을 때
{code:sql}
public class ParseCall
{
  public static void NoCaching ( Connection onn, int count ) throws Exception{
    PaeparedStatement stmt;
    ResultSet rs;
    
    for ( int i = 1; i <= count; i++ ){
    
      stmt = conn.prepareStatement(
        "select /* no_caching */ " ?,?,?,a.* " +
        "from emp a where e.ename like 'W%'");
        
        stmt.setInt( 1, i );
        stmt.setInt( 2, i );
        stmt.setString( 3, "test" );
        
        rs=stmt.executeQuery();
        
        rs.close();
        stmt.close();
    
    }
    
  }
}

  • Parse Call이 Execute Call 횟수만큼 발생하게 된다. ( Page.300 )

(3) 커서를 닫지 않고 재사용할 때


public class ParseCall
{
  public static void CursorHolding ( Connection onn, int count ) throws Exception{
  
    // 루프문 바깥에 선언
    PaeparedStatement stmt = conn.prepareStatement(
                              "select /* no_caching */ " ?,?,?,a.* " +
                              "from emp a where e.ename like 'W%'");
    ResultSet rs;
    
    for ( int i = 1; i <= count; i++ ){
    
      stmt = conn.prepareStatement(
        "select /* no_caching */ " ?,?,?,a.* " +
        "from emp a where e.ename like 'W%'");
        
        stmt.setInt( 1, i );
        stmt.setInt( 2, i );
        stmt.setString( 3, "test" );
        
        rs=stmt.executeQuery();
        
        rs.close();
       
    }
     // 루프문 빠져 나왔을 때 커서를 닫는다. 
     stmt.close();
    
  }
}

  • Parse Call 1번 발생 ( Page.301 )

(4) 묵시적 캐싱 기능을 사용할 때 ( Page.302 )


public class ParseCall
{
  public static void CursorCaching ( Connection onn, int count ) throws Exception{
    
    // 캐시 사이즈를 1로 지정
    ((OracleConnection)conn).setStatementCacheSize(1);
    
    // 묵시적 캐싱 기능을 활성화
    ((OracleConnection)conn).setImplicitCachingEnabled(true);
    
    for ( int i = 1; i <= count; i++ ){
    
      PaeparedStatement stmt = conn.prepareStatement(
                              "select /* no_caching */ " ?,?,?,a.* " +
                              "from emp a where e.ename like 'W%'");
        
        stmt.setInt( 1, i );
        stmt.setInt( 2, i );
        stmt.setString( 3, "test" );
        
        ResultSet rs=stmt.executeQuery();
        
        rs.close();
        
         // 커서를 닫지만 내부적으로 닫히지 않은 채 캐시에 보관
         stmt.close();
       
    }
    
    
  }
}

  • Parse Call 1번 발생 ( Page.302 )

위 4가지 사례 성능 검증


public static void main( Straing[] args ) throws Exception{

  //Bind 변수를 사용하지 않았을 대
  NoBinding( conn, 5000 );
  ...
  
  // Bind 변수를 사용하지만 Caching 옵션을 사용하지 않을 때
  NoCaching( conn, 5000 );
  ...
  
  // Cursor를 닫지 않고 반복적으로 재사용할 때
  CursorHolding( conn, 5000 );
  ...
  
  // Caching 옵션을 사용할 때
  CursorCaching( conn, 5000 );
  
}

  • PL/SQL 에서는 자동적으로 어플리케이션 커서 까지 사용함. ( Page.303 그림 4-11 )

09 Static VS. Dynamic SQL ( Page 308, 그림 4-12 )

(1) Static SQL

  • String형 변수에 담지 않고 코드 사이에 직접 기술한 SQL문을 말한다. ( Embedded SQL )
    
    -- Pro*C
    int main()
    {
      printf( "사번을 입력하십시오 : ");
      scanf("%d", &empno);
      EXEC SQL WHENEVER NOT FOUND GOT notfound;
      EXEC SQL SELECT ENAME INTO :ENAME
                 FROM EMP
                WHERE EMPNO = :empno;
      printf( "사원명 : %s.\n", ename);
      
    notfound:
      printf("%d는 존재하지 않는 사번입니다.\n", empno );  
    }
    
  • 1. Pro*C에서 소스 프로그램을 작성해서 PreCompiler로 PreComfile하면 순수 C/C++ 코드가 생성됨
    • 1.1 PerComfile 과정에서 Static SQL을 발견하면 이를 SQL 런 타임 라이브러리에 포함된 함수를 호출하는 코드로 변환한다.
    • 1.2 이 과정에서 결국은 String형 변수에 담긴다. Static SQL은 런타임 시에 절대 변하지 않으므로
      • 1.2.1 PreCompile 단계에서 구문 분석, 유효 오브젝트 여부, 오브젝트 액세스 권한등을 체크하는 것이 가능하다.
  • 2. 이를 다시 C/C++ Compiler로 Compile하면 실행파일이 만들어지고, 이것을 시행함.
    
    
    -- Prc*C
    -- test.pc
    EXEC SQL INCLUDE SQLCA.H;
    
    int main()
    {
      EXEC SQL
        UPDATE EMP SET SAL = SAL * 1.1
        WHER EMPNOO = 7900;
      RETURN 0;
    }
    
    $ proc test.pc sqlcheck=syntax
    
    
  • sqlcheck=syntax 옵션을 주고 PreCompile하면 사전에 구문 분석을 실시한다. ( 오타 에러 )
    
    
    -- Prc*C
    -- test.pc
    EXEC SQL INCLUDE SQLCA.H;
    
    int main()
    {
      EXEC SQL
        UPDATE EMPs SET SAL = SAL * 1.1
        WHERE EMPNOO = 7900;
      RETURN 0;
    }
    
    $ proc test.pc sqlcheck=syntax -- Success!!
    $ proc test.pc sqlcheck=full userid=scott/tiger  -- Error!! 유효 오브젝트 및 액세스 권한까지 체크
    
    

(2) Dynamic SQL

Pro*C 에서 제공하는 Dynamic Method

  • Method 1 : 입력 Host 변수 없는 Non-Query ( SELECT 이외 )
    • 'DELETE FROM EMP WHERE DEPTNO = 20'
    • 'GRANT SELECT ON EMP TO scott'
  • Method 2 : 입력 Host 변수 개수가 고정적인 Non-Query ( SELECT 이외 )
    • 'INSERT INTO EMP( ENAME, JOB ) VALUE( :ename, :job )'
    • 'DELETE FROM EMP WHERE EMPNO = :empno'
  • Method 3 : select-list 컬럼 개수와 입력 Host 변수 개수가 고정적인 Query
    • 'SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO'
    • 'SELECT DNAME, LOC FROM DEPT WHERE DEPTNO = 20'
    • 'SELECT ENAME, EMPNO FROM EMP WHERE DEPTNO = :deptno'
    • 'DELETE FROM EMP WHERE EMPNO = :empno'
  • Method 4 : select-list 컬럼 개수와 입력 Host 변수 개수가 가변적인 Query
    • 'INSERT INTO EMP ( <unknown> ) values( <unknown> )'
    • 'SELECT <unknown> FROM EMP WHERE DEPTNO = :deptno'

(3) 일반 프로그램 언어에서 SQL 작성법

  • Static SQL : PowerBuilder, PL/SQL, Pro*C, SQLJ ( 개발언어 )
  • Dynamic SQL : String 에 담는 모든것 ( 아래 및 Toad, Orange, SQL*PLUS )
    
    -- JAVA
    PreparedStatment stmt;
    ResultSet rs;
    StringBuffer SQLStmt = new StringBuffer();
    SQLStmt.append( "SELECT ENAME, SAL FROM EMP " );
    SQLStmt.append( "WHERE EMPNO = ? " );
    
    stmt = conn.prepareStatement( SQLStmt.toString() );
    stmt.setLong( 1, txtEmpno.value );
    rs = stmt.executeQuery();
    
    // do anything
    
    rs.close();
    stmt.close();
    
    -- Delphi
    begin
      Query1.Close;
      Query1.Sql.Clear;
      Query1.Sql.Add( 'SELECT ENAME, SAL FROM EMP ');
      Query1.Sql.Add( 'WHERE EMPNO = :empno');
      Query1.ParamByName( 'empno').AsString := txtEmpno.Text;
      Query1.Open;
    end;
    
    -- Visual Basic
    Dim conn As NEW ADODB.Command
    Dim rs As ADODB.Recordset
    DIM SQLStmt as String
    
    SQLStmt = "SELECT ENAME, SAL FROM EMP"
    SQLSTMT = SQLStmt & "WHERE EMPNO = ? "
    comm.CommandText = SQLStmt
    comm.Parameters.Append comm.CreateParameter( "empno", adNumeric, adParamInput );
    comm.Parameters( "empno" ).Value = txtEmpno.Text
    Set Rs = Comm.Execute
    
    ' do anything
    
    rs.Close
    Set rs = Nothing
    Set comm = Nothing
    

(4) 문제의 본질은 바인드 변수 사용 여부

  • 애플리케이션 커서 캐싱 기능을 사용하지 않는다면 Dynamic, Static 구분은 라이브러리 캐시 효율과도 전혀 무관하다.
  • Dynamic SQL을 사용해 문제가 되는 것이 아니라 바인드 변수를 사용하지 않았을 때 문제가 되는 것이다 ( OLTP )

10 Dynamic SQL 사용 기준

(1) Dynamic SQL 사용에 관한 기본 원칙

  • 1. Static SQL을 지원하는 개발환경이라면 Static SQL로 작성하는 것을 원칙으로 한다.
    Static SQL은 PreCompile을 과정을 거치므로 런타임 시 안정적인 프로그램 Build가 가능하다는 장점이 있다.
    그리고 Dynamic SQL을 사용하면 애플리케이션 커서 개싱 기능이 작동하지 않는 경우가 있는데,
    이 기능이 필요한 상황( 예를 들어, 루프 내에서 반복 수행되는 쿼리 ) 에서 Dynamic SQL을 사용하면 성능이 나빠지기 때문이다.
  • 2. 아래 경우에는 Dynamic SQl를 사용해도 무방하다.
    • 2.1 PreCompile 과정에서 컴파일 에러가 나는 구문을 사용할 때. 예를 들어, Pro*C에서 스칼라 서브 쿼리, 분석함수, ANSI 조인
    • 2.2 상황에 조건에 따라 생성될 수 있는 SQL 최대 개수가 많아 Static SQL로 일일이 나눠서 작성하려면 개발 생산성이 저하되고
      유지보수 비용이 매우 커질 때
  • 3. 2번 경우에 해당해서 Dynamic SQL를 사용하더라도 조건절 컬럼의 값 종류가 매우 많을 때는 반드시 준수한다.
  • 4. 3번 바인드 변수 사용원칙을 준수하되 아래 경우는 예외적으로 인정한다.
    • 4.1 배치 프로그램이나 DW, OLAP 등 정보계 시스템에서 사용되는 Long Running 쿼리, 이들 쿼리는 파싱 소요시간이 쿼리
      총 소요시간에서 차지하는 비중이 매우 낮고, 수행빈도가 낮아 하드 파싱에 의한 라이브러리 캐시 부하를 유발할 가능성이 적음
    • 4.2 OLTP성 애플리케이션이더라도 사용빈도가 매우 낮아 하드파싱에 의한 라이브러리 캐시 부하를 유발할 가능성이 없을 때,
      예외적으로 인정하는 것이므로 단순히 바인드 변수 정의하는 게 귀찮다고 그렇게 해서는 안됨
    • 4.3 조건절 컬럼의 값 종류가 소수일 때, 특히 값 분포도가 균일하지 않아 옵티마이저가 칼럼 히스토그램 정보를 활용하도록 유도하고자
      할때
      예) 증권시장구분코드 = { '유가','코스닥', '주식파생', '상품파생' )

(2) 기본 원칙이 잘 지켜지지 않는 첫 번째 이유, 선택적 검색 조건 ( Page.317, 그림 4-13 )

  • 성능 고도화 2건으로...

(3) 선택적 검색 조건에 대한 현실적인 대안

  • 성능 고도화 2건으로...

(4) 선택적 검색 조건에 사용할 수 있는 기법 성능 비교

  • 성능 고도화 2건으로...

11 Static SQL 구현을 위한 기법들

  • 성능 고도화 2건으로...

문서에 대하여

  • 최초작성자 : 이재현
  • 최초작성일 : 2016년 03월 29일
  • 이 문서는 오라클클럽 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
  • 이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법 I'를 참고하였습니다.

문서정보

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