Personally, I find it fascinating that, within Oracle, it is possible for a query to produce redo. In the previous chapter's discussion of the interested transaction list (ITL), I conveniently glossed over the fact that when a block cleanout occurs and the buffer changes, redo is generated. All it takes to trigger a cleanout (normally called a delayed block cleanout) is for the buffer to be touched by any session after a transaction commits. This causes the buffer to be cleaned out and the ITL flag to change from --U- to C---. The session that touches the buffer could be accessing the buffer as a result of a query. So a query can generate redo!

I understand that this is difficult to believe, so I created a simple script to demonstrate it, as shown in Figure 8-2. The script is executed while other sessions are making changes to the customers table. First, the script records the redo associated with my connected session. The v$mystat view's redo size statistic shows how much redo in bytes a connected session has generated since connection.

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.