Transactions - Oracle Database Transactions and Locking Revealed - page 86

COMMITS in a Nondistributed PL/SQL Block
Since PL/SQL was first introduced in version 6 of Oracle, it has been transparently using an asynchronous commit.
That approach has worked because all PL/SQL is like a batch program in a way—the end user does not know the
outcome of the procedure until it is completely finished. That's also why this asynchronous commit is used only
in nondistributed PL/SQL blocks of code; if we involve more than one database, then there are two things—two
databases—relying on the commit being durable. When two databases are relying on the commit being durable, we
have to utilize synchronous protocols or a change might be committed in one database but not the other.

■ of course, pipelined pL/sQL functions deviate from “normal” pL/sQL functions. in normal pL/sQL functions, the
outcome is not known until the end of the stored procedure call. pipelined functions in general are able to return data to a
client long before they complete (they return “chunks” of data to the client, a bit at a time). But since pipelined functions
are called from SELECT statements and would not be committing anyway, they do not come into play in this discussion.

Note

Therefore, PL/SQL was developed to utilize an asynchronous commit, allowing the COMMIT statement in PL/SQL
to not have to wait for the physical I/O to complete (avoiding the “log file sync” wait). That does not mean that
you can't rely on a PL/SQL routine that commits and returns control to your application to not be durable with
respect to its changes—PL/SQL will wait for the redo it generated to be written to disk before returning to the client
application—but it will only wait once, right before it returns.

■ the following example demonstrates a bad practice—one that i call “slow-by-slow processing” or
“row-by-row processing,” as row-by-row is synonymous with slow-by-slow in a relational database. it is meant
just to illustrate how pL/sQL processes a COMMIT statement.

Note

First let's create table T :

EODA@ORA12CR1> create table t
2 as
3 select *
4 from all_objects
5 where 1=0
6 /
Table created.

Now consider this PL/SQL procedure:

EODA@ORA12CR1> create or replace procedure p
2 as
3 begin
4 for x in ( select * from all_objects )
5 loop
6 insert into t values X;
7 commit;
8 end loop;
9 end;
10 /

Procedure created.