Saturday, May 29, 2010

Read only tables in 11g is not fully read only.

When any large transaction is committed, it is quite possible that not all blocks it modified are cleaned out and these blocks have to be cleaned out before any data in them can be retrieved. But what happens if you open the database in read only mode, or make table/tablespace read only.

All the blocks that require a clean out will be done in shadow process PGA memory when parallel query option or direct path reads are used.

Blocks will be clean out but not written to disk when the database is opened in read only mode or tablespace in read only mode.

But after the table is put in read only mode, any further select queries against this table that require block cleanout (dirty blocks) will be written to the disk. In this sense, it is a surprise that read only table can have dirty buffers which will be written to disk. It is definitely the right thing to do.

Oracle can safely ignore any block clean out operations for read only tables and tablespaces as it is given that there can't be any other open DML transactions against them. I'll try to open an enhancement request for this.

Until this issue is fixed, make sure to manually clean out the blocks (by running full scans of the table) before you put any tablespace in read only mode to avoid repeated clean out of the same blocks later on which would incur more performance overhead.

Also, be aware that in 11g by default Oracle will use direct path reads for full table scans if the table size is large enough as dictated by _small_table_threshold/_very_large_object_threshold, you can use event 10949 at level 1 to disable direct path reads for serial full table scans (parallel query option will still use direct path reads).


At July 16, 2010 at 9:47:00 PM PDT, Blogger Paresh said...

Nice one...

At October 31, 2011 at 4:45:00 AM PDT, Anonymous sap upgrades said...

This is very informative post. I wasn't aware of these things. It happens when we put table in read only mode, some queries require clean out operation. This facility is provided by Oracle. This post surely enhances your knowledge about this concept.

At February 3, 2016 at 4:50:00 AM PST, Blogger Diya Patel said...

Best SQL Query Tuning Training Center In ChennaiIt’s too informative blog and I am getting conglomerations of info’s about Oracle interview questions and answer .Thanks for sharing, I would like to see your updates regularly so keep blogging.


Post a Comment

Links to this post:

Create a Link

<< Home