Re: Why index access is not happening in parallel
From: Pap <oracle.developer35_at_gmail.com>
Date: Sun, 25 Jun 2023 13:36:11 +0530
Message-ID: <CAEjw_fi+vx0d4E5djcgvmrYDpjcA8UTX44Kkj1AmqrfAFQ_J3w_at_mail.gmail.com>
Plan hash value: 2707744295
> 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'));
> On Sun, Jun 25, 2023 at 2:13 AM Mladen Gogala <gogala.mladen_at_gmail.com>
> wrote:
Date: Sun, 25 Jun 2023 13:36:11 +0530
Message-ID: <CAEjw_fi+vx0d4E5djcgvmrYDpjcA8UTX44Kkj1AmqrfAFQ_J3w_at_mail.gmail.com>
Thank you so much Lothar, Yudhi.
I think you guys are spot on. I don't see 'enable parallel dml' in the procedure in which this query is getting executed from and also no such hints.
And another thing which you posted and also i am seeing (even i ran your sample SELECT query independently without INSERT). is also doing index access in parallel( "PX SELECTOR" above "index range scan" and "PX SEND QC (RANDOM)" above the "TABLE ACCESS BY INDEX ROWID BATCHED" line in the plan) as its showing in the plan as below. So it's clear that the INSERT can be made parallel. But I am still unable to understand why in the case of my independent SELECT query which I posted in the thread initially , the index access is getting serialized and if there is any way to make that parallelized?
explain plan for select /*+parallel(10) */ * from x where object_id=123 ;
select plan_table_output from
table(dbms_xplan.display('plan_table',null,'ADVANCED'));
PLAN_TABLE_OUTPUT
Plan hash value: 2707744295
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT 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 | TABLE ACCESS BY INDEX ROWID BATCHED| X | 1 | 30 | 4 (0)| 00:00:01 | Q1,01 | PCWP | | | 4 | BUFFER SORT | | | | | | Q1,01 | PCWC | | | 5 | PX RECEIVE | | 1 | | 3 (0)| 00:00:01 | Q1,01 | PCWP | | | 6 | PX SEND HASH (BLOCK ADDRESS) | :TQ10000 | 1 | | 3 (0)| 00:00:01 | Q1,00 | S->P | HASH (BLOCK| | 7 | PX SELECTOR | | | | | | Q1,00 | SCWC | | |* 8 | INDEX RANGE SCAN | IDX11 | 1 | | 3 (0)| 00:00:01 | Q1,00 | SCWP | | -------------------------------------------------------------------------------------------------------------------------------
On Sun, Jun 25, 2023 at 1:21 PM Lothar Flatz <l.flatz_at_bluewin.ch> wrote:
> 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-1217https://dbwhisperer.wordpress.com >> >> >
-- http://www.freelists.org/webpage/oracle-lReceived on Sun Jun 25 2023 - 10:06:11 CEST