direct path read & db_file_multiblock_read_count

From: Hameed, Amir <>
Date: Fri, 25 Oct 2013 22:21:38 +0000
Message-ID: <>

Hi Folks,
Here is the environment configuration:
- RHEL 6/u4
  • Cisco UCS 16xcores & 128GB RAM
  • Oracle RDBMS (Oracle ERP database)
  • db_cache_size=6G
  • pga_aggregate_target=2G
  • db_file_multiblock_read_count8 (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 cnt8
   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 cnt8.

Does anyone know what might be causing Oracle to choose 16 blocks as opposed to the larger 128 blocks reads.


Received on Sat Oct 26 2013 - 00:21:38 CEST

Original text of this message