Archive for February, 2011

Trace an Oracle application client user in 3-tier environment

February 10, 2011

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)


Advertisements

Trace an arbitrary Oracle application session in client/server architecture

February 10, 2011

Let us suppose that you want to trace a session which was established in client/server architecture (Dedicated Server architecture)

Tracing  a  session

This method is applicable, if the session was created by an c/s application.
Very easy to identify which is the target  session:

Let us suppose that  the session is identified by
SID=420 and SERIAL#=2103.
The HR user’s  Operating system process identifier (SPID) is 6684.
You can find the directory of the trace file which will be created later:

Now  you can execute (as SYS) the following PL/SQL program:
EXEC DBMS_MONITOR.session_trace_enable(420,2103,TRUE,TRUE,’ALL_EXECUTIONS’)

3. argument (waits)  TRUE, wait information is present in the trace
4. argument (binds)  TRUE, bind information is present in the trace
If you want to set the statistics level for that session
create an 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;
/

Connect and execute the following query as HR :

You can turn off the tracing for the same session:

EXEC DBMS_MONITOR.session_trace_disable(420,2103)

Under the trace directory you will find the following file: orcl_ora_6684.trc where 6684 is HR’s SPID.
This file contains very detailed information.
If You need a more readable text file, please run the tkprof utility in  command  line
tkprof orcl_ora_6684.trc hr_sqlplus2.txt
Detail from the file:

Execute the sqlid stored procedure
(which can be found in “Trace your Oracle session” blog)
as SYS:

EXEC hr.sqlid(‘MAX(SUM(salary))’)

Tracing your Oracle session

February 10, 2011

How to trace Oracle session using SQL and PL/SQL tools?

Many times developers and/or DBA’s want to trace their own and/or other session’s  SQL and PL/SQL activities.
There are many possibilities to solve this problem especially, if you  want to trace your  session.
Very easy to identify which is your session:

   

Let us suppose that  your session is identified by SID=403 and SERIAL#=1242.
Your Operating system process identifier (SPID) is 8564
You can find the directory of the trace file which will be created later:

Modify your session:
ALTER SESSION SET SQL_TRACE=TRUE;

ALTER SESSION SET  STATISTICS_LEVEL=ALL;

Execute the following query:

Under the trace directory You will find the following file: orcl_ora_8564.trc where 8564 is your SPID.
This file contains very detailed information.
If You need a more readable text file, please run the tkprof utility in  command  line
tkprof orcl_ora_8564.trc hr_sqlplus.txt

Detail from the text file:

Executing  a suitable PL/SQL stored procedure, called  SQLID:

You want to find your SELECT statement’s execution plan with plan statistics (due to statistics level is ALL!)
EXEC SQLID(‘MAX(SUM(salary))’)