File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Oracle 9i reading BLOB performance issues Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Oracle 9i reading BLOB performance issues" Watch "Oracle 9i reading BLOB performance issues" New topic

Oracle 9i reading BLOB performance issues

carlos fernandez

Joined: Mar 30, 2004
Posts: 17
Windows XP Pro SP2
JDK 1.5.0_05
Oracle 9i
Oracle Thin Driver for JDK 1.4 v.
DBCP v.1.2.1
Spring v1.2.7 (I am using the JDBC template for convenience)

I have run into serious performance issues reading BLOBs from Oracle using oracle's JDBC thin driver. I am not sure if it a constraint/mis-configuration with oracle or a JDBC problem.

I am hoping that someone has some experience accessing multi-MB BLOBs under heavy volume.

We are considering using Oracle 8 or 9 as a document repository. It will end up storing hundreds of thousands of PDFs that can be as large as 30 MBs. We don't have access to Oracle 10.

I am running tests against Oracle 8 and 9 to simulate single and multi-threaded document access. Out goal is to get a sense of KBps throughput and BLOB data access contention.

There is a single test table with 100 rows. Each row has a PK id and a BLOB field. The blobs range in size from a few dozen KB to 12MB. They represent a valid sample of production data. The total data size is approx. 121 MBs.

Single Threaded Test
The test selects a single blob object at a time and then reads the contents of the blob's binary input stream in 2 KB chunks. At the end of the test, it will have accessed all 100 blobs and streamed all 121 MBs. The test harness is JUnit.

8i Results: On 8i it starts and terminates successfully on a steady and reliable basis. The throughput hovers around 4.8 MBps.

9i Results: Similar reliability to 8i. The throughput is about 30% better.

Multi-Threaded Test
The multi-threaded test uses the same "blob reader" functionality used in the single threaded test. However, it spawns 8 threads each running a separate "blob reader".

8i Results: The tests successfully complete on a reliable basis. The aggregate throughput of all 8 threads is a bit more than 4.8 MBps.

9i Results: Erratic. The tests were highly erratic on 9i. Threads would intermittently lock when accessing a BLOB's output stream. Sometimes they lock accessing data from the same row, othertimes it is distinct rows. The number and the timing of the thread "locks" is indeterminate. When the test completed successfully the aggregate throughput of the 8 threads was approx. 5.4 MBps.

I would be more than happy to post code or the data model if that would help.

Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33098

Welcome to the JavaRanch.

We're pleased to have you with us in this forum, but we do require members to have valid display names.Pleaseedit your profile and correct your display name since accounts with display names get deleted.

In particular, your display name must be a first and a last name separated by a space character.

Forum Bartender

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
I agree. Here's the link:
subject: Oracle 9i reading BLOB performance issues
jQuery in Action, 3rd edition