OraFAQ Forum: Performance Tuning » Query Optimization

Home » RDBMS Server » Performance Tuning » Query Optimization (Oracle SQL Devloper)

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

Query Optimization [message #649056]

Fri, 11 March 2016 01:42

Ranjeet29590
Messages: 1
Registered: March 2016
Location: MUMBAI

Junior Member

Hi all,

I need help. I have complex query when I run this query on dev server then index on table is working but same query when I try on prod server indexes not working .
so how to invoke index on production.

Report message to a moderator

Re: Query Optimization [message #649057 is a reply to message #649056]

Fri, 11 March 2016 01:46

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Report message to a moderator

Re: Query Optimization [message #649063 is a reply to message #649057]

Fri, 11 March 2016 07:39

Bill B
Messages: 1971
Registered: December 2004

Senior Member

What version of the database? Do you have the exact same indexes? Do you have current Stats?

Report message to a moderator

Re: Query Optimization [message #649064 is a reply to message #649063]

Fri, 11 March 2016 07:45

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

does query use bind variables?

Report message to a moderator

Re: Query Optimization [message #649109 is a reply to message #649064]

Sun, 13 March 2016 11:15

garan
Messages: 27
Registered: January 2016

Junior Member

Hi

Please compare the number of rows for each table involved in the join between development and prodn. If large number of rows present in prodn and
the column involved in the index is less distinct values oracle might prefer full table scan than the index scan
Also check the statistics of all the tables involved in the query is uptodate in prodn
Also compare the session level values for index_cost_adj factor between prodn and development

garan

Report message to a moderator

Previous Topic:	Size of SYSAUX is getting bigger
Next Topic:	How to deal with 'Free buffer waits' event

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 05:55:17 CST 2024