Descubriendo los secretos detrás de los Oracle buffer busy waits

Uno de los problemas más confusos de Oracle es la resolución de eventos de espera ocupados en el búfer. Los eventos de espera ocupados en el búfer son comunes en un sistema Oracle con limitaciones de E/S, como se evidencia en cualquier sistema con esperas de lectura (secuencial/diseminada) en los primeros cinco eventos en el informe STATSPACK de Oracle, como este:

Eventos con mayor tiempo de espera:

La forma principal de reducir las esperas ocupadas en el búfer es reducir la E/S total en el sistema. Esto se puede lograr ajustando el SQL para acceder a las filas con menos lecturas de bloques (es decir, agregando índices). Incluso si tenemos un tamaño de db_cache_size grande, es posible que sigamos viendo esperas ocupadas en el búfer y aumentar el tamaño del búfer no ayudará.

Para analizar los eventos de espera en todo el sistema, podemos consultar la vista de rendimiento v$system_event. Esta vista, que se muestra en el listado A, proporciona el nombre del evento de espera, el número total de esperas y tiempos de espera, y el tiempo promedio de espera por evento.

Listado A: Vista v$system_event

SELECT event, total_waits, time_waited, average_wait
FROM v$system_event;

El tipo de búfer que causa la espera se puede consultar utilizando la vista v$waitstat. Esta vista muestra las esperas por tipo de búfer para las esperas ocupadas en el búfer, donde COUNT es la suma de todas las esperas para la clase de bloque y TIME es la suma de todos los tiempos de espera para esa clase.

SELECT *
FROM v$waitstat;

Las esperas ocupadas en el búfer ocurren cuando una sesión de Oracle necesita acceder a un bloque en la memoria caché del búfer, pero no puede hacerlo porque la copia en memoria caché del bloque de datos está bloqueada. Esta condición de espera ocupada en el búfer puede ocurrir por una de las siguientes razones:

• El bloque está siendo leído en el búfer por otra sesión, por lo que la sesión en espera debe esperar a que se complete la lectura del bloque.
• Otra sesión tiene el bloque de búfer bloqueado en un modo incompatible con la solicitud de la sesión en espera.

Debido a que las esperas ocupadas en el búfer se deben a conflictos entre bloques específicos, no se puede hacer nada hasta que se sepa qué bloques están en conflicto y por qué se producen los conflictos. Por lo tanto, la optimización implica identificar y eliminar la causa de la contención de bloques.

Cómo ahorrar espacio en disco utilizando campos calculados en una base de datos

La vista de rendimiento v$session_wait, que se muestra en el listado B, puede brindar ideas sobre qué se está esperando y por qué se produce la espera.

Listado B: Vista v$session_wait

SELECT
p1 "Número de archivo",
p2 "Número de bloque",
p3 "Código de razón"
FROM
v$session_wait
WHERE
event = 'buffer busy waits';

Las columnas de la vista v$session_wait que son de particular interés para un evento de espera ocupada en el búfer son:

• P1: El número absoluto de archivo para el archivo de datos involucrado en la espera.
• P2: El número de bloque dentro del archivo de datos referenciado en P1 por el que se está esperando.
• P3: El código de razón que describe por qué se produce la espera.

Aquí tienes una consulta del diccionario de datos de Oracle para estos valores:

SELECT
p1 "Número de archivo",
p2 "Número de bloque",
p3 "Código de razón"
FROM
v$session_wait
WHERE
event = 'buffer busy waits';

Si la salida de ejecutar repetidamente la consulta anterior muestra que un bloque o rango de bloques está experimentando esperas, la siguiente consulta debe mostrar el nombre y tipo del segmento:

SELECT
owner,
segment_name,
segment_type
FROM
dba_extents
WHERE
file_id = &P1
AND
&P2 BETWEEN block_id AND block_id + blocks -1;

Una vez que se identifica el segmento, la vista de rendimiento v$segment_statistics facilita la monitorización en tiempo real de las estadísticas a nivel de segmento. Esto permite a un DBA identificar problemas de rendimiento asociados con tablas o índices individuales, como se muestra en el listado C.

Listado C: Vista v$segment_statistics

SELECT
owner,
object_name,
statistics_name,
value
FROM
v$segment_statistics;

También podemos consultar la vista dba_data_files (listado D) para determinar el nombre de archivo (file_name) del archivo involucrado en la espera utilizando el valor P1 de v$session_wait para el file_id.

Ventajas y desventajas de las bases de datos ISAM frente a las bases de datos relacionales

Listado D: Vista dba_data_files

SELECT
file_id,
file_name
FROM
dba_data_files
WHERE
file_id = &P1;

Interrogar el valor P3 (código de razón) de v$session_wait para un evento de espera ocupada en el búfer nos dirá por qué la sesión está esperando. Los códigos de razón van del 0 al 300 y se pueden descifrar, como se muestra en la Tabla A.

Tabla A: Códigos de razón para esperas ocupadas en el búfer

Como mencioné al principio de este artículo, las esperas ocupadas en el búfer son frecuentes en sistemas con limitaciones de E/S. La contención de E/S, que resulta en esperas de bloques de datos, a menudo se debe a numerosas sesiones que leen repetidamente los mismos bloques, como cuando muchas sesiones analizan el mismo índice. En este escenario, la sesión uno analiza rápidamente los bloques en la memoria caché del búfer, pero luego un bloque debe leerse desde el disco. Mientras la sesión uno espera a que se complete la lectura del disco, otras sesiones que exploran el mismo índice pronto alcanzan a la sesión uno y desean el mismo bloque que se está leyendo desde el disco. Aquí es donde ocurre la espera ocupada en el búfer, esperando los bloques de búfer que se están leyendo desde el disco.

Las siguientes reglas generales pueden ser útiles para resolver cada una de las situaciones de contención mencionadas:

• Contención de bloques de datos: Identificar y eliminar los bloques "calientes" de la aplicación cambiando los valores de PCTFREE y/o PCTUSED para reducir el número de filas por bloque de datos. Verificar los índices explorados repetidamente. Dado que cada transacción que actualiza un bloque requiere una entrada de transacción, aumentar el valor de INITRANS.
• Contención de bloques de listas de liberación: Aumentar el valor de FREELISTS. Además, cuando se utiliza Parallel Server, asegurarse de que cada instancia tenga su propio grupo de FREELIST.
• Contención de encabezado de segmento: Nuevamente, aumentar el número de FREELISTS y utilizar grupos de FREELIST, lo cual puede marcar la diferencia incluso dentro de una sola instancia.
• Contención de encabezado de deshacer: Aumentar el número de segmentos de rollback.

Conclusiones:

La identificación y resolución de las esperas ocupadas en el búfer pueden ser muy complejas y confusas. Oracle proporciona la vista v$segment_statistics para ayudar a monitorizar las esperas ocupadas en el búfer y las vistas v$system_event para identificar los bloques específicos para la espera ocupada en el búfer. Si bien identificar y corregir las causas de las esperas ocupadas en el búfer no es un proceso intuitivo, los resultados de sus esfuerzos pueden ser muy gratificantes.

Cómo especificar valores predeterminados en una tabla de Oracle

En Newsmatic nos especializamos en tecnología de vanguardia, contamos con los artículos mas novedosos sobre Gestión de datos, allí encontraras muchos artículos similares a Descubriendo los secretos detrás de los Oracle buffer busy waits , tenemos lo ultimo en tecnología 2023.