Home » RDBMS Server » Performance Tuning » PERFORMANCE OF A QUERY ;
PERFORMANCE OF A QUERY ; [message #307693] Wed, 19 March 2008 09:02 Go to next message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
AOA // hI ALL
..
i was using a query , which was working all right having records in millions almost , but when i introduced a parameter , and used
nvl() function to handle that parameter , its working allright but the performance has decreased considerably , what should i use , will decode serve any better from response time point of view.
Re: PERFORMANCE OF A QUERY ; [message #307694 is a reply to message #307693] Wed, 19 March 2008 09:04 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Yes/No/Maybe.

Not enough information to answer that.

Have a look at the execution plans for both queries for a start, you must know how since you posted in the experts forum.

[Updated on: Wed, 19 March 2008 09:06]

Report message to a moderator

Re: PERFORMANCE OF A QUERY ; [message #307696 is a reply to message #307694] Wed, 19 March 2008 09:11 Go to previous messageGo to next message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
ya i do ,
but i wanted all the experts to share thier techniques that they used to improve performance during different scenario's. i have one scenario and three possible conditions.
1. use NVL
2. use decode
3. use nvl while creating parameter list.
i am looking for little thoeratical knowledge about improving performance .
Re: PERFORMANCE OF A QUERY ; [message #307697 is a reply to message #307693] Wed, 19 March 2008 09:21 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>i am looking for little thoeratical knowledge about improving performance .
As opposed to 1st hand knowledge gained by doing actual benchmarks yourself?

The usual answer for Oracle when it comes to any performance question is, "It depends".
If there was a one size fits all solution, Oracle would hard code it into the DB engine.

[Updated on: Wed, 19 March 2008 09:21] by Moderator

Report message to a moderator

Re: PERFORMANCE OF A QUERY ; [message #307698 is a reply to message #307697] Wed, 19 March 2008 09:29 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Perhaps the only general thing might be to try to avoid functions on the data side, like don't do

select number,name
  from table
 where number * 2 = 4;


but do

select number,name
  from table
 where number  = 4 / 2;


so that the expression is calculated only once and the optimizer can use an index on number.

But the only real answer is "it depends" Wink
Previous Topic: database configuration of SGA,shared pool size,redolog buffer and sort area size (merged)
Next Topic: 9i-Clearing cache and memory
Goto Forum:
  


Current Time: Sat Dec 10 15:08:17 CST 2016

Total time taken to generate the page: 0.26979 seconds