Database News You Can Use
A monthly newsletter for Oracle users
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500
Ask DB Specialists: Queries That Generate Redo
You’re invited to submit your Oracle-related questions to us at email@example.com. Include your name, telephone number and problem description. If your question gets published, you’ll receive a free copy of our reference poster on Oracle Wait Events. So, stay tuned for future issues of The Specialist! Don’t Miss the Next NoCOUG Conference
This month’s question comes from Warren L. in Los Angeles, California: Why do queries sometimes generate redo?
Roger Schrag of the Database Specialists team responds: If you’ve ever used Auto Trace in SQL*Plus or a comparable feature in other tools, you may have noticed that sometimes queries generate redo entries. As rows of data are inserted, updated, and deleted from tables, Oracle writes entries into the redo log so that the data manipulations can be reapplied during recovery from a media failure or instance crash. However, why should a simple SELECT statement cause redo entries to be generated?
SELECT FOR UPDATE statements generate redo entries because Oracle must create locks to reserve the desired rows for update. Queries can also generate redo if auditing is enabled. If you don’t have auditing enabled and you find that a SELECT statement with no FOR UPDATE clause sometimes generates redo entries, you might be witnessing a case of delayed block cleanout.
When a user updates a row in a table, Oracle puts an entry in the header for the data block showing what part of the block has been changed and which rollback segment holds the undo information. If a second user runs a query that reads this block before the first user commits their change, the second user’s query will look to the rollback segment to see how the data appeared before the uncommitted update.
When a user commits a transaction, Oracle updates the rollback segment header accordingly. Oracle also attempts to find data blocks that were modified by the transaction and updates the headers in these blocks to indicate that the changes have been committed. We call this commit cleanout. Oracle keeps a short list of data blocks affected by each transaction in order to know which blocks need cleanout at commit time.
If a transaction updates too many blocks for Oracle to keep on its short list, or if an updated block is no longer in the buffer cache at commit time, or if a block is in the buffer cache but locked by another session, then Oracle may not clean out all affected blocks at commit time. We call these commit cleanout failures.
If Oracle fails to clean out a data block header at commit time, then that data block will incorrectly indicate that a portion of the block has been updated by an uncommitted transaction. The next time Oracle reads this data block, Oracle will detect the inconsistency in the block header and correct it. This is the delayed block cleanout. If Oracle discovers such a condition while processing a query, for example, Oracle will pause the query processing to perform the delayed block cleanout. This generates redo entries and slows down the query execution.
This explains why redo can be generated by a read operation such as a simple SELECT statement. You can measure commit cleanouts, commit cleanout failures, and delayed block cleanouts on your system by monitoring the v$sysstat and v$sesstat dynamic performance views–look for statistics with the word cleanout in their name. Commit cleanout was introduced in Oracle 7.3 and enhanced in Oracle 8i. Before then, nearly every update would cause a delayed block cleanout the next time the affected block was read.
You won’t want to miss the upcoming conference of the Northern California Oracle Users Group (NoCOUG). It’s scheduled for Thursday, November 13, 2003 in San Francisco, California. Some outstanding speakers will share their knowledge on topics for Oracle DBAs and developers. Here is a sampling of what’s in store: A Knowledge Center for Oracle Professionals
- Managing an Oracle Optimization Project, by Gary Goodman, Hotsos
- Frequently Asked Questions: A Smorgasbord of Common Questions and Problems Received by World Wide Support – and How to Resolve Them, by David Austin, Oracle Corporation
- How Statspack Was Used to Solve Common Performance Issues, by Brian Hitchcock, Sun Microsystems
- Help! I Got a Request for ANSI SQL – What Is It, and What Do You Do With It? by Walter Guerrero, Computer Associates
- What a DBA Needs to Know about Bitmap Indexing in Oracle to Retrieve Data Quickly, by Vilin Roufchaie, Cingular Wireless
For details and a complete schedule, check out NoCOUG’s website at http://www.nocoug.org.
Billing itself as “The Knowledge Center for Oracle Professionals,” DBA Support.com is full of Oracle resources. There is a community section that includes the subjects of scripts, forums, FAQs, and the OCP Zone. A Bit “About” Oracle
And, the following articles are just a sampling of those included on the home page:
- Preventing Corruption Before it’s Too Late: Part 1
- DBA Call to Action: Oracle Database Integrity
- Oracle Label Security, Part 1: Overview
- Tracking Data Access Patterns in Oracle
Check it all out at http://www.dbasupport.com/.
Here’s a quick listing of links on Oracle from the folks at About.com. They’ve selected topics that include:
- Book Review of Tom Kyte’s book: Expert One-on-One Oracle
- Top 5 Oracle Reference Books
- Bulletproofing, Backups, and Disaster Recovery Scenarios
- Oracle and XML
You’ll find these resources and more at http://databases.about.com/cs/oracle.