Cacheo automático de grandes tablas

Resumen

Automatic Big Table Caching es la técnica por la cual la base de datos usa una parte dedicada de la buffer cache exclusivamente para tablas medianas/grandes en SGA en lugar de enviarlas a la PGA usando direct path reads. El artículo describe su funcionamiento, parámetros, ejemplos de pruebas, monitorización y recomendaciones.

Introducción

Desde la versión 12.1.0.2, cuando se hace un full scan de tablas grandes, Oracle suele usar lecturas directas (direct path reads) enviando bloques a la PGA para salvaguardar la buffer cache (SGA), aunque así se pierde reutilización entre ejecuciones.

La ventaja, es que no se "lava" la buffer cache, en la buffer cache el mecanismo LRU desalojaría a todos los bloques anteriores al realizarse un full scan de una tabla muy grande. Al ser enviados esos bloques a una work area de la PGA evita el lavado de la buffer cache.

El inconveniente, que la PGA no trabaja con LRU, con lo que una vez terminada la consulta la memoria de la PGA se libera y una sesion posterior tendrá que volver a cargar todos los bloques en la PGA de nuevo, leyendo todos esos los bloques desde los discos físicos.

En el caso de tablas medianas, la base de datos puede considerar aplicar la solucion anterior, saltando la SGA y mandándo los bloques a la PGA, o bien enviarlos a la buffer cache pero al final de la lista LRU, con lo que serán los primeros en salir. Esto se puede cambiar definiendo o alterando la tabla como CACHE o usando el hint CACHE. En ese caso, los bloques entrarán en el mecanismo LRU de forma normal.

Una tercera opción es usar cachear la tabla en el buffer keep. Esta opción siempre se ha contemplado para tablas pequeñas y muy accedidas.

Este artículo se centrará en el mecanimo de Automatic Big Table Caching, pensado para que tablas medianas y grandes usen la buffer cache beneficiándose de la reutilización de bloques y de manera no traumática para el resto de datos.

Versiones a las que aplica

El Automatic Big Table Caching se introduce en 12.1.0.2 en Enterprise Edition. En single instance funciona con full scans y parallel query, en RAC solo aplica para parallel query.

Funcionamiento

Se usa un mecanismo de temperatura para tablas medianas y grandes sobre las que se realicen Full Table Scans (FTS), Fast Full index Scans (FFS) o Parallel queries (PQ), que devuelvan un porcentaje grande de registros de la tabla.

No funcionará para consultas por índices con where selectivos (Table Access By Index ROWID), que no devuelvan un porcentaje grande de datos de la tabla.

La big table cache, se localiza dentro de la buffer cache, es un area fuera del mecanismo LRU controlada por un mecanismo de temperatura a nivel de objeto.

Constantatemente se está evaluando el acceso mediante escaneos masivos (Full Table Scans (FTS), Fast Full index Scans (FFS)), se asigna un número a modo de temperatura inicial que suele ser 1000, y comienzan a cachearse bloques físicos en la Big Table Cache, a medida que nuevos escaneos acceden a la tabla, la temperatura aumenta.

Si con el tiempo la tabla deja de recibir Full table Scans, la temperatura empieza a decrecer.

Cuando la Big Table Cache esté al 100% Oracle comparará las temperaturas de los nuevos segmentos a cachear con los existentes, saliendo los bloques de la tabla mas fría y entrando los de la tabla mas caliente.

Eso se sigue en los campos TEMPERATURE y POLICY(MEM_ONLY,MEM_PART y DISK) de la vista GV$BT_SCAN_OBJ_TEMPS.

Configuración

Se definen dos parámetros para que el Automatic Big Table Caching funcione.

• DB_BIG_TABLE_CACHE_PERCENT_TARGET. Es el % de la buffer cache que se reservará para big table caching. El valor por defecto es 0 (deshabilitado), debe configurarse a un valor mayor que 0 y menor que 90.

• PARALLEL_DEGREE_POLICY. Debe ser AUTO o ADAPTIVE, el valor por defecto es MANUAL, con ese valor está deshabilitado el Automatic Degree of Parallelism.

En RAC el Automatic Big Table Caching, funcionará solo en parallel query. En instancia simple funciona tambien en serial query, pero en ambos casos deben estar configurados los dos parámetros descritos.

Los dos parámetros son dinámicos. Al ser configurados, la base de datos empezará automáticamente a usar el Automatic Big Table Cache.

Tablas y segmentos candidatos

Las tablas candidatas a entrar en la Big Table cache son decididas por la base de datos, por su tamaño y la operación que se realiza sobre ellas. Pueden ser tablas particionadas, en ese caso se considerarán las particiones como segmentos independientes.

• Tamaño: Oracle considera tablas medianas y grandes aquellas que superan el 2% del buffer cache. Esto es controlado por el valor del parámetro oculto _small_table_threshold (Nota oracle KB81352)

• Uso: en RAC y single instance se considerán las tablas medianas y grandes que usen parallel query (PQ). En instancia simple se tendrán en cuenta las operaciones de paralllel query y los full scans nombrados anteriormente.

Mantenimiento de la Big Cache Area

• Hot Objects: El mecanismo por el que se mantiene la Big Cache Area NO ES LRU, es por "hot" objects. Cada vez que un objeto es accedido, oracle aumenta un contador, "su temperatura". En caso de llegar al 100% de la Big Cache Area, son los objetos de temperatura mas alta los que prevalecen en la Big Cache Area y los de temperatura inferior los que salen.

• Cacheo Parcial, se permite un cacheo parcial de tablas o particiones, si solo un porcentaje de la tabla cabe en memoria, la base de datos puede elegir dejar el resto en disco.

• Comportamiento en RAC: Cada instancia tiene su Big Table Cache. Si la tabla es mayor que la cache de una instancia, pueden tener distintos fragmentos de tabla cacheada las distintas caches de las distintas instancias del RAC.

Caso práctico

Se va a configurar una single instance 19c Enterprise Edition con ASMM (Automatic Shared Memory Management), sobre linux.

Parametrizacion previa:

SGA_MAX_SIZE= 6g

SGA_TARGET= 0

Se configuran los dos parámetros necesarios, DB_BIG_TABLE_CACHE_PERCENT_TARGET y PARALLEL_DEGREE_POLICY

ALTER SYSTEM SET PARALLEL_DEGREE_POLICY = 'ADAPTIVE' SCOPE=BOTH;

ALTER SYSTEM SET DB_BIG_TABLE_CACHE_PERCENT_TARGET = 30 SCOPE=BOTH;

Se fuerza un full table scan durante 2000 ejecuciones sobre una tabla de tamaño medio :

DECLARE
    v_count NUMBER;
BEGIN
    FOR i IN 1..2000 LOOP
        EXECUTE IMMEDIATE 'SELECT /*+ FULL(e) PARALLEL(e, 4) */ COUNT(id) FROM HR.OPERATION_LOG e' INTO v_count;
    END LOOP;
END;
/

y se verifica

Han entrado tanto la tabla de la prueba como otra que no para de consultarse usando full scan en el esquema Sales.

Las tablas van entrando y saliendo en la big data cache en función de su temperatura. No hay que configurar ningún parámetro de ellas.

Monitorización

Hay varios factores que tienen que tenerse en cuenta para que la implementacion de la big data cache sea provechosa.

• Verificar el buffer cache hit ratio. La big data cache es un porcentaje de la database buffer cache, por lo tanto ese espacio se le está quitando al resto de consultas. Hay que monitorizar el ratio de aciertos del buffer caché (1 -physical reads/ (db block gets +consistent gets)) , en un entorno OLTP o mixto no conviene que este ratio baje a costa de la big data cache.

SELECT 
    inst_id,
    SUM(CASE WHEN name = 'db block gets' THEN value ELSE 0 END) AS db_block_gets,
    SUM(CASE WHEN name = 'consistent gets' THEN value ELSE 0 END) AS consistent_gets,
    SUM(CASE WHEN name = 'physical reads' THEN value ELSE 0 END) AS physical_reads,
    ROUND(
        (1 - (SUM(CASE WHEN name = 'physical reads' THEN value ELSE 0 END) / 
             (SUM(CASE WHEN name = 'db block gets' THEN value ELSE 0 END) + 
              SUM(CASE WHEN name = 'consistent gets' THEN value ELSE 0 END)))
        ) * 100, 
    2) AS buffer_cache_hit_ratio
FROM 
    gv$sysstat
WHERE 
    name IN ('db block gets', 'consistent gets', 'physical reads')
GROUP BY 
    inst_id
ORDER BY 
    inst_id;

 

En este ejemplo el buffer cache hit ratio ha bajado demasiado despues de configurar el Big Data cache.

• Tablas candidatas a entrar, son las tablas que cumplen por tamaño ya no son consideradas pequeñas ( por defecto, un 2% de la buffer cache), que sobre ellas existan operaciones de lectura Full Table Scans (FTS), Fast Full index Scans (FFS) o Parallel Query (PQ) que disparen el evento direct path read.

• Estas tablas candidatas aparecen como CACHE_POLICY a DISK y con al menos 1000 puntos de temperatura

SELECT 
    t.inst_id AS 'NODE',
    o.owner AS 'OWNER,
    o.object_name AS 'OBJECT_NAME',
    t.size_in_blks AS  'TOTAL_BLOCKS',
    t.temperature AS 'TEMPERATURE',
    t.policy AS 'CACHE_POLICY'
FROM 
    gv$bt_scan_obj_temps t,
    dba_objects o
WHERE 
    t.dataobj# = o.data_object_id
    AND t.cached_in_mem = 0
ORDER BY 
    t.inst_id,
    t.temperature DESC;

• Uso de la big data cache, mediante las vistas del sistema debe verse que la big table cache se usa convenientemente, y que el mecanismo de temperatura deja fuera a las tablas que debe dejar.

• Las tablas que están dentro de la big data cache y su porcentaje

SELECT 
    t.inst_id AS 'NODE',
    o.owner AS 'OWNER',
    o.object_name AS 'OBJECT_NAME',
    t.size_in_blks AS 'TOTAL_BLOCKS',
    t.temperature AS 'TEMPERATURE',
    t.policy AS 'CACHE_POLICY',
    t.cached_in_mem AS 'BLOCKS_IN_MEM',
    ROUND((t.cached_in_mem * TO_NUMBER(p.value)) / 1048576, 2) AS 'MEM_SIZE_MB'
FROM 
    gv$bt_scan_obj_temps t,
    dba_objects o,
    gv$parameter p
WHERE 
    t.dataobj# = o.data_object_id
    AND t.inst_id = p.inst_id
    AND p.name = 'db_block_size'
    AND t.cached_in_mem > 0
ORDER BY 
    t.inst_id,
    t.temperature DESC, 
    t.cached_in_mem DESC;

• Estado Global de la big table cache

SELECT 
    c.inst_id as 'NODE' ,
    c.con_id AS 'CON_ID',
    ROUND(d.current_size / 1048576, 2) AS 'BUF_CACHE_MB',
    c.bt_cache_target AS 'TARGET_PCT',
    ROUND((d.current_size / 1048576) * (c.bt_cache_target / 100), 2) AS 'TARGET_MB',
    ROUND(((c.memory_buf_alloc * TO_NUMBER(p.value)) / d.current_size) * 100, 2) AS 'ALLOC_PCT',
    ROUND((c.memory_buf_alloc * TO_NUMBER(p.value)) / 1048576, 2) AS 'BT_ALLOC_MB',
    ROUND((NVL((SELECT SUM(t.cached_in_mem) FROM gv$bt_scan_obj_temps t WHERE t.inst_id = c.inst_id), 0) * TO_NUMBER(p.value)) / 1048576, 2) AS 'USED_MB',
        NVL(ROUND((NVL((SELECT SUM(t.cached_in_mem) FROM gv$bt_scan_obj_temps t WHERE t.inst_id = c.inst_id), 0) / NULLIF(c.memory_buf_alloc, 0)) * 100, 2), 0) AS 'USED_PCT',
    c.object_count AS 'TRACKED_OBJS'
FROM 
    gv$bt_scan_cache c,
    gv$parameter p,
    gv$sga_dynamic_components d
WHERE 
    c.inst_id = p.inst_id
    AND p.name = 'db_block_size'
    AND c.inst_id = d.inst_id 
    AND d.component = 'DEFAULT buffer cache'
ORDER BY
    c.inst_id;

Aquí se bajó el DB_BIG_TABLE_CACHE_PERCENT_TARGET a 20 desde 30, por eso el ALLOC_PCT (% asignado) es mayor que el TARGET_PCT % objetivo.

• Eventos direct path read, aparecen con full scans a PGA, si son muchos, puede que el valor de la big table cache sea pequeño. Esto puede verse en la sección Foreground Wait Events de un AWR.

Resumen

En entornos no exadata, configurando el Automatic Big Table Caching, puede evitarse el uso de direct path reads hacia la PGA reutilizando el uso de bloques en memoria y a la vez conservar un buffer hit ratio alto en la SGA sin tener que cambiar manualmente las tablas ( no se usa el CACHE/KEEP).

Vistas y consultas usadas.

Las vistas que Oracle proporciona para monitorizar la Big area cache son

• v$bt_scan_cache: Muestra el tamaño exacto, la fonfiguracion y el estado de la big table cache.

• v$bt_scan_obj_temps: Muestra el contenido de la big table cache, el estado de las tablas en ella y su temperatura.

Derivadas de estas vistas y junto con las dba y v$ tradicionales se han construido los scripts usados en este artículo.

• chk_buffer_cache_ratio.sql , buffer cache hit ratio.

• chk_big_tables_in.sql , para ver que tablas estan o han estado en la big cache.

• chk_big_tables_candidates.sql tablas candidatas a entrar

• chk_big_table_cache.sql , estado de la big table cache.

• Secciones waits events de los informes AWR, secciones top sql de los informes ASH.

Notas y documentacion oficial

• Como norma general, Oracle desaconseja su uso en EXADATA, ya que el uso de la Big Table Cache invalida el Smart Scan de las celdas al no usar direct path read.

• smallness logic, cuando se considera pequeña una tabla, parametro _small_table_threshold: Nota KB81352, How does Oracle load data into the buffer cache for table scans ? Al ser un parámetro interno, oracle puede cambiar su funcionamiento y valores sin previo aviso. Revisad la nota referida antes de hacer cualquier valoración.

• Oracle Database Performance Tuning Guide 19c (Doc ID: E96175-14)

• Oracle Database Administrator’s Guide 19c (Doc ID: E96368-16)

Licencias y disponibilidad

La Big Table Cache está disponible en Enterprise Edition a partir de Oracle 12.1.0.2

No requiere licencia adicional para ser usada.

Consideraciones

• Las consultas que aparecen en el artículo se han elaborado a partir de la documentación oficial de Oracle y se ofrecen como ejemplos y herramientas de apoyo al diagnóstico. Se facilitan para su uso y adaptación, pero su aplicación en entornos productivos debe validarse previamente por el responsable del sistema o un DBA; cualquier modificación que afecte a la producción es responsabilidad del usuario. Para casos críticos o dudas, consulte la documentación oficial de Oracle y/o Oracle Support (MOS). Si detecta errores o desea proponer mejoras, agradeceremos sus informes o contribuciones mediante issues o pull requests.

• Los problemas son reales, los datos no.