Let us suppose that you want to trace client user which was established in an application environment( for example Oracle E-Business-Suite application in 3-tier architecture)
Tracing a client
Let’s suppose there are two client or application user (JOEL and LASZLO).
They use the same application, but different modules and doing different activities.
Via connection pool they use –implicitly – the apps Oracle user account.
LASZLO is responsible for REPORTING using the EDUCATION module.
JOEL is manager working with SME REPORTING in MANAGEMENT MODULE.
LASZLO is working in EDUCATION_DEV modul using DEVELOPING activity
JOEL wants to observe LASZLO’s activity tracing LASZLO as a client user:
We can imitate with DBMS_SESSION and DBMS_APPLICATION_INFO packages:
In SQL*Plus APPS executed the following PL/SQL program:
EXEC DBMS_SESSION.SET_IDENTIFIER(‘LASZLO’)
EXEC DBMS_APPLICATION_INFO.SET_MODULE(‘EDUCATION_DEV’,'DEVELOPING’)
In an other SQL*Plus instance APPS executed the following PL/SQL program:
EXEC DBMS_SESSION.SET_IDENTIFIER(‘LASZLO’)
EXEC DBMS_APPLICATION_INFO.SET_MODULE(‘EDUCATION’,'REPORTING’)
In SQL Devepoler SYS executed the following PL/SQL program:
EXEC DBMS_SESSION.SET_IDENTIFIER(‘JOEL’)
EXEC DBMS_APPLICATION_INFO.SET_MODULE(‘MANAGEMENT’,'SME REPORTING’)
We want to trace LASZLO’S activity
The trace files will be created under this directory:
Now you can execute (as SYS) the following PL/SQL program:
EXEC DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(‘LASZLO’)
If you want to set the statistics level for that session
create AFTER LOGON trigger, like this:
CREATE OR REPLACE TRIGGER logon_trig
AFTER LOGON ON hr.SCHEMA
BEGIN
EXECUTE IMMEDIATE ‘ALTER SESSION SET STATISTICS_LEVEL=ALL’;
END;
/
LASZLO executed the following query as DEVELOPING using apps Oracle user :
SELECT /* LASZLO_DEVELOPING */ last_name,e.salary
FROM hr.employees e
WHERE
E.SALARY>(select avg(d.salary) FROM hr.employees d
WHERE d .department_id=e.department_id);
LASZLO executed the following query as REPORTING using apps Oracle user :
SELECT /* LASZLO_REPORT */ last_name,e.salary
FROM hr.employees e
WHERE E.SALARY>(select avg(d.salary)
FROM hr.employees d
WHERE d.department_id=e.department_id);
Now you can execute (as SYS) the following PL/SQL program:
EXEC DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE(‘LASZLO’)
Under the trace directory you will find the following files: orcl_ora_9508.trc and orcl_ora_1752.trc.
But you don’t have to know them!
It is enough to run the TRCSESS utility which collects the necessary information from the trace files and produce a new one:
trcsess output=laszlo.trc clientid=LASZLO
This file contains very detailed information. If You need a more readable text file, please run the tkprof utility in command line
tkprof LASZLO.trc LASZLO.txt
Part from laszlo.txt (LASZLO_DEVELOPING)
Part from laszlo.txt (LASZLO_REPORT)
