direct path read & db_file_multiblock_read_count

• 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_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.

Thanks,
Amir

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