- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=29065393&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
라이브러리 캐시 최적화 원리
- 01 SQL과 옵티마이저 ( 그림 4-3, Page 245 )
- 옵티마이저의 최적화 수행단계를 요약 ( Page.246 )
- 02 SQL 처리 과정 ( 그림 4-5, Page 247 )
- (1) SQL 파싱
- (2) SQL 최적화
- Adaptive search strategy
- Multiple Initial orderings heuristic
- (3) Row-Source Generation
- Shared Pool
- latch 대기 이벤트 ( 그림 4-6, Page 255 )
- 라이브러리 캐시 최적화 ( 개발자 측면 )
- (1) 커서란?
- 공유 커서( Shared cursor )
- 세션 커서( session cursor )
- 애플리케이션 커서( application cursor ) ( 그림 4-7, Page.259 )
- (2) 커서 공유
- (3) Child 커서를 공유하지 못하는 경우
- V$SQL_SHARED_CURSOR ( Page.267 )
- (4) Parent 커서를 공유하지 못하는 경우
- (1) 바인드 변수 Peeking ( 그림 4-8, Page.281 )
- (2) 적응적 커서 공유
- (3) 입력 값에 따라 SQL 분리
- (3) 입력 값에 따라 SQL 분리 주의 사항
- (4) 예외적으로, Literal 상수값 사용
- 애플리케이션 커서 캐싱 - JAVA
- (1) 바인드 변수를 사용하지 않을 때
- (3) 커서를 닫지 않고 재사용할 때
- (4) 묵시적 캐싱 기능을 사용할 때 ( Page.302 )
- 위 4가지 사례 성능 검증
- (1) Static SQL
- (2) Dynamic SQL
- Pro*C 에서 제공하는 Dynamic Method
- (3) 일반 프로그램 언어에서 SQL 작성법
- (4) 문제의 본질은 바인드 변수 사용 여부
- (1) Dynamic SQL 사용에 관한 기본 원칙
- (2) 기본 원칙이 잘 지켜지지 않는 첫 번째 이유, 선택적 검색 조건 ( Page.317, 그림 4-13 )
- (3) 선택적 검색 조건에 대한 현실적인 대안
- (4) 선택적 검색 조건에 사용할 수 있는 기법 성능 비교
- 11 Static SQL 구현을 위한 기법들
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 개의 행이 선택되었습니다.
- LCO 각각에는 자신을 참조하는 다른 실행 가능한 LCO 목록을 갖는다.
- 라이브러리 캐시 중복 저장 이유 : 데이터 딕셔너리 캐시에서 읽어 LCO간 의존성를 관리하는 데에 목적이 있다.
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
- 특히, 등치 조건이 아닌 부등호나 Between 같은 범위 기반 검색 조건일 때는 고정된 규칙을 사용하므로 더 부정학환 예측 ( 1 ~ 4 : 5%, 5 ~ 8 : 0.25% )
- 카디널리티 = 선택도 * 전체 레코드 수
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 모드로 전환- 기존 커서는 사용이 중지되고, 새로운 커서가 생성되기 시작
- 커서가 바인드 값 별로 다로 만들어지는 것은 아니며, 선택도가 비슷한 것끼리는 같은 커서를 공유.
- 오라클은 Bind Sensitive 커서에 대해 내부적으로 별도의 히스토그램과 수행 통계를 관리하며,
- 단점 : 처음 한번은 많은 일량으로 실행 되어야지만, 차후 새로운 실행계획을 수립의 근거가 됨.
(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이더라도
바로 직전 커서를 캐싱한다.( 하드 파싱으로 테스를 함 @,.@ )
- 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 조건절 컬럼의 값 종류가 소수일 때, 특히 값 분포도가 균일하지 않아 옵티마이저가 칼럼 히스토그램 정보를 활용하도록 유도하고자
할때
예) 증권시장구분코드 = { '유가','코스닥', '주식파생', '상품파생' )
- 4.1 배치 프로그램이나 DW, OLAP 등 정보계 시스템에서 사용되는 Long Running 쿼리, 이들 쿼리는 파싱 소요시간이 쿼리
(2) 기본 원칙이 잘 지켜지지 않는 첫 번째 이유, 선택적 검색 조건 ( Page.317, 그림 4-13 )
- 성능 고도화 2건으로...
(3) 선택적 검색 조건에 대한 현실적인 대안
- 성능 고도화 2건으로...
(4) 선택적 검색 조건에 사용할 수 있는 기법 성능 비교
- 성능 고도화 2건으로...
11 Static SQL 구현을 위한 기법들
- 성능 고도화 2건으로...
문서에 대하여
- 최초작성자 : 이재현
- 최초작성일 : 2016년 03월 29일
- 이 문서는 오라클클럽 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
- 이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법 I'를 참고하였습니다.
문서정보
- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=29065393&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.