OraFAQ Forum: Performance Tuning » NVL is degraing performance, any alternative

Home » RDBMS Server » Performance Tuning » NVL is degraing performance, any alternative

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

NVL is degraing performance, any alternative [message #514439]

Mon, 04 July 2011 13:53

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

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

Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...

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

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

Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...

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

cookiemonster
Messages: 13920
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

cookiemonster
Messages: 13920
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

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

Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...

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

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 02:18:04 CDT 2024