How big db_file_multiblock_read_count ??

Purpose
This example shows what can be maximum MBRC. Setting more than this would be really giving the wrong information to optimizer.

Time Needed : 10min

Preparation

How to check what OS is able to read in one fatch. Setting too high of db_file-multiblock_read_count does not help on the other hand it tells optimizer to choose most of the time full scan even though the value is not correct.

Example
create table t storage ( initial 100m next 100m ) tablespace xxx as select * from all_objects;

create a table with a pretty large initial extent. We never will read more
then an extent at a time (eg: I initially did this test in a locally managed
tablespace with 512k extents — I read 64 8k blocks at a time, then realized “oh
yeah, thats my extent size”…)

insert /*+ append */ into t select * from t;
Commit;
insert /*+ append */ into t select * from t;
Commit;
insert /*+ append */ into t select * from t;
Commit;
insert /*+ append */ into t select * from t;
Commit;
alter session set db_file_multiblock_read_count = 1024;

now, set this paramter to something really really high…
alter session set events ‘10046 trace name context forever, level 12’; –set the tracing with wait events
select /*+ FULL(t) */ count(*) from t;
COUNT(*)
———-
365728

1. Test Case 1 (Look maximum block size for OS)

and full scan. Now, look at the trace file:

PARSING IN CURSOR #1 len=38 dep=0 uid=439 oct=3 lid=439 tim=3742056778
hv=194957013 ad=’86989890′
select /*+ FULL(t) */ count(*) from t
END OF STMT
PARSE #1:c=0,e=1,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=3742056778
BINDS #1:
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=3742056778
WAIT #1: nam=’SQL*Net message to client’ ela= 0 p1=1650815232 p2=1 p3=0
WAIT #1: nam=’file open’ ela= 0 p1=0 p2=0 p3=0
WAIT #1: nam=’db file scattered read’ ela= 14 p1=10 p2=2514 p3=128
WAIT #1: nam=’db file scattered read’ ela= 12 p1=10 p2=2642 p3=128
WAIT #1: nam=’db file scattered read’ ela= 10 p1=10 p2=2770 p3=128
WAIT #1: nam=’db file scattered read’ ela= 10 p1=10 p2=2898 p3=128
WAIT #1: nam=’db file scattered read’ ela= 12 p1=10 p2=3026 p3=128
WAIT #1: nam=’db file scattered read’ ela= 12 p1=10 p2=3154 p3=128
WAIT #1: nam=’db file scattered read’ ela= 10 p1=10 p2=3282 p3=128
WAIT #1: nam=’db file scattered read’ ela= 9 p1=10 p2=3410 p3=128

db file scattered read is our wait event — p3 is the number of blocks we
tried to read. Apparently on my OS+filesystem+db version, I can read 1m at a
time (128 * 8K block)…

Conclusion : Yes, setting db_file_multiblock_read_count to an unfeasibly high number can unduly affect the optimizer. If you can never obtain that read rate, and it
thinks you will, it could lead it down the incorrect path.

Backout Procedure

Drop table t;
Make sure you exit the current SQL Plus session which is laready tracing the events.

Advertisements

One Response to “How big db_file_multiblock_read_count ??”

  1. Christian BIERO Says:

    Hello

    Can you confirm that oracle do not set back db_file_multiblock_read_count to a maximum preset after the
    alter session set db_file_multiblock_read_count = 1024;

    My thought is that even if you try to set db_file_multiblock_read_count to 1024, the max value you can store is 128 (for a 8k block)… leading to 1M.

    so please add a
    select value from v$parameter where name = ‘db_file_multiblock_read_count’;
    after your alter session in your example.

    Regards

    Christian BIERO

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: