Trace Level 8 and SQLTXPLAIN

One of my Service Request needs to be updated with Trace Level 8 and SQLTXPLAIN:

I got the sql which took longer wait event through enabling the trace at the forms level.

Enabling Trace level 8 at backend:

ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

Session Altered

Run the query.

SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

Session altered.


To obtain the trace file use the following SQL

SET LINESIZE 100
COLUMN trace_file FORMAT A60

SELECT s.sid,
      s.serial#,
      pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||   
      '_ora_' || p.spid || '.trc' AS trace_file
FROM   v$session s,
      v$process p,
      v$parameter pa
WHERE  pa.name = 'user_dump_dest'
AND    s.paddr = p.addr
AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

This will provide you the trace file location.


SQLTXPLAIN:

1. Download the file sqlt.zip file from Note 215187.1 and unzip it to an empty local or server directory. This will create the "sqlt" directory and a number of subdirectories.
2. In the sqlt/install directory run the script "sqcreate.sql"
   
   • Navigate to the sqlt/install directory
   • Start SQL*Plus connecting INTERNAL (SYS) as SYSDBA
   • Execute script sqcreate.sql and respond to values requested
     

EXAMPLE:
$ sqlplus / as sysdba
SQL> start sqcreate.sql




During the run, it will ask for the Connect Descriptor like @PROD, give your SID with @symbol.
It will ask for the sqltxplain Tablespace (Provide the tablespace name other than SYSTEM and SYSAUX). Later provide the schema password for the sqltxplain. It will create views and tables related with SQLTXPLAIN.

Create the sql to be analyzed as txt file. (eg. sqltxt.txt)

and move to the directory $ORACLE_HOME/sqlt/run

connect as apps and with apps password.

Run
@sqltxplain sqltxt.txt

It will run for a moment and asks you the password for SQLTXPLAIN schema password. Providing that will enable the script to export the output files to a dmp file and later it will zip them as a single file.

Move the zip file to your local computer and upload to SR.

Now, you will be having the sql with trace level 8 and SQLTXPLAIN.

Cheers!!!

Enable Event level Trace in Oracle Applications

We need to generate the trace for some application user (either Apps 11i or E-business suite R12). In this case when a user connects to an application its very difficult to track the session.

For this reason we have a profile option, which can be set to generate trace file for any event we want within the application.

Follow the below steps for enabling the event level tracing within an application.

1) Login to application and go to “System Administrator” responsibility.

2) Navigate to “Profile -> System”

3) In the user field, enter the name of user you want to enable tracing for. This will be your application user.

4) On the profile search screen search for “Initialization SQL Statement - Custom” profile.

5) When the profile is shown you can set the value as

begin

fnd_ctl.fnd_sess_ctl(”,”,’TRUE’,'TRUE’,'LOG’,'ALTER SESSION SET EVENTS=”10046 TRACE NAME CONTEXT FOREVER, LEVEL 12” tracefile_identifier=”AppsTrace_10046”’); end;

All the quotes are single quote here. You can just copy and paste the profile value.

6) DO NOT SAVE THE PROFILE

7) In another browser window, login as the user you are going to trace and prepare to reproduce the problem

8 ) Once you are ready to reproduce the problem, go back to the Applications Forms and Save the profile change

9) Reproduce the problem

10) Back in the Applications form, set profile to null so it does not trace anymore and Save the change

11) The trace will be located in the user_dump_dest. The trace can be identified using the trace identifier we have set - “AppsTrace_10046″. If you have set some different identifier, then you can search using that key word.