Re: sanity check - histograms, cursor sharing & bind var peeking

Hey Jo,
not quite sure about that old 10.2.0.4 stuff (only have 10.2.0.5 as the oldest version here), but ...

1. By disabling bind peeking you will prevent the optimizer from peeking at the initial bind value and it will not use the histogram to determine the cardinality estimates for the statement. Instead the Optimizer will assume a uniform distribution of rows across the distinct values in a column and will use NDV to determine the cardinality estimate. (Source: http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-for-stats-gather-12c-1967354.pdf)
2. You are using "cursor_sharing = similar", so your literals are not necessarily replaced by binds and so bind peeking does not matter at all in these cases. Cursor_sharing = similar. There are two different cases for this:
   • There is a histogram on the job column. In this case, literal replacement will not take place. The presence of a histogram indicates that the column is skewed, and the optimal plan may depend on the literal value. Hence, the optimizer sees the query as: select * from employees where job = 'Clerk' and subsequent executions with a different literal will not necessarily use the same plan.
   • There is no histogram on the job column. This indicates that the column is not skewed, and the optimizer is likely to choose the same plan no matter the literal, so literal replacement takes place. (Source: https://blogs.oracle.com/optimizer/entry/explain_adaptive_cursor_sharing_behavior_with_cursor_sharing_similar_and_force)

So verify in your environment if literal replacement is not taking place (as it should be) in case of histograms - if this is true your histograms have impact due to "cursor_sharing = similar".  

Best Regards
Stefan Koehler

Freelance Oracle performance consultant and researcher Homepage: http://www.soocs.de
Twitter: _at_OracleSK  

> Jo Holvoet <jo.holvoet_at_gmail.com> hat am 1. Juli 2016 um 09:36 geschrieben:
>
> Hi all,
>
> I recently inherited a database (10.2.0.4 EE 2-node RAC, about 3 TB of data).
>
> Stats are gathered manually for certain tables with : method_opt=> 'for all columns size skewonly'
>
> But :
> 1) Cursor_sharing = similar
> 2) _optim_peek_user_binds = FALSE
>
> I would have thought in this scenario it makes no sense at all to gather histograms ? Or am I missing something somewhere ?
>
> Thanks & regards
>
> Jo

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