의 
블로그
Bind Peeking을 Disable해도 Bind Peeking이 된다!
Advanced Oracle 2009/01/05 20:23 이 제목을 믿기 어렵겠지만 이런 현상이 종종 발생한다.
내가 최근에 이 질문을 받은 것만 두 번째이므로, 생각보다 많은 시스템에서 이런 문제가 발생하는 것으로 생각된다.
도대체 왜 Bind Peeking을 Disable해도, 즉 _optim_peek_user_binds 파라미터의 값을 false로 변경해도 Bind Peeking이 이루어진단 말인가?
여기와 관련된 Bug가 일부 보고되어 있다. Pro*C로 작성된 A
pplication에서 Bind Peeking을 Disable해도 Bind Peeking이 이루어지는 현상이 종종 보고된다. 비슷한 현상이 Pro*Cobol에서도 보고되어 있는 것으로 보아 아마 C나 Cobol등의 Interface상에 존재하는 버그가 아닐까 한다.
(사실 직관적으로 이해는 안가지만)
자세한 내용은 Metalink bug# 6981155을 참조한다.
----------------------------------------------------------------------------------
부록으로 현재 Bind Peeking이 활성화되었는지 어떤지를 아는 방법은 무엇일까?
1) Optimizer 변수를 참조한다.
v$ses_optimizer_env 뷰나 v$sys_optimizer_env 뷰를 보면 현재 Optimizer가 참조하는 파라미터들을 알 수 있다. 이 뷰에서 _optim_peek_user_binds 값이 false이면 Bind Peeking이 Disable된 것이다.
사용법은 아래와 같다.
값을 변경하면 아래와 같이 관찰된다.
하지만 위에서 말한 Bug는 이 파라미터의 값이 false임에도 불구하고 Bind Peeking이 이루어진다는 것이다.
이것은 또 어떻게 확인하는가?
2) v$sql_plan.other_xml을 참조한다.
v$sql_plan.other_xml 에는 Oracle이 Plan을 관리하기 위해 사용하는 부가적인 정보들이 XML의 형태로 들어가 있다. 여기서 peeked_binds 항목이 존재하면 Bind Peeking이 이루어진 것이다.
3) dbms_xplan.display_cursor 함수 호출시 +peeked_binds 옵션을 사용한다.
2)번과 동일한 일을 하지만 훨씬 보기 쉽게 출력해 준다.
내가 최근에 이 질문을 받은 것만 두 번째이므로, 생각보다 많은 시스템에서 이런 문제가 발생하는 것으로 생각된다.
도대체 왜 Bind Peeking을 Disable해도, 즉 _optim_peek_user_binds 파라미터의 값을 false로 변경해도 Bind Peeking이 이루어진단 말인가?
여기와 관련된 Bug가 일부 보고되어 있다. Pro*C로 작성된 A
pplication에서 Bind Peeking을 Disable해도 Bind Peeking이 이루어지는 현상이 종종 보고된다. 비슷한 현상이 Pro*Cobol에서도 보고되어 있는 것으로 보아 아마 C나 Cobol등의 Interface상에 존재하는 버그가 아닐까 한다. (사실 직관적으로 이해는 안가지만)
자세한 내용은 Metalink bug# 6981155을 참조한다.
----------------------------------------------------------------------------------
부록으로 현재 Bind Peeking이 활성화되었는지 어떤지를 아는 방법은 무엇일까?
1) Optimizer 변수를 참조한다.
v$ses_optimizer_env 뷰나 v$sys_optimizer_env 뷰를 보면 현재 Optimizer가 참조하는 파라미터들을 알 수 있다. 이 뷰에서 _optim_peek_user_binds 값이 false이면 Bind Peeking이 Disable된 것이다.
사용법은 아래와 같다.
Hidden Parameter이기 때문에 Default 값일때는 보이지 않는다.
select name, isdefault, value
from v$ses_optimizer_env
where sid = userenv('sid')
;
no rows selected
select name, isdefault, value
from v$ses_optimizer_env
where sid = userenv('sid')
;
no rows selected
값을 변경하면 아래와 같이 관찰된다.
alter session set "_optim_peek_user_binds" = false;
select name, isdefault, value
from v$ses_optimizer_env
where sid = userenv('sid')
;
NAME ISDEFAULT VALUE
------------------------------ --------- --------------------
_optim_peek_user_binds NO false
select name, isdefault, value
from v$ses_optimizer_env
where sid = userenv('sid')
;
NAME ISDEFAULT VALUE
------------------------------ --------- --------------------
_optim_peek_user_binds NO false
하지만 위에서 말한 Bug는 이 파라미터의 값이 false임에도 불구하고 Bind Peeking이 이루어진다는 것이다.
이것은 또 어떻게 확인하는가?
2) v$sql_plan.other_xml을 참조한다.
v$sql_plan.other_xml 에는 Oracle이 Plan을 관리하기 위해 사용하는 부가적인 정보들이 XML의 형태로 들어가 있다. 여기서 peeked_binds 항목이 존재하면 Bind Peeking이 이루어진 것이다.
var b1 number;
exec :b1 := 1;
select /* test */ * from t1 where c1 = :b1
;
set long 10000
select sql_id, other_xml
from v$sql_plan
where sql_id in
(select sql_id from v$sql where sql_text like 'select /* test */%')
;
<other_xml><info type="db_version">10.2.0.1</info><info type
="parse_schema"><![CDATA["UKJA"]]></info
><info type="dynamic_sampling">yes</info
><info type="plan_hash">3617692013</info
><peeked_binds><bind nam=":B1" pos="1" d
ty="2" pre="0" scl="0" mxl="22">c102</bi
nd></peeked_binds><outline_data><hint><!
[CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></h
int><hint><![CDATA[OPTIMIZER_FEATURES_EN
ABLE('10.2.0.1')]]></hint><hint><![CDATA
[ALL_ROWS]]></hint><hint><![CDATA[OUTLIN
E_LEAF(@"SEL$1")]]></hint><hint><![CDATA
[FULL(@"SEL$1" "T1"@"SEL$1")]]></hint></
outline_data></other_xml>
exec :b1 := 1;
select /* test */ * from t1 where c1 = :b1
;
set long 10000
select sql_id, other_xml
from v$sql_plan
where sql_id in
(select sql_id from v$sql where sql_text like 'select /* test */%')
;
<other_xml><info type="db_version">10.2.0.1</info><info type
="parse_schema"><![CDATA["UKJA"]]></info
><info type="dynamic_sampling">yes</info
><info type="plan_hash">3617692013</info
><peeked_binds><bind nam=":B1" pos="1" d
ty="2" pre="0" scl="0" mxl="22">c102</bi
nd></peeked_binds><outline_data><hint><!
[CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></h
int><hint><![CDATA[OPTIMIZER_FEATURES_EN
ABLE('10.2.0.1')]]></hint><hint><![CDATA
[ALL_ROWS]]></hint><hint><![CDATA[OUTLIN
E_LEAF(@"SEL$1")]]></hint><hint><![CDATA
[FULL(@"SEL$1" "T1"@"SEL$1")]]></hint></
outline_data></other_xml>
3) dbms_xplan.display_cursor 함수 호출시 +peeked_binds 옵션을 사용한다.
2)번과 동일한 일을 하지만 훨씬 보기 쉽게 출력해 준다.
select p.plan_table_output
from (select sql_id, child_number from v$sql
where sql_text like 'select /* test */%') s,
table(dbms_xplan.display_cursor(s.sql_id, s.child_number, 'typical +peeked_binds')) p
;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 13 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :B1 (NUMBER): 1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C1"=:B1)
from (select sql_id, child_number from v$sql
where sql_text like 'select /* test */%') s,
table(dbms_xplan.display_cursor(s.sql_id, s.child_number, 'typical +peeked_binds')) p
;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 13 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :B1 (NUMBER): 1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C1"=:B1)
'Advanced Oracle' 카테고리의 다른 글
| [Oracle Virus] - 왜 Index가 불필요하게 커지는가? (1) | 2009/01/14 |
|---|---|
| Trigger와 Cursor 재활용 (0) | 2009/01/08 |
| Bind Peeking을 Disable해도 Bind Peeking이 된다! (4) | 2009/01/05 |
| View Merging이 실패하는 또 다른 원인 - Multiple Table에 대한 Outer Join (7) | 2008/12/27 |
| [Optimizing Oracle Optimizer]가 온라인 서점에서 구매 가능해졌네요. (6) | 2008/12/25 |
