Forms builder is the most used tool to design GUI in Oracle Apps 11i. R12 also uses a form builder to design custom and standard forms. Forms internally run SQL Queries, call package/procedure as per the business logic against Oracle Database. Sometimes, you find forms are running slow, taking time to perform the transaction. In this case, you can enable a trace of the form and generate TKPROF.
This TKPROF should help you to identify the query that is the bottleneck and taking time to execute. This helps you to take corrective action by fine-tuning.
1. Enable Trace for forms (trace with binds and waits)
Log in to Oracle Apps and open the concern form. Click on Help–>Diagnostics–>Trace. The open below tracing options,
- No Trace
- Regular Trace
- Trace with Binds
- Trace with Waits
- Trace with Binds and Waits.
Select the Trace with Binds and Waits option as it provides the maximum information that could be helpful to identify the problem.
This opens a pop-up window as shown below which displays the path where the trace file is stored. Write it down.
2. Do a transaction
Now perform required transactions on the forms that are taking time like querying forms, inserting records, or updating.
Oracle apps log all these transactions in the trace file. Now, here trace is enabled till the time it is not explicitly disabled. Whatever action you do, it will get recorded in the Trace file.
Do not open any other form when the trace is on.
3. Disable form Trace
Click on Help–>Diagnostics–>Trace and select the No Trace option. Oracle Apps confirms back that the trace is disabled and shows the trace file name and location.
4. Generate Tkprof
Now login to the Unix or Linux tier and navigate to that path. Run tkprof command to generate a readable trace file as shown below.
tkprof abc.trc abc.tx sys=no explain=apps/<apps password> sort=prsela,exeela,fchela
Summary
Tracing forms is a good utility that can help to identify performance issues in form and fine-tune it.
I hope you liked this article. Please share and subscribe.