바인드변수
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 커서 쉐어링[편집]
- Literal SQL을 강제적으로 BIND변수로 바꿔서 SQL을 공유화 하는 기법
- 무조건 상수부를 바꿔 버리는 문제
| 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개로 고정
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";3 바인드 피킹 (bind peeking)[편집]
- 실행계획을 생성하기 전에 바인드 변수의 값을 쿼리 옵티마이져가 가져와서 리터럴 처럼 이용하는것
- 바인드 변수를 사용하는 SQL은 처음 실행 될때 어떤값이 바인딩되어 수행 되었는지에 따라 , 이후 동일 SQL에 대한 실행계획이 고정되어 버리는 문제점이 있음
4 어뎁티브 커서 쉐어링[편집]
- Skewed 데이터에 대한 Cursor Sharing의 문제점을 보완하고 바인드 변수에 따른 최적의 Plan을 결정하는 기능
- _optimizer_adaptive_cursor_sharing = true (default)
- 바인드 변수에 따라 여러개의 Plan을 가지고 있어 Bind Peeking 문제점을 보완 함
4.1 장점[편집]
- Bind 값이 동일한 Selectivity를 가질때 Plan 공유
- Bind 값이 다른 Selectivity를 가질때 새로운 Plan 생성
5 바인드 변수 확인[편집]
5.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;