Function much slower than same statement.

[Carlos]

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.

[Daniel Roy]

Could you please provide us the execution plans? (do a search on this
site if you're not sure how, you'll get tons of results).

Daniel

[Connor McDonald]

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
[url]http://www.oracledba.co.uk[/url]

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

[Carlos]

Connor McDonald <connor_mcdonald@yahoo.com> wrote in message news:<3F09928D.2F7@yahoo.com>...

>
> You need a space:
>
> select /*+ORDERED*/ works in SQL but not PLSQL
>
> select /*+ ORDERED */ works in both
>
> hth
> connor

Thank you so much, Connor. I knew there was a space issue, but I was
not sure if I shoud place a space or not, and when It worked in
sqlplus without spaces I inferred (wrong!) that this was the right
way, so I didn't try the other (stupid!!).
I will never forget again.

Greetings from Spain.