Home » RDBMS Server » Performance Tuning » NVL is degraing performance, any alternative
NVL is degraing performance, any alternative [message #514439] Mon, 04 July 2011 13:53 Go to next message
goparaju
Messages: 54
Registered: June 2011
Member
Hi Experts,

I working in 11.5.10 EBS and database 9i.

I have below query

begin
select empno,ename, sal, job
from emp
where empno=nvl(:eno,empno)
and ename=nvl(:ena,ename);
end;

Note: I have 10 nvl's in my query.

If i don't pass any parameter,it's taking very long time because of nvl condition. Could somebody help me how to improve the performance of above query, is there any alternative other than NVL function.

Kindly help me.

Thanks in advance

Report message to a moderator

Re: NVL is degraing performance, any alternative [message #514440 is a reply to message #514439] Mon, 04 July 2011 14:04 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Note: I have 10 nvl's in my query.
The why do I NOT see them in your post?

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof

Report message to a moderator

Re: NVL is degraing performance, any alternative [message #514441 is a reply to message #514439] Mon, 04 July 2011 14:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
is there any alternative other than NVL function.


1/ Upgrade to a newer version
2/ ( empno = :eno or :eno is null ) and ...

Regards
Michel

[Updated on: Mon, 04 July 2011 14:29]

Report message to a moderator

Re: NVL is degraing performance, any alternative [message #514516 is a reply to message #514441] Tue, 05 July 2011 04:39 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
What about dynamic sql?

Report message to a moderator

Re: NVL is degraing performance, any alternative [message #514517 is a reply to message #514516] Tue, 05 July 2011 04:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
It implies a parse each time.
And a parse does not slow only you but the whole instance (and all of them if you are in RAC).

Regards
Michel

[Updated on: Tue, 05 July 2011 04:42]

Report message to a moderator

Re: NVL is degraing performance, any alternative [message #514521 is a reply to message #514517] Tue, 05 July 2011 04:52 Go to previous messageGo to next message
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
Senior Member
There's an expanded version of the query here which casts things in a different light. Probably we should merge, I haven't since I'm not sure if it's better here or there.

Report message to a moderator

Re: NVL is degraing performance, any alternative [message #514522 is a reply to message #514521] Tue, 05 July 2011 04:53 Go to previous messageGo to next message
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
Senior Member
Actually it's a different query but I assume the one at the top of this thread is just an illustration.

Report message to a moderator

Re: NVL is degraing performance, any alternative [message #514556 is a reply to message #514522] Tue, 05 July 2011 06:57 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Hello,

Oracle exchanges automaticaly nvl via or expansion (s. the parameter _or_expand_nvl_predicate). So you don't need to change your sql with ( empno = :eno or :eno is null ) or to use dynamic sql. I suppose, you have to change the logics not the sql.

Regards
Leonid

Report message to a moderator

Re: NVL is degraing performance, any alternative [message #514559 is a reply to message #514556] Tue, 05 July 2011 07:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
It depends on OP's actual version and this is the reason of my point 1.

Regards
Michel

Report message to a moderator

Re: NVL is degraing performance, any alternative [message #515735 is a reply to message #514559] Wed, 13 July 2011 02:37 Go to previous message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
What depends from OP's version? The parameter _or_expand_nvl_predicate was introduced in 8.1.6. goparaju uses 9i. Could you please explain, what you mean?
Regards
Leonid

Report message to a moderator

Previous Topic: Help SQL Tuning
Next Topic: Wait Stats
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Nov 24 21:49:41 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.