Home » RDBMS Server » Performance Tuning » Query Optimization (Oracle SQL Devloper)
Query Optimization [message #649056] Fri, 11 March 2016 01:42 Go to next message
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.
Re: Query Optimization [message #649057 is a reply to message #649056] Fri, 11 March 2016 01:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Query Optimization [message #649063 is a reply to message #649057] Fri, 11 March 2016 07:39 Go to previous messageGo to next message
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?
Re: Query Optimization [message #649064 is a reply to message #649063] Fri, 11 March 2016 07:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
does query use bind variables?
Re: Query Optimization [message #649109 is a reply to message #649064] Sun, 13 March 2016 11:15 Go to previous message
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
Previous Topic: Size of SYSAUX is getting bigger
Next Topic: How to deal with 'Free buffer waits' event
Goto Forum:
  


Current Time: Thu Mar 28 18:05:55 CDT 2024