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: -
Login with “Application Developer” responsibility -
Open the “Create Profile” form –> Query the profile “FND_INIT_SQL” -
Make sure that “visible” and “updateable” are checked in user level.  -
Switch responsibility to “System Administrator” -
Navigate to Profile –> System –> Query the profile “Initialization SQL Statement - Custom” in user level for the user we would like to enable trace for.  - 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.
 - Now, after the user logout from the application (the user you enabled trace for), the user can login and reproduce the issue.
- 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..)
- 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; | Hope you find it helpful…
Feel free to leave a comment or share your thought about this issue. Aviad |
