En ocasiones necesitamos analizar la carga transaccional real de una base de datos Oracle, más allá de los informes AWR estándar. Un buen enfoque es calcular las transacciones por segundo (TPS) directamente a partir de las métricas históricas que guarda Oracle en sus vistas de AWR.
En esta entrada te muestro cómo hacerlo paso a paso.
¿De dónde salen los datos?
Oracle guarda en la vista DBA_HIST_SYSSTAT distintos contadores acumulados. Para el caso que nos interesa:
user commits → número de commits ejecutados por los usuarios.
user rollbacks → número de rollbacks ejecutados.
Estos valores son acumulativos desde que se inició la instancia. Para obtener la actividad en un intervalo debemos calcular el delta (diferencia entre snapshots consecutivos).
La consulta
/* ---------------------------------------------------------
Calcular transacciones por segundo (TPS) desde AWR
usando las métricas de DBA_HIST_SYSSTAT y DBA_HIST_SNAPSHOT
--------------------------------------------------------- */
WITH snaps AS (
SELECT s.dbid,
s.instance_number,
s.snap_id,
-- Redondeamos el inicio del intervalo al minuto
TRUNC(s.begin_interval_time, 'MI') AS snap_time,
-- Diferencia de tiempo entre snapshots (en segundos)
(EXTRACT(DAY FROM (s.begin_interval_time - LAG(s.begin_interval_time)
OVER (PARTITION BY s.dbid, s.instance_number ORDER BY s.snap_id))) * 86400) +
(EXTRACT(HOUR FROM (s.begin_interval_time - LAG(s.begin_interval_time)
OVER (PARTITION BY s.dbid, s.instance_number ORDER BY s.snap_id))) * 3600) +
(EXTRACT(MINUTE FROM (s.begin_interval_time - LAG(s.begin_interval_time)
OVER (PARTITION BY s.dbid, s.instance_number ORDER BY s.snap_id))) * 60) +
(EXTRACT(SECOND FROM (s.begin_interval_time - LAG(s.begin_interval_time)
OVER (PARTITION BY s.dbid, s.instance_number ORDER BY s.snap_id))))
AS interval_sec
FROM dba_hist_snapshot s
),
stats AS (
SELECT t.dbid,
t.instance_number,
t.snap_id,
-- Calculamos delta para commits y rollbacks
GREATEST(NVL(t.value - LAG(t.value)
OVER (PARTITION BY t.dbid, t.instance_number, t.stat_name ORDER BY t.snap_id),0),0)
AS delta_tx
FROM dba_hist_sysstat t
WHERE t.stat_name IN ('user commits', 'user rollbacks')
),
calc AS (
SELECT sn.snap_time,
sn.instance_number,
SUM(st.delta_tx) / NULLIF(sn.interval_sec,0) AS tx_per_sec
FROM snaps sn
JOIN stats st
ON st.dbid = sn.dbid
AND st.instance_number = sn.instance_number
AND st.snap_id = sn.snap_id
WHERE sn.interval_sec IS NOT NULL
GROUP BY sn.snap_time, sn.instance_number, sn.interval_sec
)
SELECT TO_CHAR(c.snap_time,'YYYY-MM-DD HH24:MI') AS minuto,
c.instance_number,
ROUND(c.tx_per_sec,2) AS transactions_per_sec
FROM calc c
ORDER BY minuto DESC, instance_number;
Interpretación
Cada fila representa la tasa de transacciones por segundo de una instancia en un minuto concreto.
Si tu base de datos es RAC, verás cada instancia por separado.
Puedes sumar las TPS de todas las instancias para tener una visión global de la base de datos.
Conclusión
Esta consulta es muy útil cuando queremos:
Ver la evolución de la actividad transaccional en detalle.
Comparar instancias de un RAC.
Correlacionar picos de carga con eventos específicos (deploys, cierres contables, etc.).
Con AWR ya tienes todos los datos, solo hace falta un poco de SQL para explotarlos
