02 July, 2008

Bind Variable Peeking

Here are two simple test cases which demonstrate how Bind Variable Peeking can cause the wrong execution plan to be used.
In this table TEST_BINDVAR_PEEKING, I have only 3 values for COUNTRY_OF_BIRTH but 'IN' accounts for 10 of every 12 rows. Therefore, with a Histogram on COUNTRY_OF_BIRTH, a query for 'IN' should use a FullTableScan while a query for 'FR' should use an IndexRangeScan.

In the first set of tests, I run a query with a bind variable. At the first, execution, the bind variable is set to 'FR' so Oracle, "peeking" into the value, selects an IndexRangeScan. However, at the next execution, the value is actually 'IN' but Oracle sticks to the old execution plan (ie, it doesn't "peek" at the value at the second execution of the same SQL). Therefore, it uses a a poor execution plan for 'IN', resulting in higher Buffer Gets.

I can force it to "re-peek" at the Bind Variable value if I execute, typically, an ANALYZE or DBMS_STATS on the table. However, what I really need is *any* DDL that will invalidate the existant SQL statement. Since my statistics are actually correct, I do not need to waste time or resources on an ANALYZE or DBMS_STATS again, but just force an Invalidation by using the "COMMENT ON TABLE" statement which is a DDL !
A simple (and elegant, you say ?) solution, without the cost of ANALYZE / DBMS_STATS !
{Of course, if my Statisticswere really incorrect, I would have to re-gather Statistics}.

In the second set of tests, I extend the query to two tables. Again, I get a poor execution plan on the larger table. However, now I can execute DDL on even the other (smaller) table to force an Invalidation and a "re-peek" ! This is a strategy I could use when I know other SQLs current in the Shared Pool are correctly executing against TEST_BINDVAR_PEEKING and I do not want to invalidate all those other SQLs. I can choose to invalidate all SQLs using COUNTRY_MASTER and yet achieve my objective of forcing a re-peek on the Bind Variable against TEST_BINDVAR_PEEKING !




drop table country_master ;
create table country_master as select distinct country_of_birth from test_cardinality;
create index country_master_ndx on country_master(country_of_birth) ;

drop table test_bindvar_peeking ;
create table test_bindvar_peeking as select * from test_cardinality where 1=2;
alter table test_bindvar_peeking nologging;

REM Can you guess why I have an ORDER BY COUNTRY_OF_BIRTH for the first two inserts ?
rem Hint : It helped me "tweak" the data !
rem In what way ? Post your answers as comments to this blog posting
insert /*+ APPEND */ into test_bindvar_peeking select * from test_cardinality order by country_of_birth;
commit;
insert /*+ APPEND */ into test_bindvar_peeking select * from test_cardinality order by country_of_birth;
commit;
insert /*+ APPEND */ into test_bindvar_peeking select * from test_bindvar_peeking ;
commit;
insert /*+ APPEND */ into test_bindvar_peeking select * from test_bindvar_peeking ;
commit;
insert /*+ APPEND */ into test_bindvar_peeking select * from test_bindvar_peeking ;
commit;
insert /*+ APPEND */ into test_bindvar_peeking select * from test_bindvar_peeking ;
commit;
insert /*+ APPEND */ into test_bindvar_peeking select * from test_bindvar_peeking ;
commit;
insert /*+ APPEND */ into test_bindvar_peeking select * from test_bindvar_peeking ;
commit;
insert /*+ APPEND */ into test_bindvar_peeking select * from test_bindvar_peeking ;
commit;
insert /*+ APPEND */ into test_bindvar_peeking select * from test_bindvar_peeking ;
commit;


create index test_bindvar_peeking_cob_ndx on test_bindvar_peeking (country_of_birth);

exec dbms_stats.gather_table_stats(user,tabname=>'COUNTRY_MASTER',method_opt=>'FOR ALL COLUMNS SIZE 250',estimate_percent=>100,cascade=>TRUE);
exec dbms_stats.gather_table_stats(user,tabname=>'TEST_BINDVAR_PEEKING',method_opt=>'FOR ALL COLUMNS SIZE 250',estimate_percent=>100,cascade=>TRUE);

REM Verify the number of records
select country_of_birth from country_master order by 1;

COU
---
FR
IN
UK
select country_of_birth, count(*) from test_bindvar_peeking group by country_of_birth order by 2;

COU COUNT(*)
--- ----------
FR 65536
UK 65536
IN 655360


REM Verify the presence of a Frequency Histogram on COUNTRY_OF_BIRTH
select endpoint_value, endpoint_number
2 from user_tab_histograms
3 where table_name = 'TEST_BINDVAR_PEEKING' and column_name = 'COUNTRY_OF_BIRTH'
4 order by 2;

ENDPOINT_VALUE ENDPOINT_NUMBER
-------------- ---------------
3.6512E+35 65536
3.8062E+35 720896
4.4287E+35 786432

REM Verify the execution plans and Buffer Gets for 'FR' and 'IN'
set autotrace on
select city_of_birth, count(*) from test_bindvar_peeking where country_of_birth='FR' group by city_of_birth;

CITY_OF_BIRTH COUNT(*)
------------------ ----------
PARIS 65536

Execution Plan
----------------------------------------------------------
Plan hash value: 3593298901

-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 132 | 570 (2)| 00:00:07 |
| 1 | HASH GROUP BY | | 12 | 132 | 570 (2)| 00:00:07 |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST_BINDVAR_PEEKING | 65536 | 704K| 566 (1)| 00:00:07 |
|* 3 | INDEX RANGE SCAN | TEST_BINDVAR_PEEKING_COB_NDX | 65536 | | 131 (1)| 00:00:02 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("COUNTRY_OF_BIRTH"='FR')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
936 consistent gets
0 physical reads
432 redo size
594 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

select city_of_birth, count(*) from test_bindvar_peeking where country_of_birth='IN' group by city_of_birth;

CITY_OF_BIRTH COUNT(*)
------------------ ----------
KOLHAPUR 65536
SOLAPUR 65536
DELHI 65536
CALCUTTA 65536
NAGPUR 65536
JABALPUR 65536
BADLAPUR 65536
MADRAS 65536
KANPUR 65536
BOMBAY 65536

Execution Plan
----------------------------------------------------------
Plan hash value: 2629260985

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 132 | 877 (7)| 00:00:11 |
| 1 | HASH GROUP BY | | 12 | 132 | 877 (7)| 00:00:11 |
|* 2 | TABLE ACCESS FULL| TEST_BINDVAR_PEEKING | 655K| 7040K| 838 (2)| 00:00:11 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("COUNTRY_OF_BIRTH"='IN')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3694 consistent gets
0 physical reads
504 redo size
758 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

set autotrace off

REM REM REM ##############################################################################################

REM Define the Bind Variables
variable bv varchar2(5) ;
variable bv2 varchar2(5) ;

REM set the value to 'FR'
exec :bv := 'FR';

select city_of_birth, count(*) from test_bindvar_peeking where country_of_birth = :bv group by city_of_birth;

CITY_OF_BIRTH COUNT(*)
------------------ ----------
PARIS 65536

select sql_id, child_number, plan_hash_value, sql_text,invalidations,executions,buffer_gets from v$sql
2 where sql_id in ('1grp430nhcxp7') order by sql_id, child_number;

SQL_ID CHILD_NUMBER PLAN_HASH_VALUE
------------- ------------ ---------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
INVALIDATIONS EXECUTIONS BUFFER_GETS
------------- ---------- -----------
1grp430nhcxp7 0 3593298901
select city_of_birth, count(*) from test_bindvar_peeking where country_of_birth = :bv group by city_of_birth
0 1 936



REM now, change the value to 'IN'. We know, from Histograms and the autotrace, that a different execution plan should be expected
exec :bv := 'IN';

select city_of_birth, count(*) from test_bindvar_peeking where country_of_birth = :bv group by city_of_birth;

CITY_OF_BIRTH COUNT(*)
------------------ ----------
KOLHAPUR 65536
SOLAPUR 65536
NAGPUR 65536
DELHI 65536
CALCUTTA 65536
JABALPUR 65536
BADLAPUR 65536
MADRAS 65536
KANPUR 65536
BOMBAY 65536

select sql_id, child_number, plan_hash_value, sql_text,invalidations,executions,buffer_gets from v$sql
2 where sql_id in ('1grp430nhcxp7') order by sql_id, child_number;

SQL_ID CHILD_NUMBER PLAN_HASH_VALUE
------------- ------------ ---------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
INVALIDATIONS EXECUTIONS BUFFER_GETS
------------- ---------- -----------
1grp430nhcxp7 0 3593298901
select city_of_birth, count(*) from test_bindvar_peeking where country_of_birth = :bv group by city_of_birth
0 2 5786


REM SURPRISED ?? Oracle used the *same* execution plan for 'IN' as it used for 'FR'
rem The 'PLAN_HASH_VALUE' didn't change ; Invalidations didn't increase
rem but Executions did increase and incremental Buffer_Gets is very high
rem Oracle had done Bind Variable Peeking the first time to read the 'FR' but did not do so again to read the 'IN' !


REM How do we force Bind Variable Peeking ? A Gather_Stats or Analyze OR *any* DDL will do the job !
REM A "COMMENT ON TABLE" is a DDL command that will Invalidate the SQL and cause it to be re-parsed at the next execution
comment on table test_bindvar_peeking is '';

exec :bv := 'IN';

select city_of_birth, count(*) from test_bindvar_peeking where country_of_birth = :bv group by city_of_birth;

CITY_OF_BIRTH COUNT(*)
------------------ ----------
KOLHAPUR 65536
SOLAPUR 65536
DELHI 65536
CALCUTTA 65536
NAGPUR 65536
JABALPUR 65536
BADLAPUR 65536
MADRAS 65536
KANPUR 65536
BOMBAY 65536

select sql_id, child_number, plan_hash_value, sql_text,invalidations,executions,buffer_gets from v$sql
2 where sql_id in ('1grp430nhcxp7') order by sql_id, child_number;

SQL_ID CHILD_NUMBER PLAN_HASH_VALUE
------------- ------------ ---------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
INVALIDATIONS EXECUTIONS BUFFER_GETS
------------- ---------- -----------
1grp430nhcxp7 0 2629260985
select city_of_birth, count(*) from test_bindvar_peeking where country_of_birth = :bv group by city_of_birth
1 1 3694

REM See that the PLAN_HASH_VALUE has changed, Invalidations has been incremented ! Different Execution Plan used this time
REM What caused it ? The "COMMENT" was DDL which Invalidated the query


REM REM ####################################################################################################
REM What if there are two tables in the query ?

exec :bv2 := 'FR';
select city_of_birth, count(*) from country_master cm, test_bindvar_peeking tbp
2 where cm.country_of_birth = tbp.country_of_birth and tbp.country_of_birth = :bv2 group by city_of_birth;

CITY_OF_BIRTH COUNT(*)
------------------ ----------
PARIS 65536

select sql_id, child_number, plan_hash_value, sql_text,invalidations,executions,buffer_gets from v$sql
2 where sql_id in ('732a37npkf6zd') order by sql_id, child_number;

SQL_ID CHILD_NUMBER PLAN_HASH_VALUE
------------- ------------ ---------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
INVALIDATIONS EXECUTIONS BUFFER_GETS
------------- ---------- -----------
732a37npkf6zd 0 1488351023
select city_of_birth, count(*) from country_master cm, test_bindvar_peeking tbp where cm.country_of_birth = tbp.country_of_birth and
tbp.country_of_birth = :bv2 group by city_of_birth
0 1 936



exec :bv2 := 'IN';
select city_of_birth, count(*) from country_master cm, test_bindvar_peeking tbp
2 where cm.country_of_birth = tbp.country_of_birth and tbp.country_of_birth = :bv2 group by city_of_birth;

CITY_OF_BIRTH COUNT(*)
------------------ ----------
KOLHAPUR 65536
SOLAPUR 65536
NAGPUR 65536
DELHI 65536
CALCUTTA 65536
JABALPUR 65536
BADLAPUR 65536
MADRAS 65536
KANPUR 65536
BOMBAY 65536

select sql_id, child_number, plan_hash_value, sql_text,invalidations,executions,buffer_gets from v$sql
2 where sql_id in ('732a37npkf6zd') order by sql_id, child_number;

SQL_ID CHILD_NUMBER PLAN_HASH_VALUE
------------- ------------ ---------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
INVALIDATIONS EXECUTIONS BUFFER_GETS
------------- ---------- -----------
732a37npkf6zd 0 1488351023
select city_of_birth, count(*) from country_master cm, test_bindvar_peeking tbp where cm.country_of_birth = tbp.country_of_birth and
tbp.country_of_birth = :bv2 group by city_of_birth
0 2 5787

REM We see the same execution plan as for 'FR'


REM This comment on the second table in the query should help
comment on table country_master is '';

exec :bv2 := 'IN';
select city_of_birth, count(*) from country_master cm, test_bindvar_peeking tbp
2 where cm.country_of_birth = tbp.country_of_birth and tbp.country_of_birth = :bv2 group by city_of_birth;

CITY_OF_BIRTH COUNT(*)
------------------ ----------
KOLHAPUR 65536
SOLAPUR 65536
DELHI 65536
CALCUTTA 65536
NAGPUR 65536
JABALPUR 65536
BADLAPUR 65536
MADRAS 65536
KANPUR 65536
BOMBAY 65536

select sql_id, child_number, plan_hash_value, sql_text,invalidations,executions,buffer_gets from v$sql
2 where sql_id in ('732a37npkf6zd') order by sql_id, child_number;

SQL_ID CHILD_NUMBER PLAN_HASH_VALUE
------------- ------------ ---------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
INVALIDATIONS EXECUTIONS BUFFER_GETS
------------- ---------- -----------
732a37npkf6zd 0 598869587
select city_of_birth, count(*) from country_master cm, test_bindvar_peeking tbp where cm.country_of_birth = tbp.country_of_birth and
tbp.country_of_birth = :bv2 group by city_of_birth
1 1 3695

REM Yes, the execution plan has changed, even if my DDL was on the smaller (smallest) table in the query




8 comments:

Anonymous said...

Hi Hemant,
Nice Article. I've read about Bind variables on Asktom.com as well.
Can you tell me why Bind variables are not good on OLTP and are good on DSS?

Regards!
Nitin.

Hemant K Chitale said...

You've understood it the wrong way round.
Bind Variables are preferred in OLTP where very many very similar SQLs are executed. If the SQLs use Literals, Oracle spends that much more time re-parsing statements. Thus two SQL statements which vary by only literals become two Hard Parses. In a busy environment you could end up with dozens or hundreds of similar statements all clogging the shared pool and taking CPU cycles to parse.

Conversely, in a DSS system, the number of SQL statements and executions is not the issue. With bind variables, Oracle (pre-9i) couldn't properly estimate the cardinality of rows for each predicate and that can make a signficant difference to statement execution. Bind Variable Peeking in 9i still makes things bad because Oracle might use the wrong execution plan. For example, the first execution of a statement with Bind Variables may be for values with low cardinality (few rows) and Oracle would choose an Index Range Scan. If subsequent executions are for values with high row counts, Oracle will not re-peek the value (it should have switched to a FullTableScan if it did) but may continue to use, what is now the very wrong, Index Range Scan.

At a very simple level :
DSS/DWH environments prefer Literals. OLTP environments prefer BindVariables.

Anonymous said...

Hi,
Thanks for the Reply.
Yes i actually wrote it wrong.
anyway got the answer.Thanks.
In Oracle 11G ,i think they have improved on "bind varible peeking".
it is something like "adaptive cursor sharing".
http://optimizermagic.blogspot.com/2007/12/why-are-there-more-cursors-in-11g-for.html
What you say about this?

Regards!

Nitin.

Hemant K Chitale said...

Yes, I'd heard about adaptive cursor sharing but haven't spent much time reading about it. Thanks for the link.

Pollocks said...

Good tips.

Another approach, which I used recently was to change the SQL each time, forcing that hard parse which caused "re-peeking".

I was in an Oracle Application Express environment and so was able to use:

select /*+ &APP_UNIQUE_PAGE_ID. */ from whereever;

In a different env, you may be able to set a different comment each time via a different approach.

That's my 2c.

-Pollocks01

Unknown said...

Hi Hemant,

Nice post could you please provide the script to populate the tables so that it can be tested.

Regards,
Shadab

Unknown said...

Hi Hemant,

Nice post could you update the script to populate the tables to that it can be tested.

Regards,
shadab

Hemant K Chitale said...

Shadab,
You can see that country_master and test_bindvar_peeking are from this post itself. test_cardinality is from a post in the previous month. http://hemantoracledba.blogspot.sg/2008/06/cardinality-estimates-dependent-columns.html