Re: CBO and default selectivity with bind variables

In article <7nvfrd$nvl$1_at_laura.pcug.co.uk>,   "Adrian Bowen" <adrian_at_raptor.win-uk.net> wrote:
> 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
>
>

Hi.

1. It is true only for a case when you have 1 field in index : SELECT * FROM emp WHERE emp_id BETWEEN :p1 AND :p2; Otherwise CBO takes in account the number of distinct values for all leading index fields having '=' in WHERE clause. ( That answers #3 as well ( I think so...)).
2. No new hints here. You will have to use /*+ INDEX ... */.
3. Dynamic SQL don't use bind variables.

 Good luck. Michael.

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Sun Aug 01 1999 - 14:58:03 CDT