Re: Slow query with Oracle 10G

slygagnon247_at_gmail.com wrote:
> 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
>

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
dba_at_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