Performance overhead in parallel direct path insert with non-partitioned index

Recently I performed a simple test that in case of partitioned or non-partitioned index exists how much the performance difference will be there when a parallel direct path insert job was performed.
To be precise for the test, of course, there must be a exact calculation on the numbers of statistics of v$sysstat, but I choose Deacon Session monitor to just briefly monitor the operation. Because that will be enough to distinguish the difference.


-------------------------
--Create the Test Tables
-------------------------
--source table
drop table emp_src;
create table emp_src as 
select * from emp
;
insert into emp_src
select * from emp_src
;
--execute several times

commit;

--confirm the segment size
SQL > @lseg emp_src

SEGMENT_NAME                   SEGMENT_TYPE               BLOCKS  [MB/8192]
------------------------------ -------------------- ------------ ----------
EMP_SRC                        TABLE                      97,792        764

--target table
drop table emp_tg1;
drop table emp_tg2;
drop table emp_tg3;

create table emp_tg1 partition by hash(empno) partitions 8 nologging as 
select * from emp_src where rownum <= 1000000
;
create table emp_tg2 partition by hash(empno) partitions 8 nologging as 
select * from emp_src where rownum <= 1000000
;
create table emp_tg3 partition by hash(empno) partitions 8 nologging as 
select * from emp_src where rownum <= 1000000
;

-------------------------------------------
--Create the Test Indexes and execute load
-------------------------------------------
--case 1. partitioned local index
drop index emp_tg1_n1;
create index emp_tg1_n1 on emp_tg1(empno) 
local 
nologging;

alter session enable parallel dml;

insert /*+parallel(8) */ into emp_tg1 
select * from emp_src;
commit;

--case 2. non-partitioned index
drop index emp_tg2_n1;
create index emp_tg2_n1 on emp_tg2(empno) 
nologging;

alter session enable parallel dml;

insert /*+parallel(8) */ into emp_tg2 
select * from emp_src;
commit;

--case 3. partitioned global index
drop index emp_tg3_n1;
create index emp_tg3_n1 on emp_tg3(empno) 
global partition by hash(empno) partitions 8
nologging;

alter session enable parallel dml;

insert /*+parallel(8) */ into emp_tg3 
select * from emp_src;
commit;


--All 3 cases are same in execution plan except the segment names

Execution Plan
-----------------------------------------------------------
   0      INSERT STATEMENT Optimizer=FIRST_ROWS (Cost=3 Card=572 Bytes=49K)
   1    0   PX COORDINATOR
   2    1     PX SEND (QC (RANDOM)) OF 'SYS.:TQ10002' (Cost=3 Card=572 Bytes=49K) (PARALLEL_TO_SERIAL) (QC (RANDOM))
   3    2       INDEX MAINTENANCE OF 'EMP_TG1' (PARALLEL_COMBINED_WITH_PARENT)
   4    3         PX RECEIVE (Cost=3 Card=572 Bytes=49K) (PARALLEL_COMBINED_WITH_PARENT)
   5    4           PX SEND (RANGE) OF 'SYS.:TQ10001' (Cost=3 Card=572 Bytes=49K) (PARALLEL_TO_PARALLEL) (RANGE)
   6    5             LOAD AS SELECT OF 'EMP_TG1' (PARALLEL_COMBINED_WITH_PARENT)
   7    6               PX RECEIVE (Cost=3 Card=572 Bytes=49K) (PARALLEL_COMBINED_WITH_PARENT)
   8    7                 PX SEND (ROUND-ROBIN) OF 'SYS.:TQ10000' (Cost=3 Card=572 Bytes=49K) (PARALLEL_FROM_SERIAL) (ROUND-ROBIN)
   9    8                   TABLE ACCESS (FULL) OF 'EMP_SRC' (TABLE) (Cost=3 Card=572 Bytes=49K)
-----------------------------------------------------------

----------------------
Results of Monitoring
----------------------
Note. Below are the results of Deacon session monitor that was bit edited for better view.
      And individual lines are printed in every 5 seconds.

--case 1. partitioned local index
--------------------------------------------------------------------------
PXs |  Exec   Logrds  Phyrds   Drrds  Phywts   Drwts   PGA[m] Rdo[m] Lfspl
-------------m-------m-------m--------------------------------------------
  0 |     0        0       0       0       0       0       3       0     0
  0 |     0        0       0       0       0       0       3       0     0
  0 |     0        0       0       0       0       0       3       0     0
 16 |     5     1037       0       0     213     213     106       0     0
 16 |     9     5669       0       0    3613    3613      88       0     0
 16 |     9     5903       0       0    3633    3633     102       0     0
 16 |     9     5738       0       0    3713    3713     109       0     0
 16 |     0        6   14541   14540    4394    4394     227       0     0
 16 |     0       12    3510    3510    3592    3592      76       0     0
  0 |    58   201014    6122    6105       0       0       3   166.5  6559
  0 |     0        0       0       0       0       0       3       0     0
  0 |     0        0       0       0       0       0       3       0     0
  0 |     0        0       0       0       0       0       3       0     0

--case 2. non-partitioned index
--------------------------------------------------------------------------
PXs |  Exec   Logrds  Phyrds   Drrds  Phywts   Drwts   PGA[m] Rdo[m] Lfspl
-------------m-------m-------m--------------------------------------------
  0 |     0        0       0       0       0       0       3       0     0
  0 |     0        0       0       0       0       0       3       0     0
 16 |    10     4218       1       0    2401    2401     101       0     0
 16 |     9     5740       0       0    3663    3663      93       0     0
 16 |     7     4644       0       0    2880    2880     104       0     0
 16 |     9     5794       0       0    3662    3662      99       0     0
 16 |     0        2    2255    2255    1530    1530      57       0     0
 16 |     0        4    1200    1200    1189    1189      67       0     0
 16 |     0        4    1275    1275     859     859     136       0     0
 16 |     0        5    1410    1410    1871    1871      85       0     0
 16 |    16    31360   12544   12526   11736   11736      55    28.3  1115
  0 |    20    99631    2782    2716       0       0       3    81.9  3235
  0 |     0        0       0       0       0       0       3       0     0
  0 |     0        0       0       0       0       0       3       0     0

--case 3. partitioned global index
--------------------------------------------------------------------------
PXs |  Exec   Logrds  Phyrds   Drrds  Phywts   Drwts   PGA[m] Rdo[m] Lfspl
-------------m-------m-------m--------------------------------------------
  0 |     0        0       0       0       0       0       3       0     0
  0 |     0        0       0       0       0       0       3       0     0
 16 |     7     2369       2       0    1159    1159      92       0     0
 16 |     8     5534       0       0    3622    3622      97       0     0
 16 |     8     5346       0       0    3351    3351     105       0     0
 16 |     9     5883       0       0    3874    3874      96       0     0
 16 |     4     2131   17886   17886   17163   17163      59       0     0
 16 |     0       16    5266    5266    5323    5323      79       0     0
 16 |    71   107435   14597   14411   11131   11131      57    98.5  3845
  0 |     3    11793     378     332       0       0       3    10.1   398
  0 |     0        0       0       0       0       0       3       0     0


For this is a direct path insert and the target table is Nologging mode, Redo are generated only when index 'leaf node splits' (Lfspl) occur in Index Maintenance phase. And the sort operation must be completed before this write operation. Probably the sort operation started when 'Physical reads direct' (Drrds) begins to be appear in the monitoring.

As expected, case. 2 is the longest. Probably because the number of the index segment is only one, so sort and write operation have to be done with one process, whereas in other two test cases the same jobs could be done with 8 processes.

Keeping open to love

Written by Paulo Coelho

There are moments when we would like very much to help someone we love deeply and we just can't seem to do a thing. Either circumstances prevent us from drawing closer or else the person has shut off to any gesture of solidarity and support.

So, all we have left is love. In those moments when everything is useless, we can still love – without expecting anything in return, any exchanges or thanks.

If we can manage to act in this way, the energy of love begins to transform the universe around us. When this energy appears, you always perform your work successfully.

"Time does not change men. Will power does not change men. Love changes men," says Henry Drummond.

I read in the newspaper about a child in Brasília who was brutally beaten by his parents. As a result, she lost her body movements and her power of speech.

Admitted to the Base Hospital, she was taken care of by a nurse who said to her every day: "I love you." Although the doctors guaranteed that she could not hear and that the nurse's efforts were all to no avail, she kept repeating: "I love you, don't you forget that."

Three weeks later on, the child had recovered her movements. Four weeks later, she started to talk and smile again. The nurse never gave any interviews and the newspapers did not publish her name – but let it be registered here, so that we will never forget: love is a great healer.

Love transforms, love heals. But at times love builds mortal traps and ends up destroying the person who has decided to surrender completely. What strange sentiment is this that deep down is the only reason for us to go on living and struggling and trying to make things better?

It would irresponsible of me to try to define it because, like any other human being, all I can do is feel it. Thousands of books have been written about it, plays put on at the theater, films produced, poems scribbled, sculptures carved in wood or marble – and even so, all that the artist can convey is the idea of a feeling, not the feeling itself.

But I have learned that this feeling is present in the small things and manifests itself in the most insignificant of attitudes we take, so we must always have love in mind when we act or fail to act.

Picking up the phone and uttering that affectionate word we have been putting off. Opening the door and showing in someone who needs our help. Accepting a job. Leaving a job. Making that decision that we were putting off for later. Apologizing for a mistake we made that will not leave us in peace. Claiming a right that we have. Opening an account at the florist's – which is more important than the jeweler's. Playing the music loud when your loved one is far away and lower the volume when he or she is nearby. Knowing how to say "yes" and "no" – because love involves all of man's energies. Discovering a sport that can be practiced by two. Not following any prescription, not even those listed in this paragraph – because love calls for creativity.

And when none of this is possible, when all that is left is loneliness, then remember a story that a reader once sent me:

A rose dreamed day and night about having the company of the bees, but none ever came to land on her petals.

But the flower went on dreaming: during many a long night she imagined a sky with lots of bees flying towards her and kissing her tenderly. In this way she managed to resist to the next day, when she opened again to the sunlight.

One night the moon, knowing how lonely the rose felt, asked her:

– Aren't you tired of waiting?

– Perhaps. But I have to struggle on.

– Why?

– Because if I don't open up, I will wither.

At moments when loneliness seems to crush all beauty, the only way to resist is to keep yourself open.

Special Tuning Approach - selectively reorg the big customer's rows

For tuning the query that retrieves many rows made by big customers, it is needed some special technical approach.
Generally big customers make big rows compare to normal customers. So in using same query(SQL) there are huge differences in response time.
For example, in the ORDER_HIST table, normal customers make around 100 rows in a month whereas the big makes 10000 rows.
Response time of the normal is around 1 second whereas the big 100 seconds above.
In this case we can tune the query simply by reorganizing the rows of that big customers - that just delete and reinsert the rows.
The attached script is made for this special tuning remedy.
The effect of the result is depends on how the rows had widely dispersed before migrated.
If the rows now were much more clustered than before, then the effects will be amazing thanks to better clustering factor of now.

=============
 SCRIPT
=============
--for not stopping the service we should use 'for update' clause.
--for preventing Tx. Row Lock contention I bond every 1000 rows in one transaction rather than execute whole rows in a commit.
  If still occur Tx. Row Lock contention then lower the value from 1000 to ig. 100.

--in a SQL*Plus session

set serveroutput on

declare
  v_exe_unit_rows number := 1000; --rows per one Tx.
  v_tot_rows number      := 0;    --target rows to be reorged
  v_reorged_rows number  := 0;    --completed rows reorged
  v_commit_cnt number    := 0;    --count of commits
  
begin

  --target rows to be reorged
  select count(*)
  into v_tot_rows
  from   ORDER_HIST t
  where  acnt_num = 1111111111 --target customer
  ;
  
  for i in 1..trunc(v_tot_rows / v_exe_unit_rows)+1 loop
   
    if v_tot_rows > v_reorged_rows then
    
        for rec in ( --You should adopt sub-query for using 'for update' in Paging query
                    select t.rowid as rid, t.*
                    from ORDER_HIST t
                    where rowid in (
                                    select rid
                                    from (
                                            select /*+index(t N_ORDER_HIST_ACNT_NUM) */
                                                   t.rowid as rid
                                                 , rownum as rn
                                            from   ORDER_HIST t
                                            where  acnt_num = 1111111111 --target customer
                                            and rownum <= (i * v_exe_unit_rows)
                                            ) 
                                    where rn >= (i - 1) * v_exe_unit_rows + 1
                                    )
                    for update
                    ) loop

        --delete first
        delete ORDER_HIST
        where rowid = rec.rid;
        
        --insert later
        insert into ORDER_HIST values (
               rec.ACNT_NUM                 ,
               rec.PROD_NUM                 ,
               rec.CUST_NUM                 ,
               rec.ORDER_DTM                ,
               .
               .
               );
          
          v_reorged_rows := v_reorged_rows + 1;
          
        end loop;
        
        commit; --by 1000 rows
        v_commit_cnt := v_commit_cnt + 1;
     
    end if;
     
  end loop;     

  dbms_output.put_line('Total Rows    : ' || v_tot_rows );
  dbms_output.put_line('Reorged Rows  : ' || v_reorged_rows );
  dbms_output.put_line('commit  count : ' || v_commit_cnt );

exception
  when others then raise;
end;
/