User level‘Initialization SQL Statement – Custom’ ( FND_INIT_SQL )
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”
- Switch responsibility to “System Administrator”
- 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.
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)
l_ret := fnd_profile.SAVE(X_NAME => 'FND_INIT_SQL',
dbms_output.put_line('Profile has updated successfully');
Execute this program to disable trace for a specific user: (substitute step 8 above)
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);
dbms_output.put_line('Profile has erased successfully');
when others then
dbms_output.put_line('Failed to erase the profile: '||sqlerrm);