Cómo calcular transacciones por segundo (TPS) a partir de AWR en Oracle

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