After a year of being the sole DBA here I learned something incredibly
useful that I wish I had understood long ago. Almost all of my posts
that involve me fighting performance issues are resolved by this one
little parameter.
*.optimizer_index_cost_adj=50
Many years ago when I first started exploring parameters I was told not to mess with this one because it was already setup exactly as needed. Many of our parameters are like that. Set them once and deploy them like that 100x without any problems. This little bastard though...
What does this do? It's a parameter that controls the optimizer's preference of Index vs Full Table Scan. It has a range of 1-10000 and defaults to 100. Apparently setting it to 100 causes the optimizer to think that both explain plans would have equal fetch times... I'm not sure which engineer decided that should be the default but I am sure that he was hitting himself in the head with a hammer when he considered it a good idea.
We have 1-10 indexes for each data set that we import and nearly an infinite number of data sets. After setting this parameter to 50 my optimizer started using the indexes exclusively and now my longest running SQL isn't even considered problematic anymore. Yipee!
*.optimizer_index_cost_adj=50
Many years ago when I first started exploring parameters I was told not to mess with this one because it was already setup exactly as needed. Many of our parameters are like that. Set them once and deploy them like that 100x without any problems. This little bastard though...
What does this do? It's a parameter that controls the optimizer's preference of Index vs Full Table Scan. It has a range of 1-10000 and defaults to 100. Apparently setting it to 100 causes the optimizer to think that both explain plans would have equal fetch times... I'm not sure which engineer decided that should be the default but I am sure that he was hitting himself in the head with a hammer when he considered it a good idea.
We have 1-10 indexes for each data set that we import and nearly an infinite number of data sets. After setting this parameter to 50 my optimizer started using the indexes exclusively and now my longest running SQL isn't even considered problematic anymore. Yipee!
Apparently
1-10,000 is the 'cost' differential. So when the optimizer looks at
explain plan of FTS vs Index they each have a cost represented as a
numeric value 0-10000. This parameter is a baseline cost. The previous DBA here used to call this 'Oracle Dollars'. Actually he was Asian so it was more like 'Oracle Doroos'. In any case I finally understand what he meant by that as each SQL in the database that gets ran through optimizer has this 'cost' associated with it.
Playing with the optimizer is not something I'm very familiar with but that's my understanding as of right now :)
It's the Oracle Cost Based Optimizer that applies to any SQL running in the database. The hand tuning would be to rewrite the SQL to duplicate the most effective explain plan. I've never heard of disabling the CBO but I'm sure it can be done.
Playing with the optimizer is not something I'm very familiar with but that's my understanding as of right now :)
It's the Oracle Cost Based Optimizer that applies to any SQL running in the database. The hand tuning would be to rewrite the SQL to duplicate the most effective explain plan. I've never heard of disabling the CBO but I'm sure it can be done.
0 comments:
Post a Comment