Database Reference
In-Depth Information
Finding Expensive Statements in a
Statspack Repository
To get a quick overview of expensive statements in an entire Statspack repository, the result of
the function
SP_SQLTEXT
from the previous section may be joined to
STATS$SQL_SUMMARY
, which
contains the measurement data of all the SQL statements captured. If the figures are normal-
ized by how many times a statement was executed (
STATS$SQL_SUMMARY.EXECUTIONS
), an initial
overview of slow statements results.
Following is the script
sp_sqltext_join.sql
, which accomplishes this. It reports elapsed
time in seconds (
STATS$SQL_SUMMARY.ELAPSED_TIME
is in microseconds). Disk reads and buffer
gets are normalized by the execution count of the statement. The script restricts the result set
to statements that completed after more than one second. Of course, before starting a tuning
session, you should confirm that statements found in this way impair business processes.
$ cat sp_sqltext_join.sql
set long 1000000
col module format a6
col snap_id format 9999999
col sql_text format a80 word_wrapped
SELECT s.snap_id, s.old_hash_value,
round(s.elapsed_time/s.executions/1000000, 2) ela_sec_per_exec,
floor(s.disk_reads/s.executions) read_per_exec,
floor(s.buffer_gets/s.executions) gets_per_exec,
s.module, t.sql_text
FROM stats$sql_summary s,
(SELECT hash_value, sql_text from table(site_sys.sp_sqltext())) t
WHERE s.old_hash_value=t.hash_value
AND s.elapsed_time/s.executions/1000000 > 1
ORDER BY s.elapsed_time, s.disk_reads, s.buffer_gets;
Running the preceding query yields this well-known statement that was used as an
example throughout this chapter:
SNAP_ID OLD_HASH_VALUE ELA_SEC_PER_EXEC READ_PER_EXEC GETS_PER_EXEC MODULE
-------- -------------- ---------------- ------------- ------------- ------
SQL_TEXT
----------------------------------------------------------------------------
33 1455318379 2.87 2380 14733 HR
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.last_name FROM hr.employees emp, hr.employees
mgr, hr.departments d, hr.locations l, hr.jobs j WHERE
emp.manager_id=mgr.employee_id AND emp.department_id=d.department_id AND
d.location_id=l.location_id AND emp.job_id=j.job_id
