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.
imo B, D right
B,E
DE
DE
By definition B&D is the only correct options given! (
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.
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.
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/