바인드변수
DB CAFE
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;