Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

What is the correct way to set the optimizer index cost adj parameter for Oracle. As a developer I have observed huge performance improvements as this parameter is lowered. Common queries are reduced from 2 seconds to 200ms. There are lots of warnings on the net that lowering this value will cause dire issues with the database, but no detail is given on what will start going wrong.

I am currently only seeing only an upside, much improved application performance and no downside. I need to better understand the possible negative repercussions of adjusting these parameters.

share|improve this question
add comment

3 Answers

The defaults for these 2 parameters are terrible for OLTP systems which are the most common type of database. They lead to more full table scans and bad queries. Generally you want to set these parameters BEFORE you go live. You do it in the test phase.

If you change them after you go live then you risk change other queries that were tuned to bad settings. It sounds like you do not know alot about database tuning since you mention response time instead of query plans. You should not touch these parameters.

Most DBAs don't grasp the difference in concepts between fix and design. After you are live, you are fixing and that is when you need to be careful changing these parameters. Before you go live in you are in design and development phase. That is when you adjust parameters like this.

BTW, a good place to start with these parameters (note BEFORE YOU GO TO PRODUCITON AND ONLY IF YOU KNOW WHAT YOU ARE DOING!)

optimizer_index_cost_adj=10 optimizer caching=90

This is for OLTPs. For batch processing the settings you want to start with are very different. I tinker with these a little, but those settings give me the best overall results 99% of the time on an OLTP. However, I do NOT touch them after we go to production. If they are bad, I leave them bad and tune the queries.

share|improve this answer
add comment

The reason it is not recommended to change this parameters is that they have db-wide impact on the optimizer - so when you change it to tune a specific query, it will likely have some impact on many other queries. So, changing it in production without carefully testing the entire app is dangerous.

However:

  1. Setting it in a dev/test environment, and staying with the same value in production might be acceeptible (used to be a common practice in OLTP systems). However, can you be sure that your app will run in a dedicated db? and will not be ever consolidated into another DB with a default set of parameters?
  2. The parameters help because Oracle uses some heuristics about relative cost of I/O versus CPU, and in your case the heuristics aren't good enough, so Oracle chooses sub-optimal execution plans. The recommended way to fix the heuristics is letting Oracle collect system statistics for your db machine - how fast is the CPU, how long does it take to get single block/multiple block from your I/O system during regular system load etc. See Oracle Documentation.

If you want to use both system statistics and the optimizer parameters, google it up, Jonathan Lewis wrote about it (sorry, the site doesn't let me post more than one link)

I hope that helps

share|improve this answer
add comment

The parameter should not be changed in a production environment. The main use is to force a plan change just to verify performance with different execution-plans. Basically you're suggesting to optimizer that all indexes in your database are cheaper to use than other access path. And this may be true for some sql and may be false for other ones.

Once you have a good performace plan , you should understand why optimizer don't use it and try to fix (i.e. no fresh/accurate statics are available -> collect fresh , more accurate stats ).

Hope this helps , Stefano

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.