Tuesday, June 15, 2010

Full table scan behavior in 11g

Before 11gr2, full table scan access path read all the blocks of a table (or index fast full scan) under high water mark into the buffer cache unless either "_serial_direct_read" hidden parameter is set to true or the table/index have default parallelism set.

In 11gR2, Oracle will automatically decide whether to use direct path reads bypassing buffer cache for serial full table scans.

For using direct path reads, Oracle first has to write any dirty blocks of the table to disk by issuing object level checkpoint and then read all the blocks from disk into the PGA of server process. If it has to undo any uncommitted transactions to provide read consistency or to do delayed block clean out, Oracle will use server process PGA to construct read consistent block buffers.

If block clean out has to be performed by the server process using direct path reads for full table scans, it won't write those modified blocks back to disk, so every time you perform full table scan using direct path reads it'd have to keep doing the same work of cleaning out the blocks for every execution. For this reason, it is recommended to manually clean out those blocks by performing full table scan without using direct path reads.

Following behavior was observed with my testing on 11.2.0.1:

Hidden parameter "_small_table_threshold" defines the number of blocks to consider a table as small. Any table having more blocks (about 5 times the value of "_small_table_threshold" if you leave it at default value) will automatically use direct path reads for serial full table scans (FTS).

Hidden parameter "_very_large_object_threshold" defines the upper limit of object size in MB for using direct path reads. There is no effect on FTS behavior just by setting this parameter alone.

Event 10949 set to any level will disable direct path reads for serial scans only if the size of an object is less then 5 times the size of buffer cache.

Combination of event 10949 and "_very_large_object_threshold" parameter will disable direct path reads for serial scans if the size of an object is less than 5 times the size of buffer cache or the value of "_very_large_object_threshold" is less than about 0.8 times the size of an object.

So, if you want to disable direct path reads for serial scans for any object, then set event 10949 at any level and set "_very_large_object_threshold" to greater than the size of largest object in MB.

Hidden parameter "_serial_direct_read" (or event 10355 set at any level) set to TRUE will enable direct path reads for all serial scans, unless the table is considered as small table and it's caching attribute is set (by issuing alter table xxxx cache). Remember that any sql statement already parsed and not using direct path reads will continue to do so unless hard parse is forced after setting these parameters. For this reason, it is better not to set these parameters.

It is not recommended to set any of the above mentioned hidden parameters if you want direct path reads to be used for serial scans, let Oracle decide dynamically based on the size of an object.

8 Comments:

At September 14, 2010 at 7:12:00 AM PDT, Blogger Dream World said...

Superb blog
Visit My Blog::
Fashion World

 
At October 4, 2010 at 5:28:00 AM PDT, Anonymous Oracle db said...

Excellent article.

 
At December 10, 2010 at 1:26:00 AM PST, Blogger Brijesh said...

nice article
for more visit www.oracledba.in

 
At November 1, 2011 at 9:49:00 PM PDT, Anonymous Era said...

Excellent Post

http://oracledbain.blogspot.com/

 
At November 4, 2011 at 10:32:00 AM PDT, Anonymous sap erp system said...

I really like your article. This post explain the new feature in 11g for full table scan. This will automatically decide whether to choose direct read bypassing or not. Thanks for sharing your experience of implementation. I am also going to try it.

 
At September 14, 2012 at 4:38:00 AM PDT, Anonymous RudeUrm said...

Let me try this out on one of our databases regarding changes of Oracle performance after switching to a new SAN. Looks like we have not taken this into account in our Oracle tuning efforts. Thx for that.

 
At September 14, 2012 at 4:43:00 AM PDT, Anonymous Oracle Tuning said...

Interesting new behaviour. This would explain why there is no Oracle performance improvement on similiar full table scan based queries.

 
At July 9, 2013 at 5:51:00 PM PDT, Anonymous Anonymous said...

Thanks Sai, very nice article. More information here on full table scans:

http://www.programmerinterview.com/index.php/database-sql/sql-full-table-scan/

 

Post a Comment

<< Home