Re: Why performancediff package--direct query?

Göran K wrote:
>
> We have a fairly complicated view 'vy_leveranstid', but I don't think
> it's specification is relevant to my question.
>
> If we execute a select count(*) in SQL*Worksheet it takes less than a
> second.
>
> If we execute it as a packaged function it takes about 35 seconds.
>
> Why? Anyone have a clue?
>
> The function (part of leveranstabell package):
> ----------------
> function har_transportor(cbutik_id varchar2 default NULL, clev_satt_id
> varchar2 default NULL, clevadress_id varchar2 default NULL) return
> boolean
> is
> cantal_tider integer;
> begin
> select count(*)
> into cantal_tider
> from vy_leveranstid
> where (butik_id=cbutik_id or cbutik_id is NULL)
> and (lev_satt_id=clev_satt_id or clev_satt_id is NULL)
> and (lev_adress_id=clevadress_id or clevadress_id is NULL);
> return cantal_tider>0;
> end har_transportor;
> --------------------
>
> If we take the select, removes the into-clause and replaces the
> cbutik_id,
> clev_satt_id and clevadress_id with values
> and executes the query, it takes less than a second,
> if we do execute:
>
> declare
> i boolean;
> begin
> i := leveranstabell.har_leverantor(1,2,4711);
> end;
>
> it takes 35 seconds
>
> Why?
>
> regards
> Göran
>
> * Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
> The fastest and easiest way to search and participate in Usenet - Free!

Throw a trace on the package execution and see what the difference in access path is. Also note that within PL/SQL the optimiser mode will be CHOOSE, even if your database level mode is set to something else.

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse