Re: strange view performance....

On Tue, 15 Jun 2004 19:14:06 +0200, "Cicciobellodrindrin" <MaQuantoSonoGrasso_at_EvvivaLaCiccia.CiccioniUniti.it> wrote:

>Hi,
>I'm working with an Oracle 8.0.6 server (we are going to migrate to a 9.2
>version) and i found something very strange. I defined a view composed of 3
>UNION ALL, every union all has 3-4 tables in joins. I known that oracle
>discourages the usage of complex view but i tried, the whole view
>performance looks good, a simple query like this:
>selec count(*) from myView
>takes only 3 seconds to execute on 50.000 records, moreover the execution
>plan looks good (only index access on tables no full scan....).
>The very firste development was also having good performances, so i decided
>to exetend the usage of such view to others applications and i found a
>strange thing. In the new applications i defined a cursor like this:
>cursor myCur is
> select v.* from myView v where v.FieldName like 'NNN%N\_NN' escape '\'
>if i execute such query into an sql session, the only one record is found in
>about 5 seconds, but into an pl/sql package, the open of the cursor takes
>about 8 minutes !!!!!!!!!!
>I solved the problen in this way:
>cursor myCursor is
> select v.* from myView v
>..
>..
>..
>open myCursor
>fetch myCursor into xxx
>if xxx.fieldName like 'NNN%N\_NN' escape '\' then
> ....
>end;
>
>The performance now looks good, about 5 seconds to execute.
>
>I'm trying to understand why the first cursor has suc poor
>performance....someone can help me????
>
>thanks a lot
>Stefano.
>

Obviously you would need to get the explain plan for both statements. My guess is you are using the Cost Based Optimizer (in it's very early immature stages) and you are suffering from the fact PL/SQL always uses the CBO in all_rows fashion and never in first_rows fashion. Your 'solution' is just a workaround, actually it is a horribly idea to do anything in a procedural language what could have been done by SQL. To summarize: to provide any accurate help, your problem description must be much more detailed, one would need explain plans, columns indexed etc, now your post boils down to one remark 'It doesn't work' and crystall balls have sold out here.

--
Sybrand Bakker, Senior Oracle DBA