Re: Why index access is not happening in parallel

Actually we have many customers for which these job/report runs at same time concurrently. Having different heap tables for each customer won't be a feasible solution I think.

On Tue, 4 Jul, 2023, 10:01 pm yudhi s, <learnerdatabase99_at_gmail.com> wrote:

> 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