Re: Why index access is not happening in parallel

With regards to your design, How about using individual heap table for each customer, so that way you will be able to get rid of global temporary tables in your queries.

On Sat, 1 Jul, 2023, 4:34 pm Pap, <oracle.developer35_at_gmail.com> wrote:

> 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