Identify two effects of theDB_FILE_MULTIBLOCK_READ_COUN…

Identify two effects of theDB_FILE_MULTIBLOCK_READ_COUNTparameter on the
optimizer.(Choose two.)

Identify two effects of theDB_FILE_MULTIBLOCK_READ_COUNTparameter on the
optimizer.(Choose two.)

A.
Decreasing the value ofDB_FILE_MULTIBLOCK_READ_COUNTfrom the default
increases the cost of index probes for DSS workloads.

B.
A full table scan can become cheaper than index scans if the database instance has a high
enoughDB_FILE MULTIBLOCK_READ_COUNTfor both OLTP and DSS workloads.

C.
Increasing the value ofDB_FILE_MULTIBLOCK_READ_COUNTwithin OS limits
lowers the costingof an index probe that is done inconjunctionwith a nested loop for OLTP
workloads.

D.
In DSS workloads where full table scans may run in parallel and bypass the buffer cache,
decreasing the value ofDB_FILE_MULTIBLOCK_READ_COUNTfrom the default
increases the cost of full table scans.

E.
Increasing the value ofDB_FILE_MULTIBLOCK_READ_COUNTwithin OS limits
lowers the cost of full table scans and can result in the optimizer choosing a full table scan
over an index scan for both OLTP and DSS workloads.



Leave a Reply 8

Your email address will not be published. Required fields are marked *


vasya

vasya

imo B, D right

RobSala

RobSala

By definition B&D is the only correct options given! (

Oracool

Oracool

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

Have to choose either OLTP or DSS, it can’t be both. So, B, E are incorrect. A is wrong as it should say increase instead of decrease. I agree that C and D are the correct answer.

Larisa

Larisa

The documentation does not state it has to be either OLTP or DSS. I would say B,D,E are all correct, but E is more exact than B because of “within OS limits”. So, imo D+E is the correct answer.

job

job

B,E

A: wrong, nothing to do with the index cost
C:wrong, nothing to do with the index cost
D:wrong, with the default value this parameter is actually ignored (it shows the maximum number of blocks it might try to request from the OS) and will only consider these two:
_db_file_optimizer_read_count
_db_file_exec_read_count

therefore actually setting it to a lower value than the default can decrease the cost of a full scan.
source:
http://kerryosborne.oracle-guy.com/2010/01/autotuned-db_file_multiblock_read_count/