11g rel 2 optimizer_index_cost_adj

We are on oracle 11.2.0.4 on a highly OLTP database and looking to improve overall performance using init.ora parameters. Currently optimizer_index_cost_adj  is 100. Is this good to keep 100 or it is better to test with a lower value like 25 ? I remember using it on old 9i and 10g databases. Is this still a good parameter to look or just leave this alone?
What is expert's advise on it?
OranewAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Geert GOracle dbaCommented:
ITSS !
It's The Stupid Sql's !

Look at your resource consumption for buffer gets and disk reads
AWR will give you a report on queries with high resource consumption

If you don't have diagnostic pack, you can do it manually with looking at v$sql
sorted by buffer_gets or disk_reads (and divide by exec count to get resource consumption rate per exec).
This will only be for all the queries in the sql cache at that moment.

Basically look at the highest resource consumers and it will reduce the load.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
Is there a reason you focused on optimizer_index_cost_adj?

>>If you don't have diagnostic pack, you can do it manually with looking at v$sql

Do not underestimate StatsPack.  It is still free and provides a LOT of information.  Some of it is even useful.
As far as your tuning question, I'll defer to the docs.
http://docs.oracle.com/cd/E11882_01/server.112/e41573/instance_tune.htm#PFGRF024

Step #1: Define the Problem

"Faster" isn't a tuning goal.

For example:
You have a query that isn't performing well.  You investigate and find creating a new index will increase performance by 25%.  In a very active OLTP database, that new index might slow down inserts by 1%.

Did you achieve your tuning goal?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.