Posts Tagged ‘BIND_AWARE’
How to turn on the Adaptive Cursor Sharing at statement level even though, _optim_peek_user_binds=false.
Although, the Adaptive Cursor Sharing(ACS) is a very useful feature, but, if you experiences some performance problem of bind peeking, maybe you turn off the bind peeking feature(_optim_peek_user_binds=false). In my previous post, I mentioned that if you turn off the bind peeking then the ACS won’t work.
In this case, we use OPT_PARAM and BIND_AWARE hint for using ACS at statement level.
SQL> show parameter _optim NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ _optim_peek_user_binds boolean FALSE _optimizer_adaptive_cursor_sharing boolean FALSE _optimizer_extended_cursor_sharing_r string NONE el var b1 number exec :b1:=10; select /*+BIND_AWARE opt_param('_optim_peek_user_binds','true') */ count(*), max(c1), avg(c1) from t1 where c2=:b1; COUNT(*) MAX(C1) AVG(C1) ---------- ---------- ---------- 1 10 10 select * from table(dbms_xplan.display_cursor); SQL_ID 2xsn3xundg0yz, child number 0 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | SORT AGGREGATE | | 1 | 17 | | | | 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 17 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IDX1 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- select child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware from v$sql where sql_id='2xsn3xundg0yz'; CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE ------------ ---------- ----------- ----------------- ------------- 0 1 3 Y Y exec :b1:=1001; select /*+BIND_AWARE opt_param('_optim_peek_user_binds','true') */ count(*), max(c1), avg(c1) from t1 where c2=:b1; COUNT(*) MAX(C1) AVG(C1) ---------- ---------- ---------- 99000 100000 50500.5 SQL_ID 2xsn3xundg0yz, child number 1 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 4134 (100)| | | 1 | SORT AGGREGATE | | 1 | 17 | | | |* 2 | TABLE ACCESS FULL| T1 | 99016 | 1643K| 4134 (1)| 00:00:50 | --------------------------------------------------------------------------- select child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware from v$sql where sql_id='2xsn3xundg0yz'; CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE ------------ ---------- ----------- ----------------- ------------- 0 1 3 Y Y 1 1 14593 Y Y
The behavior of BIND_AWARE hint.
Oracle supports BIND_AWARE and NO_BIND_AWARE hints that could control the Adaptive Cursor Sharing. From the name of each hint, We could expect the behavior. The test of NO_BIND_AWARE hint is unnecessary. That hint is so clear. But, BIND_AWARE hint isn’t clear to me. So let’s try to test.
BIND_AWARE hint.
For the test, the disable parameter value would be needed.
alter session set "_optimizer_adaptive_cursor_sharing"=false; alter session set "_optimizer_extended_cursor_sharing_rel"=false;
Next, execute query using hint. The test environment is all same as previous post.
From the test result, we know that BIND_WARE hint works well regardless of the parameter value.
var b1 number exec :b1:=10; select /*+BIND_AWARE*/ count(*), max(c1) from t1 where c2=:b1; COUNT(*) MAX(C1) ---------- ---------- 1 10 select * from table(dbms_xplan.display_cursor); SQL_ID 9avbm7xarm430, child number 0 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | SORT AGGREGATE | | 1 | 17 | | | | 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 17 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IDX1 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- select child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware from v$sql where sql_id='9avbm7xarm430'; CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE ------------ ---------- ----------- ----------------- ------------- 0 1 3 Y Y exec :b1:=1001; select /*+BIND_AWARE*/ count(*), max(c1) from t1 where c2=:b1; COUNT(*) MAX(C1) ---------- ---------- 99000 100000 SQL_ID 9avbm7xarm430, child number 1 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 4134 (100)| | | 1 | SORT AGGREGATE | | 1 | 17 | | | |* 2 | TABLE ACCESS FULL| T1 | 99016 | 1643K| 4134 (1)| 00:00:50 | --------------------------------------------------------------------------- select child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware from v$sql where sql_id='9avbm7xarm430'; CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE ------------ ---------- ----------- ----------------- ------------- 0 1 3 Y Y 1 1 14593 Y Y
After the test, I wonder that BIND_AWARE hint can even work, even if “_optim_peek_user_binds”=false? As expected, if “_optim_peek_user_binds”=false then ACS can’t work. Because, if you turn off the feature of bind peeking then the oracle doesn’t peek any bind variable.
alter session set "_optim_peek_user_binds"=false;
var b1 number exec :b1:=10; select /*+BIND_AWARE*/ count(*), max(c1), min(c1) from t1 where c2=:b1; COUNT(*) MAX(C1) MIN(C1) ---------- ---------- ---------- 1 10 10 select * from table(dbms_xplan.display_cursor); SQL_ID 1wx1pmnfj5ajp, child number 0 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 16 (100)| | | 1 | SORT AGGREGATE | | 1 | 17 | | | | 2 | TABLE ACCESS BY INDEX ROWID| T1 | 100 | 1700 | 16 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IDX1 | 100 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- select child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware from v$sql where sql_id='1wx1pmnfj5ajp'; CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE ------------ ---------- ----------- ----------------- ------------- 0 1 3 N N