martes, 16 de octubre de 2007

DB_FILE_MULTIBLOCK_READ_COUNT (MBRC)

El parámetro DB_FILE_MULTIBLOCK_READ_COUNT especifica la cantidad de bloques que van a ser leídos en cada I/O a través de un Full Scan.

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.

6 comentarios:

Anónimo dijo...

buen dia

Encontre su blog recientemente y esta muy interesante pero quisiera solicitarle que escribiera uno que explicara cada uno de los diferentes tipos de acceso que se realiza al momento de generar el plan de ejecucion, en otras palabras que explique que es un full scan, un index range scan, etc y su 'categorizacion' Muchas gracias

Leonardo Horikian dijo...

Hola,

En la documentación se explica claramente cada uno de los tipos de accesos:

10gR2 --> http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#i82080

Saludos.

Anónimo dijo...

Leonardo, muchisimas gracias por la explicación referente a MBRC.

Saludos cordiales,

Rodrigo,
Chile

lesanch dijo...

Hola Leonardo,

Gracias ante todo por el blog que es genial. Quería preguntarte referente a este tema pues tengo una mega sentencia que corre dos vez al día cada 12 horas para depurar un histrórico, con un costo=70,967; bytes=20,985,096 y cardinalidad=723,624; en este caso cuando cambio a 64 el valor que por defecto está en 16 baja el costo, pero mi pregunta es si sería conveniente dejarlo así o puede repercutir negativamente en las otras transacciones.

lesanch dijo...

me falto agradecerte y muchos saludos

Leonardo Horikian dijo...

Hola lesanch,

Generalmente, este parámetro suele modificarse en los ambiente de Data Warehouse. En los ambientes OLTP el valor por defecto suele ser el más conveniente.

Si modificas este parámetro a nivel de instancia, todas las consultas de todas las sesiones de la base de datos se verán afectadas y esto puede impactar negativamente en el ambiente.

Cuando analizas una consulta, no te bases en el costo, sino en el "trabajo" que hace la consulta. Lo más importante, es ver la cantidad de bloques lógicos y físicos que se estan leyendo.
Sobre este tema hablo en el post "¿Tunear en base al COSTO del plan de ejecución?". Te recomiendo que lo leas ya que te será de gran utilidad.

Saludos