Databases Reference
In-Depth Information
set trimout on
set trimspool on
set feedback off
set heading off
set linesize 32767
col sql_fulltext format a32767
spool sp_sqltext_&old_hash_value..lst
SELECT sql_fulltext FROM v$sql WHERE old_hash_value=&old_hash_value;
spool off
exit
Let's test the script
sql_fulltext.sql
with the
SELECT
statement that needs tuning.
$ sqlplus -s system/secret
@sql_fulltext.sql 14
55318379
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
This time, the tab stops in lines 2 and 3 as well as the line breaks in the original positions
have been preserved. Do not despair, even if the statement has been aged out of the shared
pool or the DBMS instance restarted. The next section will show how pipelined table functions
introduced in PL/SQL with Oracle9
i
may be used to solve the problem.
Accessing STATS$SQLTEXT
A closer look at the main Statspack report file
sprepins.sql
soon reveals that the full statement
text is stored in the table
STATS$SQLTEXT
, while the measurement data of the statement are in
table
STATS$SQL_SUMMARY
. The source code of the package
STATSPACK
in
spcpkg.sql
reveals that
STATS$SQLTEXT
copies statement texts from
V$SQLTEXT
. Both V$ views split SQL statements into
several
VARCHAR2
pieces with a maximum length of 64 characters.
SQL> DESCRIBE v$sqltext
Name Null? Type
------------------------------- -------- ----------------------------
ADDRESS RAW(4)
HASH_VALUE NUMBER
SQL_ID VARCHAR2(13)
COMMAND_TYPE NUMBER
PIECE NUMBER
SQL_TEXT VARCHAR2(64)
SQL> DESCRIBE perfstat.stats$sqltext
Name Null? Type
------------------------------- -------- ----------------------------
