Database Reference
In-Depth Information
Thus, the solution for getting the full text of the poorly performing SQL statement is to
increase the value of
num_rows_per_hash
. Since the documentation does not reveal how Statspack
stores captured SQL statements, we simply set
num_rows_per_hash
to an arbitrarily large value
such as 1000 and run the Statspack report again (script
$ORACLE_HOME/rdbms/admin/spreport.sql
).
This time, the complete SQL statement text is in the report.
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
ment_id=d.department_id AND d.location_id=l.location_id AND emp.
job_id=j.job_id
Unfortunately it's still not in a format that adheres to correct SQL syntax. The remaining
task consists of copying the statement from the report and editing it, such that line breaks in
the middle of identifiers, SQL reserved words, and literals are removed. This procedure can be
quite annoying—especially for statements that exceed 50 lines or so.
The more elegant approach, which will pay off sooner or later, is to go directly to the Statspack
repository in the
PERFSTAT
schema and to retrieve the statement from there. Note, however,
that Statspack copies SQL statement texts from
V$SQLTEXT
instead of from
V$SQLTEXT_WITH_
NEWLINES
. Only the latter view contains the statement with line breaks and tab stops in the original
positions where the developer placed them preserved. If you're dealing with a more complex
statement, proper formatting may significantly ease the process of finding out what the state-
ment does. SQL statement texts retrieved from
V$SQL
and
V$SQLTEXT
have been subjected to an
undocumented normalization procedure which removes line breaks as well as tab stops.
In Oracle10
g
, the column
SQL_FULLTEXT
was added to
V$SQL
to provide SQL statements
with intact formatting as a
CLOB
.
SQL> DESCRIBE v$sql
Name Null? Type
------------------------------- -------- --------------
SQL_TEXT VARCHAR2(1000)
SQL_FULLTEXT CLOB
SQL_ID VARCHAR2(13)
…
When working with Oracle10
g
, given that the statement text is still cached in the shared pool
within the SGA, the following SQL*Plus script retrieves the statement text with intact formatting:
$ cat sql_fulltext.sql
-- pass old_hash_value as the single argument to the script
define old_hash_value='&1'
set verify off
set long 100000
