Home -> Mailing Lists -> Oracle-L -> Question related to 'direct path read"

Question related to 'direct path read"

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Thu, 29 Nov 2018 21:08:23 +0000
Message-ID: <DM6PR11MB2780B366A9732F557AEB2746F4D20_at_DM6PR11MB2780.namprd11.prod.outlook.com> 





Hi,



I have a sub-query shown below:



SELECT


  TO_CHAR(INVOICE_ID) INVOICE_ID,
  TO_CHAR(POSTING_GROUP) POSTING_GROUP,
  TO_CHAR(REQUEST_ID) REQUEST_ID,
  TO_CHAR(CONTRACT_ID) CONTRACT_ID,
  CONTRACT_VERSION,
  CONTRACT_SEQUENCE,




  COVERAGE_START_DT COVG_PERIOD_ST_DT,



  COVERAGE_END_DT



  COVG_PERIOD_END_DT,



  TO_CHAR(CREDIT_INVOICE_ID) CREDIT_INVOICE_ID,

  RANK() OVER(PARTITION BY INVOICE_ID ORDER BY INVOICE_DETAIL_ID) RNK

FROM



  METRIX.INVOICE_DETAIL ID



WHERE POSTING_GROUP = NVL(:B1 , POSTING_GROUP) AND

  INVOICE_DETAIL_ID IS NOT NULL



;



The METRIX.INVOICE_DETAIL is a 38 million rows table. Column POSTING_GROUP is indexed but contains only 98 distinct values; so, it is not a good index. When this subquery runs, it does a FTS of the INVOICE_DETAIL table. The trace file shows that FTS was done using un-buffered I/O (DIRECT PATH READ waits). However, when I add the PARALLEL hint to the statement with a DOP of 2 or 4, the PQ processes scan the table using buffered I/O (DB FILE SCATTERD READ waits). I am trying to understand why multiple PQ processes are not doing un-buffered reads whereas the single process is.



Thanks,


Amir



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


Received on Thu Nov 29 2018 - 22:08:23 CET












Original text of this message