Why index access is not happening in parallel
Date: Sat, 24 Jun 2023 20:13:13 +0530
Message-ID: <CAEjw_fheKT8s6tLkP_PycZgMGm0-OzpNG1D49rWX3nEAKzBv0w_at_mail.gmail.com>
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-lReceived on Sat Jun 24 2023 - 16:43:13 CEST