Re: Why index access is not happening in parallel

Of course you could partition by customer id. But now I wonder how the rest of the data is organized.
How to you separate the data for different customers anyway? Seems like I still do not get the point here.

Am 04.07.2023 um 19:27 schrieb Pap:
> 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