Home » RDBMS Server » Performance Tuning » Performance tuning (Oracle 10.2.0.3.0)
Performance tuning [message #409628] Tue, 23 June 2009 06:26 Go to next message
deepbans
Messages: 32
Registered: February 2009
Member
Hi,

I am running a query with search criteria that contain only one Value in IN.Its giving a low cost at explain plan.All the tables are accessed by INDEX.

But at time same time if I give two values in IN Block of Where.Its giving huge cost in execution plan and most of the tables are full scanned(causing High cost)

What can be Reason for this,any idea?


Thanks In Advance.

Report message to a moderator

Re: Performance tuning [message #409633 is a reply to message #409628] Tue, 23 June 2009 06:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Not up to date statistics.
Or one value is restrictive and the other one not.

Regards
Michel

Report message to a moderator

Re: Performance tuning [message #409652 is a reply to message #409633] Tue, 23 June 2009 07:19 Go to previous messageGo to next message
deepbans
Messages: 32
Registered: February 2009
Member
Its like When I give
e.g.
Select * from emp where empno IN(1234)

It is giving fast data with low cost.But if I give

Select * from emp where empno IN(1234,12345)

It is giving data very late and cost of the query is too high.


Thanks

Report message to a moderator

Re: Performance tuning [message #409664 is a reply to message #409652] Tue, 23 June 2009 07:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Does not change my answer.

Regards
Michel

Report message to a moderator

Re: Performance tuning [message #409668 is a reply to message #409664] Tue, 23 June 2009 07:48 Go to previous messageGo to next message
deepbans
Messages: 32
Registered: February 2009
Member
Hi,

I dint get you..

Report message to a moderator

Re: Performance tuning [message #409669 is a reply to message #409668] Tue, 23 June 2009 07:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What part don't you get?
Explain what you don't understand.

Regards
Michel

Report message to a moderator

Re: Performance tuning [message #409815 is a reply to message #409669] Wed, 24 June 2009 03:11 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Use DBMS_STATS.GATHER_TABLE_STATS() to gather fresh statistics on the table.
Ross Leishman

Report message to a moderator

Re: Performance tuning [message #410184 is a reply to message #409652] Thu, 25 June 2009 10:22 Go to previous message
prachij593
Messages: 266
Registered: May 2009
Senior Member
If stats are up to date then Do we need to know how many rows aree there individually ?
Select count(*) from emp where empno IN(1234);
Select count(*) from emp where empno IN(12345);

[Updated on: Thu, 25 June 2009 10:22]

Report message to a moderator

Previous Topic: Parallel and First_rows Hints
Next Topic: How do you know whether the Index is getting used or not
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Nov 25 16:38:17 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.