Paremeter OPTIMIZER_MODE in Oracle 10g

[peter_strauss]

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 · Accepted solution]

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

[peter_strauss · Accepted solution]

Thank you Martin.

Peter

[Former Member]

Dear friend

setting it does not do any thing Because optimizer_mode=ALL_ROWS is the default.

Shailesh Tiwari

[peter_strauss]

Hello Shailesh,

Are you 100% certain we wont run into a similar problem to the one when explicitly setting DB_FILE_MULTIBLOCK_READ_COUNT?

Peter

[Former Member]

Hi Peter

For 10g optimizer_mode=ALL_ROWS is the default. So explicitely setting it does not do any harm, it is just not necessary.

Regards

Michael

[peter_strauss]

Hi Michael,

Thank you.

I know that explicitly setting DB_FILE_MULTIBLOCK_READ_COUNT to the default can cause problems and I was wondering if the same thing might apply to OPTIMIZER_MODE.

Kind regards,

Peter

[Former Member]

...but re-setting DB_FILE_MULTIBLOCK_READ_COUNT (means using the default) will not harm you, when the default is 128 and you are already on 128.

DB_FILE_MULTIBLOCK_READ_COUNT is a good example because the default not always the same (depending on platform and block size):

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams047.htm#sthref199

Regards

MIchael

[peter_strauss]

I've read some training material that says you should not set DB_FILE_MULTIBLOCK_READ_COUNT even if set to it's actual value.

This is because the CBO only considers DB_FILE_MULTIBLOCK_READ_COUNT if it’s explicitly set. Otherwise the CBO will rely on the parameter DBFILE_OPTIMIZER_READ_COUNT (default: 😎 or the workload system statistics value MBRC (if workload statistics are created).

If it reads 128 instead of 8 then the cost of a full table scan decreases, making it more likely, which is something we don't want in an SAP system.

Regards,

Peter

[Former Member]

hm, i just checked, you seem to be right:

without DB_FILE_MULTIBLOCK_READ_COUNT my full scan on table MARC costed 55027. When i set it to 128 the costs, where only 20814.

I consider that as a bug, and obviously this is possible with the optimizer_mode parameter too.

Regards Michael

[peter_strauss]

Well, the guy from Oracle at training said it was normal behaviour!

It does seem strange to have a default value for DB_FILE_MULTIBLOCK_READ_COUNT if it isn't used.

[Former Member]

Wait...

<b>It does seem strange to have a default value for DB_FILE_MULTIBLOCK_READ_COUNT if it isn't used.</b>

We might still do bulk io operations of 128 blocks, i did not check that. I only verified, that the optimizer is calculating different costs. But this is leading too far

Regards Michael