CBO and default selectivity with bind variables

In the process of trying to find why the CBO in 8.0.5 appears consistently to arrive at the slowest possible execution plan for all of the queries embedded in my stored package, I came across an Oracle Technical Note which seemed to be saying (unless I misunderstood it) that:

(a) any SQL query within a stored procedure which used PL/SQL variables
(i.e. bind variables) as parameters to the query could not make use of
histograms.

(b) in such a situation, the CBO would make the assumption that all queries
returned 25% of the rows in the table.

This would certainly explain the behaviour I am seeing. Some questions:

(1) Is this true?

If so ...

(2) Is there any way of 'hinting' for a particular statement that this
percentage is several orders of magnitude too high? I'm not sure whether just hinting that a particular index be used is quite what I want, since my queries typically join several tables with a variety of conditions.

(3) Is there any point in ANALYZE-ing tables which are only ever accessed
from PL/SQL stored packages?

(3) On a more general note, what sort of applications *don't* use bind
variables?

Adrian Bowen Received on Sat Jul 31 1999 - 13:36:49 CDT