Re: SQL fast, PL/SQL slow, optimizer confused.

sybrandb_at_yahoo.com wrote in message news:<a1d154f4.0402180658.67148708_at_posting.google.com>...
> Bjorn Augestad <root_at_localhost.com> wrote in message news:<szFYb.4806$rj4.68168_at_news2.e.nsc.no>...
> > I'm trying to tune a select statement which is called a lot. My new
> > version runs fast when written as a straight SQL statement, but runs
> > very slow when I wrap it in a PL/SQL block. I must be missing something
> > very obvious here, please help.
> >
> > Environment:
> > - Oracle EE 8.0.5.2.1 with Objects option and PL/SQL 8.0.5.2.0
> > - optimizer_features_enable 8.0.0
> > - optimizer_index_caching 0
> > - optimizer_index_cost_adj 100
> > - optimizer_max_permutations 80000
> > - optimizer_mode CHOOSE
> > - optimizer_percent_parallel 0
> > - optimizer_search_limit 5
> > - Fresh statistics on the table
> >
> > The where-clause has a construct like this:
> > where (
> > foo like 'XX-__1111111111'
> > or foo like 'YY-__1111111111'
> > or foo like 'ZZ-__1111111111'
> > or foo like 'WW-__1111111111'
> > )
> >
> > The column foo has a non-unique index. I've checked the execution plan
> > and the SQL version of my statement uses that index.
> >
> > The PL/SQL version looks more or less like this:
> > declare
> > key varchar2(10) := '1111111111';
> > key1 varchar2(160);
> > key2 varchar2(160);
> > key3 varchar2(160);
> > key4 varchar2(160);
> > begin
> > key1 := 'XX-__' || key;
> > key2 := 'YY-__' || key;
> > key3 := 'ZZ-__' || key;
> > key4 := 'WW-__' || key;
> > select ...
> > from ...
> > where (
> > foo like key1
> > or foo like key2
> > or foo like key3
> > or foo like key4
> > )
> > end;
> >
> > This version performs a FTS. If I remove 3 of the 4 variables and just
> > use one of the keys, e.g. key1 :
> > where foo like key1
> > then Oracle uses the index.
> >
> > Does anyone know why Oracle behaves like this and hopefully what I can
> > do about it?
> >
> > Thanks,
> > Bjørn
>
> PL/SQL by design runs in ALL_ROWS mode.
>
> Sybrand Bakker
> Senior Oracle DBA

Hi,
 actually this is not the case in 9.2 anymore, i believe it changed in 8.1.7 or something. Now PL/SQL runs in whatever the sessions optimizer mode happens to be. (at least that's what I found the last time i checked)

 For 8.0.5 - you are correct.

Karsten Received on Thu Feb 19 2004 - 18:59:54 CST