RE: direct path read & db_file_multiblock_read_count
Date: Sun, 27 Oct 2013 16:37:37 +0000
Message-ID: <AF02C941134B1A4AB5F61A726D08DCED0DED47CB_at_USA7109MB012.na.xerox.net>
Thanks Jonathan.
Based on your explanation of direct path reads, it seems that for contiguous extents, up to 1M reads will be attempted. I probably did not do a good job in giving the background of this particular application. It was a fresh install of Oracle 11.5.10.2 ERP about five years ago. With 11.5.10.2, Oracle started to distribute OATM (Oracle Application Tablespace Model) where all transactional data related tablespaces had 128KB of extent sizes. This application is now at R12 (12.1.3). Over the years we have been adding data to it. So, your hypotheses is right that there are intervening extents from other tables that are prohibiting in doing a larger read.
I will definitely write a statement to check it out.
Thank you.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Sunday, October 27, 2013 12:22 PM
To: 'ORACLE-L'
Subject: RE: direct path read & db_file_multiblock_read_count
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_at_xerox.com] 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_at_gmail.com]
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_at_gmail.com
Phone: +31 20 8946342
On 26 Oct 2013, at 00:21, Hameed, Amir <Amir.Hameed_at_xerox.com> 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
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Sun Oct 27 2013 - 17:37:37 CET