Oracle Database Discussions

Hello performance guys,
i am a little bit confused about the parameter optimpeek_user_binds and soft/hard parse.

I have got the following scenario...

A table with a column F_FOR which is numeric and has a very bad value spreading.
The values are something like this..
5 -> 150 times
7 -> 140000 times
8 -> 3 times
102 -> 500000 times

So i created histograms on the table to have statistics about the value spreads.

No i execute a statement with bind peeking with the following where clause:
VAR k_val NUMBER;
EXEC :k_val := 102;
select from table WHERE F_FOR = :k_val;

The execution plan shows an full table scan which is correct in this case.

After that i rexecute the statement with the following value:
VAR k_val NUMBER;
EXEC :k_val := 5;
select from table WHERE F_FOR = :k_val;

Now it uses also the full table scan (which is correct, because the first statement was parsed with the value 102), but an index access is very faster for the value 5.

So i set in my session the "hidden" parameter optimpeek_user_binds to FALSE and rexecutes the statements.

In both cases i have set an 10053 trace... after i set the optimpeek_user_binds parameter the traces shows the following (which is absolutly correct)
--> No bind buffers allocated
and uses the correct access path...

So my question to you:

1) Is for every statement (by using binds) running a hard parse, when parameter optimpeek_user_binds=FASLE is set?
2) How does Oracle estimates the costs of the accesses without knowing the values behind the bind variables, when using optimpeek_user_binds=FALSE...

In the other case it looks at the first statement values and use it for all the same statements...

Thanks.

Regards
Stefan