Thanks for the question, Gustavo.

Asked: May 26, 2003 - 4:32 pm UTC

Last updated: January 14, 2008 - 3:34 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hello Tom,

I trying to set up an optimal value for db_file_multiblock_read_count on two Oracle instances I'm running on the same box (O.S. version is Compaq Tru64 V5.1A.) and both instances are 8.1.7.3..
For one hand ODS instance has db_block_size = 8K whereas MOVICS3 instance has db_block_size = 32 K.
For the other hand, Tru64 max_iosize_read is 65536.
I run the following commands on ODS instances:

ODS>alter session set db_file_multiblock_read_count = 500;

Session altered.

ODS>select value from v$parameter where name='db_file_multiblock_read_count';

VALUE
----------------------------
32

1 row selected.

ODS>alter session set events '10046 trace name context forever, level 8';

Session altered.

ODS>select /*+ FULL(a) */ count(*) from sys.source$ a;

COUNT(*)
----------
40712

1 row selected.

Viewing the trace generated above, I see ODS is reading 10 blocks by trip :

...
WAIT #1: nam='db file scattered read' ela= 2 p1=1 p2=762 p3=10
WAIT #1: nam='db file scattered read' ela= 2 p1=1 p2=948 p3=10
WAIT #1: nam='db file scattered read' ela= 2 p1=1 p2=966 p3=10
...

Then, I go ahead with MOVICS3 instance:

MOVICS3>alter session set db_file_multiblock_read_count=500;

Session altered.

MOVICS3>select value from v$parameter where name='db_file_multiblock_read_count';

VALUE
------------------------
128

1 row selected.

MOVICS3>alter session set events '10046 trace name context forever, level 8';

Session altered.

MOVICS3>select /*+ FULL(a) */ count(*) from sys.source$ a;

COUNT(*)
----------
436772

1 row selected.

Now, watching the trace generated I see MOVICS3 instance is reading 25 blocks per trip:
...
WAIT #1: nam='db file scattered read' ela= 13 p1=1 p2=1859 p3=25
WAIT #1: nam='db file scattered read' ela= 13 p1=1 p2=1909 p3=25
WAIT #1: nam='db file scattered read' ela= 10 p1=1 p2=1949 p3=25
...

My question is:

Why ODS instance is able to read 320K (10 blocks * 32K) while instance
MOVICS3 reads 200K (25 block * 8K) since they are running simultaneously on the same box?.
Is there any way for reaching the 1MB limit defined by SSTIOMAX on both instances?

Your answer will help. Thanks

And your answer really helped me...

Here you are sizes for extents:

ODS>select bytes/1024, count(*)
2 from dba_extents where owner='SYS' and
3 segment_name='SOURCE$'
4 group by bytes/1024
5 order by 1;

BYTES/1024 COUNT(*)
---------- ----------
64 1
320 36

2 rows selected.

MOVICS3>select bytes/1024, count(*)
2 from dba_extents where owner='SYS'
3 and segment_name='SOURCE$'
4 group by bytes/1024
5 order by 1;

BYTES/1024 COUNT(*)
---------- ----------
16 1
200 281
224 1

3 rows selected.

Thanks a lot.

and Tom said...

what are the EXTENT SIZES for sys.source$ on your systems?

For example, on my one 817 system with 8k blocks:

1 select bytes/1024, count(*)
2 from dba_extents where owner = 'SYS'
3 and segment_name = 'SOURCE$'
4 group by bytes/1024
5* order by 1
sys@ORA817DEV> /

BYTES/1024 COUNT(*)
---------- ----------
16 1
200 147
224 1

200k represents most of the table as far as extents go. We read maximally by EXTENT size (eg: a read never spans extents). perhaps your are being restricted by your extent size.

Rating

(28 ratings)

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

Comments

A reader, May 31, 2003 - 12:12 pm UTC

Hi Tom,

I have some confusion on trace 10046 level 8.

SQL> alter session set db_file_multiblock_read_count =128;

Session altered.

Elapsed: 00:00:00.50
SQL>  alter session set events '10046 trace name context forever, level 8';

Session altered.

Elapsed: 00:00:00.60
SQL> select /*+ FULL(a) */ count(*) from sys.source$ a;

  COUNT(*)
----------
    853391

Elapsed: 00:00:09.30
SQL>  alter session set events '10046 trace name context off';


Total Time to ecxecute statement is 00:00:09.30 If you see trace file

PARSING IN CURSOR #1 len=50 dep=0 uid=18 oct=3 lid=18 tim=0 hv=165126988 ad='4385298'
select /*+ FULL(a) */ count(*) from sys.source$ a
END OF STMT
PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1111838976 p2=1 p3=0
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=584 p3=4
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=2557 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=6013 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=6365 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=6621 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=6877 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=7069 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=7453 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=7869 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=8157 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=8605 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=8765 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=8893 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=9213 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=9533 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=9725 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=9853 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=9981 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=10109 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=10237 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=10368 p3=125
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=10493 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=11133 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=11389 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=12669 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=12829 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=12957 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=13213 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=13405 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=13533 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=14045 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=14173 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=14365 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=14493 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=14621 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=15005 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=15453 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=16989 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=17181 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=17437 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=17629 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=17949 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=18077 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=18461 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=18653 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=18973 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=19293 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=19549 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=19805 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=19933 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=20061 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=20381 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=20765 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=20893 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=21277 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=21533 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=21661 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=21789 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=21917 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=22045 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=22173 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=22813 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=23069 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=23197 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=23517 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=27325 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=27773 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=27965 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=28221 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=28349 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=28669 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=28797 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=29117 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=29245 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=29501 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=29629 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=29949 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=30269 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=30525 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=30653 p3=4
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=30913 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=31041 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=31297 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=31617 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=31745 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=31873 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=32001 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=32449 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=32641 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=32769 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=32897 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=33025 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=33153 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=33281 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=33921 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=34049 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=34177 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=34305 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=34433 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=35009 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=35137 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=35521 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=35649 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=35905 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=36353 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=36673 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=36929 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=37057 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=37313 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=37441 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=37569 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=38081 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=38209 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=38337 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=38657 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=38785 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=38913 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=39041 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=39169 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=39617 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=39745 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=40193 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=40386 p3=127
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=40641 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=40769 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=41089 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=41345 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=41473 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=41793 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=42049 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=42177 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=42305 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=42689 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=42945 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=86017 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=98529 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=103521 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=104801 p3=5
FETCH #1:c=0,e=0,p=17289,cr=17292,cu=18,mis=0,r=1,dep=0,og=4,tim=0
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1111838976 p2=1 p3=0
FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=0
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1111838976 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1111838976 p2=1 p3=0
STAT #1 id=1 cnt=1 pid=0 pos=0 obj=0 op='SORT AGGREGATE '
STAT #1 id=2 cnt=853391 pid=1 pos=1 obj=64 op='TABLE ACCESS FULL SOURCE$ '
=====================
PARSING IN CURSOR #1 len=57 dep=0 uid=18 oct=42 lid=18 tim=0 hv=1975444627 ad='441e848'
 alter session set events '10046 trace name context off'
END OF STMT
PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0


Elapsed Time is zero in all waits..

Question => If elapsed time is zero in all wait then why i am getting 9 second elapsed time in sql*plus.

Thanks

May 31, 2003 - 1:13 pm UTC

do you have timed statistics enabled.

and ela will be in 1/1000th (in 8i) or 1/1000000th of a second. If they all complete in less than that period of time, they'll all be zero as well.

db_file_multiblock_read_count behaviour in 9i

A reader, September 16, 2003 - 12:17 am UTC

Tom,
DB_FMRC is suggested to be set to maxphys/db_block_size so that FTS and Fast Index scans can scoop in optimum chunks. If the table/index happens to be in a tablespace having a different db_block_size than the default ( using the 9i feature of having multiple block size tablespaces ) how do we drive oracle to derive the parameter based on the object tablespace's block size? OR we have to take care thru alter session?

Thanks as always...

September 16, 2003 - 8:01 am UTC

I cover this anomoly in my new book "Effective Oracle by Design". There I show that db_file_multiblock_read_count is used at start up to compute "default block size" * "mb read count" to come up with a "size"

so, say your block size is 8k and you set your mb read count to 64. We'll read 512k (if the OS permits it) at a time.

We'll read 512k from a 2k blocksize tablespace in that database
We'll read 512k from a 16k blocksize tablespace in that database

all of them will read 512k at a time -- since we effectively computed a read size based on the mb read count * default block size.

A reader, September 17, 2003 - 3:31 pm UTC

Tom,
If *extent size* is less than the "size" does oracle breakup the reads.Say, just for example, if "size" is 512k and extent size is 64k and if oracle has to do a 1 MB FTS,
would it take 16 (would small extent size become speed bumps?) or 2 reads?

Thanks as always...

September 17, 2003 - 5:49 pm UTC

we read in but not across extents, so yes, that small extent would cause the read size to go down.

A reader, September 18, 2003 - 11:05 am UTC

so can we derive:
For big objects the extent size should be "size" or its multiples.

Going by your good recommendation of small, medium, large TBS, we should ensure that atleast the large TBS sastisfies the above derivative

Thanks as always...

September 18, 2003 - 11:15 am UTC

for big objects you plan on full scanning.....

A reader, November 25, 2003 - 9:22 pm UTC

Hi Tom,

Question on setting db_file_multiblock_read parameter in RULE base optimizer..

We are using siebel CRM application and it is using RULE based optimizer. We've lots of siebel query doing Index range scan. We have db_file_multiblock_read parameter set to 16 (Default).

We are using Oracle 8.1.7.4 with sun solaris 2.8. We've 8k block size. MAX. i/o performed by solaris is 64(block)*8 ..

Question : Can I set db_file_multiblock_read to 64 or 128 ... in rule base optimizer since this is not counted in execution plan (like cost based optm..) ?

If yes, what are side effects of high db_file_multiblock_read ?

Thanks

November 26, 2003 - 7:38 am UTC

the RBO doesn't care about anything other then its rules (and an important rule to it is "if there is an index we'll use it").

it cares not at all about dfmbrc.

dfmbrc will only come into play when the plan includes a full scan -- since you are using index range scans -- it matters not what you set it to as it

a) isn't being used
b) won't affect the rbo

suggest you leave it out of the init.ora all together.

additional book reference - Thanks again.

bob, April 01, 2004 - 8:54 am UTC

This conversation isn't complete without also pointing you to p448-453 in Tom's book where he points out that the value reported for the maximum size that your system can do in the trace file, *might* not be actually ideal.

Tom, it would be nice if this section was co-located with p.321-325. I read the book in its entirety last week and wanted to come back to the section on p448 on dbfmbrc later, but couldn't find it. The index only pointed me to 321-325 with regards to db_file_multi. Since I read the entire book in a short time span my mental indexing process misplaced the rowid.

Great book. I learned a lot of practical things this past week. Thank you.

db_file_multiblock_read

Sam, May 04, 2004 - 5:23 pm UTC

If I set a "large" value for db_file_multiblock_read_count it might influence the optimizer not to consider using index. How large a value for this parameter that would influence the optimizer? Thanks.

May 04, 2004 - 7:38 pm UTC

depends on many factors -- clustering factor, existence (or not) of system stats, optimizer_index_* settings, the alignment of the moon with Jupiter (only kidding on that one)......

suggestion: let it at the default, set it explicitly for large batch jobs you expect to be full scanning frequently via alter session in that application.

db_file_multiblock_read_count optimal size

Parag J Patankar, April 19, 2005 - 2:30 am UTC

Hi Tom,

I am trying to set db_file_multiblock_read_count parameter by setting up trace event and very high value for this parameter such as 50000000 ...etc. I am trying to set optimal parameter for

After this when I am trying to grep trace file I found for p3 there are some different values

WAIT #1: nam='db file scattered read' ela= 785 p1=5 p2=71110 p3=63
WAIT #1: nam='db file scattered read' ela= 781 p1=5 p2=71174 p3=63
WAIT #1: nam='db file scattered read' ela= 781 p1=5 p2=71238 p3=63
WAIT #1: nam='db file scattered read' ela= 780 p1=5 p2=71302 p3=63
WAIT #1: nam='db file scattered read' ela= 777 p1=5 p2=71366 p3=63
WAIT #1: nam='db file scattered read' ela= 779 p1=5 p2=71430 p3=63
WAIT #1: nam='db file scattered read' ela= 775 p1=5 p2=71494 p3=63
WAIT #1: nam='db file scattered read' ela= 775 p1=5 p2=71558 p3=63
WAIT #1: nam='db file scattered read' ela= 771 p1=5 p2=71622 p3=63
WAIT #1: nam='db file scattered read' ela= 775 p1=5 p2=71686 p3=63
WAIT #1: nam='db file scattered read' ela= 264 p1=5 p2=71751 p3=23
WAIT #1: nam='db file scattered read' ela= 169 p1=5 p2=71775 p3=15
WAIT #1: nam='db file scattered read' ela= 169 p1=5 p2=71791 p3=15
WAIT #1: nam='db file scattered read' ela= 168 p1=5 p2=71807 p3=15
WAIT #1: nam='db file scattered read' ela= 168 p1=5 p2=71823 p3=15
WAIT #1: nam='db file scattered read' ela= 169 p1=5 p2=71839 p3=15
WAIT #1: nam='db file scattered read' ela= 171 p1=5 p2=71855 p3=15
WAIT #1: nam='db file scattered read' ela= 167 p1=5 p2=71871 p3=15
WAIT #1: nam='db file scattered read' ela= 169 p1=5 p2=71887 p3=15
WAIT #1: nam='db file scattered read' ela= 169 p1=5 p2=71903 p3=15
WAIT #1: nam='db file scattered read' ela= 169 p1=5 p2=71919 p3=15
WAIT #1: nam='db file scattered read' ela= 168 p1=5 p2=71935 p3=15
WAIT #1: nam='db file scattered read' ela= 168 p1=5 p2=71951 p3=15
...etc

Why it is showing different values for p3 ? Secondly for my db_file_multiblock_read_count which parameter I should set up for optimal perfomance ?

I am doing this test on Oracle 9iR2 with ASSM

regards & thanks
pjp

April 19, 2005 - 7:31 am UTC

you can use the trace to find the MAX size, the optimal size is something entirely different :)

when you do a multi-block IO, some of the blocks may be in the buffer cache already. So, lets say Oracle says "lets read 64 blocks starting from file 5, block 17"

it is saying "I need blocks 17 .. 80, but wait, block 17 is in the cache, lets read 63 blocks starting at block 18". So it'll read 63 blocks

Later it says "I could read upto 64 blocks, but this extent only has 15 blocks left, lets read 15 of them"

Last it says "I could read upto 64 blocks, I want the 64 blocks from file 10 starting at block 1, but blocks 2, 4, 6, 8, 10, ..... 64 are in the cache. I'll do 32 single block IO's to pick up the rest"

So, you might (in order to find the max IO size on your system)

a) offline the tablespace (will flush cache of blocks for that tablespace)
b) online the tablespace
c) enable trace
d) full scan
e) exit sqlplus
f) scan trace file for max p3= value....

as long as your extents are bigger than your IO size, you'll have it.

the suggestion is to leave all parameters at a default setting unless you can really identify a good reason for changing them

db_file_multiblock_read_count optimal size

Parag J Patankar, April 19, 2005 - 2:30 am UTC

stripe size

reader, August 20, 2005 - 12:27 pm UTC

When we chose the stripe size (RAID), what should be the recommended size if my multi_block_read_count is set at 64? Thanks.

August 20, 2005 - 5:16 pm UTC

min( 64 * block_size, IO_MAX_SIZE_ON_YOUR_OS ) might make sense.

db_file_multiblock_read_count

Vikas Khanna, April 06, 2006 - 1:47 pm UTC

Hi tom

If the value of p3 = 128 during the wait event 'db file scattered read' is the

db_file_multiblock_read_count = 128
Or
db_file_multiblock_read_count = 128/8 = 16 if the given Oracle block size is 8K.

I think it should be 128, but still needs your confirmation to be doubly sure!

Thanks

April 07, 2006 - 8:17 pm UTC

p3 is the number of blocks read.

</code> http://docs.oracle.com/docs/cd/B19306_01/server.102/b14237/waitevents003.htm#sthref4386 <code>

The question remains unanswered

Vikas, April 09, 2006 - 1:51 am UTC

Hi Tom,

So what should be the value of db_file_multiblock_read_count parameter?

Thanks

April 09, 2006 - 8:52 am UTC

"the default" unless and until you can come up with a really good reason to modify it.

I actually read and re-read your posting above. I simply see "no question to be answered" regarding setting the value for this parameter.

Actually, in looking at what was posted:

....
If the value of p3 = 128 during the wait event 'db file scattered read' is the
db_file_multiblock_read_count = 128
Or
db_file_multiblock_read_count = 128/8 = 16 if the given Oracle block size is 8K.
I think it should be 128, but still needs your confirmation to be doubly sure!
Thanks
........

the only question I see is "is the db_file_multiblock_read_count set to X or Y" and the only sensible answer is "you cannot tell - p3 tells the number of blocks actually observed to be read - it doesn't tell you what the multiblock read count was set to"

It is LIKELY your multiblock read count is set to 128 here (unless you are using multiple block size tablespaces - then it gets nasty - but assuming you weren't)...

Value of db_file_multiblock_read_count

Vikas Khanna, April 10, 2006 - 3:31 am UTC

Hi Tom,

If you see in the trace the value of p3 to be 128 in the case of 'db scattered read', then my assumption is that Oracle is trying to read 128 blocks in a single I/O.

This means that oracle running on Linux O/S can worth read 128*8K = 1M of data in a single I/O during Full table/Index scans.

I just wanted to verify that the parameter value needs to be set = 128 to take advantage of this parameter during these scans. Questions in my mind:

1. If I set this parameter to too high,does the optimizer gives more weightage to Full scans rather than UNIQUE index scan OR RANGE SCANS.

2. What value does the db_file_multiblock_read_count to be set to take full advantage,
a) 128
b) 16. ie 128/8 (since 8K is the default block size)

April 10, 2006 - 5:49 am UTC

p3 is the number of blocks ACTUALLY READ - it is not the number of blocks "attempted to have been read", but the number of blocks we actually did.

that it is 128 LIKELY means - multiblock read cound was 128. But does not prove it.

IF you want 128 blocks to be read at a time
THEN
you must set multiblock read count to 128
END IF

1) higher values will decrease the cost of a full scan - YES it may. So it would in fact, give "less weight" not more.

big_table@ORA9IR2> set autotrace traceonly explain
big_table@ORA9IR2> alter session set db_file_multiblock_read_count=1;

Session altered.

big_table@ORA9IR2> select /*+ FULL(a) */ * from big_table a;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8562 Card=1000000 Bytes=98000000)
1 0 TABLE ACCESS (FULL) OF 'BIG_TABLE' (Cost=8562 Card=1000000 Bytes=98000000)

big_table@ORA9IR2> alter session set db_file_multiblock_read_count=128;

Session altered.

big_table@ORA9IR2> select /*+ FULL(b) */ * from big_table b;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=353 Card=1000000 Bytes=98000000)
1 0 TABLE ACCESS (FULL) OF 'BIG_TABLE' (Cost=353 Card=1000000 Bytes=98000000)

big_table@ORA9IR2> set autotrace off

but - it depends on the costing model

big_table@ORA10GR2> set autotrace traceonly explain
big_table@ORA10GR2> alter session set db_file_multiblock_read_count=1;

Session altered.

big_table@ORA10GR2> select /*+ FULL(a) */ * from big_table a;

Execution Plan
----------------------------------------------------------
Plan hash value: 3993303771

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10M| 946M| 46408 (4)| 00:03:41 |
| 1 | TABLE ACCESS FULL| BIG_TABLE | 10M| 946M| 46408 (4)| 00:03:41 |
-------------------------------------------------------------------------------

big_table@ORA10GR2> alter session set db_file_multiblock_read_count=128;

Session altered.

big_table@ORA10GR2> select /*+ FULL(b) */ * from big_table b;

Execution Plan
----------------------------------------------------------
Plan hash value: 3993303771

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10M| 946M| 46408 (4)| 00:03:41 |
| 1 | TABLE ACCESS FULL| BIG_TABLE | 10M| 946M| 46408 (4)| 00:03:41 |
-------------------------------------------------------------------------------

big_table@ORA10GR2> set autotrace off
big_table@ORA10GR2>

2) "full advantage" - interesting term. I've already said that unless really hard pressed - I would "take full advantage of the DEFAULT value and let it be".

multiblock read count - for tablespaces using the DEFAULT BLOCKSIZE for the database - is the number of BLOCKS - not bytes - BLOCKS to read. If you want to read 128 blocks at a time, you would set this to 128.

A reader, April 10, 2006 - 11:57 am UTC

Jonathan's new book deals in depth with block size, db_file_multiblock_read_count , how the optimizer costs changes wrt to these ....no where else you can find so many details :)

thanks
Anto

db_file_multiblock_read_count

A reader, April 10, 2006 - 12:11 pm UTC

Hi Tom,

I have asked this qn in "Things you don't like about Oracle" thread but I am not clear yet

I know increasing db_file_multiblock_read_count value reduces the cost associated with FTS and reducing it favours more index usage.

Suppose I have set this parameter db_file_multiblock_read_count to say 64 - which (after testing), I found, generates the best optimizer plans for my database. Suppose my system is capable of reading say 128 blocks at a time

My question is

Irrespective of the db_file_multiblock_read_count setting - suppose a query is using a FTS on a table, should n't it be doing that at the maximum rate possible for my system, always, whenever it is doing a FTS ?

Or is it that this parameter should always be set to the maximum value possible for your OS ? If that is the case, why set this parameter at all - can't Oracle automatically set this parameter from the system, just like it does for CPU_COUNT etc ?

thanks
Anto

April 11, 2006 - 10:27 am UTC

There is a "trick" you can use.

using dbms_stats, you can set system statistics to say "the average observed multi-block read count is 64". This is what the optimier will utilize. Then, you can set the db_file_multiblock_read_count to 128 - and that is what it'll use when it goes to do the actual IO.

A reader, April 11, 2006 - 10:33 am UTC

Thanks - Tom

Only we need to enable the system stats in that case, but still this is something new which can be tried out

Anto

db file multiblock read count

Karmit, June 12, 2006 - 9:26 am UTC

Hi Tom,
Was using the method mentioned by you for finding out the max possible DBFMRC on a system. The box is AIX and the oracle version is 9204. There are multiple instances running on the box. When I tried the method on instance A, I deduced that the max possible i/o the system offers for full scan is 1MB. However, when I tried the exact same test on instance B, I was surpised that the results were different?! Basically the difference is as follows:

Instance A: db_block_size=4K
max p3 value in trace file = 256
i.e 1MB (max possible)

Instance B: db_block_size=8K
max p3 value in trace file = 16
i.e 128Kb (max possible)

To confirm things I repeated the tests with initial extents of 200M - just to be sure - but get the same results.

Any idea why this could be happening? Note - there are no
multi block size settings on the databases, and the instances share the Oracle binaries (v9204).

Perplexed!

Regards,
Karmit

June 12, 2006 - 10:17 am UTC

I don't have AIX around - can anyone else out that that does and is using a 4 or 8k block size post what they see?

and Karmit - you are sure in instance b, you set the mbrc high - given 16 is the typical "default"?

db file multiblock read count

Karmit, June 12, 2006 - 9:56 am UTC

Hi Tom,

Apologies to take your time with my previous query!!

Read the full thread again and realised the issue.
The 8KB (blocksize) instance has actually got locally managed tablespaces with uniform extent size of 128K :-)

Created a new one with 2MB and re-ran the test.

Now it can read a max of 128 blocks in a go. Perfect!

Thanks,
Karmit

June 12, 2006 - 10:20 am UTC

hehe, it would have taken a minute or two to deduce that, thanks!

Self-Tuning Multiblock Read Count

RR, June 12, 2006 - 12:03 pm UTC

Hi Tom,

My understanding is that in Oracle10g we no longer need to worry about this parameter as the "Self-Tuning Multiblock Read Count" feature takes care of optimal value required for a particular query.

Please could you correct me if I am wrong and shed some light on this particular feature.

Thanks

June 13, 2006 - 10:34 am UTC

see fourth bullet:

</code> http://docs.oracle.com/docs/cd/B19306_01/server.102/b14211/whatsnew.htm#PFGRF000 <code>

very good

PRASANNA, June 13, 2006 - 5:22 am UTC

hi I used oracle9i client in oracle 10g server
but i have some problems using sql loader
is there any difficulties in that.. and tell me other problems too..

June 13, 2006 - 12:21 pm UTC

there are no systemic problems that I am aware of, no.

"have some problems" is "sort of vague"

db_file_multiblock_read_count

Karmit, June 19, 2006 - 11:48 am UTC

Hi Tom,

If a table had a billion records and most of it were to
be deleted - leaving only say ~10... thus assuming
the high water mark is "not in sync", would a FULL table
scan (forced via a hint) - still scan the full table?

I'm asking this since I once read your comment to a query from somebody in context of finding max. possible I/o,
for which you answered:
"the table isn't full. we only read what we need -- and
some of it can be in the buffer cache (hence we won't
read that necessarily)."

When you say "the table isn't full" I assume you mean't that
though the HWM is high up, the records are really not there.

However, I also read this in the 9i docs:

"High Water Mark in DBA_TABLES
The data dictionary keeps track of the blocks that have been populated with rows. The high water mark is used as the end marker during a full table scan. The high water mark is stored in DBA_TABLES.BLOCKS. It is reset when the table is dropped or truncated.
For example, consider a table that had a large number of rows in the past. Most of the rows have been deleted, and now most of the blocks under the high water mark are empty. A full table scan on this table exhibits poor performance because all the blocks under the high water mark are scanned.
"

So.. I'm confused as to what does it exactly do in such a case? does it read upto the HWM (as stated in the docs) OR does it scan only what is required as the "table isn't full"?

Thanks,
Karmit

June 19, 2006 - 6:41 pm UTC

the high water mark of a table is the high water mark and unless and until you LOWER IT (rebuild, reorg, alter table shrink in 10g...) it'll be the high water mark and a full scan reads every block up to that mark.

You'll really need to provide the context of my quotes there, they don't seem to have come from this page.

dbfmrc

Karmit, June 20, 2006 - 6:59 am UTC

Hi Tom,

Got the excerpt I was referring to, below:

****
Topic - "Blocks read using index vs. full table scan"

Reviewer: sankar from KY,USA
I created a table that spans across 5 extents and each extent is 260 blocks . I did the alters and part of the trace file is below . It is on 8.1.7.2 / hp-ux 11.0

Select extent_id ,blocks from dba_extents where segment_name='T';

EXTENT_ID BLOCKS
---------- ----------
0 260
1 260
2 260
3 260
4 260

PARSING IN CURSOR #1 len=70 dep=0 uid=163 oct=42 lid=163 tim=1608319387 hv=347037164 ad='85c9e8a0'
alter session set events '10046 trace name context forever, level 12' END OF STMT EXEC #1:c=1,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1608319387
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 807 p1=1650815232 p2=1 p3=0
=====================
PARSING IN CURSOR #1 len=38 dep=0 uid=163 oct=3 lid=163 tim=1608320195 hv=194957013 ad='85caffd0'
select /*+ FULL(t) */ count(*) from t
END OF STMT
PARSE #1:c=1,e=1,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1608320195
BINDS #1:
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1608320195
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0
WAIT #1: nam='db file scattered read' ela= 5 p1=3 p2=867 p3=128
WAIT #1: nam='db file scattered read' ela= 1 p1=3 p2=995 p3=47
WAIT #1: nam='db file scattered read' ela= 4 p1=3 p2=1302 p3=128
WAIT #1: nam='db file scattered read' ela= 3 p1=3 p2=1430 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=3 p2=1558 p3=4
WAIT #1: nam='db file scattered read' ela= 4 p1=3 p2=2082 p3=128
WAIT #1: nam='db file scattered read' ela= 1 p1=3 p2=2210 p3=39
FETCH #1:c=34,e=43,p=602,cr=1210,cu=12,mis=0,r=1,dep=0,og=4,tim=1608320238
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1650815232 p2=1 p3=0
FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1608320238
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 462 p1=1650815232 p2=1 p3=0
STAT #1 id=1 cnt=1 pid=0 pos=0 obj=0 op='SORT AGGREGATE '
STAT #1 id=2 cnt=99200 pid=1 pos=1 obj=5187 op='TABLE ACCESS FULL T '
XCTEND rlbk=0, rd_only=1

I expected to see in the trace file that oracle tried to
read 128 blocks at a time twice and 4 blocks next time for each extent . My question is why do i see as below .

WAIT #1: nam='db file scattered read' ela= 5 p1=3 p2=867 p3=128
WAIT #1: nam='db file scattered read' ela= 1 p1=3 p2=995 p3=47
Also i see a p3=39 once and i dont see this for all extents .

Followup:
the table isn't full. we only read what we need -- and some of it can be in the buffer cache (hence we won't read that necessarily).

It "looks" like you are using 3 of the 5 extents to hold data (this is probably, not 100%, caching can affect this).

extent 1: blocks 867 .. 1042 (128+47)
extent 2: blocks 1302 .. 1562 (128+128+4)
extent 3: blocks 2082 .. 2249 (128+39)

the other two extents have nothing, two of the three that do have data are not full.
****

In the followup you mention that the "table isn't full" - for which I assumed you meant this - * since the table
isn't full, the db file scattered read will not necessarily
read upto potential - as the data is simply not there *..
which to me seems akin to saying that a full table scan
will not read empty blocks below the HWM.

Maybe I've misunderstood?

Thanks,
Karmit

June 20, 2006 - 10:06 am UTC

thanks - in his case

THE TABLE WASN'T FULL. He had five extents, not all of which are used.

the hwm wasn't advanced to cover all 5 extents, that is all. You can have 1 TB of space allocated in extents, but only 100mb of data under the high water mark.

Beautifull Note

Amit Kumar Midha, October 25, 2006 - 1:11 pm UTC

Hi i am using asktom from last more than 2 years but this is my very first review. This artical help me alot and clear my lot of doubts regarding db_fmbrc parameter. I hope u and ur team help to all dba's like in same manner

db file scattered read --> direct path read in 11g ?

harri, November 21, 2007 - 8:22 pm UTC

Is the terminology different in 11g or does 11g do fullscan differently?

SQL> alter session set db_file_multiblock_read_count = 16;

Session altered.

SQL> alter session set events '10046 trace name context forever, level 8';

Session altered.

SQL> select count(*) from test;

COUNT(*)
----------
43652

SQL> alter session set events '10046 trace name context off';

Session altered.

WAIT #1: nam='direct path read' ela= 6 file number=1 first dba=99986 block cnt=14 obj#=73135 tim=1195693895891571
WAIT #1: nam='direct path read' ela= 1 file number=1 first dba=100000 block cnt=16 obj#=73135 tim=1195693895891750
WAIT #1: nam='direct path read' ela= 1 file number=1 first dba=100016 block cnt=16 obj#=73135 tim=1195693895891925
WAIT #1: nam='direct path read' ela= 1 file number=1 first dba=100032 block cnt=16 obj#=73135 tim=1195693895892097
WAIT #1: nam='direct path read' ela= 1 file number=1 first dba=100048 block cnt=16 obj#=73135 tim=1195693895892280
WAIT #1: nam='direct path read' ela= 1 file number=1 first dba=100064 block cnt=16 obj#=73135 tim=1195693895892452
WAIT #1: nam='direct path read' ela= 1 file number=1 first dba=100080 block cnt=16 obj#=73135 tim=1195693895892626
WAIT #1: nam='direct path read' ela= 1 file number=1 first dba=100096 block cnt=9 obj#=73135 tim=1195693895892773

November 26, 2007 - 10:25 am UTC

direct path reads bypass the buffer cache - so it would have checkpointed the relevant data and then just read from disk, bypassing the overhead of the buffer cache entirely.

are you sure that didn't go parallel?

why it is so

Reene, January 10, 2008 - 6:45 am UTC

Hi Tom

I have a query which takes 1.5 minutes in test environment and but it takes 6 min in production environemt.

my test and prod environment are same,we refresh test from prod every week.

Here is the tkprof output from production

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 100.36 352.86 522403 547686 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 100.37 352.87 522403 547686 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 47

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=547686 r=522403 w=26979 time=352862137 us)
3207403 HASH JOIN OUTER (cr=547686 r=522403 w=26979 time=351749911 us)
3203182 TABLE ACCESS FULL OBJ#(9244) (cr=523268 r=470215 w=0 time=292543370 us)
2232817 VIEW (cr=24418 r=41028 w=16935 time=42348348 us)
2232817 HASH JOIN (cr=24418 r=41028 w=16935 time=37954083 us)
2232817 INDEX FAST FULL SCAN OBJ#(9251) (cr=10750 r=10575 w=0 time=11569127 us)(object id 9251)
5435999 INDEX FAST FULL SCAN OBJ#(9248) (cr=13668 r=13518 w=0 time=8269936 us)(object id 9248)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file scattered read 68566 0.34 248.97
db file sequential read 5293 0.06 11.24
direct path write 1453 0.00 0.31
latch free 1 0.00 0.00
direct path read 1489 0.20 0.89
SQL*Net message from client 2 25.93 25.94

and below is the tkprof of same query in test

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 72.49 71.16 564334 534007 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 72.50 71.17 564334 534007 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 47 (PRECISE_I3)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=534007 r=564334 w=28868 time=71167328 us)
3139401 HASH JOIN OUTER (cr=534007 r=564334 w=28868 time=70548417 us)
3135246 TABLE ACCESS FULL OBJ#(151710) (cr=511533 r=511492 w=0 time=39206142 us)
2175400 VIEW (cr=22474 r=41201 w=18837 time=21387372 us)
2175400 HASH JOIN (cr=22474 r=41201 w=18837 time=20202704 us)
2175400 INDEX FAST FULL SCAN OBJ#(151716) (cr=9725 r=9677 w=0 time=3780210 us)(object id 151716)
5310646 INDEX FAST FULL SCAN OBJ#(151713) (cr=12749 r=12687 w=0 time=3699207 us)(object id 151713)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 37 0.01 0.03
db file scattered read 66729 0.05 17.65
direct path write 4124 0.10 0.86
direct path read 4354 0.00 0.15
SQL*Net message from client 2 38.44 38.44

As we can see there is huge differnece in db file scattered wait time in the production and test.

total time waited is not very different (as production table has slighlty more rows than test table) but total time waited is 248.97 sec in production but it is just about 18 seconds in QA.

what could be the possible reason.
what should I conclude from this.

January 10, 2008 - 2:34 pm UTC

... my test and prod environment are same ...

oh really - they have the same user load and concurrent workloads?

and given the object numbers differ - this "refresh" is some sort of logical refresh (meaning you export/import or something)

these systems are about as different as you can get - please keep that in mind... They are similar, they are not the same.

Now, what could cause this:

db file scattered read 68566 0.34 248.97
db file scattered read 66729 0.05 17.65

well, immediately what pops into mind is production has 1,000 users, test has 1 user - that is a possibility (working with lots of imperfect information here)

or, production is a super slow san and test is a bunch of local disk that happens to blow the san away (for whatever reason)

or production has the data spread out way differently then test - and we are seeking like mad - but in test, all of the data is nicely compacted and the seek times are reduced (it is a logical copy, it is very different data layout)

or the production san is used by many other systems and they are nailing this same set of disk - so even if production has 1 user and test has 1 user, you are missing 1,000 users messing with productions disk (because it is on another server). This happens A LOT anymore, surprisingly often. The symptom is always "the database is very slow, IO is the cause and we have only one user". My first question anymore is "who owns the san, get them in here now and lets talk"

but I'm just guessing now - you should really involve the guys that "own" the disk and ask them "whats up - why is the response time so different"

then you'll find out how unlike these two machines are.

this is reporting database

A reader, January 11, 2008 - 7:01 am UTC

Hi Tom,

This system or database is a reporting database and this query is being run once in a day, just batch kind of ETL program .it takes some data from one table,applies some logic and load into another table.
i did a breakup of timing and could make out that select from a table is taking more, the transformation and insert part takes same time in qa and prod.

to refresh they don't use the export ,import they do a from production , i'll find the exact process.

i was jsut thinking that if there is a simpler reason than the mismatches in disk ,san etc.

Thanks

January 11, 2008 - 8:18 am UTC

"they do a ??? ? from production"

What would be simpler than "IO is very different, response time wise"

I'll bet that on production, there are a thousand other things going on
And in test, there are not.

very useful

Reene, January 14, 2008 - 1:29 am UTC

Hi Tom

The QA schema is being refreshed using export/import.
our usual process is synch split.

now should i try alter table move in the production to see if thre is an improvement or not.

How to check if the alter table move will help.

I will try to be more clear on this.

the query which I talked about above is on a single large table (just about 1 gb in size) and the query is doing a full table scan.

Regards

January 14, 2008 - 3:33 pm UTC

I think there is more than one person in production still :)

and this is consistent reads against undo...

some more data

Reene, January 14, 2008 - 5:48 am UTC

Hi Tom,

i collected some more data about the table on which the query is being run -

in production :

select count (distinct dbms_rowid.rowid_block_number(rowid)) "used blocks" from GLOBBKLG.ORDER_LINE_INFO_CLONE

148480

in qa this value is 132318

select blocks,empty_blocks,num_rows from dba_tables where table_name='ORDER_LINE_INFO_CLONE'

in production values are

bolcks empty num_rows
529916 0 5452200

in qa these values re

bolcks empty num_rows
511491 0 5309030

similarly

for the query

select blocks, extents from dba_segments where segment_name='ORDER_LINE_INFO_CLONE' ;

529920 414 -- prod

512000 400 -- qa

so like you said - the prod and qa are similar but not same.

i did talk to my storage team - but they say everything is ok in qa too.

they want me to give data to show this.
i sent them the db file scattered read data , tried to explain hard but to no avail.

any other suggestion for this.

January 14, 2008 - 3:34 pm UTC

what else can I say. what else can I say.

you have numbers that show io response time is much slower on system a vs b.

what more can I say?

More to Explore

Performance

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

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

db_file_multiblock_read_count behaviour in 9i

additional book reference - Thanks again.

db_file_multiblock_read

db_file_multiblock_read_count optimal size

db_file_multiblock_read_count optimal size

stripe size

db_file_multiblock_read_count

The question remains unanswered

Value of db_file_multiblock_read_count

db_file_multiblock_read_count

db file multiblock read count

db file multiblock read count

Self-Tuning Multiblock Read Count

very good

db_file_multiblock_read_count

dbfmrc

Beautifull Note

db file scattered read --> direct path read in 11g ?

why it is so

this is reporting database

very useful

some more data

More to Explore

Performance

db_file_multiblock_read_count and different read rates

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

db_file_multiblock_read_count behaviour in 9i

additional book reference - Thanks again.

db_file_multiblock_read

db_file_multiblock_read_count optimal size

db_file_multiblock_read_count optimal size

stripe size

db_file_multiblock_read_count

The question remains unanswered

Value of db_file_multiblock_read_count

db_file_multiblock_read_count

db file multiblock read count

db file multiblock read count

Self-Tuning Multiblock Read Count

very good

db_file_multiblock_read_count

dbfmrc

Beautifull Note

db file scattered read --> direct path read in 11g ?

why it is so

this is reporting database

very useful

some more data

More to Explore

Performance