Skip to Main Content
  • Questions
  • DB_FILE_MULTIBLOCK_READ_COUNT in 11g

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Rajeshwaran.

Asked: November 25, 2014 - 6:40 am UTC

Last updated: November 26, 2014 - 8:46 pm UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Tom:

I am practising "Set DB_FILE_MULTIBLOCK_READ_COUNT to Reduce Full-Scan Costs" from Effective Oracle by design in Oracle 10g database.
I am aware that things changes over the version of oracle.

q1 - below is what i got from Trace file in 10g, i dont see P3 as you said in the book, How do i know how many blocks read in single IO (is that P3 renamed to "blocks" in trace, if so i am reading only 3 blocks per IO) ?
q2 - When i run this script in 11g, i dont see "scattered read" instead i see "direct path reads".
1) what is the different between "direct path reads" and "scattered read" ?
2) when can i one see "scattered read" in 11g database ?
---------- D:\APP10G.TXT
WAIT #2: nam='db file scattered read' ela= 4567 file#=8 block#=154798 blocks=3 obj#=235067 tim=4748052306
WAIT #2: nam='db file scattered read' ela= 16412 file#=8 block#=154802 blocks=3 obj#=235067 tim=4748358797
WAIT #2: nam='db file scattered read' ela= 7850 file#=8 block#=154806 blocks=3 obj#=235067 tim=4748538957
WAIT #2: nam='db file scattered read' ela= 4498 file#=9 block#=159115 blocks=3 obj#=235067 tim=4748860182
WAIT #2: nam='db file scattered read' ela= 374 file#=9 block#=159119 blocks=3 obj#=235067 tim=4749957890
WAIT #2: nam='db file scattered read' ela= 8091 file#=9 block#=159123 blocks=3 obj#=235067 tim=4750749103
WAIT #2: nam='db file scattered read' ela= 183 file#=9 block#=159127 blocks=2 obj#=235067 tim=4752921661
WAIT #2: nam='db file scattered read' ela= 9632 file#=8 block#=154812 blocks=3 obj#=235067 tim=4754122728
WAIT #2: nam='db file scattered read' ela= 9859 file#=8 block#=154816 blocks=3 obj#=235067 tim=4754733815
WAIT #2: nam='db file scattered read' ela= 8522 file#=8 block#=154820 blocks=3 obj#=235067 tim=4754775451
WAIT #2: nam='db file scattered read' ela= 493 file#=9 block#=159178 blocks=2 obj#=235067 tim=4755100403


Script used for this testing

exec dbms_monitor.session_trace_enable(waits=>true,binds=>true);
alter session set db_file_multiblock_read_count = 5000000;
set termout off
/* big_table has around 1M rows */ 
select * from big_table;
set termout on

and Tom said...

I would like to mention that tuning techniques from the turn of the century do not necessarily apply today! db_file_multi_block_read_count should be left at its default value (unset in the init.ora). If you allow it to default, Oracle will always attempt to read using your maximum IO size on your platform (typically 1mb) but it will cost queries using the observed IO size. That is - it'll will try to issue a 1mb read, but if it cannot (because some of the blocks are already in the buffer cache) - it'll remember how large the IO's were. It'll use the observed IO size to cost queries, but it will attempt to do the largest IO possible. If you set it - we won't be able to do that (we'll cost it and use IO sizes based on what you set it to - not good)

In 10g, they made the trace nicer to read. It says "blocks=3" in your output. there is p3. It knew that p1 is the file# and p2 is the block# and it translated them too.

Prior to 11g, a direct path read was something you would expect from parallel query. In parallel query, when we full scan, we checkpoint the segment we are scanning to disk (get any modifications on disk) and then we bypass the buffer cache and go straight to disk - reading the full IO size from disk into the PGA (not the cache).

Starting in 11g, at query execution time, the database will determine if it wants to do a conventional path read or a direct path read.

A conventional path read is from the buffer cache if it is there - else go to disk and then put into the buffer cache the blocks we need - which might not be able to do a full maximum IO size read (if you are reading blocks 1-64 and block 22 is in the cache, we'll have to do two IOs - one for blocks 1-21 and another for blocks 23-64)

A direct path read is as described above.

You might see a query use a conventional path read on one execution and a direct path read on another. It is an execution time decision based on many factors.

search for

direct path reads 11g

on the web for quite a few articles about this direct path read for serial queries.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.