Re: Why index access is not happening in parallel

Thank You.
Actually both the tables(RTNI and TAB1) which getting used in the SELECT query which I posted are global temporary tables and are non partitioned and also the indexes are not partitioned too.The INSERT query is having the table RFFT partitioned and the index(RFFT_IX7) is local.

However according to that theory the SELECT query should not need parallel_index hint to go parallel index scan.

On Mon, Jun 26, 2023 at 11:45 AM yudhi s <learnerdatabase99_at_gmail.com> wrote:

> Is the index of the table you are dealing here is partitioned one? As that
> may need a parallel_index hint for making them work in parallel.
>
> https://jonathanlewis.wordpress.com/2016/08/31/parallel_index-hint/
>
> https://www.oreilly.com/library/view/oracle-parallel-processing/156592701X/ch04.html
>
>
>
> On Sun, Jun 25, 2023 at 8:45 PM Pap <oracle.developer35_at_gmail.com> wrote:
>
>> Thank You So much.
>>
>> Below is the outline section from the disply_cursor plan from the
>> production database.
>>
>> I do see a few underscore parameters and also fix_control parameters in
>> the outline section, but even when I tried copying the same as hint in a
>> sample query on my Dev database on your sample test case, I am unable to
>> reproduce the behavior there. It's going for parallel index access on the
>> dev database without any issue. Still not sure what is special about
>> production here?
>>
>> Both the tables used in our prod query are global temporary tables but I
>> believe that won't cause such behaviour.
>>
>> Outline Data
>> -------------
>> /*+
>> BEGIN_OUTLINE_DATA
>> IGNORE_OPTIM_EMBEDDED_HINTS
>> OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
>> DB_VERSION('19.1.0')
>> OPT_PARAM('optimizer_dynamic_sampling' 5)
>> OPT_PARAM('_optimizer_aggr_groupby_elim' 'false')
>> OPT_PARAM('_optimizer_reduce_groupby_key' 'false')
>> OPT_PARAM('_fix_control' '27268249:0')
>> ALL_ROWS
>> SHARED(8)
>> OUTLINE_LEAF(_at_"SEL$1")
>> FULL(_at_"SEL$1" "RTNI"_at_"SEL$1")
>> INDEX_RS_ASC(_at_"SEL$1" "TAB1"_at_"SEL$1" ("TAB1"."EID" "TAB1"."ETYP"))
>> LEADING(_at_"SEL$1" "RTNI"_at_"SEL$1" "TAB1"@"SEL$1")
>> USE_NL(_at_"SEL$1" "TAB1"_at_"SEL$1")
>> END_OUTLINE_DATA
>> */
>>
>> On Sun, Jun 25, 2023 at 8:16 PM yudhi s <learnerdatabase99_at_gmail.com>
>> wrote:
>>
>>> As Lothar also mentioned , you may see the parallelism for the index
>>> access path along for your INSERT once you set the enable_parallel_dml hint
>>> for that INSERT query. However I can't think of a reason why your
>>> standalone "select" query is not doing the Index scan in parallel?
>>>
>>> Tried with the exact same setup as I posted in my earlier example and I
>>> have added Decode and a SUM function along with group by and order by
>>> clause as its there in your query, still I see it's doing parallel
>>> processing for the index range scan and table access by index rowid. See
>>> below plan..
>>>
>>> Do you see anything special in your outline section of the SELECT query
>>> plan?
>>>
>>> explain plan for select /*+parallel(10) */ decode(object_name,
>>> 'abc','D', 'E'), object_name, sum(object_id)
>>> from x where object_id=123
>>> group by decode(object_name, 'abc','D', 'E'), object_name order by
>>> object_name;
>>>
>>>
>>> select plan_table_output from
>>> table(dbms_xplan.display('plan_table',null,'ADVANCED'));
>>>
>>> Plan hash value: 1269364911
>>>
>>>
>>> -----------------------------------------------------------------------------------------------------------------------------------
>>> | Id | Operation | Name | Rows |
>>> Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
>>>
>>> -----------------------------------------------------------------------------------------------------------------------------------
>>> | 0 | SELECT STATEMENT | | 1 |
>>> 30 | 5 (20)| 00:00:01 | | | |
>>> | 1 | PX COORDINATOR | | |
>>> | | | | | |
>>> | 2 | PX SEND QC (ORDER) | :TQ10002 | 1 |
>>> 30 | 5 (20)| 00:00:01 | Q1,02 | P->S | QC (ORDER) |
>>> | 3 | SORT GROUP BY | | 1 |
>>> 30 | 5 (20)| 00:00:01 | Q1,02 | PCWP | |
>>> | 4 | PX RECEIVE | | 1 |
>>> 30 | 5 (20)| 00:00:01 | Q1,02 | PCWP | |
>>> | 5 | PX SEND RANGE | :TQ10001 | 1 |
>>> 30 | 5 (20)| 00:00:01 | Q1,01 | P->P | RANGE |
>>> | 6 | HASH GROUP BY | | 1 |
>>> 30 | 5 (20)| 00:00:01 | Q1,01 | PCWP | |
>>> | 7 | TABLE ACCESS BY INDEX ROWID BATCHED| X | 1 |
>>> 30 | 4 (0)| 00:00:01 | Q1,01 | PCWP | |
>>> | 8 | PX RECEIVE | | 1 |
>>> | 3 (0)| 00:00:01 | Q1,01 | PCWP | |
>>> | 9 | PX SEND HASH (BLOCK ADDRESS) | :TQ10000 | 1 |
>>> | 3 (0)| 00:00:01 | Q1,00 | S->P | HASH (BLOCK|
>>> | 10 | PX SELECTOR | | |
>>> | | | Q1,00 | SCWC | |
>>> |* 11 | INDEX RANGE SCAN | IDX11 | 1 |
>>> | 3 (0)| 00:00:01 | Q1,00 | SCWP | |
>>>
>>> -----------------------------------------------------------------------------------------------------------------------------------
>>>
>>> Query Block Name / Object Alias (identified by operation id):
>>> -------------------------------------------------------------
>>>
>>> 1 - SEL$1
>>> 7 - SEL$1 / X_at_SEL$1
>>> 11 - SEL$1 / X_at_SEL$1
>>>
>>> Outline Data
>>> -------------
>>>
>>> /*+
>>> BEGIN_OUTLINE_DATA
>>> USE_HASH_GBY_FOR_PUSHDOWN(_at_"SEL$1")
>>> GBY_PUSHDOWN(_at_"SEL$1")
>>> BATCH_TABLE_ACCESS_BY_ROWID(_at_"SEL$1" "X"_at_"SEL$1")
>>> INDEX_RS_ASC(_at_"SEL$1" "X"_at_"SEL$1" ("X"."OBJECT_ID"))
>>> OUTLINE_LEAF(_at_"SEL$1")
>>> ALL_ROWS
>>> OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
>>> OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
>>> OPT_PARAM('_px_adaptive_dist_method' 'off')
>>> DB_VERSION('19.1.0')
>>> OPTIMIZER_FEATURES_ENABLE('19.1.0')
>>> IGNORE_OPTIM_EMBEDDED_HINTS
>>> END_OUTLINE_DATA
>>> */
>>>
>>> Predicate Information (identified by operation id):
>>> ---------------------------------------------------
>>>
>>> 11 - access("OBJECT_ID"=123)
>>>
>>> Column Projection Information (identified by operation id):
>>> -----------------------------------------------------------
>>>
>>> 1 - "OBJECT_NAME"[VARCHAR2,128],
>>> DECODE("OBJECT_NAME",'abc','D','E')[1], SUM()[22]
>>> 2 - (#keys=0) "OBJECT_NAME"[VARCHAR2,128],
>>> DECODE("OBJECT_NAME",'abc','D','E')[1], SUM()[22]
>>> 3 - (#keys=2; rowset=256) "OBJECT_NAME"[VARCHAR2,128],
>>> DECODE("OBJECT_NAME",'abc','D','E')[1], SUM()[22]
>>> 4 - (rowset=256) "OBJECT_NAME"[VARCHAR2,128],
>>> DECODE("OBJECT_NAME",'abc','D','E')[1], SYS_OP_MSR()[25]
>>> 5 - (#keys=2) "OBJECT_NAME"[VARCHAR2,128],
>>> DECODE("OBJECT_NAME",'abc','D','E')[1], SYS_OP_MSR()[25]
>>> 6 - (#keys=2; rowset=256) "OBJECT_NAME"[VARCHAR2,128],
>>> DECODE("OBJECT_NAME",'abc','D','E')[1], SYS_OP_MSR()[25]
>>> 7 - "OBJECT_ID"[NUMBER,22], "OBJECT_NAME"[VARCHAR2,128]
>>> 8 - "X".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
>>> 9 - (#keys=2) "X".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
>>> 10 - "X".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
>>> 11 - "X".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
>>>
>>> Hint Report (identified by operation id / Query Block Name / Object
>>> Alias):
>>> Total hints for statement: 1
>>>
>>> ---------------------------------------------------------------------------
>>>
>>> 0 - STATEMENT
>>> - parallel(10)
>>>
>>> Note
>>> -----
>>> - Degree of Parallelism is 10 because of hint
>>>
>>> Query Block Registry:
>>> ---------------------
>>>
>>> <q o="2"
>>> f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[X]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
>>>
>>> On Sun, Jun 25, 2023 at 2:00 PM Pap <oracle.developer35_at_gmail.com>
>>> wrote:
>>>
>>>> Below is the Note section of the INSERT and SELECT query. Both the
>>>> tables used in the SELECT query are Global temporary tables , but I believe
>>>> that should not cause this oddity in the parallel execution plan behaviour.
>>>> and Also "Select" query won't need any such parameter as 'enable parallel
>>>> dml' to be set to the execution to happen in parallel. Correct me if I'm
>>>> wrong. So, I am still unable to understand, why don't we have Index access
>>>> being parallelized here and how could we fix that?
>>>>
>>>>
>>>> *Insert query:- *
>>>> Note
>>>> -----
>>>> - PDML is disabled in current session
>>>> - parallel query server generated this plan using optimizer hints
>>>> from coordinator
>>>>
>>>>
>>>> *Select query:- *
>>>> Note
>>>> -----
>>>> - dynamic statistics used: dynamic sampling (level=5)
>>>> - Degree of Parallelism is 4 because of hint
>>>> - Global temporary table session private statistics used
>>>>
>>>> Also Lothar as you pointed out few key things, i was trying to relook
>>>> into the sql monitoring reports of both of my posted queries:-
>>>>
>>>> In the select query too the "parallel execution" section of the sql
>>>> monitoring report(as shown one below and detail plan is here :-
>>>> https://gist.github.com/oracle9999/ff2073c222398416c8095d109c233765) ,
>>>> It shows majority of time being spent on the "PX Coordinator" step(same as
>>>> INSERT query case). However if i see the "SQL Plan Monitoring Details"
>>>> section it shows top activity is on the Index access and table access
>>>> section of TAB1. Whereas in case of the sql monitoring report of the
>>>> "INSERT query" both the sections were showing top contributors as "PX
>>>> Coordinator". Isn't this discrepancy?
>>>>
>>>> and thus I was thinking the figures which were collected in the
>>>> gv$active_session_history would be reliable. And that was showing the
>>>> majority of the samples were spending time on the "index access" and "table
>>>> access by index rowid" part of the plan in both the Insert and select query
>>>> cases. Correct me if I'm wrong.
>>>>
>>>>
>>>> Parallel Execution Details (DOP=8 , Servers Allocated=8)
>>>>
>>>> =======================================================================================================================================================================================
>>>> | Name | Type | Server# | Elapsed | Cpu | IO |
>>>> Application | Concurrency | Cluster | Buffer | Read | Read |
>>>> Wait Events |
>>>> | | | | Time(s) | Time(s) | Waits(s) |
>>>> Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes |
>>>> (sample #) |
>>>>
>>>> =======================================================================================================================================================================================
>>>> | PX Coordinator | QC | | 849 | 218 | 533 |
>>>> 2.41 | 0.11 | 96 | 1M | 1M | 9GB | enq: RO - fast
>>>> object reuse (2) |
>>>> | | | | | | |
>>>> | | | | | | gc cr grant
>>>> 2-way (25) |
>>>> | | | | | | |
>>>> | | | | | | gc cr multi
>>>> block request (1) |
>>>> | | | | | | |
>>>> | | | | | | gc cr request
>>>> (1) |
>>>> | | | | | | |
>>>> | | | | | | gc current grant
>>>> 2-way (23) |
>>>> | | | | | | |
>>>> | | | | | | gc current
>>>> request (1) |
>>>> | | | | | | |
>>>> | | | | | | cell list of
>>>> blocks physical read (3) |
>>>> | | | | | | |
>>>> | | | | | | cell multiblock
>>>> physical read (5) |
>>>> | | | | | | |
>>>> | | | | | | cell single
>>>> block physical read (114) |
>>>> | | | | | | |
>>>> | | | | | | cell single
>>>> block physical read: flash cache (424) |
>>>> | | | | | | |
>>>> | | | | | | cell single
>>>> block read request (3) |
>>>> | p000 | Set 1 | 1 | 0.01 | 0.00 | 0.00 |
>>>> | | | 12 | 5 | 40960 |
>>>> |
>>>> | p001 | Set 1 | 2 | 0.01 | 0.00 | 0.00 |
>>>> | | 0.00 | 12 | 4 | 32768 |
>>>> |
>>>> | p002 | Set 1 | 3 | 0.00 | 0.00 | 0.00 |
>>>> | 0.00 | | 9 | 3 | 24576 |
>>>> |
>>>> | p003 | Set 1 | 4 | 0.01 | 0.00 | 0.00 |
>>>> | 0.00 | 0.00 | 12 | 4 | 32768 |
>>>> |
>>>> | p004 | Set 1 | 5 | 0.01 | 0.00 | 0.00 |
>>>> | 0.00 | | 12 | 4 | 32768 |
>>>> |
>>>> | p005 | Set 1 | 6 | 0.01 | 0.00 | 0.00 |
>>>> | | 0.00 | 12 | 4 | 32768 |
>>>> |
>>>> | p006 | Set 1 | 7 | 0.01 | 0.00 | 0.00 |
>>>> | | | 12 | 4 | 32768 |
>>>> |
>>>> | p007 | Set 1 | 8 | 0.01 | 0.00 | 0.00 |
>>>> | | | 12 | 4 | 32768 |
>>>> |
>>>>
>>>> =======================================================================================================================================================================================
>>>>
>>>> SQL Plan Monitoring Details (Plan Hash Value=2978234190)
>>>>
>>>> ======================================================================================================================================================================================================================
>>>> | Id | Operation | Name |
>>>> Rows | Cost | Time | Start | Execs | Rows | Read | Read |
>>>> Mem | Activity | Activity Detail |
>>>> | | | |
>>>> (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes |
>>>> (Max) | (%) | (# samples) |
>>>>
>>>> ======================================================================================================================================================================================================================
>>>> | 0 | SELECT STATEMENT | |
>>>> | | 746 | +4 | 1 | 23 | | |
>>>> . | | |
>>>> | 1 | SORT GROUP BY | |
>>>> 33 | 2M | 746 | +4 | 1 | 23 | | |
>>>> 10240 | | |
>>>> | 2 | NESTED LOOPS | |
>>>> 28007 | 2M | 746 | +4 | 1 | 42428 | | |
>>>> . | | |
>>>> | 3 | PX COORDINATOR | |
>>>> | | 749 | +1 | 9 | 699 | | |
>>>> . | 0.27 | enq: RO - fast object reuse (2) |
>>>> | 4 | PX SEND QC (RANDOM) | :TQ10000 |
>>>> 699 | 2 | 16 | +2 | 8 | 699 | | |
>>>> . | | |
>>>> | 5 | PX BLOCK ITERATOR | |
>>>> 699 | 2 | 16 | +2 | 8 | 699 | | |
>>>> . | | |
>>>> | 6 | TABLE ACCESS STORAGE FULL | RTNI |
>>>> 699 | 2 | 16 | +2 | 31 | 699 | 32 | 256KB |
>>>> . | | |
>>>> | 7 | TABLE ACCESS BY INDEX ROWID BATCHED | TAB1 |
>>>> 40 | 21849 | 747 | +3 | 699 | 42428 | 791K | 6GB |
>>>> . | 57.40 | gc cr grant 2-way (17) |
>>>> | | | |
>>>> | | | | | | | |
>>>> | | gc cr multi block request (1) |
>>>> | | | |
>>>> | | | | | | | |
>>>> | | gc cr request (1) |
>>>> | | | |
>>>> | | | | | | | |
>>>> | | gc current grant 2-way (16) |
>>>> | | | |
>>>> | | | | | | | |
>>>> | | Cpu (86) |
>>>> | | | |
>>>> | | | | | | | |
>>>> | | cell list of blocks physical read (3) |
>>>> | | | |
>>>> | | | | | | | |
>>>> | | cell multiblock physical read (5) |
>>>> | | | |
>>>> | | | | | | | |
>>>> | | cell single block physical read: flash cache (289) |
>>>> | | | |
>>>> | | | | | | | |
>>>> | | cell single block read request (1) |
>>>> | 8 | INDEX RANGE SCAN | TAB1_IX1 |
>>>> 272K | 16266 | 746 | +4 | 699 | 56M | 366K | 3GB |
>>>> . | 42.33 | gc cr grant 2-way (8) |
>>>> | | | |
>>>> | | | | | | | |
>>>> | | gc current grant 2-way (7) |
>>>> | | | |
>>>> | | | | | | | |
>>>> | | gc current request (1) |
>>>> | | | |
>>>> | | | | | | | |
>>>> | | Cpu (42) |
>>>> | | | |
>>>> | | | | | | | |
>>>> | | cell single block physical read (114) |
>>>> | | | |
>>>> | | | | | | | |
>>>> | | cell single block physical read: flash cache (135) |
>>>> | | | |
>>>> | | | | | | | |
>>>> | | cell single block read request (2) |
>>>>
>>>> ======================================================================================================================================================================================================================
>>>>
>>>> On Sun, Jun 25, 2023 at 1:36 PM Pap <oracle.developer35_at_gmail.com>
>>>> wrote:
>>>>
>>>>> 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-l