Author's name: Jonathan Lewis
Author's Email: Jonathan@jlcomp.demon.co.uk
Date written: 26th July 2001
Oracle version(s): 7.3 - 8.1.7
I run a large data warehouse, with data partitioned by date and stored in tablespaces that are date-dependent. This means I can make 'old' tablespaces readonly. However I find that when trying to make a tablespace read only, the session seems to hang forever. What's going wrong ?
One of the manuals around the 8.0 period introduced a documentation bug regarding read only tablespace. When you issue the command
alter tablespace XXX read only;
there must be NO active transactions on the database. (One of the manuals erroneously states 'no active transactions on the tablespace'). If there are any active transactions, then the session will wait (rather than returning with error ORA-00054 resource busy and acquire with NOWAIT specified) until there are no outstanding transactions that started before the call to make the tablespace read only, and then convert the tablespace.
There are various reasons for this - chiefly that there is no way to determine cheaply which rows are currently locked. Any current transaction might have a lock (i.e. at least a bit set, if not an actual update) on a row in that tablespace. To identify such transactions, Oracle would probably have to freeze all transactions on the database and effectively roll them back to find out if they had any UNDO relating to that tablespace - the overhead could be enormous.
There is a beneficial side-effect to this restriction too. Oracle does not clean out all changed blocks at commit time, and some blocks will end up written to disc in a dirty state. These blocks are normally cleaned out the next time that they are read, but this puts them into a new, dirty, state that requires them to be written again, it can also be an expensive process because it can require Oracle to generate a historic image of the rollback segment header blocks. If a dirty block is found in a read only tablespace though, Oracle knows that it must have been committed before the tablespace was made readonly, and therefore never needs to worry about cleaning the block out and re-writing it - it has a good, cheap, approximation to the commit SCN from the SCN at which the tablespace went read only.
Thanks to a note from Ric Van Dyke of Hotsos, I have realized that the comments I made about the read consistency mechanism for read only tablespaces are wrong. Unfortunately I can’t find the test cases I originally used to discover where they went wrong, so I can’t tell if it was version dependency, a special case, or just plan wrong (I suspect the latter).
If you do a very large update in some tablespace – large enough to ensure that at least some of the updated blocks are flushed from the buffer - commit, and then make that tablespace read only, you will find that some of the blocks in the read only tablespace have not been cleaned out and can no longer be cleaned out. This means that whenever you read any of those blocks from disc Oracle will run through some of its clean-out code – specifically making a call to check the control table from the undo segment header identified in the ITL entry that your transaction used in the data block header (Call ktuwh05: ktugct for those who care about these things).
The overhead is not huge – but there’s no point in paying it. So consider forcing a scan of all objects in a tablespace before switching it to read only mode to make Oracle clean out any blocks that need it. Note – however, that there are at least a couple of reasons why this might be a pointless use of resources. For example, any blocks that were in memory and not cleaned out when you switched the tablespace to read only mode will be cleaned before being written. Moreover, if the contents of the tablespace have been created through ‘create as select’ they will have been created as clean. If there are any other reasons, I can’t think of them right now.
Further reading: N/A