Database Reference
In-Depth Information
begin
hotsos_ilo_timer.set_mark_all_tasks_interesting(mark_all_tasks_interesting=>true,
ignore_schedule=>true);
end;
I used the module name “img_load” for the entire program and defined two actions:
The action “exif_insert” encompassed the generation of a new primary key for the next
row and the retrieval of the LOB locator, which was then used to load the LOB data with
the Perl DBI function
ora_lob_append
.
The action “lob_load” comprised reading the JPEG file from disk and loading it into the
BLOB
column. Due to the average image file size of 1 MB, the LOB was loaded piece-wise.
I assumed that the file system access to read the JPEG file did not cause a lot of overhead.
If this assumption had turned out to be wrong, I would have instrumented the file system access in
Perl, in addition to instrumenting database access with Hotsos ILO.
The test program allowed me to indicate how many LOBs it should load. For the sake of
simplicity, a single JPEG file was loaded again and again, although this would reduce the impact of
reading the image file due to file system caching, whilst the original application needed to read
separate image files each time.
I set up the database to collect statistics on the service, module, and actions of interest as
follows:
SQL> EXEC dbms_monitor.serv_mod_act_stat_enable('TEN.oradbpro.com', -
> 'img_load', 'exif_insert')
SQL> EXEC dbms_monitor.serv_mod_act_stat_enable('TEN.oradbpro.com', -
> 'img_load', 'lob_load')
I measured the response time of a load run comprising ten LOBs with the UNIX utility
time.
5
$ time perl img_load.pl 10 sample.jpg
real 0m9.936s
user 0m0.015s
sys 0m0.015s
Then I retrieved the relevant statistics on service, module, and action from
V$SERV_MOD_ACT_STATS
.
SQL> SELECT action, stat_name, round(value/1000000, 2) AS value
FROM v$serv_mod_act_stats
WHERE service_name='TEN.oradbpro.com'
AND module='img_load'
AND action IN ('exif_insert','lob_load')
AND stat_name in ('DB time', 'DB CPU', 'sql execute elapsed time',
A
time
utility for Windows ships with Cygwin.
5.
