Slow query with Oracle 10G

[slygag...@gmail.com]

Hi,

During the last month, we have promote version of Oracle server 9i to
10g. A cursor is very slow since the 10g version. This cursor uses
much NVL function to retrieve information.

I would like know if the function NVL can be used in Oracle 10g?
Do there exist operational limits of function NVL?
Does function NVL slow down the speed of execution?

Here the defintion of Cursor:

CURSOR Cur_Request IS
SELECT *
FROM TABLE1
WHERE FIELD1 = NVL(p_no_inter_relie, p_no_inter)
AND FIELD2 >= p_dt_deb_extrac
AND FIELD3 <= p_dt_fin_extrac
AND NVL(FIELD4,'0') = NVL( FIELD12, NVL(FIELD4,'0'))
AND NVL(FIELD5,'0') = NVL( FIELD13, NVL(FIELD5,'0'))
AND NVL(FIELD6,0) = NVL( FIELD14, NVL(FIELD6,0))
AND NVL(FIELD7,'0') = NVL( FIELD15, NVL(FIELD7,'0'))
AND NVL(FIELD8,'0') = NVL( FIELD16, NVL(FIELD8,'0'))
AND NVL(FIELD9,'0') = NVL( FIELD17, NVL(FIELD9,'0'))
AND NVL(FIELD10,'0') = NVL( FIELD18, NVL(FIELD10,'0'))
AND NVL(FIELD11,'@') = NVL( FIELD19,NVL(FIELD11,'@'));

Thanks

[slygag...@gmail.com]

Hi,

Thanks

Sly Gagnon

[Jan Krueger]

Most likely the execution plan has changed for some reason.
If you can, compare the plans of the old and the new environment.

Jan

[slygag...@gmail.com]

On 21 août, 11:43, Jan Krueger <j...@stud.uni-hannover.de> wrote:
> Most likely the execution plan has changed for some reason.
> If you can, compare the plans of the old and the new environment.
>
> Jan
>

I have the same execution plan with two versions of oracle 9i and 10g.

Sly

[Brian Peasland]

I would be very surprised if the NVL function were causing your
problems. It can still be used in 10g. You stated that the execution
plans were the same in 9i and 10g. What other factors are slowing down
your performance? Have you looked at your session's wait events? Being
able to compare this to an existing 9i instance is great since you have
a "good" baseline to compare to. So see where your query is spending its
time compared to the 9i version. You'll know where to focus your efforts.

HTH,
Brian

--
===================================================================

Brian Peasland
d...@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.

"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

--
Posted via a free Usenet account from http://www.teranews.com

[DA Morgan]

Your assumption that this small code snippet is responsible is
likely a misdirection. Have you run DBMS_PROFILER and determined
where the issue actually is?
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[HansH]

<slygag...@gmail.com> schreef in bericht
news:1187704220.7...@o80g2000hse.googlegroups.com...

> I would like know if the function NVL can be used in Oracle 10g?
> Do there exist operational limits of function NVL?
> Does function NVL slow down the speed of execution?

Unable to answer either of these question.

> CURSOR Cur_Request IS
> SELECT *
> FROM TABLE1
> WHERE FIELD1 = NVL(p_no_inter_relie, p_no_inter)
> AND FIELD2 >= p_dt_deb_extrac
> AND FIELD3 <= p_dt_fin_extrac
> AND NVL(FIELD4,'0') = NVL( FIELD12, NVL(FIELD4,'0'))
> AND NVL(FIELD5,'0') = NVL( FIELD13, NVL(FIELD5,'0'))
> AND NVL(FIELD6,0) = NVL( FIELD14, NVL(FIELD6,0))
> AND NVL(FIELD7,'0') = NVL( FIELD15, NVL(FIELD7,'0'))
> AND NVL(FIELD8,'0') = NVL( FIELD16, NVL(FIELD8,'0'))
> AND NVL(FIELD9,'0') = NVL( FIELD17, NVL(FIELD9,'0'))
> AND NVL(FIELD10,'0') = NVL( FIELD18, NVL(FIELD10,'0'))
> AND NVL(FIELD11,'@') = NVL( FIELD19,NVL(FIELD11,'@'));

Blindly following your assumption I'ld first replace the (nested) NVLs

AND NVL(FIELD4,'0') = NVL( FIELD12, NVL(FIELD4,'0'))
AND NVL(FIELD5,'0') = NVL( FIELD13, NVL(FIELD5,'0'))

by COALESCE
AND COALESCE(FIELD4,'0') = COALESCE( FIELD12, FIELD4,'0'))
AND COALESCE(FIELD5,'0') = COALESCE( FIELD13, FIELD5,'0'))

Then cut the number of coalesces in half by rewriting the lines to
AND ( FIELD12 IS NULL OR FIELD12 = COALESCE( FIELD4, '0' ) )
AND ( FIELD13 IS NULL OR FIELD13 = COALESCE( FIELD5, '0' ) )

And, if binary shortcutting does not do so already, force the last coalesce
per line to be executed only if needed :
AND case when FIELD12 IS NULL then TRUE else FIELD12 = COALESCE( FIELD4,
'0' ) END
AND case when FIELD13 IS NULL then TRUE else FIELD13 = COALESCE( FIELD5,
'0' ) END
( Effects of this step will largely depend on the number of NULLed fields
per record in your data; it may even backfire )

Just my 2ct

HansH