Using "Commit Write Batch Nowait" from within JDBC

Anyone who has used the new asynchronous commit feature of Oracle 10.2 will be aware that it's very useful for transaction processing systems that would traditionally be bound by log_file_sync wait events.

COMMIT WRITE BATCH NOWAIT is faster because it doesn't wait for a message assuring it that the transaction is safely in the redo log - instead it assumes it will make it. This nearly eliminates log_file_sync events. It also arguably undermines the whole purpose of commit, but there are many situations where the loss of a particular transaction (say to delete a completed session) is perfectly survivable and far more preferable than being unable to serve incoming requests because all your connections are busy with log_file_sync wait events.

The problem anyone using Oracle's JDBC driver is that neither the 10.2 or 11.1 drivers have any extensions which allow you to access this functionality easily - while Oracle have lots of vendor specific extensions for all sorts of things support for async commit is missing.

This means you can:

• Turn on async commit at the instance level by messing with the COMMIT_WRITE init.ora parameter. There's a really good chance this will get you fired, as throughout the entire system COMMIT will be asynchronous. While we think this is insane for production systems there are times where setting it on a development box makes sense, as if you are 80% log file sync bound setting COMMIT_WRITE to COMMIT WRITE BATCH NOWAIT will allow you to see what problems you face if you can somehow fix your current ones.

• Change COMMIT_WRITE at the session level. This isn't as dangerous as doing it system wide but it's hard to see it being viable for a real world system with transactions people care about.

• Prepare and use a PL/SQL block that goes "BEGIN COMMIT WRITE BATCH NOWAIT; END". This is safer than the first two ideas but still involves a network round trip.

• Wrap your statement in an anonymous block with an asynchronous commit. This is the best approach we've seen. Your code will look something like this:

BEGIN

--

insert into generic_table

(a_col, another_col, yet_another_col)

values

(?,?,?);

--

COMMIT WRITE BATCH NOWAIT;

--

END;

Oracle 11G first look

Oracle 11g is now available for download, but only for Linux. This blog posting covers practical issues we've seen since we started playing with it. If you are looking for marketing/new feature info you can find the PDF's here.

Installation

1. Download is a 1.8GB zip file.
2. The Installer requires a minimum of 922MB of memory and large quantities of swap space. If you need more swap you can use 'swapadd'.
3. Installer for X86 expects recent patches - don't think that your copy of RHEL4 will work without new rpm's.
4. Oracle has changed the default location of the *dump and other configuration directories.
5. Some minor Kernel tweaks required.

DB Upgrade Process

Process expects that your copy of 10.2.0 is patched to 10.2.0.3.0 to address a DST issue. If not you have to manually apply Oracle patches and jump through various other hoops. We encountered problems with the upgrade and eventually built an11g DB from scratch for our regression testing. This may a fundamental problem with the upgrade but is more likely to be because 'someone' unplugged our router during the upgrade process and left the DB in a mess.

Security

Passwords are now case sensitive. This will cause hours of fun.

11g JDBC Driver

1. 1.4 and earlier versions of Java are not supported.
2. 9.0.1 and prior versions of Oracle are not supported.
3. 'oracle.jdbc.driver' is gone - but then it's been deprecated since 9i...
4. We were expecting OracleConnection to be extended to prrovide support for Asynchronous Commit, but that didn't happen. We'll be adding our own support to OrindaBuild.

JPublisher

The 11g version of JPublisher still creates .sqlj files but then deletes them after producing .java files. JPublisher still has all its other limitations.

OrindaBuild 11g Support

We have completed regression testing and the first build that supports 11g is available here. JDeveloper users can use this update center:

http://www.orindasoft.com/public/jdev103Center.xml

SQLDeveloper users can use this update center:

http://www.orindasoft.com/public/sqldevCenter.xml

Oracle's DECODE funtion...

Oracle's decode function can be used to create arbitrary ORDER BY clauses. This is useful if you are providing lists with the most frequently picked value at the top - e.g. a list of countries which starts with 'United States' and then turns into a conventional alphabetical listing. Your SELECT statement will have two things in the ORDER BY clause - first it sorts by a DECODE that turns your favoured value into '1' and all other values into '2' and then it sorts the rest of the list conventionally. The alternatives to using DECODE are to hard code the entire list (bad!) or add a numeric column to the underlying table that defines the sort order, which is a lot more work than a DECODE in an ORDER BY.

SQL> r

1  select country_name

2  from iso_countries

3* order by decode(country_name,'UNITED STATES',1,2)

  , country_name

COUNTRY_NAME

--------------------------------------------------

UNITED STATES

AFGHANISTAN

ALBANIA

ALGERIA

AMERICAN SAMOA

ANDORRA

ANGOLA

ANGUILLA

ANTARCTICA

ANTIGUA AND BARBUDA

ARGENTINA

...

This behavior is common for web sites where drop down lists have to be dynamic (the contents may change) but also require location or market specific default preferences.





While we're on the subject of lists of countries one really should think twice before including Bouvet Island one's list...

Not All Jdbc Advice Is Good JDBC Advice...

While wandering around the web I stumbled across a link to a sample chapter for 'Java Programming with Oracle JDBC', published in December 2001 by O'Reilly. Now 2001 is a bit dated but one would assume that if they are plugging it on their web site it must still be relevent. And that any glaring

errors would have been corrected, right? Think again...

The author spends the chapter conducting elaborate benchmarks which in his view demonstrate that there's nothing special about PreparedStatement
and that you should really be using Statement most of the time. His reasoning is based on the premise that the additional set up costs of Prepareing a statement and sending all the bind variables etc cause applications to run more slowly than if you had simply said something like:


code>




Statement stmt = conn.createStatement();


stmt.executeUpdate(


"insert into oehr.testxxxperf ( id, code, descr,insert_user, insert_date ) "


"values ( " + Integer.toString( i ) + ", '125678901234567890', "


"'1234567890', " +










"USER, to_date('" + sdf.format(new java.util.Date(System.currentTimeMillis()))
+ "', 'YYYYMMDDHH24MISS'))");

instead of:

PreparedStatement stmt = conn.prepareStatement(


"insert into oehr.testxxxperf ( id, code, descr,insert_user,

insert_date ) " +


"values ( ?, ?, ?, ?, ? )");


startTime = System.currentTimeMillis();


int i=1;


stmt.setInt(1,i);


stmt.setString(2,"123456789012345678901234567890");


stmt.setString(3,"123456789019012345678901234567890");


stmt.setString(4,"ZXVI01");


stmt.setDate(5,newjava.sql.Date(System.currentTimeMillis()));


stmt.executeUpdate();




The point made is that unless you plan on issuing hundreds of statements the first method is faster because it has fewer network round trips and therefore less elapsed time. All of which is true - provided there is only 1 copy of the client software being used. In practice what will happen is that using Statement in preference to PreparedStatement will cause the database server to devote significant resources to compiling execution plans for each and every version of the insert statement being called. Indeed, not using PreparedStatement and bind variables is listed as the second most common mistake in Oracle's '
Top Ten Mistakes Found in Oracle Systems'.There's also a less obvious but equally serious problem with cobbling together your SQL statements instead of using bind variables and PreparedStatement - sooner or later someone with an apostrophe in their name will show up and cause a syntax error:

Int empno = 42;


String empname = "O'Reilly";


String myStatement = "INSERT INTO EMP (empno, ename) VALUES ("


+ empno + ",'" + empname + "');";


But wait... it gets worse! If you're working on a public facing web system and some bright spark realizes you are hacking together SQL statements you could have something like this happen:


Int empno = 42;


String ename = "'Smith', sal = 99999";


String myStatement = "UPDATE EMP set ename = "


+ ename+ " WHERE empno = " + empno');";





which means that your Statement would be:

UPDATE EMP set ename = 'Smith', sal = 99999 WHERE empno = 42;


[This post originally appeared on JRoller]

Rowid Madness

For years the first of my interview questions for Oracle developers has been one in which I ask them to explain why it's a bad idea to use the ROWID of table 'A' as a foreign key to table 'B'. This is not hard for a competant person to answer, as ROWID changes when you import the data into another database which will happen sooner or later. It never occured to me that someone could be crazy enough to actually do this, but someone has.

[This post originally appeared on JRoller]