A recent post in the OTN mentioned that DBMS_SQL does not use bind peeking for binded variables. I couldn’t believe that, so I decided to do some tests for myself. The findings are strange…
This is potentially relevant for APEX developers, since the APEX engine uses DBMS_SQL. I still have to do further testing to check the behaviour in APEX.
First I setup some test to show bind peeking and adaptive cursor behaviour using normal statements in SQL*Plus or SQL Developer. After that we move to dynamic SQL, especially DBMS_SQL, and try the same again.
scenario setup
create skewed testdata
--drop table demo_big;
create table demo_big as
select level as id,
case when mod(level,10000)=0
then 'VALID'
else 'INVALID'
end as status
from dual
connect by level <= 1000000;
desc demo_big;
Name Null Type
------ ---- -----------
ID NUMBER
STATUS VARCHAR2(7)
select status, count(*)
from demo_big
group by rollup(status);
STATUS COUNT(*)
INVALID 999900
VALID 100
1000000
So we have a few VALID values and a lot of INVALID ones.
Even if we have only two different values an index will be useful on this column. The data distribution is so skewed that any access trying to read the VALID values would profit from an index. However if we access the INVALID column we don’t want to use the index and instead want a full table scan.
-- create indexes on all the important columns create unique index demo_big_id_ix on demo_big(id); create index demo_big_status_ix on demo_big(status);
create statistical data(histograms)
First we create the statistics so that the optimizers knows what is in that table and how the data looks like.
-- create statistics and test histogram execute dbms_stats.gather_table_stats(user, 'DEMO_BIG', method_opt=>'for all indexed columns size skewonly');
Then we check the data dictionary checks to see what has been created so far.
The hist_numtochar2 function is copied from Martin Widlake (Source: https://mwidlake.wordpress.com/2009/08/11/). It just helps to do a crude translation of the numerical histogram bucket endpoints. The code of the function can be found at the end of this post.
I don’t show the results from all selects but the last one. The other selects are here just as references. They are helpful to see what kind of statistics are in place.
select table_name, num_rows, blocks, last_analyzed from user_tables where table_name = 'DEMO_BIG'; select table_name, column_name, num_distinct, histogram, num_buckets, sample_size from user_tab_columns where table_name = 'DEMO_BIG'; select * from user_histograms where table_name = 'DEMO_BIG' and column_name = 'STATUS'; select table_name, column_name, endpoint_number, endpoint_value, hist_numtochar2(endpoint_value) as translated_value from user_histograms where table_name = 'DEMO_BIG' and column_name = 'STATUS';
Here we see a frequency histogram with two buckets for the column STATUS.
TABLE COLUMN ENDPOINT_NUMBER ENDPOINT_VALUE TRANSLATED_VALUE DEMO_BIG STATUS 999900 380626532452853000000000000000000000 INVALJ* DEMO_BIG STATUS 1000000 447861930473196000000000000000000000 VALID
The first bucket holds 999900 values where status= INVALID.
The next bucket holds 1000000-999900 = 100 where status = VALID.
This of cause matches exactly what we created. So the statistical info in the dictionary is absolutly correct.
Tests
Now that our setup is in place, we can do some basic testing to see different plans.
check execution plan with LITERALS
-- test different cursor/execution plan using plain selects select count(*) from demo_big where status = 'VALID'; select * from table(dbms_xplan.display_cursor);
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | INDEX RANGE SCAN| DEMO_BIG_STATUS_IX | 100 | 800 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STATUS"='VALID')
select count(*) from demo_big where status = 'INVALID'; select * from table(dbms_xplan.display_cursor);
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 701 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | TABLE ACCESS FULL| DEMO_BIG | 999K| 7811K| 701 (2)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("STATUS"='INVALID')
Perfect! As expected one does an index access / index range scan, the other does a full table scan.
check execution plan with BIND parameters
select count(*) from demo_big where status = :P_ENTER_VALID; select * from table(dbms_xplan.display_cursor);
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | INDEX RANGE SCAN| DEMO_BIG_STATUS_IX | 100 | 800 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STATUS"=:P_ENTER_VALID)
select count(*) from demo_big where status = :P_ENTER_INVALID; select * from table(dbms_xplan.display_cursor);
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 701 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | TABLE ACCESS FULL| DEMO_BIG | 999K| 7811K| 701 (2)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("STATUS"=:P_ENTER_INVALID)
The two statements are not identical because the name of the bind parameter is different. Because of that we get two different cursors. Each with a different execution plan.
This test shows that bind peeking works. During the hard parse phase the value of the binded parameter was checked (peeked) so that the correct estimations for the resulting rows/cardinalities were made. Which led in turn to the correct plan for each of the two different statements. However this first parameter “freezes” the execution plan. So that if we change the binded value, then the same plan is reused.
This behaviour was enhanced in 11g with the introduction of adaptive cursor sharing and got steadily improved since then.
To test adaptive behaviour we run the first query again a few times (at least 4 times). But this time we do not pass VALID, but instead INVALID as a parameter.
After that we can see a new child cursor 1 for the sql_id “7rjdcm7v7hfrs”.
select is_bind_sensitive, is_bind_aware, sql_id, child_number, sql_text from v$sql where upper(sql_text) like 'SELECT%FROM DEMO_BIG WHERE%' and sql_text not like '%v$sql%' ;
IS_BIND IS_BIND SQL_ID CHILD SQL_TEXT _SENSITIVE _AWARE _NUMBER Y N 7rjdcm7v7hfrs 0 select count(*) from demo_big where status = :P_ENTER_VALID Y Y 7rjdcm7v7hfrs 1 select count(*) from demo_big where status = :P_ENTER_VALID Y N 5zkmtfj331xmc 0 select count(*) from demo_big where status = :P_ENTER_INVALID
select * from table(dbms_xplan.display_cursor('7rjdcm7v7hfrs',0));
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | INDEX RANGE SCAN| DEMO_BIG_STATUS_IX | 100 | 800 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STATUS"=:P_ENTER_VALID)
select * from table(dbms_xplan.display_cursor('7rjdcm7v7hfrs',1));
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 701 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | TABLE ACCESS FULL| DEMO_BIG | 999K| 7811K| 701 (2)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("STATUS"=:P_ENTER_VALID)
This is adaptive behaviour. After a few bad tries a second execution plan is created for the same cursor and used. How many tries are needed? Often it changes on the third try. But it can happen that more are needed.
Test with DBMS_SQL
Now comes the more difficult part. Setup a small plsql block to use DBMS_SQL to run the same statement again using binded parameters.
-- testcase for BIND peeking/aware using DBMS_SQL declare curid NUMBER; ret INTEGER; sql_stmt VARCHAR2(200); begin sql_stmt := 'select count(*) from demo_big where status = :P_STATUS'; -- get cursor handle curid := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(curid, sql_stmt, DBMS_SQL.NATIVE); DBMS_SQL.BIND_VARIABLE(curid, 'P_STATUS', 'VALID'); ret := DBMS_SQL.EXECUTE_and_fetch(curid); DBMS_SQL.PARSE(curid, sql_stmt, DBMS_SQL.NATIVE); DBMS_SQL.BIND_VARIABLE(curid, 'P_STATUS', 'INVALID'); for i in 1..5 loop ret := DBMS_SQL.EXECUTE_and_fetch(curid); end loop; DBMS_SQL.close_cursor(curid); end; /
The v$sql view has two interesting columns.
IS_BIND_SENSITIVE shows cursors where the execution plan can evolve.
IS_BIND_AWARE shows child cursors where a new plan was created, meaning that the cursor was evolved.
select is_bind_sensitive, is_bind_aware, sql_id, child_number, sql_text from v$sql where upper(sql_text) like 'SELECT%FROM DEMO_BIG WHERE%' and sql_text not like '%v$sql%' ;
IS_BIND_SENSITIVE IS_BIND_AWARE SQL_ID CHILD_NUMBER SQL_TEXT Y N 7rjdcm7v7hfrs 0 select count(*) from demo_big where status = :P_ENTER_VALID Y Y 7rjdcm7v7hfrs 1 select count(*) from demo_big where status = :P_ENTER_VALID N N 3kpu54a461gkm 0 select count(*) from demo_big where status = :P_STATUS N N 3kpu54a461gkm 1 select count(*) from demo_big where status = :P_STATUS Y N 5zkmtfj331xmc 0 select count(*) from demo_big where status = :P_ENTER_INVALID N N fjjm63y7c6puq 0 select count(*) from demo_big where status = :P_STATUS2 N N 1qx03gdh8712m 0 select count(*) from demo_big where status = 'INVALID' N N 2jm3371mug58t 0 select count(*) from demo_big where status = 'VALID'
The two child cursors
-- find the cursor id
select sql_id, child_number, bucket_id, count, is_bind_sensitive, is_bind_aware, sql_text
from v$sql s
left join v$sql_cs_histogram h using (sql_id, child_number)
where upper(s.sql_text) like 'SELECT%FROM DEMO_BIG WHERE%'
and s.sql_text not like '%v$sql%'
;
-- check the execution plan for both child cursors
select * from table(dbms_xplan.display_cursor('3kpu54a461gkm',0));
select * from table(dbms_xplan.display_cursor('3kpu54a461gkm',1));
-- see the plans in the SGA
select * from v$sql_plan where sql_id = '3kpu54a461gkm';
select * from v$sql_plan where sql_id = 'fjjm63y7c6puq';
Now the strange thing is: The first cursor is using a FULL table scan. But the first execution was done using the VALID value and should have resulted in the index range scan. The second child cursor does not even have an execution plan!
NOTE: cannot fetch plan for SQL_ID: 3kpu54a461gkm, CHILD_NUMBER: 1
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)
What is going on here? v$sql has a column EXECUTIONS which tells us how often this child cursor was called. It is always 0 for the child 1 from the DBMS_SQL cursor!
I did several more tests using DBMS_SQL. Even a case where the cursor was closed and opened several times. All with the same result.
Interpreting the results
I’m still not yet exactly sure what is going on there. It seems as if bind peeking and adaptive cursor sharing does not work with DBMS_SQL. But why do we see then two child cursors? It seems as if the different parameter values at least have the effect that a new child is created. And this happens only when there is a need for a different execution plan. But where is the plan for that? I still have some doubts. Maybe the execution plan in v$sql is lying is this case? Since DBMS_SQL goes deep into the internals it might be that some of the normal behaviours are not reflected in some of the views.
The cursor itself is in the private SQL workarea and I never checked that. Another approach would be to setup a scenario where we can measure the performance difference. The test case I used was too small to see a desicive difference between the two possible plans.
Also we have to remember that the need for DBMS_SQL is rare. A normal select with binded parameters is certainly not a case where need dynamic SQL. A more typical case would be a cursor | statement where we do not know at compile time what columns are returned. Then we can use DBMS_SQL to analyse the structure of such a cursor and react on that.
However if we build some kind of dynamic frameworks and think about using DBMS_SQL we should rethink our strategy. Maybe it is easier to provide all the possible cases as plsql apis and thereby compiling during creation, instead of building the statement in a completly dynamic fashion but suffering some essential drawbacks.
Recommendations
1) Avoid DBMS_SQL, consider to use native SQL (execute_immediate) instead
2) If you have a skewed data distribution, make sure your plans are bind_sensitive
3) If you can guarantee an even data distribution, consider to add the NO_BIND_AWARE hint. This should be needed only in some extrem situations (very high performance requirements or cursor cache issues)
Appendix
The function that I used previously:
create or replace function hist_numtochar2(p_num number
,p_trunc varchar2 :='Y') return varchar2
-- Author: Martin Widlake
-- Source: https://mwidlake.wordpress.com/2009/08/11/
is
m_vc varchar2(15);
m_n number :=0;
m_n1 number;
m_loop number :=7;
begin
m_n :=p_num;
if length(to_char(m_n))>36 then
--dbms_output.put_line ('input too short');
m_vc:='num format err';
else
if p_trunc !='Y' then
m_loop :=15;
else
m_n:=m_n+power(256,9);
end if;
--dbms_output.put_line(to_char(m_N,'999,999,999,999,999,999,999,999,999,999,999,999'));
for i in 1..m_loop loop
m_n1:=trunc(m_n/(power(256,15-i)));
-- dbms_output.put_line(to_char(m_n1));
if m_n1!=0 then m_vc:=m_vc||chr(m_n1);
end if;
dbms_output.put_line(m_vc);
m_n:=m_n-(m_n1*power(256,15-i));
end loop;
end if;
return m_vc;
end;
/
svenweller 