Re: Why index access is not happening in parallel

Recaptured the plan for both the INSERT and SELECT again as below and after using Enable_parallel_dml the conventional insert does changed to 'Load as select' which means its doing direct path load, however as the note section showing , it's still not doing the write/Insert in parallel. and Majority of time and resources get spent while writing/Inserting records only.

https://gist.github.com/oracle9999/8805315be86cc9b74c52609f73bb4e03

On Sat, Jul 1, 2023 at 3:56 PM Pap <oracle.developer35_at_gmail.com> wrote:

> Thank you Jonathan.
>
> Actually i posted two queries in this thread earlier (as below) one was
> INSERT and Other was SELECT. Lothar pointed out in both of the cases the
> execution time was spent in the query coordinator section and how the
> complete execution path of the query was not happening in parallel. Some
> parts were being serialized.
>
> https://gist.github.com/oracle9999/ff2073c222398416c8095d109c233765
> https://gist.github.com/oracle9999/618251c1e48b315dc70c73e157443773
>
> In case of INSERT query as you see, the parallel execution was not
> happening initially and it was enable_parallel_dml which was missing. But
> after applying that hint, I got the below message in the Note section of
> the query , which exposes the restriction which GTT is having in oracle.
>
> Note
> -----
> * - PDML disabled because temporary tables with indexes used*
> - parallel query server generated this plan using optimizer hints from
> coordinator
>
> The other thread I initiated to put this observation in a cleaner fashion,
> and to see if any workaround is possible as we use global temporary tables
> heavily in one of the customer applications and this restriction with
> parallel execution for indexes does not feel good. And correct me if wrong,
> I believe converting all the queries(noth INSERT/SELECT) into UNION ALL to
> avoid this restriction is not a good idea and also it may not always be
> possible I think.
>
>
>
> On Sat, Jul 1, 2023 at 3:04 PM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> >> People here are expecting the query which runs using parallel(4) with
>> Append should be faster as compared to the other one (i.e. with just
>> parallel(2)). But it's not happening that way. Want to understand why?
>>
>> I see there's already a trail of notes about the index range scan not
>> running parallel, but I can't see any comment addressing the your question
>> about the APPEND hint. One possible explanation is that the index
>> maintenance mechanisms for "load as select" are different from those used
>> for "conventional load".
>>
>> Unless things have changed in recent versions of Oracle:
>> Under conventional load each index is maintained in "real time", i.e.
>> as rows are inserted.
>> Under direct path load Oracle creates an index segment on the
>> appended table segment, then merges the original index with the new index
>> segment and writes a new index segment.
>>
>> I'm sure I wrote a document about this once, but I can't find it at
>> present. The thing that's most likely to have changed is the way that
>> Oracle merges the newly created index segment with the existing index
>> segments - and there may be all sorts of variations anyway for partitioned
>> tables, parallel loads, and GTTs.
>>
>> Depending on the number of indexes, the size of the table and its
>> indexes, and the volume of data loaded a conventional load might,
>> therefore, do less work than the direct path load.
>>
>>
>> Regards
>> Jonathan Lewis
>>
>>
>>
>> On Sat, 24 Jun 2023 at 15:44, Pap <oracle.developer35_at_gmail.com> wrote:
>>
>>> Hello Listers,
>>> This Oracle database version 19C and its exadata. We have a customer
>>> query in which one of the executions is happening in parallel(2) and
>>> another execution is happening in parallel(4) in two different places. The
>>> query is exactly the same , just that the place in which it's getting used
>>> with PARALLEL(4) it's also happening with the APPEND hint.
>>>
>>> People here are expecting the query which runs using parallel(4) with
>>> Append should be faster as compared to the other one (i.e. with just
>>> parallel(2)). But it's not happening that way. Want to understand why?
>>>
>>> And also in this query the step no which is coming as top contributor in
>>> ASH is table access RFFT using index access path - RFFT_IX7. This is a big
>>> table partitioned on column C_KEY. Other tables are small tables. So just
>>> wondering if the higher parallel threads were not helping the query because
>>> the index access is happening using BATCHED index range scan (i.e. TABLE
>>> ACCESS BY LOCAL INDEX ROWID BATCHED). or are we misinterpreting the
>>> execution plan here and something in the query is not making the index scan
>>> to happen in parallel?
>>>
>>> Below is the sql monitor details of the two plans. Below are two sql
>>> monitors, in one case it has processed 2million vs other 3million , however
>>> if we see the processing speed i.e. ~67 seconds for 3million vs 38 seconds
>>> for 2million. The throughput seems to be more or less the same only. The
>>> Parallel-8+ Append seems to be comparatively slower though, which is odd.
>>>
>>> https://gist.github.com/oracle9999/618251c1e48b315dc70c73e157443773
>>>
>>> Regards
>>> Pap
>>>
>>

--
http://www.freelists.org/webpage/oracle-l