Re: Why index access is not happening in parallel
Date: Sat, 1 Jul 2023 10:33:06 +0100
Message-ID: <CAGtsp8nSvMJxiYzZN8XJ66_tksTytm1SZS-oYah8t41KGxtdQQ_at_mail.gmail.com>
>> People here are expecting the query which runs using parallel(4) with
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
On Sat, 24 Jun 2023 at 15:44, Pap <oracle.developer35_at_gmail.com> wrote:
> Hello Listers,
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?
Jonathan Lewis
> 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-lReceived on Sat Jul 01 2023 - 11:33:06 CEST