Database Reference
In-Depth Information
Finding Snapshots with High Resource Utilization
As part of my consulting work, I was occasionally asked to scrutinize an entire Statspack repos-
itory. A client would create a schema level export of the
PERFSTAT
schema in his production
database and I would import it into a test database (more on importing Statspack data in a
moment). This is a situation where initially you don't have any idea which snapshots are worth
investigating.
Since a Statspack repository contains snapshots of performance data from V$ views, the
Statspack report must subtract the measurements taken at the beginning of the snapshot interval
from the measurements taken at the end of the snapshot interval to arrive at the resource
consumption during the interval. The report script
spreport.sql
is passed the beginning and
end snapshot numbers as input. Hence a simple join (or outer join where necessary) is sufficient.
Matters are more complicated when all the snapshots must be investigated. This task is a
case for the analytic function
LAG
, which maps column values of a previous row visited by a
SELECT
statement into the current row without a self join, such that a window containing two
rows is available simultaneously. At this point the attentive reader will rightfully object that
there is no previous row in a relational database. This is why an ordering must be defined using
the SQL keywords
OVER
and
ORDER
BY
. Following is an example of a query on
STATS$SNAPSHOT
using
LAG
:
SQL> SET NULL <NULL>
SQK> SELECT LAG(snap_id) OVER (ORDER BY snap_id) AS start_snap_id,
snap_id AS end_snap_id
FROM stats$snapshot;
START_SNAP_ID END_SNAP_ID
------------- -----------
<NULL> 33
33 34
34 35
Note that the start snapshot identifier in the first row is
NULL
, since it is outside of the window.
For details on
LAG
, please consult the
Oracle Database SQL Reference
manual.
A view that yields successive snapshot identifiers and verifies that the interval is valid may
be created as the basic building block for analysis. The check for interval validity is performed
by comparing the values in the column
STATS$SNAPSHOT.STARTUP_TIME
. If the startup time does
not match, then the instance was restarted in between snapshots and the measurements are
not usable. In a RAC environment, matters get even more intricate. Since a single repository
might contain measurements from several RAC instances, the instance numbers of the start
and end snapshots must also match. This is accomplished by using the columns
INSTANCE_
NUMBER
and
SNAP_ID
in the
ORDER
BY
supplied with the function
LAG
. The view, which retrieves
identifiers of valid consecutive beginning and ending snapshots, is called
SP_VALID_INTERVALS
(script
sp_valid_intervals.sql
).
CREATE OR REPLACE VIEW site_sys.sp_valid_intervals AS
SELECT *
FROM (
SELECT lag(dbid) over (order by dbid, instance_number, snap_id) AS start_dbid,
dbid AS end_dbid,
