Re: Question related to 'direct path read"
Date: Fri, 30 Nov 2018 05:59:16 +0000
Message-ID: <DB7PR04MB44431399827A8D90BCA53E3EA1D30_at_DB7PR04MB4443.eurprd04.prod.outlook.com>
https://docs.oracle.com/database/121/VLDBG/GUID-AFEB5E27-9E6F-4924-B9C2-5354A948A8A5.htm
Sent from my iPhone
On 29 Nov 2018, at 21:56, Hameed, Amir <Amir.Hameed_at_xerox.com<mailto:Amir.Hameed_at_xerox.com>> wrote:
Hi,
I have a sub-query shown below:
SELECT
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,
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
;
Amir
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Nov 30 2018 - 06:59:16 CET