RE: Question related to 'direct path read"
Date: Fri, 30 Nov 2018 17:57:25 +0000
Message-ID: <DM6PR11MB278095FA77691EA8A63ED7B6F4D30_at_DM6PR11MB2780.namprd11.prod.outlook.com>
Thank you Dominic. The IMPQ blog makes the following statement:
First the query coordinator (QC) decides to use IMPQ for an object or not. This decision is based on the object size to be scanned. By default if the object size is between 2% and 80% of the total buffer cache size in the cluster the object is a candidate for IMPQ. If the object is larger IMPQ will not be used and that object will be accessed using direct reads bypassing the buffer cache. For smaller objects IMPQ will not be used but the object can be cached like an object accessed by a serial query, Cache Fusion will be used to transfer data between nodes when necessary. For object size Oracle looks at the optimizer stats if they have been gathered, if not it looks at the actual object size.
In my case, I set PARALLEL_DEGREE_POLICY to AUTO. The DB_CACHE size is 8G, whereas the table size is 17G. So, the table is a little above twice the size of the buffer cache. When I ran the statement with parallel hint, the trace files of PQ processes still showed db file scattered read.
From: Dominic Brooks <dombrooks_at_hotmail.com> Sent: Friday, November 30, 2018 1:03 AM To: Hameed, Amir <Amir.Hameed_at_xerox.com> Cc: ORACLE-L <oracle-l_at_freelists.org> Subject: Re: Question related to 'direct path read"
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 - 18:57:25 CET