3

There is a need to run 2 jobs in parallel from Informatica to insert data in parallel to the same table in Oracle.

One process will insert to one partition and other process will insert into another partition.

How does oracle do by default? Does it lock the whole Oracle table so that no 2 parallel processes can insert data from Informatica or can it lock only partition of one table and another process can run in parallel and insert into another partition?

Thanks.

Joe
  • 11,983
  • 31
  • 109
  • 183

2 Answers2

3

With plain old inserts, two statements won't ever block each other.

  1. Oracle uses row-level locking and does not randomly escalate them into table locks. Since INSERTs by definition create new rows they can't possibly block each other.

There are a few weird exceptions to that rule. For example, if the table had a trigger that changed other rows, or if two INSERTs try to use the same primary key value. But both cases are extremely unlikely and would probably be an unintended bug that you wouldn't want to run anyway. Bitmap indexes aren't designed for concurrent DML and may also cause regular inserts to block.

With direct-path writes or parallel inserts, the answer is more complicated.

Things get more complicated with parallel processing and direct-path writes.

  1. If the INSERT operation uses parallelism or a direct-path write, adding even a single row will lock the entire table and prevent other sessions from changing anything. For example, if it generates statements like insert /*+ parallel(8) */ ... they will prevent any other DML from running. This is because direct-path writes directly modify the data files for optimal performance and the typical concurrency controls don't work.
  2. But it's possible that Informatica won't use Oracle's parallel processing, and will instead create multiple concurrent threads for homegrown parallel processing. If that's the case then the two processes won't block each other.
  3. But it's also possible that Informatica does not use Oracle parallel processing but does use direct-path writes. For example, if it generates statements like insert /*+ append */ ... they will prevent any other DML from running, even if that DML is modifying a different partition. This is probably because Oracle can't easily predict which partition will be modified ahead of time and it's simpler to just lock them all.
  4. But if the partitions are explicitly specified, then two parallel or direct-path writes can run concurrently, as long as they modify different partitions.

Below is a quick demonstration. First, create a simple test schema with a partitioned table.

create table test1(a number)
partition by list(a)
(
    partition p1 values (1),
    partition p2 values (2)
) nologging;

Demonstrate that a direct-path write in one session will block any type of insert in another session:

--Session 1: Run this statement but don't commit.  It should finish in less than a second.
insert /*+ append */ into test1 select 1 from dual;

--Session 2: Run this statement but it will never finish, even though it
--           inserts into a different partition.
insert into test1 select 2 from dual;

Demonstrate that a direct-path write in one session will not block another session as long as the partitions are explicitly named:

--Session 1: Run this statement but don't commit.  It should run in less than a second.
insert /*+ append */ into test1 partition (p1) select 1 from dual;

--Session 2: This statement will run immediately.
insert into test1 partition (p2) select 2 from dual;

Now what?

Any of the above scenarios are possible depending on how Informatica is configured. Start by checking the Informatica settings.

Look at the generated SQL to see what Informatica is running. Use a query like select * from gv$sql where sql_fulltext like '....

Look at the explain plans for those SQL statements to see if the queries are running the way you want them too. Use a query like select * from table(dbms_xplan.display_cursor(sql_id => '... to find the plan. Look at the operations column; LOAD AS SELECT means direct-path is being used and LOAD TABLE CONVENTIONAL means direct-path is not used. You can also check for PARALLEL or PX to see if Oracle parallel processing is used.

Unfortunately there are many reasons why direct-path or parallelism may be requested but not used. See my frustratingly long list of possible reason for a lack of parallelism here, and see this list for reasons for a lack of direct-path writes.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
1

@OldProgrammer provided an excellent reference. There is no need to worry about table locks when inserting into Oracle. The only locks Oracle does by default is to the row being inserted, updated, or deleted, and a lock that prevents DDL operations while the DML operation is underway.

Brian Leach
  • 2,025
  • 1
  • 11
  • 14