Database Reference
In-Depth Information
optimizer parameters, changing the SQL statement, changing database objects with DDL, intro-
ducing previously unused features (e.g., Partitioning option, stored outlines, SQL profiles), etc.
Effects of the improvement are measured in the same way as the original code path. Comparing the
measurement data of the original code path with the measurement data of the improvements
achieved in phase 4 may be used to extrapolate the magnitude of the performance improvement
(phase 5). In other words, it is possible to forecast the effect of an improvement in a test case on
the code path that was measured in phase 1. If the improvement is deemed sufficient, the
necessary changes need to be approved and installed on the target (production) system at some
point. Discussing each phase of the MERITS method in full detail is a subject for a separate
book. However, I provide enough information on each phase to allow you to use the method as
a framework for performance optimization tasks.
Measurement
Since extended SQL trace is the most complete account of where a database session spent its
time and a resource profile may be compiled from extended SQL trace data, this data source is
at the core of the measurements taken. However, an extended SQL trace file does not provide
a complete picture of an application, system, or DBMS instance. Some aspects that are not covered
by an extended SQL trace file are as follows:
•
Load at the operating system level (I/O bottlenecks, paging, network congestion, waiting
for CPU)
ORACLE DBMS parameters
Session statistics (
V$SESSSTAT
)
Contending database sessions
To capture a complete picture of the system, I recommend using tools such as
sar
,
iostat
,
vmstat
, and
top
to record activity at the operating system level. Concerning the DBMS, I advo-
cate taking a Statspack or AWR snapshot that spans the same interval as the extended SQL trace
file. The Statspack snapshot should include the traced session (
STATSPACK.SNAP
parameter
i_session_id
). If AWR is preferred, an active session history report may be used to get addi-
tional information on the session. It may be necessary to take several measurements and to
compute an average to compensate for fluctuations in response time. Both AWR and Statspack
reports contain a list of all initialization parameters with non-default values. An Active Session
History (ASH) report contains a section on contending sessions entitled “Top Blocking Sessions”.
Measurement Tools
This section presents two SQL scripts that may serve as measurement tools at session and
instance level. The script
awr_capture.sql
is based on AWR and ASH, while
sp_capture.sql
is
based on Statspack. Both scripts require
SYSDBA
privileges. The scripts do not invoke any oper-
ating system tools to collect operating system statistics. Yet, an Oracle10
g
Statspack report includes
CPU and memory statistics at operating system level in the “Host CPU” and “Memory Statis-
tics” sections and an AWR report includes a section titled “Operating System Statistics”.
