Re: Question related to 'direct path read"
Date: Fri, 30 Nov 2018 06:02:58 +0000
Message-ID: <DB7PR04MB4443DD9B518D507CE4144630A1D30_at_DB7PR04MB4443.eurprd04.prod.outlook.com>
https://blogs.oracle.com/datawarehousing/in-memory-parallel-execution-in-oracle-database-11gr2 https://blogs.oracle.com/datawarehousing/in-memory-parallel-query
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
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-lReceived on Fri Nov 30 2018 - 07:02:58 CET