Configure the Database Environment to Support Optimal Data Access Performance

1. Documentación en Tahiti -> Masters Book List -> Performance Tuning Guide -> 8 I/O Configuration and Design

Documentación en Tahiti -> Masters Book List -> Database Installation Guide for Linux -> D Optimal Flexible Architecture

2. «Configurar el entorno de BD para soportar un rendimiento óptimo de acceso a los datos». Después de dar varias vueltas a lo que se pide en este punto, me sigue pareciendo una descripción muy ambigua para un objetivo. Así que voy a aprovechar este punto como cajón desastre de mis apuntes relacionados con el Acceso a Datos (I/O).

3. Los parámetros DB_BLOCK_SIZE y DB_FILE_MULTIBLOCK_READ_COUNT

Ya hemos hablado de DB_BLOCK_SIZE, y volvemos a ello de nuevo por la importancia que tiene. Me gusta las directrices que se nombran en la documentación (http://docs.oracle.com/cd/E11882_01/server.112/e16638/iodesign.htm#CHDEFEGC). Lo resumo aquí:

• Filas Pequeñas + Acceso Aleatorio => Tamaño de bloque pequeño (DB_BLOCK_SIZE = 8192)
• Filas Pequeñas + Acceso Secuencial => Tamaño de bloque grande (DB_BLOCK_SIZE = 16384 ó 32768)
• Filas Pequeñas + Acceso Aleatorio y Secuencial => Tamaño de bloque grande (DB_BLOCK_SIZE = 16384 ó 32768)
• Filas Grandes (Por. Ej. con objetos LOB) => Tamaño de bloque grande (DB_BLOCK_SIZE = 16384 ó 32768)

Esto no es más que una recomendación que suele funcionar bien. Al final depende de la aplicación/es específica/s que usa/n la BD. Os recuerdo que podemos tener la BD con un tamaño de bloque por defecto (el que más beneficio tenga por el tipo de acceso que se haga a la BD) y luego tener varios TABLESPACES con diferente de tamaño de bloque porque tenemos un conjunto de datos que se beneficia de ello.

Pongamos un ejemplo. Tenemos un Datawarehouse (DW) donde se registran todas las ventas de una empresa. Utilizamos esta BD para generar unos reports fantásticos. Por diferentes pruebas que hemos hecho en el entorno de desarrollo hemos visto que el tamaño de bloque óptimo es de 16K (16384). Tenemos un esquema en estrella (STAR SCHEMA, lo veremos en la sección de «Data Management») donde tenemos una gran tabla de datos (FACT TABLE) y luego varias tablas pequeñas relacionadas, llamadas tablas dimensionales (DIMENSION TABLES). Como nuestros informes suelen recorrerse la FACT TABLE enterita, nos interesa optimizar al máximo cada lectura trayéndonos a memoria el máximo de datos en cada operación, por eso creamos uno o varios TABLESPACES de 32K para alojarla. Sin embargo para las tablas dimensionales hacemos pocas lecturas que deben ser rápidas, luego podemos meterlas en un TABLESPACE de 8K. Resumiendo el ejemplo:

• Para la BD DW utilizamos => DB_BLOCK_SIZE = 16K
• Para las FACT TABLES utilizamos uno o varios TABLESPACES => DB_BLOCK_SIZE = 32K
• Para las DIMENSION TABLES utilizamos uno o varios TABLESPACES => DB_BLOCK_SIZE = 8K

El parámetro DB_FILE_MULTIBLOCK_READ_COUNT específica el número máximo de bloques que se pueden leer en una operación I/O durante un barrido secuencial de lectura (TABLE FULL SCAN o INDEX FAST FULL SCAN). Dicho de otra forma, es el número de bloques contiguos que puede leer la BD en una sola operación. Un valor alto propiciará planes de ejecución más pesados. Luego su valor tiene un efecto muy importante en la forma en la que la BD accede a disco. Este parámetro se calcula de la siguiente forma:

• db_file_multiblock_read_count = min(1048576/db_block_size , db_cache_size/(sessions * db_block_size))

Tom Kyte, en su libro «Effective Oracle by Design» hace un interesante ejercicio con este parámetro. Hacemos algo similar:

-- Fijaremos el valor DB_FILE_MULTIBLOCK_READ_COUNT a 1 y sacamos el coste de una consulta a la tabla SEG$
show parameter db_file_multiblock_read_count
SET AUTOTRACE TRACEONLY EXPLAIN
ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=1;
SELECT * FROM SEG$;

-- Ahora modificamos el valor del parámetro a 32 y volvemos a sacar el plan de ejecución para la misma consulta
-- Veremos como el coste (Cost %CPU) se ha reducido considerablemente
-- Cuando más alto sea el valor de este parámetro menor será el coste de hacer una operación de FULL SCAN
ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=32;
SELECT * FROM SEG$;

-- Ahora vamos a realizar el "truco" que utiliza Tom Kyte para obtener el valor real máximo de lectura de bloques de disco
-- Deshabilitamos el modo AUTOTRACE
 SET AUTOTRACE OFF

-- Primero creamos un tablespace donde meteremos un tabla muy grande (BIGTAB)
CREATE TABLESPACE TEST DATAFILE '/u01/app/oracle/oradata/OCM/test01.dbf' SIZE 512M;
-- Creamos la tabla BIGTAB (usamos un script de Tom Kyte)
CREATE TABLE BIGTAB TABLESPACE TEST AS SELECT ROWNUM ID, A.* FROM ALL_OBJECTS A WHERE 1=0;
-- Deshabilitamos el modo LOGGING de la tabla para no generar REDO innecesario
ALTER TABLE BIGTAB NOLOGGING;
-- Poblamos la tabla
DECLARE
  L_CNT NUMBER;
  L_ROWS NUMBER := 1000000;
BEGIN
  INSERT /*+ APPEND */ INTO BIGTAB SELECT ROWNUM, A.* FROM ALL_OBJECTS A;
  L_CNT := SQL%ROWCOUNT;
  COMMIT;
  WHILE (L_CNT < L_ROWS)
  LOOP
    INSERT /*+ APPEND */ INTO BIGTAB
    SELECT ROWNUM+L_CNT,
      OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED,
      LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME
    FROM BIGTAB
      WHERE ROWNUM <= L_ROWS-L_CNT;
    L_CNT := L_CNT + SQL%ROWCOUNT;
    COMMIT;
   END LOOP;
END;
/

-- Obtenemos el fichero de trazas de nuestra sesión
SELECT TRACEFILE FROM V$SESSION S, V$PROCESS P WHERE S.PADDR=P.ADDR AND S.SID=SYS_CONTEXT('USERENV','SID');

-- Activamos trazas para nuestra sesión y elevamos el valor del parámetro DB_FILE_MULTIBLOCK_READ_COUNT a un valor altísimo
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=1000000;
SET AUTOTRACE TRACEONLY

-- Lanzamos la consulta de nuevo forzando un FULL SCAN
SELECT /*+ noparallel(SEG$) full(SEG$) */ * FROM BIGTAB;

# Buscamos el parámetro 'blocks' del evento 'db file scattered read'
grep scattered /u01/app/oracle/diag/rdbms/ocm/OCM/trace/OCM_ora_18573.trc

-- Nos saldrán muchas lineas con el evento 'db file scattered read':
-- => WAIT #2: nam='db file scattered read' ela= 158 file#=7 block#=42163 blocks=8 obj#=74006 tim=1356020746340933
-- => WAIT #2: nam='db file scattered read' ela= 156 file#=7 block#=42433 blocks=8 obj#=74006 tim=1356020746341211
--
-- Como podemos ver, el máximo número de bloques que puede leer en nuestra VM son 8 bloques * 8192 Bytes = 64KB
-- Luego el parámetro adecuado para nuestro entorno sería DB_FILE_MULTIBLOCK_READ_COUNT = 8

-- Limpiamos el entorno
DROP TABLESPACE TEST INCLUDING CONTENTS AND DATAFILES;

4. En este punto ya hemos realizado bastante trabajo para optimizar el acceso a disco. Nos hemos basado en la arquitectura Optimal Flexible Architecture (OFA), donde se establece una jerarquía para los FS y directorios para la instalación de BD, DATAFILES, FRA, etc. Esta distribución permite un reparto adecuado de aplicaciones entre diferentes discos y permite que convivan varias instalaciones de Oracle con cierto orden. Adjunto la jerarquía de directorios que hemos seguido (como veréis, no hemos utilizado ‘/03’ y ‘/04’ pero sería recomendable en nuestro caso):

5. Como estamos utilizando Filesystems para alojar nuestros ficheros de datos, podemos utilizar este parámetro (FILESYSTEMIO_OPTIONS). Este parámetro tiene 4 valores posibles:

• DIRECTIO => Habilitamos sólo Direct I/O (consiste en evitar la cache de SO)
• ASYNCH => Habilitamos sólo operaciones asíncronas (reduce ciertos cuellos de botella)
• SETALL => Habilitamos operaciones asíncronas y Direct I/O
• NONE => Ninguna opción habilitada

-- Habilitamos la opción SETALL y reiniciamos la instancia
 ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=SETALL SCOPE=SPFILE;
 SHUTDOWN IMMEDIATE
 STARTUP

-- Comprobamos que hemos habilitado las operaciones I/O asíncronas
 COL NAME FORMAT A50
 SELECT NAME, ASYNCH_IO FROM V$DATAFILE F,V$IOSTAT_FILE I WHERE F.FILE#=I.FILE_NO AND FILETYPE_NAME='Data File';

-- Tenemos otra forma de comprobar y es verificar que los procesos utilizan las funciones de SO de AIO (Asynchronous IO)
 -- Para ello trazaremos las llamadas a sistema del proceso DBWR para buscar funciones AIO
 SELECT SPID FROM V$PROCESS WHERE PNAME='DBW0';

-- En otra sesión ejecutamos el siguiente comando de SO (con el SPID que hemos obtenido antes)
 -- /usr/bin/strace -p 22062
 -- El proceso DBWR debería utilizar las funciones asíncronas io_submit y io_getevens (en vez de pread y pwrite)
 -- io_submit(46919427829760, 29, {{0x2aac47ca77f8, 0, 1, 0, 20} ...
 -- io_getevents(46919427829760, 2, 128, {{0x2aac47ca67c0, ...
 -- Si no aparece ninguno porque tenéis la BD muy ociosa podéis forzar un CHECKPOINT con la siguiente instrucción:
 -- ALTER SYSTEM CHECKPOINT;
 -- En Metalink se hace referencia también a consultar el fichero slabinfo para comprobar que hay estructuras utilizadas por AIO
 -- cat /proc/slabinfo | grep kio # Ej.: "kioctx 26 36 320 12 1" y "kiocb 3 15 256 15 1"

-- Para comprobar si estamos utilizando Direct I/O, vamos a levantar la BD y sacar trazas nuevamente del proceso DBWR
 SHUTDOWN IMMEDIATE
 STARTUP MOUNT
 SELECT SPID FROM V$PROCESS WHERE PNAME='DBW0';

-- En otra sesión ejecutamos el siguiente comando de SO (con el SPID que hemos obtenido antes)
 -- /usr/bin/strace -p 22949

-- Levantamos la BD
 ALTER DATABASE OPEN;

-- Si revisamos el log de las trazas, veremos el flag O_DIRECT, indicando que hemos abierto el fichero en modo DIRECT I/O
 -- open("/u01/app/oracle/oradata/OCM/undotbs02.dbf", O_RDWR|O_SYNC|O_DIRECT) = 24

Nota importante! Cuando tenemos ASM no tenemos que preocuparnos del parámetro FILESYSTEMIO_OPTIONS (éste sólo aplica a FS). ASM nos brinda operaciones I/O asíncronas y Direct I/O de forma predeterminada.

6. Hay un tema concreto que podemos encajar en este objetivo. Se trata de responder a la siguiente pregunta ¿Cuál es el tamaño óptimo para los fichero REDOLOG ONLINE? Cada BD tienen una carga de transacciones diferente y la generación de REDO también varia de una BD a otra. La recomendación general es que entre cada SWITCH LOGFILE deben pasar al menos 20 minutos. Podemos ir al fichero ALERT de la BD y ver con que frecuencia se producen. Si es inferior a 20 minutos, deberíamos aumentar el tamaño de los REDOLOG. En BD con mucha carga, es fácil tener REDOLOGS de 2GB.

# Vamos a revisar nuestro log y de paso aprovechamos para utilizar el comando ADRCI
 adrci

# Vamos directamente a abrir nuestro log de BD
 show alert

# Aqui podemos buscar cada cuanto tiempo se produce un SWITCH de los REDOLOG "Thread 1 advanced to log sequence 94"
 # El tiempo entre cada uno no debería ser inferior a 20 minutos

Existe otra aproximación cuando hemos configurado el parámetro FAST_START_MTTR_TARGET. Este parámetro especifica el tiempo deseado de recuperación (CRASH RECOVERY) al levantar la BD cuando ésta no se ha cerrado ordenadamente. Cuando fijamos este parámetro, la BD empieza a ejecutar checkpoint incrementales periodicámente para que la recuperación antes una caída de BD no supere el tiempo establecido en FAST_START_MTTR_TARGET. El objetivo es priorizar la disponibilidad sobre el rendimiento, es decir, preferimos que la BD esté disponible lo más rápido posible a costa de una sobrecarga por los checkpoints. Al final, buscaremos un valor que equilibrado.

Cuando activamos el parámetro FAST_START_MTTR_TARGET se activa el MTTR Advisor, y parte de la información que nos da es el tamaño óptimo (que luego es es el mínimo) para el tamaño de los ficheros REDOLOG. Más información acerca del MTTR Advisor en http://docs.oracle.com/cd/E11882_01/server.112/e16638/instance_tune.htm#i1018945.

-- El parámetro FAST_START_MTTR_TARGET puede tener un valor entre 1 y 3600
 -- Supongamos que nuestra BD es super crítica y tiene que recuperarse lo antes posible ante un fallo
 -- Vamos a buscar el valor inferior siguiendo este procedimiento
 ALTER SYSTEM SET FAST_START_MTTR_TARGET=1 SCOPE=BOTH;
 SHUTDOWN IMMEDIATE
 STARTUP

-- El campo TARGET_MTTR nos indica el tiempo mínimo necesario para hacer el CRASH RECOVER
 -- El campo ESTIMATED_MTTR nos dice el tiempo que tardaríamos en levantar en la situación actual de la BD si cayera
 SELECT TARGET_MTTR, ESTIMATED_MTTR FROM V$INSTANCE_RECOVERY;

-- Como nos interesa únicamente la disponibilidad podríamos fijar el parámetro FAST_START_MTTR_TARGET con el valor de TARGET_MTTR
 -- Podemos analizar el valor que más nos interesa consultado la vista V$MTTR_TARGET_ADVICE
 ALTER SYSTEM SET FAST_START_MTTR_TARGET=9 SCOPE=BOTH;

-- También obtendríamos el tamaño recomendado (en MB) de los ficheros REDOLOG
 SELECT OPTIMAL_LOGFILE_SIZE FROM V$INSTANCE_RECOVERY;

-- Limpiamos el entorno y deshabilitamos el MTTR ADVISOR
 ALTER SYSTEM RESET FAST_START_MTTR_TARGET SCOPE=SPFILE;
 ALTER SYSTEM SET FAST_START_MTTR_TARGET=0 SCOPE=MEMORY;