Oracle DB optimizer configuration

On a project, I had a SQL query which was already tuned and working file. However, Oracle optimizer was chosing the full tale scan in place of indexes on the production system. After setting the following system properties, the behavior of the optimizer is changed as we wanted:

optimizer_index_cost_adj=10
optimizer_index_caching=90

If you experience that Oracle prefers full table scans in place of indexes, you could use these parameters to tune the database.

This parameters should be used with caution and only if you really need it.

You could use Oracle em console for diagnosing the database first.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s