Re: Why index access is not happening in parallel

Why would it do the insert in parallel when the notes (and other readers) have already told you that PDML will have been disabled because of the indexes on GTTs?
The only point I was addressing in my comment was the one that hadn't been raised by anyone else - the side effect of direct path loads and index maintenance.

Since you've posted a new page to github I have looked at it - and any further comment is based only on that page and not on any previous material you may have posted.

1. the SELECT: the "Rows (Actual)" column tells you that the nested loop found 56 million index entries (operation 8), which means you visited the table 56M times (operation 7), where "Rows (Actual)" is 42,428 - so you spent most of your time reading data that you then threw away. The index is not a suitable index for that query so you need to find out why not: did Oracle pick the wrong index, or is this the best index but in need of an extra column or two, possibly rearranging the order, so that you can do a smaller range scan and visit the table far fewer times.
2. the INSERT: processes approximately 3M rows, and as I've said, this may be sorting and merging to add index entries, so you need to review what indexes you have on the the inserted table. If you have only one index then POSSIBLY an insert with an ORDER BY clause that matches the index definition might make the insert and subsequence queries faster. Most of the elapsed time, though, may be due to a bad choice of execution plan - there's a massive discrepancy between the estimated and the actual row counts; add to this that the Set 1 parallel servers show a distinct skew in the amount of data processed, p00t and p00v seem to do most of the work, and doing 3M index and table probes will be expensive. You might be able to eliminate the workload skew if you recreate RTFX (which appears to be a very small table) with a large PCTFREE (e.g. 90%) You should also see what you can do by changing that part of the plan (operations 5,6, and 12-18) - different table ordering, or different join methods may help (hash joins look more appropriate, but I don't know your data so I can't really comment).

> 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
>>
>>
>>

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