Oracle Apps Tuning – How to Run Statement Level/Java trace or a SQL Trace in Self Service Application


Statement Level/ Java Trace:
1 – Remove any messages from the FND_LOG_MESSAGES table for the user running the process to be traced.
To get the user id of your self service user:

select user_id
from fnd_user
where user_name = ‘????’
Delete all rows in the tables for this user:
delete from FND_LOG_MESSAGES
where user_id =� xxx���� <insert the value received in the first query>

2- To set fnd logging you need to set the following FND system profiles:
Log onto the Applications forms with System Administrator responsibility
Navigate to Profile > System
At User Level for the employee you are tracing set these values:
In the Profile field, query and then set the value as noted

FND: Debug Log Enabled – Yes           OAF 11.5.10 CU1 it is called FND: Log Enabled
FND: Debug Log Level – Statement
FND: Diagnostics = Y – Enables the Diagnostics Button on the SS Framework
FND: Debug Log Filename = This is optional. Use if want to direct to specific directory/name

Reference a directory where you have write privledges Directory/filename�
Example: /usr/tmp/MyDump.txt�
At the Site level, set these values
In the Profile field, query and then set the value as noted
FND: Debug Log Mode – Asynchronous with Cross-Tier Sequencing (this may or may not be there)
FND: Debug Log Module – xxx.plsql.%    xxx is the application ex. per.plsql.% (NOTE HXC, OTL uses PER)�
When you log into self service, a message will be displayed that logging is enabled.
Perform the action which caused the problem.�
To extract the log messages from the database you again need the user id of your self service user and then run this query:

select *
from fnd_log_messages
where user_id = ????
order by log_sequence

*** Do not forget to turn off the logging when done.
*** Reset all the profile values

 

————————

SQL trace:
Responsibility = SysAdmin
Navigation: Security > Profile
User: Enter User name
Query the Profile: FND: Diagnostics
Set the FND:Diagnostics profile to Yes at User level.
Login into Self Service under the same user used to set the profile value.
Click the diagnostic icon at the top of the page.
It shows two options:
Show Log
Set Trace Level
Select ‘Set Trace Level’
Click Go.It shows a page with a set of options.�Set Trace Level – This is like a forms trace – with several options:
Disable Trace – used to end the trace
Trace (Regular) – just like a forms trace
Trace with Binds – record the bind variables in the trace
Trace with waits – Good for performance issues.
Trace with binds and waits – combines both of the above
Choose�� Trace option�
Click Save.Execute the process in Self Service. This will put the sql trace in the usual log directory.
Turn off Trace.
Select the Diagnostic icon.
Click on option: Set Trace Level
Click Go
Select : Disable Trace
To determine where the raw trace file is located.
From SQLPlus:
SELECT value
FROM v$parameter
WHERE name = ‘user_dump_dest’;

run TKPROF procedure on the raw trace file
For example:
hrab51_ora_18190.trc is the name of the raw trace file
trace1.txt is the name of the TKPROF file
tkprof hrab51_ora_18190.trc ~/trace1.tkt explain=apps/apps sort=exeela,prsela,fchela’

Advertisements

2 thoughts on “Oracle Apps Tuning – How to Run Statement Level/Java trace or a SQL Trace in Self Service Application

Want to give some comment to author ( Shivmohan Purohit )

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s