Database Reference
In-Depth Information
I decided to try the extreme and used a piece size of 1048576 (1 MB) instead of 8192 (8 KB).
The average of another ten runs was 1.71 seconds. Another 43% reduction in response time.
The two changes tested so far already more than met the goal set by the client, so I could have
stopped the optimization at this point. However, I wished to show the benefits of parsing once
and executing many times. Furthermore, I wanted to point out how
INSERT
RETURNING
may be
used to reduce the number of round-trips between client and database server.
The original algorithm of the application was as follows:
1.
Increment and retrieve the sequence used for numbering the primary key with the SQL
statement
SELECT
image_id_seq.NEXTVAL
FROM
dual
.
Insert a row into the table
IMAGES
using the sequence value as the key for column
ID
. The
INSERT
statement also initialized the
BLOB
with
empty_blob()
.
2.
3.
Retrieve the LOB locator using the index on column
ID
with
SELECT
image_data
FROM
images
WHERE
id=:id
.
This required parsing and execution of three separate statements. However, the three
steps may be combined into a single step by using
INSERT
RETURNING
as shown here:
INSERT INTO images (id, date_loaded, exif_make, exif_model, exif_create_date,
exif_iso, exif_f_number, exif_exposure_time, exif_35mm_focal_length, image_data)
VALUES(image_id_seq.NEXTVAL, sysdate, :exif_make, :exif_model,
to_date(:exif_create_date, 'yyyy:mm:dd hh24:mi:ss'), :exif_iso,
:exif_f_number, :exif_exposure_time, :exif_35mm_focal_length, empty_blob())
RETURNING id, rowid, image_data INTO :id, :row_id, :lob_loc
Unfortunately this crashes the release of Perl that ships with Oracle10
g
Release 2 (DBI
version: 1.41 DBD::Oracle version: 1.15),
6
but works with more recent releases. As a workaround,
the LOB locator may be fetched separately. With this workaround in place, the average response
time of ten runs was reduced further to 1.11 seconds.
Three issues remained to be fixed:
The sequence, which was not cached
The superfluous parse calls inside the loop which loaded the images
Frequent commits inside the loop instead of once after finishing the load process (or at
least intermittently, say after 10000 rows when loading a large number of images)
I assigned the sequence a cache of 1000 numbers with the following DDL statement:
SQL> ALTER SEQUENCE image_id_seq CACHE 1000;
6.
Oracle11
g
ships with the same DBI and DBD::Oracle releases, such that it's not an option to use an
Oracle11
g
Perl client.
