RE: direct path read & db_file_multiblock_read_count
- From: "Hameed, Amir" <Amir.Hameed@xxxxxxxxx>
- To: "jonathan@xxxxxxxxxxxxxxxxxx" <jonathan@xxxxxxxxxxxxxxxxxx>, 'ORACLE-L' <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 26 Oct 2013 14:08:41 +0000
You are right Jonathan, these tablesspaces have default extent sizes of 128k.
Since all Oracle ERP installations now come with the OATM (Oracle Application
Tablespace Management) model enabled/implemented with 128k as the default
tableplespace extent size, it seems that in order to get the optimal IO of 1M
bandwidth, the extent size will need to be altered soon after the initial
installation when the only data in the system is the seeded data.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Jonathan Lewis
Sent: Saturday, October 26, 2013 3:40 AM
To: 'ORACLE-L'
Subject: RE: direct path read & db_file_multiblock_read_count
Assuming you have an 8KB block size I'd check to see if one of your tables was
sitting in a tablespace defined with a uniform extent size of 128KB.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Hameed, Amir [Amir.Hameed@xxxxxxxxx]
Sent: 25 October 2013 23:30
To: 'ORACLE-L'
Subject: RE: direct path read & db_file_multiblock_read_count
It seems that the text was clobbered a bit after it was sent. So, I am pasting
some of the numbers and text again:
...
288 cnt=80
307 cnt=48
1345 cnt=128
3493 cnt=15
48942 cnt=16
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=128.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Hameed, Amir
Sent: Friday, October 25, 2013 6:22 PM
To: 'ORACLE-L'
Subject: direct path read & db_file_multiblock_read_count
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--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
