Re: Why index access is not happening in parallel
Date: Sun, 25 Jun 2023 13:11:58 +0530
Message-ID: <CAEzWdqeX6BgJ50_KDuvvDM38yK1nOGfEjctcOQ0uG83JhNY56w_at_mail.gmail.com>
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..
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 - 09:41:58 CEST