RE: direct path read & db_file_multiblock_read_count

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Sat, 26 Oct 2013 14:08:41 +0000
Message-ID: <AF02C941134B1A4AB5F61A726D08DCED0DED42E4_at_USA7109MB012.na.xerox.net>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Hameed, Amir [Amir.Hameed_at_xerox.com] 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Sat Oct 26 2013 - 16:08:41 CEST