Skip to Main Content
  • Questions

  • OPTIMIZER_INDEX_COST_ADJ at 1 and SIEBEL

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, David.

Asked: July 25, 2019 - 7:33 am UTC

Last updated: July 30, 2019 - 3:08 am UTC

Version: 12

Viewed 1000+ times

You Asked


Hello TOM,

I worked with SIEBEL and I was very very surprised to see that it was a recommendation of Oracle to set the parameter OPTIMIZER_INDEX_COST_ADJ at 1. In the Cloud Control, screen "SQL Monitor", there were horrible things in the columns E-ROWS and A-ROWS : for exemple always 1 in E-ROWS but 100, 1000 or 42M in A-ROWS...

In Metalink I read that it was really a recommendation of Oracle to set that parameter to 1 (even if it was writed that we could put it at 100 for some customers).

My question is : why have we to set this parameter to 1? We lie to the CBO and sometime the execution plan is a disaster. I read that when SIEBEL was tuned, this parameter was at 1, OK but why did they set it at 1? Is there a technical reason for that? Was it a mistake at this time?

Have a good day and thank a lot for your job,

David D.

and Connor said...

That reflects a historical position.

Siebel *used* to be written for the rule optimizer. As time went on, and the cost optimizer became mandatory, the Siebel code base stay heavily reflected its rule-based origins which has a huge bias toward indexes. Thus to avoid regression, the recommendation of that parameter kept the code leaning toward indexes.

However, times have changed. There is definitive guide about what to set for Siebel for each version of the database. You can find that at MOS note 2077227.2, where OPTIMIZER_INDEX_COST_ADJ = 1 still an option, but the defaults are also strongly recommended.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Perfect as usual

David D, July 26, 2019 - 7:41 am UTC


Thank you very much Connor, I knew there was a good reason about that but I didn't think about the RBO.

Good response, as usual :-)
Connor McDonald
July 30, 2019 - 3:08 am UTC

Glad we could help
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.