Oracle数据库：如何获取已执行事务的平均大小？

方法1：基于AWR统计（周期平均） #

通过AWR的历史统计视图，计算指定时间段内的平均事务redo大小（redo能直观反映事务的修改规模，几乎所有事务修改都会产生redo）：

SELECT
    (SUM(DECODE(s.stat_name, 'redo size', s.value, 0)) / 
     SUM(DECODE(s.stat_name, 'user transactions', s.value, 0))) AS avg_transaction_redo_bytes
FROM
    dba_hist_sysstat s
JOIN
    dba_hist_snapshot sn ON s.snap_id = sn.snap_id
WHERE
    sn.begin_interval_time BETWEEN TO_DATE('2024-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') 
                              AND TO_DATE('2024-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS')
    AND s.stat_name IN ('redo size', 'user transactions');

说明：redo size是统计周期内所有用户事务产生的redo总字节数，user transactions是同期总事务数，两者比值即为平均每个事务的redo大小。

方法2：实时累计统计（基于V$SYSSTAT） #

如果需要查看实例启动以来的累计平均事务大小，或者自行计算时间段增量：

SELECT
    (ss1.value / ss2.value) AS avg_transaction_redo_bytes
FROM
    v$sysstat ss1,
    v$sysstat ss2
WHERE
    ss1.name = 'redo size'
    AND ss2.name = 'user transactions';

说明：若要计算某段时间的增量，可间隔一段时间两次查询，用两次结果的差值相除，避免累计值干扰。

方法3：基于物理IO的平均事务大小 #

如果关注事务涉及的磁盘读写规模，可结合物理IO统计与事务数：

SELECT
    ( (ss_read.value + ss_write.value) / ss_trans.value ) AS avg_transaction_io_bytes
FROM
    v$sysstat ss_read,
    v$sysstat ss_write,
    v$sysstat ss_trans
WHERE
    ss_read.name = 'physical reads'
    AND ss_write.name = 'physical writes'
    AND ss_trans.name = 'user transactions';

说明：该指标反映平均每个事务触发的物理IO总字节数，注意物理读包含缓存未命中的部分，与redo指标侧重点不同。

方法4：基于Undo的事务大小分析 #

通过Undo块统计事务的平均修改量，适合深入分析单事务规模：

SELECT
    avg(t.used_ublk * (SELECT value FROM v$parameter WHERE name = 'db_block_size')) AS avg_transaction_undo_bytes
FROM
    dba_hist_active_sess_history ash
JOIN
    dba_hist_transaction t ON ash.txn_id = t.txn_id AND ash.txn_addr = t.txn_addr
WHERE
    ash.sample_time BETWEEN TO_DATE('2024-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') 
                        AND TO_DATE('2024-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS');

说明：used_ublk是事务占用的Undo块数，乘以数据库块大小得到Undo字节数，平均值可反映事务的平均修改规模。

注意事项 #

• redo size是最通用的事务大小指标，覆盖绝大多数事务修改场景；
• 计算时间段平均时，务必用AWR快照的差值或两次V$查询的差值，避免实例启动以来的历史数据影响；
• 不同指标对应不同维度的事务规模，可根据实际监控需求选择。

内容的提问来源于stack exchange，提问作者Jack Gibson