how your response answered the q on bind variable
peeking concept from JDBC?

Please keep up the excellent work on your site!

So the bind variable peeking (and Oracle parsing) occurs at the time of the first execute, not at the time at the Java PreparedStatement object is created. Each subsequent execution of the same PreparedStatement object is not parsed and has the same plan as was generated from the first execution (and the values of the bind variables at that point).

Thanks,
Jeff

Does bind peeking work with thin driver?

I see that one statspack show parse>execute.
I know it means sql is parsed but not executed.I just want to know how it archive the effect(parse>execute) using jdbc?
Counld you show me an example?

Thanks very much!

Looks like that the thin drivers don't peek (9.2.0.6); sorry if you already know, but this is in contrast to what you say above on June 08, 2004 if the test case is correct ...

dellera@ORACLE9I> create table t as
2 select decode(rownum,1,1,2) x, rpad('x',100) padding
3 from dual connect by level <= 1000;

Table created.

dellera@ORACLE9I> create index t_idx on t(x);

Index created.

dellera@ORACLE9I> exec dbms_stats.gather_table_stats (user, 't', cascade=>true, method_opt =>'for columns x size skewonly');

PL/SQL procedure successfully completed.

dellera@ORACLE9I> alter system flush shared_pool;

System altered.

dellera@ORACLE9I> variable x number
dellera@ORACLE9I> exec :x := 1;

PL/SQL procedure successfully completed.

dellera@ORACLE9I> select count(padding) from t sqlplus_peek_1 where x = :x;

COUNT(PADDING)
--------------
1

dellera@ORACLE9I>
dellera@ORACLE9I> exec :x := 2;

PL/SQL procedure successfully completed.

dellera@ORACLE9I> select count(padding) from t sqlplus_peek_2 where x = :x;

COUNT(PADDING)
--------------
999

From v$sql_plan, this confirms that bind variables peeking makes a difference:

======================================================================
module: SQL*Plus, dump_date: 2005/09/24 21:39:14

select count(padding) from t sqlplus_peek_1 where x = :x

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 |
| 1 | SORT AGGREGATE | | 1 | 55 | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 55 | 2 |
|* 3 | INDEX RANGE SCAN | T_IDX | 1 | | 1 |
----------------------------------------------------------------------------

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

3 - access("X"=:X)

=====================================================================
module: SQL*Plus, dump_date: 2005/09/24 21:39:14

select count(padding) from t sqlplus_peek_2 where x = :x

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 |
| 1 | SORT AGGREGATE | | 1 | 55 | |
|* 2 | TABLE ACCESS FULL | T | 999 | 54945 | 4 |
--------------------------------------------------------------------

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

2 - filter("X"=:X)

Now, let's use the thin drivers:

import java.sql.*;

public class Test {
public static void main(String args[])throws Exception
{
DriverManager.registerDriver
(new oracle.jdbc.driver.OracleDriver());

System.out.println(args[0]);
Connection conn=DriverManager.getConnection (args[0], "dellera", "dellera");

conn.setAutoCommit(false);
Statement stmt = conn.createStatement ();
stmt.execute( "alter system flush shared_pool" );


PreparedStatement pstat =
conn.prepareStatement
("select /*+ "+args[0]+"*/ count(padding) from t javathinpeek where x = ?" );
pstat.setInt ( 1, 1 );
ResultSet rset = pstat.executeQuery();
}
}

$ echo $CLASSPATH
.;/cygdrive/c/jdk1.3.1_12\lib;C:\oracle\ora92\jdbc\lib\classes12.jar

$ java Test "jdbc:oracle:thin:@localhost:1521:oracle9i"

======================================================================
module: JDBC Thin Client, dump_date: 2005/09/24 21:45:50

select /*+ jdbc:oracle:thin:@localhost:1521:oracle9i*/ count(padding)
from t javathinpeek where x = :1

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 |
| 1 | SORT AGGREGATE | | 1 | 55 | |
|* 2 | TABLE ACCESS FULL | T | 500 | 27500 | 4 |
--------------------------------------------------------------------

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

2 - filter("X"=:1)

This is tracked by note 273635.1, but it wasn't clear if this
was a general issue since the linked bug 3037615 is only about
setTimestamp() - now it seems general ...

(seen today on Tim Hall's blog - credit for pointing out the note goes to "Austin", demerit for the test case to me).

concurr that my example shows it had the potential to peek, but didn't actually show peeking.

With the 10g thin (10gr1 thin) however:

drop table t;
create table t
as
select 99 id, a.* from all_objects a;
update t set id = 1 where rownum = 1;
create index t_idx on t(id);
exec dbms_stats.gather_table_stats( user, 'T', method_opt=>'for all indexed columns size 254', cascade=>true );


and:

import java.sql.*;
                                                                                                                                         
public class test {
public static void main(String args[])throws Exception
{
    DriverManager.registerDriver
       (new oracle.jdbc.driver.OracleDriver());
                                                                                                                                         
    System.out.println(args[0]);
    Connection conn=DriverManager.getConnection (args[0], "ops$tkyte", "foobar");
                                                                                                                                         
    conn.setAutoCommit(false);
    Statement stmt = conn.createStatement ();
    stmt.execute( "alter system flush shared_pool" );
    stmt.execute( "alter session set sql_trace=true" );
                                                                                                                                         
                                                                                                                                         
    PreparedStatement pstat =
    conn.prepareStatement
    ("select * from t x_1 where id = ?" );
    pstat.setInt ( 1, 1 );
    ResultSet rset = pstat.executeQuery();
                                                                                                                                         
    PreparedStatement pstat2 =
    conn.prepareStatement
    ("select * from t x_99 where id = ?" );
    pstat2.setInt ( 1, 99 );
    ResultSet rset2 = pstat2.executeQuery();
                                                                                                                                         
    pstat.close();
    pstat2.close();
}
}


then

$ java test "jdbc:oracle:thin:@localhost:1521:ora10g"

shows:

select *
from
 t x_1 where id = :1
                                                                                                                                         
                                                                                                                                         
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.01       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.02       0.01          0          3          0           1
                                                                                                                                         
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 170
                                                                                                                                         
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID OBJ#(80565) (cr=3 pr=0 pw=0 time=112 us)
      1   INDEX RANGE SCAN OBJ#(80566) (cr=2 pr=0 pw=0 time=67 us)(object id 80566)
                                                                                                                                         
********************************************************************************
                                                                                                                                         
select *
from
 t x_99 where id = :1
                                                                                                                                         
                                                                                                                                         
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          4          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.01       0.00          0          4          0          10
                                                                                                                                         
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 170
                                                                                                                                         
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  TABLE ACCESS FULL OBJ#(80565) (cr=0 pr=0 pw=0 time=0 us)


So that release and above does bind peek 


Thanks for the followup, always appreciate it.

 

I've re-run my test case (which is basically the same as yours) using 9i/10g thin drivers against 9i/10g database:

9.2.0.6 thin drivers against either 9.2.0.6 or 10.1.0.4: it doesn't peek
10.1.0.4 thin drivers against either 9.2.0.6 or 10.1.0.4: it does peek

So, interestingly, it's the version of the driver that counts, not of the database.

I'd like to have note 273635.1 enhanced with this additional infos (useful for when we will upgrade to 10g); is it ok to point Support to this page ?

Which is the best way - open an iTar or just email them ?

TIA

FYI - just logged iTar 4731884.992 requesting to enhance note 273635.1 "Thin JDBC Driver generates different Execution Plan". Provided the URL of the followup as test case.

Tom where is the link to that JDBC book that you like from Apress?

Hi Tom, this has been the most useful page for me ever :-)

I am hitting this exact problem, I have a query which runs fine from sqlplus but really slow from jdbc (Oracle AS 10g 9.0.4)

I used your test java program to run my query and with classes12.jar its really slow, when I use ojdbc14.jar its really quick - so bind variable peeking seems to be my problem.

Now the question is what can I do about it. I have read note 273635.1 and it had a few suggestions.

1) upgrade drivers - cant do that as they are embedded in the OAS version

2) use literals - no thanks, OLTP here and its a 3rd party app

3) change optimzer_mode to first_rows_10 for example - 3rd party app, dont really know what overall effect that will have.

Do you have any other suggestions as to what we could do. We gather stats like this

dbms_stats.gather_schema_stats(user, cascade=>true, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>'FOR ALL INDEXED COLUMNS');

every 3 hours. Any changes we can do here?

as a side note, doing
ALTER SESSION SET "_OPTIM_PEEK_USER_BINDS" = FALSE; in sqlplus makes it go slow in sqlplus so I am pretty sure it is this issue

Thanks

1) didnt know that - ill have to check with support I guess whether they will still offically support the App server if i do this

2) my pleasure

3) 9.2.0.5, 2 Node RAC, Redhat Linux 2.1, in my test java program I set it to be first_rows_10 in the session, didnt make a difference - same for first_rows_100 (the query actually returns no rows)

Thank you

As for vendor help and stored outlines

yes and yes - I read some metalink notes, can use the 10.1.0 jdbc driver (not the 10.2 one) to remain supported, some inter dependancies somewhere - note 262550.1 for reference there.

I guess there are no 'quick fixes' here in terms of different way to generate stats so will go down the stored outline / upgrading the drivers route.

Thanks

Hi Tom,

Commenting about your answer to:
"mixed versions results" from "Alberto Dell'Era"

We have seen this behaviour too, both with thin and OCI, when upgrading from 9i jdbc driver to 10g jdbc driver (connected to a 9i database). The upgrade to the 10g driver is required because only the 10g driver is certified for JDK 1.5

I can understand the change in the way the 10g jdbc driver work as I suppose it's an optimization for the 10g database, but why the driver doesn't react differently based on the underlying database it's connected to ?

In our case, it's a problem because now we are getting poor performances on some queries where it doesn't use the same execution plan with the 10g driver as with the 9i driver. The only way we have found to solve this problem is by using the (hidden) parameter ALTER SESSION SET "_optim_peek_user_binds"=FALSE with the 10g driver.

Could you comment on this solution?

Thank you very much.
Jean-Pol.

Hi Tom,

Yes, you are perfectly right. I have asked our DBAs to check and we have histograms for all our tables in the application. They say it's there for historical reasons (I know, that sounds stupid). We are going to change that.

Thank you very much for your help,
Jean-Pol.

Tom, how are you defining "peeking" and does it matter if it occurs when the statement is prepared
or when it, and the variables to be bound, are sent to the server? Also,you say,

"So that release and above does bind peek"


(have no idea what you are looking at. What is it? Both places, please)

Hi

I have problems with one our queries, sometimes it runs fast and sometimes it runs slow, fro 3 seconds to 150 seconds, there is no statistics changes. So far I have been flushing the shared pool to fix it.

I read that you said that in order for bind peeking to be an issue most probably is that histograms are present in the statistics. We do not gather histograms what can our issue be? We are running 9.2.07 on Windows.

I think flushing shared pool is not very good idea, do you think if I run alter table XXX noparallel would achive same effects?

Thanks

Hi

I am sure we dont have histograms because when I query user_histograms ENDPOINT_NUMBER always shows 0 and 1.

Same effects I mean if I run alter against a table (such as noparallel) I would invalidate the execution plan.

Thanks

It has also happened to me where there are no histograms and a query can give different execution plan in same day.

It is an Oracle E-Business Suite:

select cus.customer_name ,cus.attribute6
from
RA_CUSTOMERS CUS
where ( UPPER(CUSTOMER_NAME) LIKE :a1
AND (CUSTOMER_NAME LIKE :a2
OR CUSTOMER_NAME LIKE :a3
OR CUSTOMER_NAME LIKE :a4
OR CUSTOMER_NAME LIKE :a5))
AND ( exists (select null
from ra_customer_trx trx
where trx.bill_to_customer_id = cus.customer_id) )
group by cus.customer_name, cus.attribute6

the optimal way is this:

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 70 | 40 |
| 1 | SORT GROUP BY | | 1 | 70 | 40 |
|* 2 | TABLE ACCESS BY INDEX ROWID | RA_CUSTOMER_TRX_ALL | 1 | 10 | 13 |
| 3 | NESTED LOOPS | | 1 | 70 | 22 |
| 4 | NESTED LOOPS | | 1 | 60 | 9 |
|* 5 | TABLE ACCESS BY INDEX ROWID| HZ_PARTIES | 1 | 38 | 6 |
|* 6 | INDEX RANGE SCAN | HZ_PARTIES_N9 | 3 | | 3 |
| 7 | TABLE ACCESS BY INDEX ROWID| HZ_CUST_ACCOUNTS | 1 | 22 | 3 |
|* 8 | INDEX RANGE SCAN | HZ_CUST_ACCOUNTS_N2 | 1 | | 2 |
|* 9 | INDEX RANGE SCAN | RA_CUSTOMER_TRX_N11 | 9 | | 3 |
----------------------------------------------------------------------------------------

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

2 - filter(NVL("RA_CUSTOMER_TRX_ALL"."ORG_ID",NVL(TO_NUMBER(DECODE(SUBSTRB(:B1,1,1),'
',NULL,SUBSTRB(:B2,1,10))),(-99)))=NVL(TO_NUMBER(DECODE(SUBSTRB(:B3,1,1),'
',NULL,SUBSTRB(:B4,1,10))),(-99)))
5 - filter(SUBSTRB("PARTY"."PARTY_NAME",1,50) LIKE :Z OR
SUBSTRB("PARTY"."PARTY_NAME",1,50) LIKE :Z OR SUBSTRB("PARTY"."PARTY_NAME",1,50) LIKE
:Z OR SUBSTRB("PARTY"."PARTY_NAME",1,50) LIKE :Z)
6 - access(UPPER(SUBSTRB("PARTY"."PARTY_NAME",1,50)) LIKE :Z)
filter(UPPER(SUBSTRB("PARTY"."PARTY_NAME",1,50)) LIKE :Z)
8 - access("CUST_ACCT"."PARTY_ID"="PARTY"."PARTY_ID")
9 - access("RA_CUSTOMER_TRX_ALL"."BILL_TO_CUSTOMER_ID"="CUST_ACCT"."CUST_ACCOUNT_ID")

But sometimes it uses this plan


Rows Row Source Operation
------- ---------------------------------------------------
10 SORT GROUP BY (cr=261175 r=250430 w=0 time=243476585 us)
407 TABLE ACCESS BY INDEX ROWID RA_CUSTOMER_TRX_ALL (cr=261175 r=250430 w=0 time=243474557 us)
434 NESTED LOOPS (cr=260812 r=250134 w=0 time=240142178 us)
26 NESTED LOOPS (cr=260729 r=250117 w=0 time=239899783 us)
26 TABLE ACCESS FULL HZ_PARTIES (cr=260649 r=250086 w=0 time=239535388 us)
26 TABLE ACCESS BY INDEX ROWID HZ_CUST_ACCOUNTS (cr=80 r=31 w=0 time=364023 us)
26 INDEX RANGE SCAN HZ_CUST_ACCOUNTS_N2 (cr=54 r=13 w=0 time=293919 us)(object id 182529)
407 INDEX RANGE SCAN RA_CUSTOMER_TRX_N11 (cr=83 r=17 w=0 time=241589 us)(object id 227679)


Note TABLE ACCESS FULL HZ_PARTIES (cr=260649 r=250086 w=0 time=239535388 us), a huge table.

Hi Tom,
We have an OLTP pepole-soft CRM application on oracle 9208.
Once for few weeks we are shuting down the database taking a BC , and after that doing a maintanace work , like adding tables , packages , view and etc.
The problem is that every time after we are opening the database to the users , i found myself tuning statment (or statments) that worked great before we shutdown the database , because now they perform poorly.
In most of the cases we are adding a hint to the statment
to solve the problem.
Its look to me a bind peeking problem.
I know that its not the right solution.
How do you think we should act when we are facing a bind peeking problem ?

Hi Tom,
Thank you the answer.

BC is Business Copy (of HP) like BCV (of EMC) or snapshots (of Netapp).

I would like to ask few questions regarding to your answer:

1. We we are using the following package to gather statistics:

DBMS_STATS.GATHER_DATABASE_STATS
(estimate_percent => SYS.DBMS_STATS.auto_sample_size,
method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',
DEGREE => 4,
granularity => 'ALL',
CASCADE => TRUE,
options => 'GATHER AUTO'
);

I understood that you implies that, in must cases , we should use "...SIZE 1" instead of auto.Is that correct ?

2. Should we have to delete statistics before that change?
3. As I mentioned, right now everything is working great -
should I be prepared for some problems after deleting
the histograms?
4. Is there a view that can help me to determine if there
are unusable histograms ?

Thanks.

Hello Tom

 Why sqlplus doesnt do a bind peeking,I expects a full scan but tkprof says a index range scan.. please see the followings: the oracle database is 9i,9.2.0


SQL> create table vtt as select *  from all_objects;

Table created.

SQL> select count(*) from vtt;

  COUNT(*)
----------
     29175

SQL> create index vtt_ind on vtt(created);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'VTT',cascade=>true,method_opt=>'for all indexed columns');

PL/SQL procedure successfully completed.

SQL> select num_rows,last_analyzed from user_tables where table_name='VTT';

  NUM_ROWS LAST_ANALYZED
---------- -------------------
     29175 2009-02-20 00:08:39

SQL> select num_rows,last_analyzed from user_indexes where index_name='VTT';

no rows selected

SQL> select num_rows,last_analyzed from user_indexes where index_name='VTT_IND';

  NUM_ROWS LAST_ANALYZED
---------- -------------------
     29175 2009-02-20 00:08:39
SQL> alter session set sql_trace=true;

Session altered.

SQL> variable ddd varchar2(30)
SQL> exec :ddd :='2008-01-10'

PL/SQL procedure successfully completed.

SQL> select *  from vtt where created<=to_date(:ddd,'yyyy-mm-dd');

and tkprof says:
BEGIN :ddd :='2008-01-10'; END;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           1

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user id: 61  
********************************************************************************

select *  
from
 vtt where created <= to_date(:ddd,'yyyy-mm-dd')


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     1939      0.15       0.41        315       4357          0       29068
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1941      0.15       0.41        315       4357          0       29068

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user id: 61  

Rows     Row Source Operation
-------  ---------------------------------------------------
  29068  TABLE ACCESS BY INDEX ROWID VTT 
  29068   INDEX RANGE SCAN VTT_IND (object id 30853)

********************************************************************************
when I use the real value in sqlplus instead of binding, full scan is expected, I think that bind peeking will take place and fts will be performed, why is that? Thanks!

Hi Tom,
Is there a way to parse a statement from jdbc without executing it ?
For unit testing, I would like to parse all prepared statements in order to check their syntax, without executing it.
Solutions can be to put the statement in an explain plan, or to put them in stored procedures... but I'm looking for a way to just have it parsed when prepareStatement is called - and can't find it.
Thanks,
Franck.

That conflicts with what you wrote on June 8th 2004 (second reply in this thread):

"the statement is not parsed by the server, in fact not sent to the server, until line 44 [the execute query, ed.]-- so we do in fact have access to the bind variable values at parse time."

No longer true in current driver/database versions?

Hi Tom,

Thanks for this ever-useful site!
From your earlier review
-----------------------------
In order for bind peeking to be an issue - in general - that means you have gathered statistics that cause different plans for different literal values, those statistics are typically "histograms"

So, do you need, want or desire histograms? In general, in a transactional system the answer is many times "no" - because they a) take lots of resource to gather in the first place and b) cause this unpredictability issue.
-------------------------------

We do have histograms in our OLTP systems - and we DO have bind variable peeking issues all the while; its causing us very high CPU utilisation all the time. This has been logged with support and the only thing we have been told so far is set hidden parameter - _optim_peek_user_binds to FALSE (Oh, yes we are running SIEBEL application)
We earlier tried to turn off gathering Histograms - but that had disastrous results for our queries and we are very very anxious that we would have something similar after setting this parameter.
Apart from that, we are not going to get this tested for another month and implemented after that - till that time, daily we are having issues with peeking and that making us kill many sessions daily and flushing shared pool. So my question is
Is there something that can be done to come out of this situation ?

Many thanks in advance,
Shalini.

Hi Tom,

The link below talks about "Adaptive Cursors". I ran a simple test below and it did not seem to "take effect". Is there something I am missing? Is there a way to force this? Both is_bind_sensitive and is_bind_aware are "N" for my example below.

http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-sqlplanmanagement.html

Thanks in Advance !!!

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Solaris: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

-- create simple 100,000 row table with 3 columns

CREATE TABLE tab_a AS
SELECT level pk_col, 0 col_1, rpad(' ', 100) col_2 FROM dual CONNECT BY level < 100001;

CREATE UNIQUE INDEX pk_tab_a ON tab_a(pk_col);

-- all the values for col_1 are 0 except change the last one (row 100,000)

update tab_a set col_1 = pk_col where pk_col = 100000;

commit;

CREATE INDEX idx_col_1 ON tab_a(col_1);

-- show the plan for the when we select 99,999 rows where col_1 is 0

explain plan for select count(*) from tab_a where col_1 = 0;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
| 0 | SELECT STATEMENT | | 1 | 13 | 46 (9)|
| 1 | SORT AGGREGATE | | 1 | 13 | |
| 2 | INDEX FAST FULL SCAN| IDX_COL_1 | 93681 | 1189K| 46 (9)|

-- show the plan for the when we select 1 row where col_1 is 100000

explain plan for select count(*) from tab_a where col_1 = 100000;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)|
| 1 | SORT AGGREGATE | | 1 | 13 | |
| 2 | INDEX RANGE SCAN| IDX_COL_1 | 1 | 13 | 1 (0)|

-- run a anonymous block using binds showing only the first call is "peeked"

declare
l_col_1 tab_a.col_1%type;
l_cnt number;
begin

-- peek "sel_0_first" with for select 99,999 rows where col_1 is 0

l_col_1 := 0;
select count(*) sel_0_first into l_cnt from tab_a where col_1 = l_col_1;

-- peek "sel_100000_first" with for select 1 row where col_1 is 100000

l_col_1 := 100000;
select count(*) sel_100000_first into l_cnt from tab_a where col_1 = l_col_1;

-- now run the EXACT same 2 statements again - this time switch the values passed in

l_col_1 := 100000;
select count(*) sel_0_first into l_cnt from tab_a where col_1 = l_col_1;

l_col_1 := 0;
select count(*) sel_100000_first into l_cnt from tab_a where col_1 = l_col_1;
end;

-- check the work done from the SGA
-- Each statement "sel_0_first" and "sel_100000_first" was executed 2 times except the first peek value was opposite

SELECT sql_id, child_number, executions, cpu_time, elapsed_time, sql_text FROM v$sql WHERE sql_text LIKE 'SELECT COUNT(*) SEL_%';

SQL_ID CHILD_NUMBER EXECUTIONS CPU_TIME ELAPSED_TIME SQL_TEXT
1bs39xs4bqhbz 0 2 150,000 153,176 SELECT COUNT(*) SEL_0_FIRST FROM TAB_A WHERE COL_1 = :B1
6c185q85674h6 0 2 80,000 77,483 SELECT COUNT(*) SEL_100000_FIRST FROM TAB_A WHERE COL_1 = :B1


-- verify the plan from the SGA for peek where col_1 is 0 first

select * FROM TABLE(dbms_xplan.display_cursor('1bs39xs4bqhbz', 0));

PLAN_TABLE_OUTPUT
SQL_ID 1bs39xs4bqhbz, child number 0
SELECT COUNT(*) SEL_0_FIRST FROM TAB_A WHERE COL_1 = :B1

Plan hash value: 1447923

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | | | 46 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX FAST FULL SCAN| IDX_COL_1 | 93681 | 1189K| 46 (9)| 00:00:01 |

Predicate Information (identified by operation id):

2 - filter("COL_1"=:B1)

-- verify the plan from the SGA for peek where col_1 is 100000 first

select * FROM TABLE(dbms_xplan.display_cursor('6c185q85674h6', 0));


PLAN_TABLE_OUTPUT
SQL_ID 6c185q85674h6, child number 0
SELECT COUNT(*) SEL_100000_FIRST FROM TAB_A WHERE COL_1 = :B1

Plan hash value: 1735350713

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX RANGE SCAN| IDX_COL_1 | 1 | 13 | 1 (0)| 00:00:01 |

Predicate Information (identified by operation id):

2 - access("COL_1"=:B1)

Hi Tom,

thanks for your reply. Let me recap to make sure that I am clear:

1) The Adaptive Cursor feature will have no impact if there are no statistics on the table(s) involved?

2) The Adaptive Cursor feature will have no impact if the statement is cached in the SGA and is only run by the same session? This is important since our software queries through an application engine which keep persistent connections.

Thanks!

hi,
Thanks for the useful post.

I am having very similar problem where Node 1 & Node 2 uses 2 different plan for the same query.Where one is good and another is the bad one. And bad one causes huge active sessions on one node(EX: node 1 has 200 active session waiting on the same SQL and node 2 works fine and executes the SQL very quick). I need to set SQL profile for node 1 as same as the plan used for node 2.I am doing it frequently now-a-days.MOS suggests to change bind peeking to false.

Should i do it as the problem happens in both the nodes?
as We are using 11G, Why the Adaptive cursor sharing cannot solve the issue?
We use histogram as Siebel recommends to use histogram for some tables when deployed in Oracle.

Please refer to SR-3-1784070001, if i miss anything here.
thanks-ershad

"what is MOS?"

It is what Oracle call Metalink these days.. ie Metalink 1082787.1 where OIT discuss MOS (My Oracle Support), so it seems a fairly official acronym.

Slightly more on topic and general observation I've made over the last few years is that bind variable peeking together with the auto histogram generation in the default stats gathering of a 10G database has caused problems in a lot of OLTP databases I've seen. I haven't had much chance to test out the adaptive cursor sharing in 11G yet but I really wish that the default setting had been to have bind variable peeking switched off in 10G. Your typical OLTP database really doesn't want to have its plan flipped by the input variables. On at least 3 separate occasions now I've had key queries end up with about a 50/50 chance on parsing of getting a good/bad plan due to very skewed auto generated histograms.

Tom,
I have a question about the following statement,
"I'd want bind peeking ON and histograms OFF - you get consistency and the best chance for a good plan."

one of the purposes for peeking is to find the value of bind to determine the best plan based on histogram, without histogram what else can bind value serves? or what are other purpose for peeking?

Thanks,
Heather

Sorry, Tom.
I may not have explain myself well.

I understand the importance of bind in OLTP system. My question is why leaving bind PEEKING on and histogram off. Peeking is to check value of bind and without histogram would optimizer be able to make any different decision?

Thanks,
Heather

So, in OLTP system, keep histograms off to have consistent plan, peeking is IRRELEVANT.

Thanks,
Heather

Great insight as always. Thanks for reviewing my question, Tom.

i have cursor_sharing=similar in my 10g db. tried your 2nd example, it peeked even the 2nd query, 10046 level 4 trace shows bind replaced and unsafe, guess based on begin/end point with method_opt=>...size 1 (when i deleted column stats completed, plans are unchanged)

create table ttable2 as select rownum r, t.* from dba_tables t;
create index ie_ttable2 on ttable2 (r);
exec dbms_stats.gather_table_stats('ty890986','ttable2',cascade=>true, method_opt=>'for all indexed columns size 1');
create table ttable as select rownum r, t.* from dba_tables t;
create index ie_ttable on ttable (r);
exec dbms_stats.gather_table_stats('ty890986','ttable',cascade=>true, method_opt=>'for all indexed columns size 1');
select *
from ttable
where r in (select r from ttable2 where r<2);
select *
from ttable
where r in (select r from ttable2 where r<1189);


Thanks again, Tom.

drop table t purge;

create table t
as
select case when rownum = 1 then 1 else 99 end as id,
    a.*
from all_objects a;

create index t_ind on t(id) nologging;

begin
 dbms_stats.gather_table_stats(
 ownname =>user,
 tabname =>'T',
 estimate_percent=>dbms_stats.auto_sample_size,
 method_opt=>'for all indexed columns size 254');
end;
/

rajesh@ORA11GR2> set autotrace traceonly explain
rajesh@ORA11GR2> select *
  2  from t
  3  where id = 1;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 35828 |  3498K|   295   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    | 35828 |  3498K|   295   (1)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("ID"=1)

rajesh@ORA11GR2>
rajesh@ORA11GR2> select *
  2  from t
  3  where id = 99;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 71649 |  6996K|   295   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    | 71649 |  6996K|   295   (1)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("ID"=99)

rajesh@ORA11GR2>

But, the same test cases when ran in 10GR2, it shows me this

rajesh@ORA10GR2> set autotrace traceonly explain
rajesh@ORA10GR2> select *
  2  from t
  3  where id = 1;
Elapsed: 00:00:00.06

Execution Plan
----------------------------------------------------------
Plan hash value: 2098067784

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    99 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    99 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IND |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("ID"=1)

rajesh@ORA10GR2>
rajesh@ORA10GR2> select *
  2  from t
  3  where id = 99;
Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
Plan hash value: 2153619298

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 58775 |  5682K|   199   (4)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| T    | 58775 |  5682K|   199   (4)| 00:00:03 |
--------------------------------------------------------------------------

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

   1 - filter("ID"=99)

rajesh@ORA10GR2>

Tom:

I was reading about Bind peeking from 11GR2 docs
http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/optimops.htm#i79423

1)I am using the literals in query and not bind variables, why the query, select * from t where id = 1 is NOT using index in 11GR2? But, the same query in 10GR2 is using index to answer it.

Cursor_Sharing parameter is set to EXACT in both 10GR2 and 11GR2

Tom:

Your subject is about bind peeking in 11g, however your question HAS NOTHING AT ALL TO DO WITH BIND PEEKING. It is about the processing of a sql statement with literals

I was reading that bind peeking from 11G doc's, before start working with Binds, I thought to see the optimizer plan using without using binds.

begin
    dbms_stats.gather_table_stats(
    ownname =>user,
    tabname =>'T',
    estimate_percent=>dbms_stats.auto_sample_size,
    method_opt=>'for all indexed columns size 254');
end;
/

1) why this table gather script is providing proper estimates in 10G and NOT in 11G ?

b) use compute to gather statistics
2) you mean to say that in the create index statement above include compute statistics? I couldn't get that properly