ORACLE 10g Finding Bind Values
from
1.
Bind Values
Bind 값은 Query 수행시 사용되는 변수를 의미한다. Query 안에는 :bind_name 으로 표현된다.
Bind 변수를 사용하는
이유는 Cursor 공유를 위해서이다. Cursor 를 공유한다는
것은 Hard Parsing 을 최소화 한다는 것이다.
이를 최소화 함으로써 얻을 수 있는 효과는 CPU Times 과
Memory 공간의 절약 (Library Cache) 과
메모리 단편화를 최소화할 수 있다.
반대로 단점(?) 도 있는데
Literal (실제값) 을 사용시 해당 값에 대한 정확한 통계정보 (이를테면 히스토그램) 를 사용해 최적의 실행플랜을 만들어 낼 수
있으나 Bind 변수를 사용하면 이를 이용못하며 잘못된
Cardinality 로 비효율적인 Plan 을 이용할 가능성이 있다.
Oracle 이 Version 을 올려감에도 불구하고 사용자의 조율 (hint, Query
Rewrite) 이 필요한 까닭이기도 하다.
2.
Bind Values 의 사용
Bind 변수는 Oracle 이 보관하고 있는 쿼리(v$sql, v$sqlarea 등) 를 수행하기 위해 필요하다. 특정
DB 를 관리하는 대상이 아니고서야, 혹은 쿼리와 관련된 개발자,설계자가 아니고서야 쿼리만 보고 Bind 변수에 어떤 실제값이 들어왔을지
판단하기란 어려운 일이다.
이런 고충(?) 이 있기에
10g 부터 v$sql_bind_capture 라는
Dictionay 가 제공된다. 해당 view 는 hash value 를 이용해 특정 쿼리가 어떤 실값을 가지고 수행했는지 정보를 가지고 있다.
쿼리를 튜닝하는 입장에서 이는 개발자, 혹은 DBA 와의 대화의 필요성을 하나 줄여준다. (이는 단점인가 장점인가…)
사실 설령 Bind 변수의 실값을 얻더라도 이는 여러 패턴중의
하나일 뿐이라 이를 가지고 튜닝 시 생각지도 못한 예외상황과 부딪힐 수 있다.
(그렇기에 여전히
현업종사자와의 대화가 필요하기도 하다.)
3.
Bind 값의 조회
본인은 아래의 쿼리를 이렇게 사용한다.
쿼리를 수행하면 hash_value 를 입력한다.
입력시 나온 결과를 상단에 붙이고(어딘가의 텍스트 에디터) 하단에는 쿼리 원문을 붙인다.
--f_bind.sql
set linesize 160
col name for a20
col value_string for a30
col datatype_string for a40
set pagesize 50
with tmp as (
select
decode(substr(name,2,2),0,':b0',
1,':b1',
2,':b2',
3,':b3',
4,':b4',
5,':b5',
6,':b6',
7,':b7',
8,':b8',
9,':b9',
10,':b10',
11,':b11',
name) name,
value_string,datatype_string
from v$sql_bind_capture
where hash_value = &hash_value)
select 'var '||name||' '||datatype_string
bind from tmp
union all
select '' from dual
union all
select 'exec '||name||':='||
decode(substr(datatype_string,1,4),'NUMB',value_string,''''||value_string||'''')
from tmp
/
SQL> @f_bind
Enter value for hash_value: 2065810788
old
18: where hash_value = &hash_value)
new
18: where hash_value = 2065810788)
BIND
------------------------------
var :b1 CHAR(32)
var :b2 CHAR(32)
var :b3 CHAR(32)
var :b4 CHAR(32)
var :b5 CHAR(32)
var :b6 CHAR(32)
var :b7 CHAR(32)
var :b8 CHAR(128)
var :b9 CHAR(32)
var :b10 CHAR(32)
var :b11 CHAR(32)
exec :b1:=''
exec :b2:='0'
exec :b3:='20101201'
exec :b4:='20101130'
exec :b5:='0201'
exec :b6:='0350'
exec :b7:='20101201'
exec :b8:='SSBR_DD_TRSC_PTCL'
exec :b9:='2'
exec :b10:='0'
exec :b11:='0'
23 rows selected.
4.
Query 의 개선
향후 계획으로 바로 복사/수행이 가능하도록 SQL 원문을 추가할 수 있게 하려 한다.
비효율 쿼리의 조회는 v$sqlarea 를 사용하는데 hash_value 는 여기에서 얻을 수 있다. 10g 는 sql full text 를 v$sqlarea 를 이용해 가지고 올
수 있기에 bind 변수만 덧붙여 주면 된다.
해결과제로는 Query 의
Formatting 이다.
웹검색을 하면 많은 결과가 있지만 이런 부분들은 스스로 해봐야 재미난 법이다!