25 years of Java – Happy Birthday!

Birthday cakeToday 25 years ago Java made its first public appearance. Back then, Java promised to be a general-purpose programming language that you “write once, run anywhere“. It came with a new and unique way to compile Java source code down to bytecode, an intermediate representation that could be understood, compiled at runtime and run by the Java Virtual Machine (JVM). It was the combination of Java bytecode and the JVM that made it possible for Java to keep that “write once, run anywhere” promise. The Java Virtual Machine was for Java programs exactly what the name suggests, a (virtual) machine. Regardless of which computer you had, which OS you were running, if you had a JVM installed, you could run your compiled Java program on it without the need for porting or recompiling the program first.

Continue reading “25 years of Java – Happy Birthday!”

Oracle Database client libraries for Java now on Maven Central

Oracle has published its Oracle Database JDBC client libraries on Maven Central. From Apache Mavennow on you can find Oracle Database related jar files under the com.oracle.database group id. You will find all libraries from version 11.2.0.4 (e.g. ojdbc6) to 19.3.0 (e.g. ojdbc10).

Going forward, Oracle will use Maven Central as one of the primary distribution mechanisms for Oracle Database Java client libraries, meaning that you will also be able to find new versions of these libraries on Maven Central in the future.

To get the latest Oracle Database JDBC driver, use the following dependency GAV in your Maven POM file:

<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc10</artifactId>
    <version>19.3.0.0</version>
</dependency>

Continue reading “Oracle Database client libraries for Java now on Maven Central”

Manually installing a Maven artifact in your local repository

I find myself once again in the situation that I have to install the Oracle JDBC driver into my local Maven repository. Usually this is easily accomplished via mvn install:install-file -Dfile=<path-to-file> -DgroupId=<group-id> -DartifactId=<artifact-id> -Dversion=<version> -Dpackaging=<packaging>, see Guide to installing 3rd party JARs for more details on that. However, this time I was thinking to go the extra mile and actually figure out a way of how to do it entirely manually. This comes in handy if you have, for example, Maven integrated in your IDE and the mvn binary not available to yourself in the command line.

Continue reading “Manually installing a Maven artifact in your local repository”

Java class finding tool: ClassFinder

I got into more coding again lately and that is a good thing I think. I’m a believer of the German saying “Wer rastet, der rostet” or in English: A rolling stone gathers no moss! Java seems to be my main programming language since quite a while now. But believe it or not I’m still coding some PL/SQL as well now and then. Anyway, sometimes when you get some ClassNotFound exceptions in Java it can be quite handy to have a little tool that can scan .jar and .zip files for the class that you’re missing. Yes, there are tons of those little programs out there on the web but as you might have guessed by the title I’ve decided to write yet another class finding tool. Damn, I really should have called it YACFT! But instead I did go for ClassFinder. Why did I write yet another class finding tool then? Well, although there are some good ones out there, non of them fulfilled all of my requirements – at least none of those that I have found. First I was a big fan of Xigole’s classfinder tool. A really slick, fast and easy to use command-line class finding tool which is really great for Unix systems where you ssh in. However, I soon got fed up with it when I did have a proper graphical interface available, whether it was Windows or X11 or simply my MacBook Pro. So I started searching for other tools and soon found that they were either just GUI based or just GUI based and on Windows only (Why? I don’t get it!) or GUI based on ugly AWT. But all that I wanted was a slick tool like Xigole’s that was clever enough to spawn a GUI when there was X11 available but still provide a nice CLI when there wasn’t. Well, as I said I couldn’t find one, so I wrote my own and here it is, ClassFinder:

ClassFinder Window-mode

It’s really easy to use:

  1. Give it either a file or a folder to search – files currently supported are .jar, .war, .ear, .zip, .rar, .class, .java
  2. Give it the class name that you’re looking for
  3. Shall the class name be case sensitive?
  4. Shall the folder be searched recursively?
  5. Hit Search!

Continue reading “Java class finding tool: ClassFinder”

Loading data fast – DML error logging performance

In the last post of my “Loading data fast” series I showed how DML error logging can be used to prevent batch loads from failing when one or multiple rows can’t be inserted. Now the question is how much performance impact errors have on the mechanism. What I try to prove is that a developer should not just blindly use DML error logging instead of thinking whether the data he intents to insert is primary valid. So let’s have a look:

First I create the same simple table with a primary key on it that I used before:

CREATE TABLE TESTLOADTABLE (id NUMBER, text VARCHAR2(255));
table TESTLOADTABLE created.

CREATE UNIQUE INDEX TESTLOADTABLE_PK ON TESTLOADTABLE(id);
unique index TESTLOADTABLE_PK created.

ALTER TABLE TESTLOADTABLE ADD PRIMARY KEY (id) USING INDEX TESTLOADTABLE_PK;
table TESTLOADTABLE altered.

Then I create the error logging table:

BEGIN
  DBMS_ERRLOG.CREATE_ERROR_LOG('TESTLOADTABLE','ERR_TESTLOADTABLE');
END;
anonymous block completed

So far so good, now let’s execute following tests:

1) Load 10k rows with no errors
2) Load 10k rows with 100 (1%) rows failing
3) Load 10k rows with 1000 (10%) rows failing
4) Load 10k rows with 2500 (25%) rows failing
5) Load 10k rows with 5000 (50%) rows failing
6) Load 10k rows with 7500 (75%) rows failing
And just out of curiosity let’s see how long a regular insert with no error logging takes
7) Load 10k rows with no errors and no error logging

Test 1 – no errors:

long startOverall = System.currentTimeMillis();
PreparedStatement stmt = conn.prepareStatement("INSERT /* addBatch with logging errors */ INTO testloadtable (id, text) VALUES (?,?) LOG ERRORS INTO ERR_TESTLOADTABLE REJECT LIMIT UNLIMITED");

for (int i=0;i&lt;10000;i++)
{
  stmt.setInt(1, i);
  stmt.setString(2, "test" + i);
  stmt.addBatch();
}
long startExecute = System.currentTimeMillis();
stmt.executeBatch();
long endExecute = System.currentTimeMillis();
conn.commit();
long endOverall = System.currentTimeMillis();

System.out.println("Executing batch took: " + (endExecute-startExecute));
System.out.println("Executing overall took: " + (endOverall-startOverall));
System.out.println("Preparation took: " + ((endOverall-startOverall)-(endExecute-startExecute)));

Executing batch took: 861
Executing overall took: 1020
Preparation took: 159

So overall it took 1020 milli seconds while the job spent 861 milli seconds in the execution of the insert.

Test 2 – 100 errors (1%):

PreparedStatement stmt0 = conn.prepareStatement("INSERT /* generate some rows before */ INTO testloadtable (id, text) VALUES(?,?)");
for (int i=0;i&lt;10000;i++)
{
  if (i%100 == 0)
  {
    stmt0.setInt(1, i);
    stmt0.setString(2, "test" + i);
    stmt0.addBatch();
  }
}
stmt0.executeBatch();
conn.commit();

long startOverall = System.currentTimeMillis();
PreparedStatement stmt = conn.prepareStatement("INSERT /* addBatch with logging errors */ INTO testloadtable (id, text) VALUES (?,?) LOG ERRORS INTO ERR_TESTLOADTABLE REJECT LIMIT UNLIMITED");

for (int i=0;i&lt;10000;i++)
{
  stmt.setInt(1, i);
  stmt.setString(2, "test" + i);
  stmt.addBatch();
}
long startExecute = System.currentTimeMillis();
stmt.executeBatch();
long endExecute = System.currentTimeMillis();
conn.commit();
long endOverall = System.currentTimeMillis();

System.out.println("Executing batch took: " + (endExecute-startExecute));
System.out.println("Executing overall took: " + (endOverall-startOverall));
System.out.println("Preparation took: " + ((endOverall-startOverall)-(endExecute-startExecute)));

Executing batch took: 1017
Executing overall took: 1069
Preparation took: 52

This time it took quite a bit long to execute the batch, 1017 milli seconds. The reason for this is obvious. Oracle now has not only to insert 10k rows but also to reject rows and insert them into another table.

Test 3 – 1000 (10%) errors:

PreparedStatement stmt0 = conn.prepareStatement("INSERT /* generate some rows before */ INTO testloadtable (id, text) VALUES(?,?)");
for (int i=0;i&lt;10000;i++)
{
  if (i%10 == 0)
  {
    stmt0.setInt(1, i);
    stmt0.setString(2, "test" + i);
    stmt0.addBatch();
  }
}
stmt0.executeBatch();
conn.commit();

long startOverall = System.currentTimeMillis();
PreparedStatement stmt = conn.prepareStatement("INSERT /* addBatch with logging errors */ INTO testloadtable (id, text) VALUES (?,?) LOG ERRORS INTO ERR_TESTLOADTABLE REJECT LIMIT UNLIMITED");

for (int i=0;i&lt;10000;i++)
{
  stmt.setInt(1, i);
  stmt.setString(2, "test" + i);
  stmt.addBatch();
}
long startExecute = System.currentTimeMillis();
stmt.executeBatch();
long endExecute = System.currentTimeMillis();
conn.commit();
long endOverall = System.currentTimeMillis();

System.out.println("Executing batch took: " + (endExecute-startExecute));
System.out.println("Executing overall took: " + (endOverall-startOverall));
System.out.println("Preparation took: " + ((endOverall-startOverall)-(endExecute-startExecute)));

Executing batch took: 1420
Executing overall took: 1470
Preparation took: 50

And as you can see the more rows that are failing, the longer the insert takes. So let’s have a look at the rest of the tests and see how bad it gets:

Test 4 – 2500 (25%) errors:

PreparedStatement stmt0 = conn.prepareStatement("INSERT /* generate some rows before */ INTO testloadtable (id, text) VALUES(?,?)");
for (int i=0;i&lt;10000;i++)
{
  if (i%4 == 0)
  {
    stmt0.setInt(1, i);
    stmt0.setString(2, "test" + i);
    stmt0.addBatch();
  }
}
stmt0.executeBatch();
conn.commit();

long startOverall = System.currentTimeMillis();
PreparedStatement stmt = conn.prepareStatement("INSERT /* addBatch with logging errors */ INTO testloadtable (id, text) VALUES (?,?) LOG ERRORS INTO ERR_TESTLOADTABLE REJECT LIMIT UNLIMITED");

for (int i=0;i&lt;10000;i++)
{
  stmt.setInt(1, i);
  stmt.setString(2, "test" + i);
  stmt.addBatch();
}
long startExecute = System.currentTimeMillis();
stmt.executeBatch();
long endExecute = System.currentTimeMillis();
conn.commit();
long endOverall = System.currentTimeMillis();

System.out.println("Executing batch took: " + (endExecute-startExecute));
System.out.println("Executing overall took: " + (endOverall-startOverall));
System.out.println("Preparation took: " + ((endOverall-startOverall)-(endExecute-startExecute)));

Executing batch took: 1877
Executing overall took: 1961
Preparation took: 84

Test 5 – 5000 (50%) errors:

PreparedStatement stmt0 = conn.prepareStatement("INSERT /* generate some rows before */ INTO testloadtable (id, text) VALUES(?,?)");
for (int i=0;i&lt;10000;i++)
{
  if (i%2 == 0)
  {
    stmt0.setInt(1, i);
    stmt0.setString(2, "test" + i);
    stmt0.addBatch();
  }
}
stmt0.executeBatch();
conn.commit();

long startOverall = System.currentTimeMillis();
PreparedStatement stmt = conn.prepareStatement("INSERT /* addBatch with logging errors */ INTO testloadtable (id, text) VALUES (?,?) LOG ERRORS INTO ERR_TESTLOADTABLE REJECT LIMIT UNLIMITED");

for (int i=0;i&lt;10000;i++)
{
  stmt.setInt(1, i);
  stmt.setString(2, "test" + i);
  stmt.addBatch();
}
long startExecute = System.currentTimeMillis();
stmt.executeBatch();
long endExecute = System.currentTimeMillis();
conn.commit();
long endOverall = System.currentTimeMillis();

System.out.println("Executing batch took: " + (endExecute-startExecute));
System.out.println("Executing overall took: " + (endOverall-startOverall));
System.out.println("Preparation took: " + ((endOverall-startOverall)-(endExecute-startExecute)));

Executing batch took: 2680
Executing overall took: 2765
Preparation took: 85

Test 6 – 7500 (75%) errors:

PreparedStatement stmt0 = conn.prepareStatement("INSERT /* generate some rows before */ INTO testloadtable (id, text) VALUES(?,?)");
for (int i=0;i&lt;10000;i++)
{
  if (i&lt;=7500)
  {
    stmt0.setInt(1, i);
    stmt0.setString(2, "test" + i);
    stmt0.addBatch();
  }
}
stmt0.executeBatch();
conn.commit();

long startOverall = System.currentTimeMillis();
PreparedStatement stmt = conn.prepareStatement("INSERT /* addBatch with logging errors */ INTO testloadtable (id, text) VALUES (?,?) LOG ERRORS INTO ERR_TESTLOADTABLE REJECT LIMIT UNLIMITED");

for (int i=0;i&lt;10000;i++)
{
  stmt.setInt(1, i);
  stmt.setString(2, "test" + i);
  stmt.addBatch();
}
long startExecute = System.currentTimeMillis();
stmt.executeBatch();
long endExecute = System.currentTimeMillis();
conn.commit();
long endOverall = System.currentTimeMillis();

System.out.println("Executing batch took: " + (endExecute-startExecute));
System.out.println("Executing overall took: " + (endOverall-startOverall));
System.out.println("Preparation took: " + ((endOverall-startOverall)-(endExecute-startExecute)));

Executing batch took: 3349
Executing overall took: 3412
Preparation took: 63

So as you can see, the more errors you get, the longer your batch will need to execute. This is only logical of course as the more errors you get, the more exceptions are thrown which then lead to more rows inserted into the error table. The main takeaway is that your insert is not failing and you can smoothly query the error logging table and act appropriately. Just in comparison:

  • Insert no errors: 861ms
  • Insert 1% errors: 1017ms
  • Insert 10% errors: 1420ms
  • Insert 25% errors: 1877ms
  • Insert 50% errors: 2680ms
  • Insert 75% errors: 3349ms

Now let’s do one more test and see how fast a regular insert without DML error logging is. You would think it’s the same but during my experiments if found an interesting fact:

Test 7 – no errors, no error logging:

long startOverall = System.currentTimeMillis();
PreparedStatement stmt = conn.prepareStatement("INSERT /* regular bulk insert */ INTO testloadtable (id, text) VALUES (?,?)");

for (int i=0;i&lt;10000;i++)
{
  stmt.setInt(1, i);
  stmt.setString(2, "test" + i);
  stmt.addBatch();
}
long startExecute = System.currentTimeMillis();
stmt.executeBatch();
long endExecute = System.currentTimeMillis();
conn.commit();
long endOverall = System.currentTimeMillis();

System.out.println("Executing batch took: " + (endExecute-startExecute));
System.out.println("Executing overall took: " + (endOverall-startOverall));
System.out.println("Preparation took: " + ((endOverall-startOverall)-(endExecute-startExecute)));

Executing batch took: 212
Executing overall took: 372
Preparation took: 160

So a regular insert takes only 212ms while a DML error logging insert takes 861ms. That’s 4 times longer!
The reason for this is because of the unpublished bug 11865420 (My Oracle Support Doc Id: 11865420.8). Once you download the patch and update the system accordingly the insert with DML error logging is just as fast as without:

Regular batch insert:

long startOverall = System.currentTimeMillis();
PreparedStatement stmt = conn.prepareStatement("INSERT /* regular bulk insert */ INTO testloadtable (id, text) VALUES (?,?)");

for (int i=0;i&lt;10000;i++)
{
  stmt.setInt(1, i);
  stmt.setString(2, "test" + i);
  stmt.addBatch();
}
long startExecute = System.currentTimeMillis();
stmt.executeBatch();
long endExecute = System.currentTimeMillis();
conn.commit();
long endOverall = System.currentTimeMillis();

System.out.println("Executing batch took: " + (endExecute-startExecute));
System.out.println("Executing overall took: " + (endOverall-startOverall));
System.out.println("Preparation took: " + ((endOverall-startOverall)-(endExecute-startExecute)));

Executing batch took: 248
Executing overall took: 399
Preparation took: 151

DML error logging insert:

long startOverall = System.currentTimeMillis();
PreparedStatement stmt = conn.prepareStatement("INSERT /* addBatch with logging errors */ INTO testloadtable (id, text) VALUES (?,?) LOG ERRORS INTO ERR_TESTLOADTABLE REJECT LIMIT UNLIMITED");

for (int i=0;i&lt;10000;i++)
{
  stmt.setInt(1, i);
  stmt.setString(2, "test" + i);
  stmt.addBatch();
}
long startExecute = System.currentTimeMillis();
stmt.executeBatch();
long endExecute = System.currentTimeMillis();
conn.commit();
long endOverall = System.currentTimeMillis();

System.out.println("Executing batch took: " + (endExecute-startExecute));
System.out.println("Executing overall took: " + (endOverall-startOverall));
System.out.println("Preparation took: " + ((endOverall-startOverall)-(endExecute-startExecute)));

Executing batch took: 227
Executing overall took: 384
Preparation took: 157

Conclusion: This is the last article of the series Loading data fast! In this series I’ve not only shown how bad it is to commit after each row (remember Autocommit in JDBC!) but also how much more speed you can get out of your program by doing batch inserts. I’ve also shown how to deal with potential errors during batch inserts and how much performance impact errors produce. The latter I have done to make it clear that developers should not just throw DML error logging inserts at every problem as they will lose the performance benefit again that batch inserts provide. So now, go over your code and batch up! 😉