En Oracle 10g Release 2, el valor por default de éste parámetro, es el valor que corresponde a la cantidad máxima de I/O que se puede realizar de forma más eficiente.
En ambientes OLTP o Batch, éste parámetro suele setearse en valores entre 4 y 16 bloques. En ambientes DSS o Data Warehouse, éste parámetro suele setearse en un valor mayor.
Este parámetro afecta al Optimizador de Costos (CBO) ya que si seteamos un valor alto, el CBO puede ser influenciado en elegir realizar un Full Scan en vez de acceder por índice. Como en los ambientes DSS o Data Warehouse suelen utilizarse planes de ejecución que incluyen Full Scan, aumentar el valor de éste parámetro puede ser beneficioso.
El máximo valor que podemos setear depende del sistema operativo.
La fórmula que se utiliza para buscar el valor de éste parámetro es:
DB_FILE_MULTIBLOCK_READ_COUNT = ( (MAX I/O SIZE) / DB_BLOCK_SIZE )
Si elegimos un valor mayor al máximo soportado, Oracle simplemente elige el valor máximo que puede utilizar.
Veamos un ejemplo de cómo buscar el valor máximo del parámetro DB_FILE_MULTIBLOCK_READ_COUNT sin utilizar la fórmula:
Primero, veamos el valor actual del MBRC:
SQL_9iR2> show parameter multiblock
NAME_COL_PLUS_SHOW_PARAM TYPE VALUE_COL_PLUS_SHOW_PARAM
------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 16
Observemos el Explain Plan de una de las tablas:
SQL_9iR2> explain plan for
2 SELECT /*+ full(test) noparallel(test) nocache(test) */ count(*)
3 FROM test ;
Explained.
SQL_9iR2> @explains
Plan hash value: 3740828345
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46709 (1)| 00:10:55 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TEST ACCESS FULL | TEST | 20M| 46709 (1)| 00:10:55 |
---------------------------------------------------------------------------------------
El costo de la consulta es 46709.
Veamos qué sucede si seteo el valor del parámetro muy alto:
SQL_9iR2> ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT = 50000 ;
Session altered.
SQL_9iR2> show parameter multiblock
NAME_COL_PLUS_SHOW_PARAM TYPE VALUE_COL_PLUS_SHOW_PARAM
------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 64
Bien, como podemos observar, seteamos el parámetro en 50.000 bloques; pero como excedemos el máximo permitido, Oracle setea el máximo valor que puede alcanzar.
Ahora veamos si Oracle realmente está utilizando el nuevo MBRC que acabamos de setear:
SQL_9iR2> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8' ;
Session altered.
SQL_9iR2> SELECT /*+ full(test) noparallel(test) nocache(test) */ count(*)
2 FROM test ;
COUNT(*)
----------
52673028
1 row selected.
SQL_9iR2> ALTER SESSION SET EVENTS '10046 trace name context off' ;
Session altered.
SQL_9iR2> @trace_file_name
TRACE_FILE_NAME
-----------------------------
testdb_ora_11379.trc
1 row selected.
[test@linux_test udump]$ cat testdb_ora_11379.trc | grep "scattered read" | awk '{ split ($11,listado,"="); print listado[2];}' | sort -n | tail -1
64
[test@linux_test udump]$ more testdb_ora_11379.trc | grep scattered
...
...
WAIT #1: nam='db file scattered read' ela= 1411 file#=115 block#=241927 blocks=64 obj#=70390 tim=1164040832168738
WAIT #1: nam='db file scattered read' ela= 1394 file#=115 block#=241991 blocks=64 obj#=70390 tim=1164040832171544
WAIT #1: nam='db file scattered read' ela= 1426 file#=115 block#=242055 blocks=64 obj#=70390 tim=1164040832174390
WAIT #1: nam='db file scattered read' ela= 1399 file#=115 block#=242119 blocks=64 obj#=70390 tim=1164040832177182
WAIT #1: nam='db file scattered read' ela= 1391 file#=115 block#=242183 blocks=64 obj#=70390 tim=1164040832179966
...
...
Bien. Vemos que efectivamente estamos leyendo 64 bloques en cada I/O que realizamos.
Ahora ejecutamos nuevamente nuestra consulta y observamos el Explain Plan:
SQL_9iR2> explain plan for
2 SELECT /*+ full(test) noparallel(test) nocache(test) */ count(*)
3 FROM test ;
Explained.
SQL_9iR2> @explains
Plan hash value: 3740828345
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41994 (1)| 00:09:49 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TEST ACCESS FULL | TEST | 20M| 41994 (1)| 00:09:49 |
---------------------------------------------------------------------------------------
Ahora el costo de la consulta pasa a ser 41994.
Hay que tener mucho cuidado si decidimos modificar éste parámetro ya que no siempre obtenemos un beneficio. Recuerden que modificando el MBRC por default puede producir que nuestro sistema funcione peor, igual o mejor. Por lo general, el MBRC por default suele ser el correcto.
Mi consejo es que no modifiquen el MBRC si no es necesario.
