Figure 8-1. Shown is a script that will report a session's redo generation since connect, sleep for 5 seconds, touch every block in the customers table, and then again report on a session's redo generation since connect. The redo generation statistic (redo size) number depends on the Oracle release. In this script, the redo size statistic number is 133. Of course, the where clause could have filtered based on the statistic name.

The redo size statistic number depends on the Oracle release, so I always double-check. This script was run on an Oracle Database 10g Release 1 system, which assigns the redo size statistic as number 133. In Oracle Database 11g Release 1, the redo size statistic number is 140. While Oracle does occasionally change a statistic number, changing the name is even less likely. Therefore, the query in Figure 8-1 could easily have filtered on the statistic name instead of its number.

After the initial redo bytes-generated value is reported, the session sleeps for 5 seconds while other sessions are modifying the customers table. When the session wakes up, it then performs a full-table scan on the customers table, which touches every block in the table, and then once again gathers how many redo bytes the session has generated. If the customers table had changes committed while the session slept, and the session was the first to touch the changed buffer containing at least one committed row, we would expect to see redo associated with the session. Figure 8-2 shows what actually happened.