Question related to 'direct path read"

[Hameed, Amir]

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

[Dominic Brooks]

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 <***@xerox.com<mailto:***@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

[Dominic Brooks]

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 <***@xerox.com<mailto:***@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

[Harmandeep Singh]

Hi Dominic,

I am fearful of this PARALLEL_DEGREE_POLICY . I have seen customers where
when it set to AUTO made even the serial queries going parallel in
Oracle Ebusiness Env

I feel it should be set to MANUAL . please share your thoughts about same

Regards,
Harman

Post by Dominic Brooks
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
Hi,
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

[Hameed, Amir]

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 <***@hotmail.com>
Sent: Friday, November 30, 2018 1:03 AM
To: Hameed, Amir <***@xerox.com>
Cc: ORACLE-L <oracle-***@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 <***@xerox.com<mailto:***@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