Parallel DML and locking – going manual

Generally speaking, if you have a giant chunk of data over “here”, and you need to load it into a table over “there”, and you have exclusive access to that target table, then parallel DML is a great option. People often neglect using parallel DML on the premise that “Database are I/O bound anyway so more sessions doesn’t help”. But the world (and in particular the hardware we use) evolves and in these times of flash-based storage, the I/O bandwidth is often no longer a bottleneck. 

Sidebar: In the days of spinning rust drives, you may be surprised to discover that even then, it was rarely disk speeds that were the issue – it was typically either the CPU on the storage array, or the pipe to the database server that killed you, but that’s a story for another post.

But the key part of my opening statement is “you have exclusive access”. Here’s an example of probably the most common form of parallel DML, namely, a direct path insert.

SQL> insert /*+ PARALLEL APPEND */ into t
  2  select *
  3  from
  4  ( select 1, d.* from dba_objects d
  5    union all
  6    select 2, d.* from dba_objects d
  7  );

167618 rows created.

Look at what happens in any other session whilst I’m running that load (or even after the load is complete but I have not yet committed).

SQL> insert into t
  2  select 3, d.* from dba_objects d
  3  where rownum = 1;
[waiting waiting ....]

That’s right…Nothing happens. I’m stuck on a lock.

But what happens when that the target table is partitioned? Here’s the same table above, this time partitioned by list (to keep the demo simple). I’ll now load the table with data that will only fill partitions P1 and P2.

SQL> create table t
  2  partition by list ( x)
  3  ( partition p1 values (1),
  4    partition p2 values (2),
  5    partition p3 values (3)
  6  )
  7  as
  8  select 0 x, d.* from dba_objects d where 1=0;

Table created.

SQL>
SQL> alter session enable parallel dml;

Session altered.

SQL> insert /*+ PARALLEL APPEND */ into t
  2  select *
  3  from
  4  ( select 1, d.* from dba_objects d
  5    union all
  6    select 2, d.* from dba_objects d
  7  );

167624 rows created.

Let me now attempt to do some DML on partition P3 which was untouched by the operation.

SQL> insert into t
  2  select 3, d.* from dba_objects d
  3  where rownum = 1;
[waiting waiting ....]

No change. The untouched partition is still locked away. This is because the lock is done based on the target in the DML, and in this case, it is my table T. The entire table is locked.

It is however possible to lock at the partition level. So in instances such as this, you may sometimes be better off taking control of the parallelism yourself and using a session per partition.

Session 1

SQL> insert /*+ APPEND */ into t partition (p1 )
  2  select *
  3  from
  4  ( select 1, d.* from dba_objects d );

83812 rows created.

Session 2

SQL> insert /*+ APPEND */ into t partition (p2 )
  2  select *
  3  from
  4  ( select 2, d.* from dba_objects d );

83812 rows created.

Session 2

SQL> insert into t
  2  select 3, d.* from dba_objects d  -- will be partition p3
  3  where rownum = 1;

1 row created.

Notice that all three sessions can happily run concurrently, and the locking in session 1 and 2 is only at the partition level because the partition is the nominated target in the DML rather than the entire table. I often adopt this approach because the greater control I have also means that if one of the loads fail, then I know exactly which partition needs to be fixed and re-run.  With a giant parallel load across the entire table it is an “all or nothing” operation.

Of course, if the source data was truly massive, you could explore parallel DML on each of the partition-level insert statements assuming you had sufficient server firepower.

So even though parallel DML is a cool piece of tech, don’t be afraid to employ your own parallel mechanisms given your knowledge of the source data.

TL;DR: If you can get down the granularity of the partition or subpartition, sometimes using your own home-grown parallelism scheme can yield more concurrency.