Using Oracle DBMS Support Package

This is a quick guide on how to setup and use the Oracle RDBMS support package for tracing sessions, formatting the output and using the output to trace an Oracle performance issue.

First install the Oracle dbms_support package:

(s1prdmgt01)oracle:/u01/app/oracle/product/9.2.0/rdbms/admin
$ sqlplus '/ as sysdba' @dbmssupp.sql

SQL*Plus: Release 9.2.0.6.0 - Production on Fri May 12 14:38:53 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production


Package created.


Package body created.

SQL>

Then using the package, trace the interesting session, you can get the session from the v$session table.

SQL> SELECT sid,serial# FROM v$session
2 WHERE username = 'SHORSMAN';

SID SERIAL#
---------- ----------
10 11

SQL>

Start the trace:

SQL> EXEC DBMS_SUPPORT.START_TRACE_IN_SESSION(10,11,waits=>true, binds=>true)

PL/SQL procedure successfully completed.

SQL>

Once the user has recreated the performance query, stop the trace:

SQL> EXEC DBMS_SUPPORT.STOP_TRACE_IN_SESSION(10,11)

PL/SQL procedure successfully completed.

SQL>

There should be a new file in the user dump directory

SQL> show parameter user_dump

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /u01/app/oracle/admin/testdb/u
dump
SQL>

Run tkprof to create the formatted output from the trace file. Use the explain option to get the explain plan of the query that is being traced.

********************************************************************************

select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#,
d_owner#, nvl(property,0),subname
from
dependency$,obj$ where d_obj#=:1 and p_obj#=obj#(+) order by order#


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 1 8 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.01 0.00 1 8 0 2

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
2 SORT ORDER BY
2 NESTED LOOPS OUTER
2 TABLE ACCESS BY INDEX ROWID DEPENDENCY$
2 INDEX RANGE SCAN I_DEPENDENCY1 (object id 127)
1 TABLE ACCESS BY INDEX ROWID OBJ$
1 INDEX UNIQUE SCAN I_OBJ1 (object id 36)

********************************************************************************

If there's access by full table scans then there's trouble ahead.....
Next would be to investigate the columns being searched on, using the dictionary tables dba_tab_columns and dba_ind_columns:

select t.column_name
from dba_tab_columns t, dba_ind_columns i
where t.table_name = 'S_EVT_ACT'
and t.column_name != i.column_name
and t.column_name like 'X_GMC%'
and i.column_name like 'X_GMC%'
group by t.column_name;