Performance of PL/SQL blocks

[welchdor]

I need to confirm that sending a group of SQL statements within a BEGIN...END block is more efficient than sending the same statements without such a block. Re: this section of PL/SQL documentation on performance:

"Without PL/SQL, Oracle must process SQL statements one at a time. Each SQL statement results in another call to Oracle and higher performance overhead. In a networked environment, the overhead can become significant. Every time a SQL statement is issued, it must be sent over the network, creating more traffic. However, with PL/SQL,
an entire block of statements can be sent to Oracle at one time. This can drastically reduce communication between your application and Oracle.... you can use PL/SQL blocks and subprograms to group SQL statements before sending them to Oracle for execution."

So, this would be one call to the database?:
BEGIN
insert into tableA values (a,b,c);
insert into tableA values (d,e,f);
insert into tableB values (a,b,c);
commit;
END

And this is multiple calls to the database?:
insert into tableA values (a,b,c);
insert into tableA values (d,e,f);
insert into tableB values (a,b,c);
commit;

In tests I can't tell any time difference with inserts of 120,000
rows, so I am wondering if it really makes a difference. Or does the BEGIN...END block have to be within a stored function or procedure to get the performance benefit?

Thanks in advance.

[Heath]

The key to the performance gains lies in the reduction of trips back and forth across the network from the client to the database. Look at your example this way:


-- Multiple calls

1. "insert into tableA values (a,b,c);" is sent to the database over the network and the statement is executed.

2. "insert into tableA values (d,e,f);" is sent to the database over the network and the statement is executed.

3. "insert into tableB values (a,b,c);" is sent to the database over the network and the statement is executed.

4. "commit;" is sent to the database over the network and the statement is executed.

-- Using a PL/SQL block for a single call

1. "BEGIN
    insert into tableA values (a,b,c);
    insert into tableA values (d,e,f);
    insert into tableB values (a,b,c);
    commit;
    END;" is sent to the database over the network and the block is executed.

You should see a noticeable performance improvement by bundling up your statements into a single block.

[santoshym]

Hi

I agree completely with heath That was a nice explanation Heath.It totally reduces ur network traffic and enhances the performance as the whole bunch of statements are put in a pl/sql block and transfered to the server and then the server sends the whole pl/sql block to be compiled and is further executed so there is surely a performance boost.

Regards