0

We have a one large table that we need to insert data of it into another table. Target table is partitioned by range (by day) and subpartitioned by departments.

For loading table data, we have used dbms_parallelel_execute and created a task using sql that gets list of departments, level is 20, that is at one time only 20 tasks corresponding to 20 departments will run. Those task will select the department's data from source table and inserts into target table.

Before doing insert, we first get subpartition name and generate the following insert:

INSERT /*+ NO_GATHER_OPTIMIZER_STATISTICS ENABLE_PARALLEL_DML APPEND_VALUES */ into Target_Table  subpartition (subpartition_name) values (:B1, :B2, :B3, ....) ;

We read on oracle documentation that specifiying subpartition during insert will lock only that subpartition and append will work . The goal of doing this was to create n jobs that will independently insert into their own give subpartitions. Append itself is working, but when we monitor v$session while loading table data, we see that

BLOCKING_SESSION_STATUS is VALID;

FINAL_BLOCKING_SESSION_STATUS is VALID;

EVENT# is library cache lock

STATE is WAITING,

WAIT_CLASS is Concurrency

From this, we are concluding that one append_values is still locking other sessions to insert to another subpartition, is there something we missed? We have enabled parallel dml, disabled target table's indexes, set skip_unusable_indexes to true, no referential constraints are present in target table, table, partitions and subpartitions are set to nologging.

EDIT: Tested the same thing with another table that is also partitioned, but it doesn't have subpartitions, it is only list partitioned. So instead of subpartition (subpartition_name) inside insert statement there was partition(partition_name) . However, in this case , insert run without sessions waiting for others and no locks were held. I am assuming with subpartitioned interval tables the above won't work.

EDIT2 I have created the same scenario in another database which is also Oracle 19c. Created a table with partitions and subpartitions, set the interval, disabled indexes, set nologging and run the job that inserts into subpartitions. Surprisingly, the insert went without errors and no sessions locking each other. Now I am thinking maybe its some database parameter that should be turned on or changed. Because database versions, table structures, jobs, inserts are the same, but in one it is locking each other, in another it is not.

UPDATE Adding the insert part of the code :

  if c_tab_cursor %isopen then 
    close c_tab_cursor;
  end if;
  open c_tab_cursor;
  loop
    fetch c_tab_cursor bulk collect
     into v_row limit 100000;
    exit when(v_row.count = 0);
    forall i in v_row.First .. v_row.Last
     insert /*+ NO_GATHER_OPTIMIZER_STATISTICS APPEND_VALUES */ into
       Target_Table subpartition(SYS_P68457)
          values v_row
          (i);
          commit;
   end loop;
  close c_tab_cursor;

Edit3 Adding table info, table is daily partitioned, and each partition has around 150 subpartitions. At the time of writing this, table had total 177845 subpartitions. My other guess is oracle is spending many time to find the right subpartition, which is also arguable because subpartition name is provided during insert.

Sherzodbek
  • 170
  • 1
  • 20

2 Answers2

1

I'd say it is expected "feature" - when you insert into the same segment. Direct path insert writes data beyond HWM(high water mark) rather than using segment's free space map. When you commit direct path insert HWM advances, when you rollback HWM stays and data is discarded.

Check Oracle segment parameter "FREELIST", but I'm afraid even this parameter wont help you.

When your inserts touch different subpartitions this should not be happening. There can be various objects held by library cache lock (maybe due to bug). IMHO only way how to investigate this would be either to use hanganalyze to check which function in oracle is being blocked or to query P1,P2,P3 parameters of library cache lock and identify which object is blocking parallel run.

PS: I saw bugs like: Only one session could run Java stored procedure at the time because Oracle unnecessarily wanted to hold exclusive lock on some library case object.

ibre5041
  • 4,903
  • 1
  • 20
  • 35
  • What is interesting is that, if table is only partitioned, that it doesn't have subpartitions, but only partitions, inserts run without having locks. Why this isn't happening with subpartitioned tables i have no clue – Sherzodbek Jan 26 '23 at 04:39
1

v$session reports the wait state at that precise instant that you query it. It's meaningless unless you keep requerying and keep seeing the same thing. Better yet, use v$active_session_history to see Oracle's own 1-second sampling of the wait state. If you see lots of rows with that wait, then it's meaningful.

Assuming that this is meaningful, I would point out that you are using a single row VALUES list and yet are asking for parallel dml. Parallel dml is for multiple row operations, not single row operations. You can use it for an insert-select, for example, but not an insert-values.

If your application is necessarily single-row driven, remove ENABLE_PARALLEL_DML APPEND_VALUES hints. If you are binding arrays to these variables, you can leave the APPEND_VALUES but remove the ENABLE_PARALLEL_DML. For inserts, parallel DML only works with insert-select.

As you clearly intend to have multiple sessions, each loading a separate subpartitions, that's your parallelism right there - you don't need nor want to add another layer of parallelism with PDML.

Paul W
  • 5,507
  • 2
  • 2
  • 13
  • Thanks i will look at v$active_session_history. Also, I have removed ENABLE_PARALLEL_DML hint from insert, but it didn't change the behaviour. The insert itself is inside forall , that is inside a loop with using cursors I am bulk collecting around 10 000 rows in one fetch from source table and inserting those 10 000 rows inside forall – Sherzodbek Jan 26 '23 at 04:43
  • Is there a reason for doing this in batches? If you are moving data from a source table to a target and don't absolutely need PL/SQL to transform the data, it usually makes a lot more sense to simply do a single insert-select operation. That can use parallel dml and is the most efficient method of doing this. – Paul W Jan 26 '23 at 17:54
  • Reason is there are other operations going on other than this insert, and if I use one insert-select, I'll get pga memory error, I faced this kind of error with other table when i did one insert-select operation for a table. That is why I was using a cursor and bulk collect to limit fetching rows – Sherzodbek Jan 27 '23 at 04:55
  • Added insert part of the code to the question – Sherzodbek Jan 27 '23 at 05:06
  • Included table's subpartition info in the question, it has 177845 subpartitions, table itself is daily partitioned, do you think this may be a reason? – Sherzodbek Jan 27 '23 at 06:12
  • Quite to the contrary, bulk collect operations use a lot more PGA than insert-select does. – Paul W Jan 27 '23 at 12:21