As a developer, you should have a good understanding of exactly what goes on during a COMMIT. In this section, we’ll investigate what happens during the processing of the COMMIT statement in Oracle. A COMMIT is generally a very fast operation, regardless of the transaction size. You might think that the bigger a transaction (in other words, the more data it affects), the longer a COMMIT would take. This is not true. The response time of a COMMIT is generally “flat,” regardless of the transaction size. This is because a COMMIT does not really have too much work to do, but what it does do is vital.

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 1000 rows takes the same X units of time, then performing work in a manner that does 1000 one-row COMMITs will take an additional 1000*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:

$ sqlplus eoda/foo@PDB1
SQL> create table test( id number,code varchar2(20),descr varchar2(20),insert_user varchar2(30),insert_date date);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 areloaded)
•\ A second time, with SQL Tracing on, specifying the number ofrows to INSERT along with how 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 (you’ll have to modify the connect string for your environment):
import java.sql.;import java.sql.DriverManager;import java.sql.Connection;import java.sql.SQLException;import java.io.;public class perftest{public static void main (String arr[]) throws Exception {DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); Connection con=DriverManager.getConnection(“jdbc:oracle:thin:@//localhost. localdomain:1521/PDB1”, “eoda”, “foo”);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();}

Note The SCOTT account or whatever account you use to test this with will need to have the EXECUTE privilege granted on the DBMS_MONITOR package.

Now, the method doInserts() is fairly straightforward. It starts by preparing (parsing) an INSERT statement so we can repeatedly bind/execute it over and over:

static void doInserts(Connection con, int count, int commitCount ) throws Exception{PreparedStatement ps =con.prepareStatement(“insert into test ” +”(id, code, descr, insert_user, insert_date)”+ ” values (?,?,?, user, sysdate)”);

It then loops over the number of rows to insert, binding and executing the INSERT over and over. Additionally, it checks a row counter to see if it needs to COMMIT or not inside the loop:
int rowcnt = 0;int committed = 0;for (int i = 0; i < count; i++ ){ps.setInt(1,i);ps.setString(2,”PS – code” + i);ps.setString(3,”PS – desc” + i);ps.executeUpdate();rowcnt++;if ( rowcnt == commitCount ){con.commit();rowcnt = 0;committed++;}}con.commit();System.out.println(“pstatement rows/commitcnt = ” + count + ” / ” + committed );}}

Tip See the Oracle Database JDBC Developer’s Guide for further details on how to connect to an Oracle database with Java.