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

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 Received on Wed Feb 18 2004 - 08:58:37 CST