Re: Question related to 'direct path read"

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_at_hotmail.com> 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_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-l