Database Reference
In-Depth Information
lob_load_imp DB CPU .116
lob_load_imp DB time .152
lob_load_imp sql execute elapsed time .001
lob_load_imp user I/O wait time 0
The DB time for both actions, which was formerly 8.77 seconds, was reduced to a mere
0.155 seconds. This accounts for only 22% of the response time as measured by the utility
time
,
such that it would not be a good idea to base a forecast on these figures. Note that DB time does
not include wait events that occur between database calls, such as
SQL*Net message from client
and
SQL*Net message to client
.
Phase 5—Extrapolation
Since the original measurement of 68 LOBs per minute was done without instrumentation in
place and without SQL trace enabled, I also needed measurements that were not influenced by
such factors as the basis for my extrapolation. I did another series of ten measurements with
instrumentation and SQL trace disabled. The average elapsed time to insert 10 LOBs was 0.931
seconds. Based on this figure, the application should be able to load almost 645 LOBs per
minute. Use of the elapsed time (0.582 s) covered by the level 8 SQL trace file would result in
about 1030 LOBs per minute. The actual figure will probably lie somewhere in between these
two values.
Table 28-2 summarizes some of the differences between the original test case and the opti-
mized test case. Figures are from a load run of 10 LOBs. Elapsed time was reduced to less than
one-tenth of the original value.
Table 28-2.
Measurements Before and After Optimization
Metric
Original Test Case
Optimized Test Case
Elapsed time covered by trace file
8.90 s
0.58 s
Total wait time
6.01 s
0.32 s
SQL*Net round-trips
1201
43
Parse calls
46
5
Executions
47
24
Fetch calls
26
12
Think time
0.34 s
0.21 s
Phase 6—Installation
The one significant obstacle that had to be overcome was the reorganization of the LOB segment,
since it required downtime. Other than that, the changes were approved quickly. New measure-
ments were taken after the table had been reorganized during a maintenance window. The
measurements were taken with instrumentation and SQL trace disabled. Throughput varied
between 640 and 682 LOBs per minute. This was reasonably close to the extrapolated value of
645 LOBs per minute. Compared to the original throughput, the speedup was more than tenfold.
