Database Reference
In-Depth Information
Total number of roundtrips (SQL*Net message from/to client): 43
CPU usage breakdown
------------------------
parse CPU: 0.00s (5 PARSE calls)
exec CPU: 0.00s (24 EXEC calls)
fetch CPU: 0.00s (12 FETCH calls)
Statistics:
-----------
COMMITs (read write): 1 -> transactions/sec 3.718
In this resource profile, the response time R accounted for 46% of the 0.582 seconds captured
by the trace file. This time, the unknown portion of response time was only 19%.
I once again measured the actions with
V$SERV_MOD_ACT_STATS
and
DBMS_MONITOR
. Since
there is no way to clear statistics in
V$SERV_MOD_ACT_STATS
, I used the action names “exif_
insert_imp” and “lob_load_imp” this time. Certainly, it would have been possible to take two
snapshots of
V$SERV_MOD_ACT_STATS
and to calculate the differences, but changing the module
and action names made more sense, since the program had changed significantly too. The
necessary calls to
DBMS_MONITOR
are as follows:
SQL> EXEC dbms_monitor.serv_mod_act_stat_enable('TEN.oradbpro.com','img_load', -
> 'exif_insert_imp')
SQL> EXEC dbms_monitor.serv_mod_act_stat_enable('TEN.oradbpro.com','img_load', -
> 'lob_load_imp')
Now the system was ready to measure another ten iterations.
$ time perl img_load_improved.pl 10 sample.jpg
real 0m0.688s
user 0m0.031s
sys 0m0.000s
This run was so fast that I had to get the figures with millisecond resolution to prevent
some values from becoming zero due to rounding.
SQL> SELECT action, stat_name, round(value/1000000, 3) AS value
FROM v$serv_mod_act_stats
WHERE service_name='TEN.oradbpro.com'
AND module='img_load'
AND action IN ('exif_insert_imp','lob_load_imp')
AND stat_name in ('DB time', 'DB CPU', 'sql execute elapsed time',
'user I/O wait time')
ORDER BY action, stat_name;
ACTION STAT_NAME VALUE
--------------- ------------------------ -----
exif_insert_imp DB CPU .003
exif_insert_imp DB time .003
exif_insert_imp sql execute elapsed time .003
exif_insert_imp user I/O wait time 0
