"바인드변수"의 두 판 사이의 차이
DB CAFE
(→리터럴 SQL) |
(→커서 (Cursor)) |
||
(같은 사용자의 중간 판 26개는 보이지 않습니다) | |||
1번째 줄: | 1번째 줄: | ||
=== 리터럴 SQL === | === 리터럴 SQL === | ||
+ | {{틀:고지 상자 | ||
+ | |내용= | ||
* 아래의 쿼리는 같은 결과과 나오지만 오라클은 각각 다른 쿼리라고 판단하여 작업을 하게 됩니다. | * 아래의 쿼리는 같은 결과과 나오지만 오라클은 각각 다른 쿼리라고 판단하여 작업을 하게 됩니다. | ||
<source lang=sql> | <source lang=sql> | ||
8번째 줄: | 10번째 줄: | ||
WHERE EMPID = 20 ; | WHERE EMPID = 20 ; | ||
</source> | </source> | ||
+ | |||
* 즉, 100% 동일해야 SQL파싱이 발생하지 않습니다. | * 즉, 100% 동일해야 SQL파싱이 발생하지 않습니다. | ||
* 이를 동일한 쿼리로 인식시키기 위해서 바인드 변수를 사용하여야 합니다. | * 이를 동일한 쿼리로 인식시키기 위해서 바인드 변수를 사용하여야 합니다. | ||
+ | }} | ||
# literal SQL은 서로 다른 SQL로 인식하므로 매번 Parsing(Hard Parsing) 작업이 필요하며 , 많은 CPU사용량과 추가적인 메모리(Shared Pool의 Library Cache)가 필요함 | # literal SQL은 서로 다른 SQL로 인식하므로 매번 Parsing(Hard Parsing) 작업이 필요하며 , 많은 CPU사용량과 추가적인 메모리(Shared Pool의 Library Cache)가 필요함 | ||
# Shared Pool Latch 및 Library Cache Latch의 경합을 유발하여 CPU 사용량이 급증함. | # Shared Pool Latch 및 Library Cache Latch의 경합을 유발하여 CPU 사용량이 급증함. | ||
16번째 줄: | 20번째 줄: | ||
# SQL이 Literal로 되어 있으면 Data가 Skewed되어 있는 경우에는 유리하나 다수가 이용하는 사용자 환경에는 적합하지 않다. | # SQL이 Literal로 되어 있으면 Data가 Skewed되어 있는 경우에는 유리하나 다수가 이용하는 사용자 환경에는 적합하지 않다. | ||
− | === 커서 쉐어링 === | + | ---- |
− | === 바인드 피킹 === | + | === 커서 (Cursor) === |
− | === 어뎁티브 커서 쉐어링 === | + | # 커서는 공유 SQL 영역(SGA 라이브러리 캐시 내부에 존재)과 프라이빗 SQL 영역(서버 프로세스 메모리 내에 존재)을 참조 하는 핸들 |
+ | #:* 핸들은 프로그램이 리소스에 엑세스할수 있도록 만들어 주는 메모리 구조 (프로그래밍 언어에서 사용하는 핸들과 같음) | ||
+ | # 공유 SQL 영역은 부모 커서 , 자식 커서 구조로 이루어짐 | ||
+ | ==== 커서 생명 주기 ==== | ||
+ | # 커서 열기 | ||
+ | # 커서 파싱 | ||
+ | # 출력 변수 정의 | ||
+ | # 입력 변수 바인딩 | ||
+ | # 커서 실행 | ||
+ | # 커서 패치 | ||
+ | # 커서 닫기 | ||
+ | |||
+ | === 커서 쉐어링 (Cursor Sharing) === | ||
+ | # Literal SQL을 강제적으로 BIND변수로 바꿔서 SQL을 공유화 하는 기법 | ||
+ | # 무조건 상수부를 바꿔 버리는 문제 | ||
+ | <source lang=sql> | ||
+ | select * from emp where ename='CLACK'; | ||
+ | select * from emp where ename='CYKIM'; | ||
+ | select * from emp where ename='JHone'; | ||
+ | </source> | ||
+ | ===> 다음과 같이 고정됨 | ||
+ | <source lang=sql> | ||
+ | select * from amp where ename=:"SYS_B_0"; | ||
+ | </source> | ||
+ | |||
+ | {| class="wikitable sortable" | ||
+ | |- | ||
+ | ! Property !! Description | ||
+ | |- | ||
+ | | Parameter type || String | ||
+ | |- | ||
+ | | Syntax || CURSOR_SHARING = {SIMILAR | EXACT | FORCE } | ||
+ | |- | ||
+ | | Default value || EXACT | ||
+ | |- | ||
+ | | Modifiable || ALTER SESSION , ALTER SYSTEM | ||
+ | |- | ||
+ | | Basic || No | ||
+ | |} | ||
+ | * EXACT : 기본값, 서로 완전히 일치하는 SQL 문장만 커서가 공유됨 | ||
+ | * SIMILAR : SQL을 바인드 변수로 무조건 바꾸고 SQL당 PLAN이 Multi-Version을 가지고 선별적사용(의미분석) | ||
+ | * FORCE : SQL을 바이드 변수로 무조건 바꼬고 SQL당 PLAN은 1개로 고정 | ||
+ | |||
+ | === 바인드 피킹 (bind peeking) === | ||
+ | # 실행계획을 생성하기 전에 바인드 변수의 값을 쿼리 옵티마이져가 가져와서 리터럴 처럼 이용하는것 | ||
+ | # 바인드 변수를 사용하는 SQL은 '''처음 실행 될때''' 어떤값이 바인딩되어 수행 되었는지에 따라 , 이후 동일 SQL에 대한 실행계획이 고정되어 버리는 문제점이 있음 | ||
+ | |||
+ | === 어뎁티브 커서 쉐어링 (adaptive cursor sharing) === | ||
+ | # 바인드 변수에 따라 여러개의 Plan을 가지고 있어 Bind Peeking 문제점 보완 | ||
+ | # 공유가능한 커서를 재사용할때 비효율이 발생하는지 자동으로 식별 | ||
+ | # Skewed 데이터에 대한 Cursor Sharing의 문제점을 보완하고 바인드 변수에 따른 최적의 Plan을 결정하는 기능 | ||
+ | # 파라미터 설정 <source lang=sql>_optimizer_adaptive_cursor_sharing = true (default)</source> | ||
+ | |||
+ | |||
+ | ==== 장점 ==== | ||
+ | # Bind 값이 동일한 Selectivity를 가질때 Plan 공유 | ||
+ | # Bind 값이 다른 Selectivity를 가질때 새로운 Plan 생성 | ||
+ | |||
+ | ==== 어뎁티브 커셔 쉐어링 확인 조회 ==== | ||
+ | <source lang=sql> | ||
+ | select child_number | ||
+ | , is_bind_sensitive | ||
+ | , is_bind_aware | ||
+ | , is_shareable | ||
+ | , plan_hash_value | ||
+ | from v$sql | ||
+ | where sql_id ='xxxx'; | ||
+ | </source> | ||
+ | * is_bind_sensitive : 실행계획을 생성하는데 바인드 변수피킹이 사용되었는지,어뎁티브 커서 쉐어링이 공유 될지 있는지를 나타냄. 어뎁티브 커셔 쉐어링을 고려해 볼수 있다면 Y , 아니면 N | ||
+ | * is_bind_aware : 커서의 어뎁티브 커서 공유 사용여부를 나타냄. 사용하고 있다면 Y, 아니면 N | ||
+ | * is_shareable : 커서가 쉐어(공유) 될수 있는지를 나타냄. Y는 공유가능 , 아니면 N | ||
+ | |||
+ | ---- | ||
+ | |||
=== 바인드 변수 확인 === | === 바인드 변수 확인 === | ||
− | + | ==== where절에 사용되는 바인드 값 확인 ==== | |
<source lang=sql> | <source lang=sql> | ||
SELECT name, position, dup_position, datatype_string, value_string | SELECT name, position, dup_position, datatype_string, value_string | ||
48번째 줄: | 125번째 줄: | ||
</source> | </source> | ||
+ | * AWR 이용 (DBA_HIST_SQLBIND + DBA_HIST_SNAPSHOT) | ||
+ | - SQL문 텍스트를 보려면 DBA_HIST_SQLTEXT | ||
+ | <source lang=sql> | ||
+ | SELECT DISTINCT | ||
+ | A.SNAP_ID , A.INSTANCE_NUMBER , B.END_INTERVAL_TIME | ||
+ | , A.LAST_CAPTURED, A.NAME , A.VALUE_STRING | ||
+ | FROM DBA_HIST_SQLBIND A | ||
+ | , DBA_HIST_SNAPSHOT B | ||
+ | WHERE A.DBID = B.DBID | ||
+ | AND A.SNAP_ID = B.SNAP_ID | ||
+ | AND A.INSTANCE_NUMBER = B.INSTANCE_NUMBER | ||
+ | AND B.END_INTERVAL_TIME >= TRUNC(SYSDATE) + 9/24 - 1 / 8 | ||
+ | AND A.SQL_ID = :sqlid | ||
+ | AND A.LAST_CAPTURED IS NOT NULL | ||
+ | ORDER BY B.END_INTERVAL_TIME DESC; | ||
+ | </source> | ||
[[Category:oracle]] | [[Category:oracle]] |
2023년 11월 22일 (수) 14:06 기준 최신판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 리터럴 SQL[편집]
android * 아래의 쿼리는 같은 결과과 나오지만 오라클은 각각 다른 쿼리라고 판단하여 작업을 하게 됩니다.
SELECT EMP_NO FROM EMPLOYEE WHERE EMPID = 10;
SELECT emp_no FROM employee WHERE empid = 10 ;
SELECT EMP_NO
FROM EMPLOYEE
WHERE EMPID = 20 ;
- 즉, 100% 동일해야 SQL파싱이 발생하지 않습니다.
- 이를 동일한 쿼리로 인식시키기 위해서 바인드 변수를 사용하여야 합니다.
- literal SQL은 서로 다른 SQL로 인식하므로 매번 Parsing(Hard Parsing) 작업이 필요하며 , 많은 CPU사용량과 추가적인 메모리(Shared Pool의 Library Cache)가 필요함
- Shared Pool Latch 및 Library Cache Latch의 경합을 유발하여 CPU 사용량이 급증함.
- 다른 SQL의 Parsing된 정보를 밀어내 버리며 Shared Pool의 Memory Pregmentation을 발생시킨다.
- ORA-4031와 같은 상황이 발생되어 DB Reboot 상황이 발생 할수도 있다.
- SQL이 Literal로 되어 있으면 Data가 Skewed되어 있는 경우에는 유리하나 다수가 이용하는 사용자 환경에는 적합하지 않다.
2 커서 (Cursor)[편집]
- 커서는 공유 SQL 영역(SGA 라이브러리 캐시 내부에 존재)과 프라이빗 SQL 영역(서버 프로세스 메모리 내에 존재)을 참조 하는 핸들
- 핸들은 프로그램이 리소스에 엑세스할수 있도록 만들어 주는 메모리 구조 (프로그래밍 언어에서 사용하는 핸들과 같음)
- 공유 SQL 영역은 부모 커서 , 자식 커서 구조로 이루어짐
2.1 커서 생명 주기[편집]
- 커서 열기
- 커서 파싱
- 출력 변수 정의
- 입력 변수 바인딩
- 커서 실행
- 커서 패치
- 커서 닫기
3 커서 쉐어링 (Cursor Sharing)[편집]
- Literal SQL을 강제적으로 BIND변수로 바꿔서 SQL을 공유화 하는 기법
- 무조건 상수부를 바꿔 버리는 문제
select * from emp where ename='CLACK';
select * from emp where ename='CYKIM';
select * from emp where ename='JHone';
===> 다음과 같이 고정됨
select * from amp where ename=:"SYS_B_0";
Property | Description |
---|---|
Parameter type | String |
Syntax | EXACT | FORCE } |
Default value | EXACT |
Modifiable | ALTER SESSION , ALTER SYSTEM |
Basic | No |
- EXACT : 기본값, 서로 완전히 일치하는 SQL 문장만 커서가 공유됨
- SIMILAR : SQL을 바인드 변수로 무조건 바꾸고 SQL당 PLAN이 Multi-Version을 가지고 선별적사용(의미분석)
- FORCE : SQL을 바이드 변수로 무조건 바꼬고 SQL당 PLAN은 1개로 고정
4 바인드 피킹 (bind peeking)[편집]
- 실행계획을 생성하기 전에 바인드 변수의 값을 쿼리 옵티마이져가 가져와서 리터럴 처럼 이용하는것
- 바인드 변수를 사용하는 SQL은 처음 실행 될때 어떤값이 바인딩되어 수행 되었는지에 따라 , 이후 동일 SQL에 대한 실행계획이 고정되어 버리는 문제점이 있음
5 어뎁티브 커서 쉐어링 (adaptive cursor sharing)[편집]
- 바인드 변수에 따라 여러개의 Plan을 가지고 있어 Bind Peeking 문제점 보완
- 공유가능한 커서를 재사용할때 비효율이 발생하는지 자동으로 식별
- Skewed 데이터에 대한 Cursor Sharing의 문제점을 보완하고 바인드 변수에 따른 최적의 Plan을 결정하는 기능
- 파라미터 설정
_optimizer_adaptive_cursor_sharing = true (default)
5.1 장점[편집]
- Bind 값이 동일한 Selectivity를 가질때 Plan 공유
- Bind 값이 다른 Selectivity를 가질때 새로운 Plan 생성
5.2 어뎁티브 커셔 쉐어링 확인 조회[편집]
select child_number
, is_bind_sensitive
, is_bind_aware
, is_shareable
, plan_hash_value
from v$sql
where sql_id ='xxxx';
- is_bind_sensitive : 실행계획을 생성하는데 바인드 변수피킹이 사용되었는지,어뎁티브 커서 쉐어링이 공유 될지 있는지를 나타냄. 어뎁티브 커셔 쉐어링을 고려해 볼수 있다면 Y , 아니면 N
- is_bind_aware : 커서의 어뎁티브 커서 공유 사용여부를 나타냄. 사용하고 있다면 Y, 아니면 N
- is_shareable : 커서가 쉐어(공유) 될수 있는지를 나타냄. Y는 공유가능 , 아니면 N
6 바인드 변수 확인[편집]
6.1 where절에 사용되는 바인드 값 확인[편집]
SELECT name, position, dup_position, datatype_string, value_string
FROM (SELECT a.*
, ROW_NUMBER () OVER (PARTITION BY a.position ORDER BY a.last_captured DESC) AS rn
FROM v$sql_bind_capture a
WHERE a.sql_id = 'ckrnp9t0x37xh'
AND a.child_number = 0)
WHERE rn = 1;
WITH BB AS
(SELECT DISTINCT NAME BIND_VAL, VALUE_STRING VAL_VAL,DATATYPE_STRING,SQL_ID
FROM V$SQL_BIND_CAPTURE A
WHERE (LAST_CAPTURED, SQL_ID) IN
(SELECT MAX(LAST_CAPTURED), SQL_ID FROM V$SQL_BIND_CAPTURE GROUP BY SQL_ID)),
CC AS
(SELECT 1 RNUM FROM DUAL UNION ALL SELECT 2 FROM DUAL)
SELECT SQL_ID,DECODE(MOD(ROWNUM, 2), 1, A, B)
FROM (SELECT SQL_ID,REPLACE('VARIABLE BIND_VAL '||DATATYPE_STRING,'BIND_VAL',BIND_VAL) A,
REPLACE(REPLACE('EXEC :BIND_VAL :=''VAL_VAL'';','VAL_VAL',VAL_VAL),'BIND_VAL',BIND_VAL) B
FROM BB,
CC)
WHERE sql_id = 'gsqxm6zz3w2by';
- AWR 이용 (DBA_HIST_SQLBIND + DBA_HIST_SNAPSHOT)
- SQL문 텍스트를 보려면 DBA_HIST_SQLTEXT
SELECT DISTINCT
A.SNAP_ID , A.INSTANCE_NUMBER , B.END_INTERVAL_TIME
, A.LAST_CAPTURED, A.NAME , A.VALUE_STRING
FROM DBA_HIST_SQLBIND A
, DBA_HIST_SNAPSHOT B
WHERE A.DBID = B.DBID
AND A.SNAP_ID = B.SNAP_ID
AND A.INSTANCE_NUMBER = B.INSTANCE_NUMBER
AND B.END_INTERVAL_TIME >= TRUNC(SYSDATE) + 9/24 - 1 / 8
AND A.SQL_ID = :sqlid
AND A.LAST_CAPTURED IS NOT NULL
ORDER BY B.END_INTERVAL_TIME DESC;