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
On Fri, Nov 30, 2018 at 11:33 AM Dominic Brooks <dombrooks@xxxxxxxxxxx>
wrote:
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> 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
