OraFAQ Forum: Performance Tuning » Performance tuning

Home » RDBMS Server » Performance Tuning » Performance tuning (Oracle 10.2.0.3.0)

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

Performance tuning [message #409628]

Tue, 23 June 2009 06:26

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

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

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

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

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

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

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

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

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

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

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jun 23 13:58:18 CDT 2024