Re: Date bind variable being peeked, but not used for cardinality estimate

   Another thought just crossed my mind. Peeked bind variables are in the other_xml column of v$sql_plan along with the hints that the optimizer thinks it needs to reproduce the plan. And it's possible to turn off BVP with an alter session, so it's possible your app does that somewhere prior to executing your query. If that has happened you should see a hint to that affect in the other_xml column.

Try this:

select
extractvalue(value(d), '/hint') as outline_hints from
xmltable('/*/outline_data/hint'
passing (
select
xmltype(other_xml) as xmlval
from
v$sql_plan
where
sql_id like nvl('&sql_id',sql_id)
and child_number = &child_no
and other_xml is not null
)
) d;

If it's been turned off, you should see a hint something like this:

OPT_PARAM('_optim_peek_user_binds' 'false')

You can also have a look at the peeked bind variables themselves. I have a script that has that embedded in it (called build_bind_vars.sql) but it's pretty long and ugly, so I'll just point you to a blog entry on it which is at:

http://kerryosborne.oracle-guy.com/2009/07/creating-test-scripts-with-bind-variables/

Kerry Osborne
Enkitec
blog: kerryosborne.oracle-guy.com

On Sep 24, 2009, at 11:50 AM, Allen, Brandon wrote:

> Hi Christian,
>
> It was news to me that the variables in v$sql_bind_capture aren't
> necessarily the ones used for peeking - I had misunderstood that
> until now that you and a few others have enlightened me.
>
> Thanks for the pointer to the Metalink note - I have read that, but
> I'm not sure yet if that's my problem or not. This is an Oracle
> Forms app running on Oracle Application Server 10.1.2.3, so it is a
> Java application on the client side but I was under the impression
> that the Forms server process (frmweb) running on the app server
> connected to the database through a regular OCI/Oracle Net
> connection (not sure how to confirm that though). Also, the note
> says that JDBC drivers 10g+ do not have this problem and I'd think
> that if Oracle forms is actually using jdbc, it would at least be 10g
> + since it's 10g app server running on a 10g database.
>
> I've got an SR open for this now so I'll reply back to the list when
> we figure it out.
>
> Thanks!
> Brandon
>

--
http://www.freelists.org/webpage/oracle-l