Redo and Undo - Oracle Database Transactions and Locking Revealed - page 116

One of the reasons this is an important fact to understand and embrace is that it will lead to letting your
transactions be as big as they should be. As we discussed in the previous chapter, many developers artificially
constrain the size of their transactions, committing every so many rows, instead of committing when a logical unit
of work has been performed. They do this in the mistaken belief that they are preserving scarce system resources,
when in fact they are increasing them. If a COMMIT of one row takes X units of time, and the COMMIT of 1,000 rows takes
the same X units of time, then performing work in a manner that does 1,000 one-row COMMIT s will take an additional
1,000*X units of time to perform. By committing only when you have to (when the logical unit of work is complete),
you will not only increase performance, you'll also reduce contention for shared resources (log files, various internal
latches, and the like). A simple example demonstrates that it necessarily takes longer. We'll use a Java application,
although you can expect similar results from most any client—except, in this case, PL/SQL (we'll discuss why that is
after the example). To start, here is the sample table we'll be inserting into:

SCOTT@ORA12CR1> create table test
2 ( id number,
3 code varchar2(20),
4 descr varchar2(20),
5 insert_user varchar2(30),
6 insert_date date
7 )
8 /
Table created.

Our Java program (stored in a file named perftest.java ) will accept two inputs: the number of rows to
INSERT ( iters ) and how many rows between commits ( commitCnt ). It starts by connecting to the database, setting
autocommit off (which should be done in all Java code), and then calling a doInserts() method a total of two times:

• Once just to warm up the routine (make sure all of the classes are loaded)

INSERT along with how • A second time, with SQL Tracing on, specifying the number of rows to

many rows to commit at a time (i.e., commit every N rows)
It then closes the connection and exits. The main method is as follows :

import java.sql.*;

public class perftest
{
public static void main (String arr[]) throws Exception
{
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
Connection con = DriverManager.getConnection
("jdbc:oracle:thin:@csxdev:1521:ORA12CR1", "scott", "tiger");
Integer iters = new Integer(arr[0]);
Integer commitCnt = new Integer(arr[1]);

con.setAutoCommit(false);

doInserts( con, 1, 1 );

Statement stmt = con.createStatement ();
stmt.execute( "begin dbms_monitor.session_trace_enable(waits=>true); end;" );

doInserts( con, iters.intValue(), commitCnt.intValue() );

con.close();
}