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

라이브러리 캐시 최적화 원리
1. 01 SQL과 옵티마이저 ( 그림 4-3, Page 245 )
03 라이브러리 캐시 구조
1. latch 대기 이벤트 ( 그림 4-6, Page 255 )
2. 라이브러리 캐시 최적화 ( 개발자 측면 )
04 커서 공유
05 바인드 변수의 중요성 ( Page.273 )
06 바인드 변수의 부작용과 해법
07 세션 커서 캐싱 ( Page.290 그림 4-9, 그림 4-10 )
08 애플리케이션 커서 캐싱 ( Page.297 참조 )
09 Static VS. Dynamic SQL ( Page 308, 그림 4-12 )
10 Dynamic SQL 사용 기준

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

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

실행계획
{code: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)




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

h3. 02 SQL 처리 과정 ( 그림 4-5, Page 247 )
* 소프트 파싱 : 파싱 단계에서 SQL 커서를 메모리에서 찾아서 곧바로 실행 단계로..
* 하드 파싱 : 찾는데 실패해 최적화 및 Row-Source 생성 단계를 거치는 것


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

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

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

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

h3. (3) Row-Source Generation
* 실행 가능한 코드 또는 프로시저 형태로 포맷팅
{code: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)

-- 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 목록을 갖는다.

라이브러리 캐시 오브젝트
{code:sql} 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 개의 행이 선택되었습니다.



h3. Shared Pool
* LRU 알고리즘
* 9i 이전부터는 1의 shared pool latch 관리, 9i부터 Sub Pool로 래치도 7개까지
* 순간적으로 과도한 하드 파싱 부하를 일으킨다면 shared pool 래치에 대한 경합 현상이 나타날 수 있다.
{code:sql}
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 : 커서가 무효화된 횟수

커서 공유 테스트
{code:sql}

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>



h3. (3) Child 커서를 공유하지 못하는 경우
* 1. SQL에서 참조하는 오브젝트명이 같지만 SQL을 실행한 사용자에 따른 다른 오브젝트 가리킬 때
* 2. 참조 오브젝트가 변경돼 커서가 무효화되면 이후 그 커서를 처음 사용하려는 세션에 의해 다시 하드 파싱돼야 하는데, 특정
  세션이 아직 기존 커서를 사용 중(Pin) 일 때
* 3. 옵티마이저 모드를 비롯해 옵티마이저 관련 파리미터가 다를 때
* 4. 입력된 바인드 값의 길이가 크게 다를 때
* 5. NLS 파라미터를 다르게 설정했을 때
* 6. SQL 트레이스를 활성화했을 때
{code: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

String 형 변수에 담아서 기술하는 SQL문을 말한다.
Dynamic SQL을 만나면 PreCompiler는 그 내용을 확인하지 않고 그대로 통과시킨다.
PreCompiler : 오라클 사이트에서 다운로드 ( http://www.oracle.com/technetwork/topics/precomp-112010-084940.html )

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'를 참고하였습니다.*

04. 라이브러리 캐시 최적화 원리 (by ljh1148) [2016.04.08]

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

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

03 라이브러리 캐시 구조

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

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

04 커서 공유

(1) 커서란?

공유 커서( Shared cursor )

세션 커서( session cursor )

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

(2) 커서 공유

V$SQL_SHARED_CURSOR ( Page.267 )

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

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

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

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

(2) 적응적 커서 공유

Bind Sensitive 커서

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

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

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

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

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

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

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

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

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

위 4가지 사례 성능 검증

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

(1) Static SQL

(2) Dynamic SQL

Pro*C 에서 제공하는 Dynamic Method

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

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

10 Dynamic SQL 사용 기준

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

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

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

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

11 Static SQL 구현을 위한 기법들

문서에 대하여