Performance tuning using SQL Trace part 1

I quite regularly trace PL/SQL stored procedures and packages which run over night,
my preferred method of generating a trace is to put a call to DBMS_SESSION.SET_TRACE(TRUE) at the top of the procedure. You can use DBMS_SYSTEM.SET_EV to set a variety of events for the session or you could use DBMS_SUPPORT if you want to capture bind variable and wait information in your trace file. The 10g documentation also states that DBMS_MONITOR can also be used for this, although I have never used this.

For SQL embedded in a 3 GL I use a logon trigger which gets the sid of the session from v$mystat, looks up the executable the process represents from the program column in v$session and then use calls DBMS_SESSION.SET_SQL_TRACE if the process to be traced has connected. This should only be used to trace processes when a performance issue is evident and the process connects and disconnects from the database so rapidly that tracing the session is not practical using a tool such as TOAD or DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION.

CREATE OR REPLACE TRIGGER trace_logon
AFTER LOGON ON DATABASE
DECLARE
v_dummy INTEGER;
v_sid INTEGER;
v_ser_hash INTEGER;
BEGIN
SELECT sess.sid,
serial#
INTO v_sid,
v_ser_hash
FROM v$session sess,
v$mystat mys
WHERE sess.sid = mys.sid AND
upper(program) LIKE '%SVMS%';
DBMS_SYSTEM.SET_EV(v_sid,v_ser_hash,10046,12,'');

EXCEPTION
WHEN NO_DATA_FOUND THEN
v_dummy := v_dummy + 1;
END;
/

If you know which schema the process you wish to trace you can change the first two lines of the create trigger statement to :-

create or replace trigger on_logon after logon on

Make sure that timed_statistics is set to true (for releases prior to 8i) at session or instance level and for releases 9i and above statistics_level should be set to typical at the very least, otherwise no timing information will appear in your trace file.

You may wonder what value there is in tracing processes the source code for which is unavailable, the answer is in stored outlines which came along in 8i allowing you to influence the executing plans of statements without having to touch any code. I believe there is some editor for stored outlines that comes in one of the 9i Enterprise Manager packs and the SQL Tuning Advisor in 10g can implement changes to execution plans via stored outlines through Enterprise Manager.

If you are stuck with 8i or without access to any of these tools, you may find this
tip from the www.oracledba.co.uk web site useful, in fact I have used this and can confirm that it does work :-

1. Identify the poorly running SQL via any conventional means (V$SQLAREA etc)

2. Create an outline for this SQL (with no tuning performed)

3. Determine a set of hints to improve the access path of the SQL

4. Create another outline for this new improved SQL.

5. Then swap the outlines by tweaking the OL$HINTS table

UPDATE OUTLN.OL$HINTS
SET OL_NAME='tmp'
WHERE OL_NAME = 'THE_BAD_SQL';

UPDATE OUTLN.OL$HINTS
SET OL_NAME='THE_BAD_SQL'
WHERE OL_NAME = 'THE_NEW_SQL';

UPDATE OUTLN.OL$HINTS
SET OL_NAME='THE_NEW_SQL'
WHERE OL_NAME = 'tmp';

Another useful tip is to set the tracefile_identifer parameter to something meaningful, this will ‘tag’ your trace file with whatever string you set this parameter to. This can be done from within SQL PLUS :-

ALTER SESSION SET tracefile_identifier=’mytrace’

Or you can embed this within PL/SQL :-

EXECUTE IMMEDIATE ‘ALTER SESSION SET tracefile_identifier=’’mytrace’’

Once you have your trace file which will be located in the user_dump_dest directory, you will need to run it through the tkprof utility covered in section 2 of this post.