RE: direct path read & db_file_multiblock_read_count
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: 'ORACLE-L' <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 27 Oct 2013 16:21:46 +0000
Amir,
The way I interpreted (or made a guess about) your results was that the extent
size was 128KB and that the tables had been moved and indexes rebuilt some time
in the past - leading to initial sections of the table that were formed of a
large number of contiguous extents that would allow direct path reads to cross
extent boundaries up to 1MB.
I then assumed that after the initial build you were adding data continuously
in an OLTP fashion, and had a number of indexes on the tables so that you have
a high probability that between one table extent and the next being added one
of your indexes (or one of the other tables in the tablespace) would have an
intervening extent added, making it impossible to read across extent
boundaries.
The 16-block reads would be then be the basic extent size, the 15 block reads
would be extents which started with an L1 bitmap block. I assumed you would
also have some reads of 1, 2, or 3 blocks short of small multiples of 15 blocks
- but not enough that you would bother to report them.
If you want to check my guess you could always write an analytic query that
identified sets of contiguous extents.
Regards
Jonathan Lewis
--------------------------------------------------------------------------------
From: Hameed, Amir [Amir.Hameed@xxxxxxxxx]
Sent: 27 October 2013 14:47
To: Jonathan Lewis
Subject: FW: direct path read & db_file_multiblock_read_count
Hi Jonathan,
I have sent this note out three times but each time its text turned into
garbage. I have the following question:
When I look at the extent size of the following two tables, they are all 128k
in size as shown below:
select segment_name, bytes, count(*) from dba_extents where segment_name in
('FA_BALANCES_REPORTS_ITF','GL_JE_LINES') group by segment_name, bytes ;
SEGMENT_NAME BYTES COUNT(*)
------------------------------ ---------- ----------
FA_BALANCES_REPORTS_ITF 131072 23833
GL_JE_LINES 131072 105087
As shown above, all extents are of 128k in size. However, I am not sure where
are the IO sizes that are higher than 16 coming from, unless the extents
boundaries are being crossed for some reason.
...
288 cnt=80
307 cnt=48
1345 cnt=128
3493 cnt=15
48942 cnt=16
Thanks
_____________________________________________
From: Hameed, Amir
Sent: Sunday, October 27, 2013 10:40 AM
To: 'Frits Hoogland'
Cc: 'ORACLE-L'
Subject: RE: direct path read & db_file_multiblock_read_count
Trying one more time as the last two sent turned out to be garbage.
From: Hameed, Amir
Sent: Saturday, October 26, 2013 4:41 PM
To: 'Frits Hoogland'
Cc: 'ORACLE-L'
Subject: RE: direct path read & db_file_multiblock_read_count
Resending as the previous sent showed all garbage characters for some reason.
From: Hameed, Amir
Sent: Saturday, October 26, 2013 4:37 PM
To: 'Frits Hoogland'
Cc: ORACLE-L
Subject: RE: direct path read & db_file_multiblock_read_count
Frits,
What I meant was that we are not setting MBRC exclusively and that it is being
set by Oracle at instance startup. I do not believe that once it is set at
instance startup, it will vary (I could be wrong though). All extents of the
tables involved in the statement have size 128k, which explains why most of the
MBRC IOs:
select segment_name, bytes, count(*) from dba_extents where segment_name in
('FA_BALANCES_REPORTS_ITF','GL_JE_LINES') group by segment_name, bytes ;
SEGMENT_NAME BYTES COUNT(*)
------------------------------ ---------- ----------
FA_BALANCES_REPORTS_ITF 131072 23833
GL_JE_LINES 131072 105087
As shown above, all extents are of 128k in size. However, I am not sure where
are the other IO sizes coming from, unless the extents boundaries are being
crossed for some reason.
From: Frits Hoogland [mailto:frits.hoogland@xxxxxxxxx]
Sent: Saturday, October 26, 2013 5:05 AM
To: Hameed, Amir
Cc: ORACLE-L
Subject: Re: direct path read & db_file_multiblock_read_count
Amir, what does "db_file_multiblock_read_count_8 (this is not set exclusively
and is being set by the Oracle kernel based on the value of db_cache_size)"
mean? How is it set in the parameterfile?
The reason for asking is: if you've not set this parameter, or have set it to
"0", it will be "auto tuning". See a discussion about this from Charles Hooper
here:
http://hoopercharles.wordpress.com/2010/04/10/auto-tuned-db_file_multiblock_read_count-parameter/
I _think_, or "have reasons to believe" the parameter is not even statically
set by the oracle instance depending on other settings, but is totally dynamic
by nature, which means it keeps "busyness" into account, and sets the MBRC
depending on (a number of) heuristics. These heuristics seem to be IO and CPU
usage at least.
This seems to be in line with what you are seeing: you have mixed sized
multiblock reads.
Please mind direct path reads can read over the extent border up to a non-data
block, or a block already in cache (the latter is true for buffer/scattered
reads, I haven't proved to myself that this is true for direct path reads).
Typically, a non-data block would be a L1/2/3 free space bitmap block.
Frits Hoogland
http://fritshoogland.wordpress.com
frits.hoogland@xxxxxxxxx
Phone: +31 20 8946342
On 26 Oct 2013, at 00:21, Hameed, Amir <Amir.Hameed@xxxxxxxxx> wrote:
Hi Folks,
Here is the environment configuration:
- RHEL 6/u4
- Cisco UCS 16xcores & 128GB RAM
- Oracle RDBMS 11.2.0.3 (Oracle ERP database)
- db_cache_size=6G
- pga_aggregate_target=2G
- db_file_multiblock_read_count_8 (this is not set exclusively and is
being set by the Oracle kernel based on the value of db_cache_size)
I am running the following query to force a FTS:
select /*+ full(GLL) */ count(*) from GL_JE_LINES GLL
union all
select /*+ full(FA) */ count(*) from FA_BALANCES_REPORTS_ITF FA
;
When I trace the session with 10046/level 8, I see that most of the 'direct
path read' are being done with cnt as shown below:
grep 'direct path read' n22vt2_ora_9850_Linux.trc|awk '{print $13}'|sort|uniq
-c|sort -k 1 -n
...
288 cnt€
307 cntH
1345 cnt_8
3493 cnt_
48942 cnt
This is quite consistent on all environments on Linux. On Solaris, a similar
type of statement shows that most of the DPR are done with cnt_8.
Does anyone know what might be causing Oracle to choose 16 blocks as opposed to
the larger 128 blocks reads.
Thanks,
Amir
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
