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@xxxxxxxxxxx>
Sent: Friday, November 30, 2018 1:03 AM
To: Hameed, Amir <Amir.Hameed@xxxxxxxxx>
Cc: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxx<mailto:Amir.Hameed@xxxxxxxxx>> 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
