Re: Function much slower than same statement.

Carlos wrote:
>
> Hi all:
>
> I am facing stunning performance results with a user defined function.
> This is the story:
> I have a function that basically performs a validation and branches
> into two selects. One of them (a three table select) was behaving very
> bad (26 secs) although the joins are PK based, and, to my surprise,
> another function that performs a query very similar to the bad one,
> but with one more 'child table' in the join performs just OK.
> "Let's see" I thought.
> I Opened SqlPlus and executed this annoying query. Again It last 26
> secs.
> Then I used a hint ('ORDERED') in the select and the query executed in
> 0.10 secs. "OK" I thought "Next step: put the hint in the select
> within the function..." but the function still takes 26 secs to
> execute!! Same select statement, same hint but the Function seems to
> be 260 times slower than the single select (Is the optimizer using the
> hint?).
> The validation issue in the function is only "if variable_val <
> fixed_val then SELECT... else SELECT..." so It shouldn't penalize.
>
> Can anybody throw some light on it?
>
> The infamous SELECT (works OK as statement -with the hint- but very
> bad inside the function):
>
> SELECT/*+ORDERED*/ NVL( SUM( ( b.N_AP_AS_A +
> b.N_AP_AS_M +
> b.N_AP_AV_A +
> b.N_AP_AV_M ) * c.N_CR_AP ), 0 )
> INTO v_ret
> FROM TC a, VEN_SOR_JA b, MOD_SOR_JA c
> WHERE a.ID_N_PV = p_ID_N_PV
> AND b.ID_N_TC = a.ID_N_TC
> AND b.ID_N_JR >= p_jr_ini
> AND b.ID_N_JR <= p_jr_fin
> AND c.ID_N_JGO = b.ID_N_JGO
> AND c.ID_N_SOR = b.ID_N_SOR
> AND c.ID_N_MOD = b.ID_N_MOD;
>
> The SELECT with one more table (in another user defined function) that
> performs OK (0.20 secs.):
> SELECT NVL ( SUM(
> (
> ( b.N_AP_AS_A +
> b.N_AP_AS_M +
> b.N_AP_AV_A +
> b.N_AP_AV_M ) * c.N_CR_AP )
> * d.N_COM_VEN / 100 )
> , 0 )
> INTO v_ret
> FROM TC a, VEN_SOR_JA b, MOD_SOR_JA c, JGO_TC d
> WHERE a.ID_N_PV = p_ID_N_PV
> AND b.ID_N_TC = a.ID_N_TC
> AND b.ID_N_JR >= p_jornada_ini
> AND b.ID_N_JR <= p_jornada_fin
> AND c.ID_N_JGO = b.ID_N_JGO
> AND c.ID_N_SOR = b.ID_N_SOR
> AND c.ID_N_MOD = b.ID_N_MOD
> AND d.ID_N_JGO = b.ID_N_JGO
> AND d.ID_N_TC = a.ID_N_TC;
>
> This is running on Oracle 8.1.6 on Win NT sp 6 (This is not my
> fault!!)
>
> Thanks in advance.

You need a space:

select /*+ORDERED*/ works in SQL but not PLSQL

select /*+ ORDERED */ works in both

hth
connor

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

"Some days you're the pigeon, some days you're the statue"