Re: Why index access is not happening in parallel

On 6/24/23 10:43, Pap 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

Well, the only way to find out why is the optimizer choosing parallel(2) instead of parallel(4) is to take the 10053 trace. My guess would be that some of the objects involved have a non-zero degree of parallelism in their definition. That happens when indexes are rebuilt in parallel. The best place to start reading about the 10053 trace is the following:

https://jonathanlewis.wordpress.com/2014/05/23/10053-trace/

Second, range scan _*never*_ happens in parallel. Maybe not even then. If a range scan should retrieve so much data that parallel execution would be beneficial, you're doing it wrong.

Lastly, Exadata is a data warehouse machine, so I assume that your DB is a data warehouse. If so, you should check stuff like star schema and snowflake schema (not to be confused with the RDBMS brand sold on the major clouds) and maybe even read Ralph Kimball's  "Data Warehouse Toolkit".  Data warehousing philosophy is not the same as the OLTP philosophy. There are tricks of the trade. I strongly advise Kimball's book.

Regards

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

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