Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Slow query with Oracle 10G

Re: Slow query with Oracle 10G

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 21 Aug 2007 13:58:09 -0700
Message-ID: <1187729889.87659@bubbleator.drizzle.com>


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

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
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Tue Aug 21 2007 - 15:58:09 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US