Database Reference
In-Depth Information
I urge you to capture at least one snapshot per hour. When no performance snapshots are
captured on a regular basis, you will be at a loss when database users call and state they had a
performance problem at some point in the recent past. You won't be able to answer the request
to figure out why, except by shrugging your shoulders. With historical performance data, you
ask at what time it happened, generate the Statspack reports for snapshots taken before and
after that time, possibly drill down by looking at the execution plan of an expensive statement
with script
sprepsql.sql
(requires snapshot level 6 or higher),
4
identify the cause of the problem,
and solve it.
Retrieving the Text of Captured SQL Statements
Statspack is a tremendous improvement over its predecessor bstat/estat. However, it is annoying
that all SQL statements are reproduced with forced line breaks as in
V$SQLTEXT.SQL_TEXT
. Addi-
tionally, statements with more than five lines of text are truncated. Following is an excerpt of a
Statspack report that contains a truncated statement:
SQL ordered by CPU DB/Inst: ORCL/orcl Snaps: 2-3
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total DB CPU (s): 11
-> Captured SQL accounts for 48.4% of Total DB CPU
-> SQL reported below exceeded 1.0% of Total DB CPU
CPU CPU per Elapsd Old
Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value
-------- ---------- -------- ------ -------- ----------- ----------
10.34 11 0.94 12.3 31.65 162,064 1455318379
SELECT emp.last_name, emp.first_name, j.job_title, d.department_
name, l.city, l.state_province, l.postal_code, l.street_address,
emp.email, emp.phone_number, emp.hire_date, emp.salary, mgr.las
t_name FROM employees emp, employees mgr, departments d, locatio
ns l, jobs j WHERE emp.manager_id=mgr.employee_id AND emp.depart
Let's assume that the statement is consuming too many resources and should be tuned.
But how do you tune a SQL statement when you don't have the complete text? At the time of
creating the Statspack report, the application may long have terminated. If it has, it is too late
to capture the statement with SQL trace.
The job created with the script
spauto.sql
does not call the STATSPACK package with a specific snap-
shot level. It uses the default snapshot level in
STATS$STATSPACK_PARAMETER
, which can be modified by
calling the procedure
STATSPACK.MODIFY_STATSPACK_PARAMETER(i_snap_level=>6)
. Snapshot level 6
may cause
STATSPACK.SNAP
to fail, due to an internal error when selecting from
V$SQL_PLAN
. Try flushing
the shared pool with
ALTER SYSTEM FLUSH SHARED_POOL
in case this problem manifests itself. If this does
not prevent an internal error from recurring, reduce the snapshot level to 5 and restart the instance
during the next maintenance window.
4.
