A reader, July 26, 2010 - 7:56 am UTC
Hi Tom,
In General the Optimizer calculates the cost for full table scans using 'MBRC' when the system statistics are gathered (workload statistics) but in 'reality' - Oracle will still use the db_file_multiblock_read_count - am I correct ? if that is so - what is the use of calculating the cost with 'MBRC' - Why do the optimizer we use the parameter'db_file_multiblock_read_count' to calculate the cost directly as normal costing method (with out cpu costing model) ?
July 26, 2010 - 8:38 am UTC
In 10gr2 and above you should not set the dbfmrc
In 10gr2 and above, we'll COST the query using the actual observed multi-block read count. We'll process the query using the MAX IO size.
What I mean by this is - say your MAX IO size is 1mb. You set your dbfmbrc to be a number such that that number * blocksize = 1mb (say dbfmrc = 128, blocksize = 8k). Oracle will use a 1mb IO size. Oracle will also cost a query using 1mb as the IO size.
In reality - will the IO size be 1mb? Almost certainly NOT - not ever. Why? Because there will be data in the cache. Suppose you go to read (full scan) a table that is in a single extent, that extent is 50mb in size. That'll take 50 IO requests to read since the IO size is 1mb right? Probably wrong. Suppose before you start the query 5% of the table is in the cache - blocks
20, 40, 60, 80, 100, 120, ....
are in the cache (one out of every 20). Now we go to multi-block read that table - we cannot issue a read request for blocks 1..128 since 20, 40, 60, ... 120 are already in the cache - therefore, we will issue reads:
1..19
21..39
41..59
and so on
You'll read 19 blocks at a time - you costed the query with 1mb IO's you are really doing 152k IOs. You probably used the wrong plan since the full scan was costed lower than it actually way.
So, you leave the dbfmbrc OFF - you do not set it at all. Now, Oracle will remember your actual observed multiblock read count (in this example, it would be about 19). It would use 19 to COST the query but when it went to do IO it would try to do up to 1mb of IO at a time (if it can). You get the right costing and you get the ability to do the IO as efficiently as possible.
Not seeing this in practice
Dan Blum, July 26, 2010 - 10:34 am UTC
What you say makes sense, but I have just been looking at db_file_multiblock_read_count and am not seeing what should be the correct behavior.
Specifically, the optimizer might be costing things correctly (I haven't tested for this), but when reading Oracle 10.2.0.4 is definitely using the dbfmrc setting and NOT the maximum allowable. Here is a trace report with the default setting of 16:
select count(*)
from
dblum3.foo
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.98 59.33 124338 124359 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.98 59.33 124338 124359 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=124359 pr=124338 pw=0 time=59332429 us)
16380441 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=124359 pr=124338 pw=0 time=98287359 us)
16380441 TABLE ACCESS FULL FOO PARTITION: 1 1 (cr=124359 pr=124338 pw=0 time=65526464 us)
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 1 0.00 0.00
db file scattered read 7816 0.21 56.20
SQL*Net message from client 2 12.26 12.26
And here is a trace report for the same statement with dbfmrc set to 32:
select count(*)
from
dblum3.foo
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 2.05 18.02 124338 124359 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 2.05 18.02 124338 124359 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=124359 pr=124338 pw=0 time=18021258 us)
16380441 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=124359 pr=124338 pw=0 time=65526002 us)
16380441 TABLE ACCESS FULL FOO PARTITION: 1 1 (cr=124359 pr=124338 pw=0 time=32765106 us)
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 1 0.00 0.00
db file scattered read 3916 0.20 15.45
latch: shared pool 1 0.00 0.00
SQL*Net message from client 2 3.02 3.02
Examination of the trace files shows that it is in fact reading the specified number of blocks on almost every read.
Notes:
1. 'alter system flush buffer_cache' was run before each execution.
2. Caching outside Oracle cannot be the whole story (if involved) since the 16 case was run AFTER the 32 case.
3. The tablespace being used here is on an NFS mount.
A reader, July 26, 2010 - 10:53 am UTC
Thanks Tom,
It means - By looking at the the statistics available - the Optimizer understands that on 'average' - a multiple block read operation can get X no of blocks in one multiple read I/O - where X is the observed - mulriple_block_read_count though we issue a multiple block read I/O with actual db_file_multiblock_read_count.
The estimate will be more accurate with the 'Observer multiblock_read_count' than the with the 'actual db_file_mutlit_block_read_count' .
1.Oracle 9i- we set two parameters db_file_mutlit_block_read_count =16 and block_szie=8K then Max I/O=16*8=128 KB , if Oracle needs to read data from a tablespace with block_size=4K , then Oracle will calculate the db_file_mutlit_block_read_count=128/4=32
Is this correct ?
2.Oracle 10gR2 and above - We DON’T set the parameter db_file_mutlit_block_read_count , Oracle will talk to OS and depends on the platform - Oracle 'remembers' the MAX I/O size (ex: 1MB) - and depends on the block_size of the current table space it is reading the data from , it will calculate the db_file_mutlit_block_read_count .
Is this correct ?
3.In Oracle 8i - How does the Optimizer will get the 'Observed multi_block_read' to calculate the cost ?Are they pre-calculated and stored somehere in the data dictionary ? We know -In 9i- through system statistics .
Many thanks
July 27, 2010 - 12:05 pm UTC
1) yes
2) yes
3) see #1, it works the same. The difference would be when it comes to costing, 9i would use system statistics if present - whereas 8i would just use the two numbers you provide.
Never Mind
Dan Blum, July 26, 2010 - 1:01 pm UTC
Upon further inspection, I see that the default value for db_file_multiblock_read_count is NOT 16. Which raises the question of why it is 16 in my database (since no one has explicitly set it as far as I know), but that is outside the scope of this discussion.
Given that everything appears to be behaving as expected.
A reader, July 26, 2010 - 1:38 pm UTC
Thanks Tom,
It means - By looking at the the statistics available - the Optimizer understands that on 'average'
- a multiple block read operation can get X no of blocks in one multiple read I/O - where X is the
observed - mulriple_block_read_count though we issue a multiple block read I/O with actual
db_file_multiblock_read_count.
The estimate will be more accurate with the 'Observer multiblock_read_count' than the with the
'actual db_file_mutlit_block_read_count' .
1.Oracle 9i- we set two parameters db_file_mutlit_block_read_count =16 and block_szie=8K then Max
I/O=16*8=128 KB , if Oracle needs to read data from a tablespace with block_size=4K , then Oracle
will calculate the db_file_mutlit_block_read_count=128/4=32
Is this correct ?
2.Oracle 10gR2 and above - We DON’T set the parameter db_file_mutlit_block_read_count , Oracle will
talk to OS and depends on the platform - Oracle 'remembers' the MAX I/O size (ex: 1MB) - and
depends on the block_size of the current table space it is reading the data from , it will
calculate the db_file_mutltiblock_read_count .
Is this correct ?
3.In Oracle 8i - How does the Optimizer will get the 'Observed multi_block_read' to calculate the
cost ?Are they pre-calculated and stored somewhere in the data dictionary ? We know -In 9i- through
system statistics .
Many thanks
July 27, 2010 - 12:10 pm UTC
see above
A reader, July 27, 2010 - 12:57 pm UTC
Hi Tom,
In 10gR2 and above - when we gather system statistics (with workload) - we could see MBRC - the avarage no of blocks that that can be read from disk in one multi_block I/O- and is used by the CBO to calculate the cost of a Full table scan ,we dont consider the 'db_file_muliti_block_count' parameter.
Let us say - I have two table spaces - with block_size =4K and 16K and when I gather system statistics - will it calculate the MBRC considering these two sizes ? becuase how come MBRC would be the same while calculating the cost of reading the tables in two tables spaces because the db_file_multi_block_read_count' MAY differ and so is it not the case that the observed multi_block_count (MBRC) should change whenever ORACLE will change the db_file_multi_block_count?
To be simple : In 1og R2 and above -- Is MBRC (Observed multi block count) SAME irrespective of the value of the parameter db_file_multi_block_read_count that ORACLE uses (on fly) as per the size of the block in that table space ??
July 28, 2010 - 7:25 am UTC
the thing that is used is dbfmbrc * default_block_size
that is what is used to cost. Think of it that way, forget about dbfmbrc for a minute, make the assumption that "cost is predicted using 275KB reads", not X blocks, but NKB.
It doesn't use dbfmbrc - it uses dbfmbrc * default block size to cost things. Regardless of the block size of the tablespace it will be scanning.
DBFMBRC documentation...
Tony Killen, December 06, 2011 - 3:37 am UTC
Hi Tom,
The link you gave in the first question to the 11.2 initialization parameters (for me it links to DB_CREATE_ONLINE_DEST_n, but DBFMBRC is just 2 pages further on):
http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/initparams053.htm #REFRN10037
...says that the default value for DBFMBRC on most platforms is 1Mb/block size, which for my 8Kb block size (and 11.2 on AIX) would be 128 blocks.
In the 11.2 performance tuning guide however, it says this:
http://docs.oracle.com/cd/E11882_01/server.112/e16638/stats.htm#i41496 "13.4.1.2 Multiblock Read Count
...If Oracle Database cannot gather or validate gathered mbrc or mreadtim values, but has gathered sreadtim and cpuspeed values, then the database uses only the sreadtim and cpuspeed values for costing. In this case, the optimizer uses the value of the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT to cost a full table scan. However, if DB_FILE_MULTIBLOCK_READ_COUNT is not set or is set to 0 (zero), then the optimizer uses a value of 8 for costing."
So is the default 8 for costing and 128 for the actual I/O operation? Wouldn't that give a big difference between plan costings and the reality of requesting 128 block reads?
Or is the documentation wrong?
Many thanks.
December 06, 2011 - 11:23 am UTC
So is the default 8 for costing and 128 for the actual I/O operation? Wouldn't
that give a big difference between plan costings and the reality of requesting
128 block reads?
not really - since we hardly ever can do the full IO anyway. We'd *like* to read 1mb - but we almost never can really read 1mb at a time.
Say you are going to read 128 blocks - if any of those blocks are in the buffer cache - we *cannot* read that block from disk - we have to start with the copy in the cache. So, say the 20th, 42nd, and 100th block are in the cache already.
we'd
read 1..19 from disk, scatter them in the buffer cache and get them from there
get block 20 from the cache
read 21..41 from disk, scatter them .....
get block 42 from the cache
read 42..99 from disk, ....
get block 100 from the data
read 101..128 from disk ....
8 for costing, max IO size attempted at runtime is a very good compromise. You get a reasonable costing for the full scan - and if at runtime we get lucky and can actually do more than 8 - we'll do it.
what about statistics
A DBA, December 07, 2011 - 12:11 am UTC
Hi Tom,
We have a 15tb 2 node RAC database 10.2.0.3. We weekly collect statistics with dbms_stats package.
Few of our DBAs set db_file_multiblock_read_count to 128 before starting statistics.
Is it right to do it since from 10g onwards,oracle automatically manages it?
Also we set estimate_percent=100 which takes lots of time for heavy tables. The tables are highly DML active. Is it appropriate to set it to 100? Would a lesser value suffice?
December 07, 2011 - 1:16 pm UTC
in 10g, do not set the db file multiblock read count, let it default
we'll use a sensible number for costing
we'll use the max IO size when reading
best of all possible worlds.
any way to know dbmrc value
A DBA, December 08, 2011 - 12:08 am UTC
Dear Tom,
Thanks for your reply.
If i set dbmrc=0 and run statistics,is there anyway to view what value oracle has choosen for dbmrc?
December 08, 2011 - 12:49 pm UTC
it is the largest IO size your system supports, typically - almost universally, 1mb.
you can run a sql_trace on a full scan after flushing the cache on a test system to see what it is - you'll see the number of blocks read in the trace file for each of the db file scattered read/direct path read wait events.
db_file_multiblock_read_count
A reader, May 30, 2012 - 4:04 am UTC
Hi Tom,
Per your response below
'in 10g, do not set the db file multiblock read count, let it default
we'll use a sensible number for costing
we'll use the max IO size when reading
best of all possible worlds.'
My question is all about parameter 'db_file_multiblock_read_count':
1. oracle will self tune this number on the fly?
2. if so, leave it default is ok, right? Then why ORACLE open this parameter out?
3. suppose i know all my apps are pure reporting app and will read huge set of data from table,
will it helpful to set this bigger? For example, i know each query will be full table scan and read
800k, i can set it to 100 which will read 800K each IO
4. suppose i know most of my query will read 80K while small portion of them will read 800K,
i set it to 10(80K per IO) will be better. If still set to 100(800K IO), most of the time is wasting IO?
5. as we know it works only with 'full table scan', how about '(fast)full index scan)? why can not index blocks take
advantage of multiple read?
May 30, 2012 - 6:14 am UTC
1) yes, for costing purposes
2) don't leave set to the default, just do NOT set it, remove it from your init.ora
3) no, we'll use the biggest value when we read. We'll use the practical observed value to cost whether to full scan or index.
do not set it.
4) no, do not set it. We'll attempt to use the maximum IO size when we read - we'll cost with the actual sizes we see you being able to read.
5) fast full index scans do take advantage of multi block reads. That is the difference between an index fast full scan and an index full scan. One does single block IO and the other multi-block IO. fast full scans just read the index in any old order (not sorted) multiple blocks at a time.
In short: please do NOT set this parameter
Thomas, May 31, 2012 - 6:15 am UTC
I'm wondering why Oracle does not simply ignore the read
blocks if they are already in the buffer cache.
Say you want to read 1000 blocks of a table and block 3, 7
and 255 are in the cache. Wouldn't it make sense to read
1000 blocks in one multiblock I/O and simply ignore the
read data for blocks 3, 7 and 255 instead of reading blocks
1-2, 4-6, 8-254 and 256-1000 as separate multiblock I/Os?
There's a trade-off, of course; if too many blocks are
cached then the single big I/O might be a waste of time
(as some smaller I/Os for the missing blocks might be
better), but if few blocks in the range to read are cached
it might very well make sense to read as one I/O and ignore
those blocks.
Or am I missing something?
May 31, 2012 - 10:21 am UTC
I'm wondering why Oracle does not simply ignore the read
blocks if they are already in the buffer cache.
because those blocks might well be newer than what is on disk.
I update a row in EMP. I modify block X. Block X is modified in the cache - it is buffered - it is not on disk.
I commit.
You run your query. You cannot ignore my NEWER copy of emp in the cache.
So, we cannot use that block and we'd still have to look in the buffer cache to see that we shouldn't be using blocks 3, 7 and 255 (and in fact ALL of the blocks).
So we look to see what blocks we need and issue IO's for them....
Thomas, May 31, 2012 - 10:47 am UTC
You don't seem to get my point. I don't see why Oracle has
to do several "micro I/Os" (to get the block ranges between
the blocks that are already in the buffer cache). Why
doesn't Oracle do a normal big multiblock I/O for all blocks
and then (from all loaded blocks) take the ones that are not
already in the buffer cache (in the desired version) and
take the already cached ones from the cache?
The result would be the same, because for every block you
could decide whether to take (and cache) the loaded one or the already
cached one. It is just that you might save a potentially
big number of small I/Os if the cached blocks are scattered
throughout the block range and use one big I/O instead.
May 31, 2012 - 11:15 am UTC
I got your point - we just chose to issue the IO's we needed to issue in order to get the blocks we need to get, that is all.
Why read 1mb when you probably only need 64k or so at a time? It is your "on the other hand" observation from above.