Efficiently Tracing Oracle Sessions!!

 Author: Tim Mladen

Page: 1 2

Tracing Oracle Sessions is one of the most widely accomplished tasks by almost all database administrators. Efficient approaches used for tracing Oracle sessions will help you get diagnostics information that can be really useful for general troubleshooting and performance tuning. In this article we will discuss a number of options for efficiently tracing Oracle sessions.

Tracing Oracle Sessions by Session ID:

Below script can be used for tracing Oracle session by session id. The script takes Oracle session ID as a parameter and shows the SQL statements running in that session. It also identifies the event for which the session is waiting.

Initializations:

SET PAUSE OFF
SET PAUSE 'PRESS ENTER TO PROCEED'
SET FEEDBACK OFF
BREAK ON SID SKIP 1
SET VERIFY OFF
COLUMN SID FORMAT 99999
COLUMN SQL_TEXT FORM A500

Enter SID:

PROMPT PLEASE ENTER SESSION ID....
ACCEPT P_SID_NUMBER

Show Running SQL:

COLUMN LAST_CALL_ET HEADING "LAST CALLED|TIME"
COLUMN EVENT FORMAT A30
COLUMN SID FORMAT 9999
COLUMN SESSION_CPU HEADING "CPU|USED"
COLUMN PHYSICAL_READS HEADING "PHYSICAL|READS"
COLUMN CONSISTENT_GETS HEADING "LOGICAL|READS"
COLUMN SECONDS_IN_WAIT HEADING "SECONDS|WAITING"

SELECT S1.SID CURRENT_SID, S1.LAST_CALL_ET ,S2.SQL_TEXT
FROM V$SESSION S1 ,V$SQLTEXT S2
WHERE S1.SID = &P_SID_NUMBER AND S1.USERNAME IS NOT NULL AND S1.STATUS = 'ACTIVE' AND S1.SQL_ADDRESS = S2.ADDRESS ORDER BY S1.LAST_CALL_ET,S1.SID,S2.PIECE;

Showing SQL Details:

SELECT A1.SID, A1.VALUE SESSION_CPU, C1.PHYSICAL_READS, C1.CONSISTENT_GETS,D1.EVENT, D1.SECONDS_IN_WAIT
FROM V$SESSTAT A1,V$STATNAME B1, V$SESS_IO C1, V$SESSION_WAIT D1
WHERE A1.SID= &P_SID_NUMBER AND B1.NAME = 'CPU USED BY THIS SESSION' AND A1.STATISTIC# = B1.STATISTIC# AND A1.SID=C1.SID AND A1.SID=D1.SID;

Tracing Oracle Sessions with DBMS_MONITOR Package:

Oracle 10g provides DBMS_MONITOR package with the help of which you can trace the sessions preventing the generation of unwanted or junk trace files. Oracle 10g DBMS_MONITOR can help you get diagnostics information similar to 10046 trace event and is helpful in shared server.

Create Logon Trigger:

First of all we will create a logon trigger that helps you use a single name/id for identifying all the sessions connected to a schema. Below code will set all the sessions connecting to the SCOTT schema to CLIENT_ID ‘MYID'.

CREATE OR REPLACE TRIGGER “SCOTT”.LOGON_TRIGGER AFTER LOGON ON SCHEMA
DECLARE MYUSERIDENTIFIER VARCHAR2(64);
BEGIN
MYUSERIDENTIFIER:='MYID';
DBMS_SESSION.SET_IDENTIFIER(MYUSERIDENTIFIER);
END;

Enable Tracing:

In this step we will trace all sessions with CLIENT_ID as MYID. Generally you will find trace files in the directory specified by the USER_DUMP_DEST parameter in INIT.ORA or SPFILE.

SELECT VALUE FROM V$PARAMETER WHERE NAME='USER_DUMP_DEST';
EXEC DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE('MYID',WAITS=>TRUE,BINDS=>TRUE);

Create Filename:

Now we will run below command in UDUMP directory so as to create a filename specified in OUTPUT parameter. The OUTPUT.TRC tracefile contains traces for all the sessions that had connected to SCOTT schema.

TRCSESS OUTPUT=OUTPUT.TRC CLIENTID=MYID *
SYNTAX:
trcsess [output=] [session=] [clientid=] [service=] [action=] [module=]

Now we will use TKPROF utility to create a human readable file from our raw trace file and diagnose the problems with our application.

TKPROF OUTPUT.TRC SCOTT_SESSIONS.TXT Continued...

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Efficient Oracle Migration, Amazing Tricks!
   The Power of Oracle Data Integration Tools!!
   Common Oracle Recovery mistakes
   Oracle Tips for Monitoring Oracle Import Speed!!