|Author's name: Mark J. Bobak
Author's Email: email@example.com
|Date written: 20th June 2001
Oracle version(s): 7.3, 8.0, 8i
|DML affecting a large number of rows is executed, consuming a significant portion of rollback tablespace. After the transaction commits, much (all?) of this space is still used in the rollback segment. Why? What's going on here?|
Back to index of questions
Oracle will do what it can, within reason, to leave the data allocated in the rollback segment. This is not necessarily a bad thing. The reason for this is to improve read consistency and attempt to avoid the infamous ORA-1555, "snapshot too old, rollback segment too small" which strikes fear into the hearts and minds of developers and DBAs around the world.
When you have two sessions connected to a database, and one is updating a table, and the other is reading that same table, Oracle is required to provide a read consistent view of the data in that table.
Consider the following example:
In session A: select emp_id, sal from emp order by emp_id;
In session B: update emp set sal=sal * 0.10; commit;
Now, if session A begins the select before session B commits its modifications, then the data returned in session A must be consistent with the state of the data when the select began, regardless of how long it takes to complete the select. In other words, none of the update can be seen by session A. So, session A may have to reconstruct the data as it appeared before session B modified it. In order to do so, it may have to refer to rollback. Note that this is true even if the commit occurs before the select completes. The select must view a snapshot of the data as it appeared when the select began execution.
So, after a large volume DML transaction, the amount of space allocated in the rollback tablespace could be significant, even after issuing a commit. This is required to support read consistent views of the data, as outlined above.
The solution, or compromise, is in the setting of the optimal parameter on your rollback segments. The optimal parameter will control how much the rollback segment will shrink. Consider a case where you create a rollback segment of 10M initial, 10M next, and 50M optimal, for example, and have a tablespace with 200M of space. Now, when you consume say, 150M of rollback in said rollback segment, and then commit, the 150M remains allocated. And it will remain allocated until another transaction comes along, and needs to allocate an extent, or an alter rollback segment segment_nameshrink is issued, or a shrink is triggered by SMON. When one of these events occurs, it will examine the rollback segment, and if there are not uncommitted transactions, deallocate those extents, until the value of optimal (50M, in this case) is left allocated.
Ideally, the sum of the optimal settings of all of your (non-system) rollback segments should be large enough to contain the transaction volume of your database measured for the length of time of your longest running query, on average. The total size of your rollback tablespace should be enough to contain the sum of the optimal settings of all your (non-system) rollback segments, plus enough space to extend a given rollback segment to satisfy the volume of your largest peak transaction.
So, in this way, Oracle attempts to strike a balance between rollback tablespace consumption and it's ability to achieve read consistency. As with most Oracle subjects, the reality is a bit more complex than I've described, but hopefully you find this a reasonable and logical introduction to a sound rollback segment strategy.
Further reading: Creating, Optimizing, and Understanding Rollback Segments (MetaLink Registration required)
Back to top
Back to index of questions