Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Paremeter OPTIMIZER_MODE in Oracle 10g

Hello,

In Oracle 10g the default for OPTIMIZER_MODE is ALL_ROWS

The recommendation for this parameter is that it should not be explicitly set.

What problems can arise if you explicitly set OPTIMIZER_ROWS to ALL_ROWS?

Kind regards,

Peter

Former Member replied

Hello Peter,

DB_FILE_MULTIBLOCK_READ_COUNT is the only parameter where it makes a difference if the default (of 128) or the explicitly set value (of 128) is active. Only in the second case the CBO takes the parameter into account - in the first case the parameter DBFILE_OPTIMIZER_READ_COUNT is used instead (in order to decouple the I/O behavior from the CBO cost calculation). SAP note 750631 describes this "feature" (I don't think that Oracle considers the behavior as a bug).

This means: It doesn't matter if you explicitly set OPTIMIZER_MODE to ALL_ROWS or if you don't set it. For simplification purposes SAP prefers that this parameter is not explicitly set.

Regards

Martin

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question