Re: Why index access is not happening in parallel

Hi,

true, that the most obvious reason. Did not think about it. Parallel_dml could be enabled in the session too.

Thanks

Lothar

Am 25.06.2023 um 09:41 schrieb yudhi s:
> I see , your hint is not having 'Enable_parallel_DML' in it . Did you
> check if the INSERT is happening in the session which is having
> 'enable parallel dml' set? I believe it has to be set explicitly for
> any DML to go for parallel processing. See below example..
>
> Also I see "TABLE ACCESS BY INDEX ROWID BATCHED '' is happening and is
> showing parallel executions in the plan, so the thought which you
> initially posted as "batched index access" (which is new in 12c) can
> not be parallelized seems not right. Others can comment on it.
>
> create  table t ( id number , c1 varchar2(130) )  ;
> create table x as select object_id , object_name from dba_objects ;
> create index indx11 on x(object_id);
> explain plan for  insert into t select * from x ;
>   select * from table(dbms_xplan.display) ;
>
>   explain plan for insert /*+APPEND enable_parallel_dml */ into t
> select /*+parallel(10)  */ * from x where object_id=123 ;
>   select plan_table_output  from
> table(dbms_xplan.display('plan_table',null,'ADVANCED'));
>
>
> ---------------------------------------------------------------------------------------------------------------------------------
> | Id  | Operation                               | Name     | Rows  |
> Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
> ---------------------------------------------------------------------------------------------------------------------------------
> |   0 | INSERT STATEMENT                        |          |   1 |  
>  30 |     4   (0)| 00:00:01 |        |      |    |
> |   1 |  PX COORDINATOR                         |          |     |    
>   |            |          |        |      |    |
> |   2 |   PX SEND QC (RANDOM)                   | :TQ10001 |   1 |  
>  30 |     4   (0)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
> |   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)     | T        |     |    
>   |            |          |  Q1,01 | PCWP |    |
> |   4 |     OPTIMIZER STATISTICS GATHERING      |          |   1 |  
>  30 |     4   (0)| 00:00:01 |  Q1,01 | PCWP |    |
> |   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| X        |   1 |  
>  30 |     4   (0)| 00:00:01 |  Q1,01 | PCWP |    |
> |   6 |       PX RECEIVE                        |          |   1 |    
>   |     3   (0)| 00:00:01 |  Q1,01 | PCWP |    |
> |   7 |        PX SEND HASH (BLOCK ADDRESS)     | :TQ10000 |   1 |    
>   |     3   (0)| 00:00:01 |  Q1,00 | S->P | HASH (BLOCK|
> |   8 |         PX SELECTOR                     |          |     |    
>   |            |          |  Q1,00 | SCWC |    |
> |*  9 |          INDEX RANGE SCAN               | IDX11    |   1 |    
>   |     3   (0)| 00:00:01 |  Q1,00 | SCWP |    |
> ---------------------------------------------------------------------------------------------------------------------------------
>
> On Sun, Jun 25, 2023 at 2:13 AM Mladen Gogala
> <gogala.mladen_at_gmail.com> wrote:
>
> 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