Database Reference
In-Depth Information
LOBs, which are significantly larger than a few database blocks, benefit from a larger block and
chunk size. The database block size of both the client's database and my test database was 8 KB. I
configured a separate buffer pool with 16 KB block size, restarted the test instance, and created
a tablespace for LOB storage with 16 KB block size. These are the SQL statements involved in
the task:
SQL> ALTER SYSTEM SET db_16k_cache_size=50m SCOPE=SPFILE;
SQL> CREATE TABLESPACE lob_ts DATAFILE '&data_file_path' SIZE 1G BLOCKSIZE 16384;
For a production system one would use a much larger value of
DB_16K_CACHE_SIZE
than
just 50 MB. Next, I moved the LOB segment into the new tablespace, increased the LOB chunk
size to the maximum value 32768, and disabled the storage of LOB data in row with the other
columns of the table.
SQL> ALTER TABLE images MOVE LOB (image_data)
STORE AS (TABLESPACE lob_ts DISABLE STORAGE IN ROW
CACHE RETENTION CHUNK 32768);
Since moving a table makes indexes unusable (an
INSERT
would cause ORA-01502), the
primary key index had to be rebuilt.
SQL> ALTER INDEX images_pk REBUILD;
The package
DBMS_REDEFINITION
, which supports online reorganization of tables and asso-
ciated indexes, may be used to reduce the downtime incurred by this operation.
After applying these changes, I ran another load of ten rows with instrumentation enabled
and SQL trace disabled.
$ time perl img_load.pl 10 sample.jpg
real 0m2.550s
user 0m0.045s
sys 0m0.061s
The response time had come down to only 2.5 seconds. This was more than I had expected, so
I repeated the run another nine times. The average response time of ten runs was 3.02 seconds.
The preceding changes alone more than halved the previous response time.
Next I took a closer look at the Perl code. Apart from parsing inside a loop, which was
already evident from the SQL trace file and the resource profiles, I saw that the LOB was read
from the file system and sent to the DBMS instance in 8 KB pieces. This seemed small, since I
had increased the block size to 16 KB and the LOB chunk size to 32 KB. Here's an excerpt of the
code that shows how the LOB column was loaded:
do {
$bytes_read=sysread(LOBFILE, $data, 8192);
$total_bytes+=$bytes_read;
if ($bytes_read > 0) {
my $rc = $dbh->ora_lob_append($lob_loc, $data);
}
} until $bytes_read <=0;
