How to enable trace for a USER session

 

User level

‘Initialization SQL Statement – Custom’ ( FND_INIT_SQL )

==============================================

BASICS

======

Oracle Applications 11i provides a profile option that allows to execute ‘custom’ code at the beginning of every database session. These sessions can be linked to an online Form, a Concurrent Program, or any other piece of code that requires accessing the database.

The most common use of this profile option is to generate detailed raw SQL Trace files including the values of the bind variables used by SQL statements. This profile is also used to report on raw SQL Trace, all database waits, used to determine gaps between elapsed and CPU times.

COMMAND

=========

1) Using System Administrator Responsibility, navigate to System Profile Values Form (Profile ➨ System from Navigator). Query profile option ‘Initialization SQL Statement – Custom’ for user <username> and update the value with string below:

BEGIN FND_CTL.FND_SESS_CTL ('','','TRUE','TRUE','LOG','ALTER SESSION SET EVENTS=''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'''); END;

BEGIN FND_CTL.FND_SESS_CTL('','', '', 'TRUE','','ALTER SESSION SET TRACEFILE_IDENTIFIER='||''''||'AVIADE' ||''''||' EVENTS ='||''''||' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 '||''''); END;

** Just replace AVIADE with the user you enable trace for. 

2) Ask user to login. As soon as  user login, all database sessions opened with this user  will generate one raw SQL Trace with detailed information produced by Event 10046.

IMPORTANT POINTS

=================

> All quotes used on FND_CTL.FND_SESS_CTL are single quotes. Where it looks like double quotes it is actually two single quotes. After LEVEL 12 it has 3 single quotes. Exact syntax is very important.

> Event 10046 level 1 is regular trace, level 4 is tracing with bind variables, level 8 is with database waits and level 12 with both, bind variables and database waits.

=======================================================

How to enable trace for a USER session

I was being asked to examine a performance issue within one of our CRM application screens, after some users complained about a specific long time action.

First thing, I tried to enable trace for the CRM session, but It turned out that it’s definitely not simple to identify a CRM session. Especially in my case, when a session opens two (sometimes more) database sessions. It’s quite impossible actually.

So how it is possible to trace those CRM sessions anyway?

Oracle has provided an option to execute custom code for every session opened in the database through a system profile. This profile called “Initialization SQL Statement - Custom” (the short name is 'FND_INIT_SQL') and allows customize sql/pl*sql code.

Once setting this profile in user level, each session opened for this user will first execute the code within the profile. No matter which type of activity the user does – Forms, CRM, Concurrent request, or anything else that opens a database session – the content of this profile will be executed.

So, clearly we can use this capability to enable Trace for users sessions.

Steps to enable trace for specific user:

1. Login with “Application Developer” responsibility
2. Open the “Create Profile” form –> Query the profile “FND_INIT_SQL”
3. Make sure that “visible” and “updateable” are checked in user level. 
   
    
4. Switch responsibility to “System Administrator”
5. Navigate to Profile –> System –> Query the profile “Initialization SQL Statement - Custom” in user level for the user we would like to enable trace for. 
   
    
6. Update the profile option value in user level to the following: 
   
   

   BEGIN FND_CTL.FND_SESS_CTL('','', '', 'TRUE','','ALTER SESSION SET TRACEFILE_IDENTIFIER='||''''||'AVIADE' ||''''||' EVENTS ='||''''||' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 '||''''); END;

       
   ** Just replace AVIADE with the user you enable trace for. 
   
     
7. Now, after the user logout from the application (the user you enabled trace for), the user can login and reproduce the issue. 
    
8. When finish to reproduce the issue, you should disable the trace by clearing the profile option value and update it to NULL. (profile “Initialization SQL Statement – Custom” of course..) 
   
9. The trace file/s will wait for you in your udump (user_dump_dest init’ parameter) directory. 
   
   

Since I enabled and disabled the trace quite a few times while investigating my performance issue, I wrote these handy simple programs which enable and disable the trace for a user in a quick and easy manner.

Execute this program to enable trace for a specific user: (substitute step 6 above)

DECLARE    l_ret     boolean;    l_user_id number;  BEGIN    select user_id      into l_user_id      from fnd_user     where user_name = '&&USER_NAME';   l_ret := fnd_profile.SAVE(X_NAME        => 'FND_INIT_SQL',                              X_VALUE       => 'BEGIN FND_CTL.FND_SESS_CTL('''','''','''', ''TRUE'','''',''ALTER SESSION SET TRACEFILE_IDENTIFIER=''||''''''''||''&&USER_NAME'' ||''''''''||'' EVENTS =''||''''''''||'' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 ''||''''''''); END;',                              X_LEVEL_NAME  => 'USER',                              X_LEVEL_VALUE => l_user_id);    commit;   dbms_output.put_line('Profile has updated successfully'); EXCEPTION    when others then      dbms_output.put_line('Failed to update the profile: '||sqlerrm);  END;

 

Execute this program to disable trace for a specific user: (substitute step 8 above)

DECLARE 
  l_ret     boolean; 
  l_user_id number; 
BEGIN

  select user_id 
    into l_user_id 
    from fnd_user 
   where user_name = '&USER_NAME';

  l_ret := fnd_profile.DELETE(X_NAME        => 'FND_INIT_SQL', 
                              X_LEVEL_NAME  => 'USER', 
                              X_LEVEL_VALUE => l_user_id); 
  commit;

  dbms_output.put_line('Profile has erased successfully');

EXCEPTION 
  when others then 
    dbms_output.put_line('Failed to erase the profile: '||sqlerrm); 
END;