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@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
