Trace an Oracle application client user in 3-tier environment


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)


About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: