Databases Reference
In-Depth Information
Lessons Learned
The case study has emphasized that it's not always possible to rely on instrumentation of the
DBMS, since some code paths may not be sufficiently instrumented. Even under such aggra-
vating circumstances, it is fairly easy to determine where an application spends most of the
time when instrumentation is used. For optimum performance, both database structure and
application coding must leverage the rich features offered by the ORACLE DBMS, such as caching
of LOBs and sequences, reduction of parse overhead with bind variables, and diminution of
network round-trips with
INSERT
RETURNING
. The default settings of LOBs are inapt to achieve
good performance.
Source Code Depot
Table 28-3 lists this chapter's source files and their functionality.
Table 28-3.
MERITS Method Source Code Depot
File Name
Functionality
awr_capture.sql
Script for capturing performance data with extended SQL trace and
AWR. Temporarily sets
_ASH_SAMPLE_ALL=TRUE
to cause ASH to sample
idle wait events for improved diagnostic expressiveness. Automatically
generates an AWR report and an ASH report for the traced session. Both
reports are generated in HTML format in the current directory.
ilo_test.sql
This SQL script enables extended SQL trace with Hotsos ILO, runs
SELECT
statements, begins tasks, and terminates tasks. It may be used
to learn what kinds of trace file entries are written by application
instrumentation.
img_load.pl
Original suboptimal LOB loading test case. Contains DDL statements
for the table and sequence used. To run the program, include the
path to the installation directory of the Perl package Image::ExifTool
in the environment variable
PERL5LIB
.
img_load_improved.pl
Optimized LOB loading test case.
sp_capture.sql
Script for capturing performance data with extended SQL trace and
Statspack. Includes taking a session level Statspack snapshot. Make
sure you install the fix for the bug that causes incorrect session level
reports (see source code depot of Chapter 25).
