Oracle Reports is an enterprise reporting tool. It (.rdf) has played a very important role in Oracle Apps 11i. Back then, it was the main tool to design reports. Now, XML Publisher replaced reports in Oracle Apps R12.
In this article, let’s see how to enable trace for reports if you are still using it.
Enable Trace Reports (rdf)
You design a report using Oracle Report Builder and register as a concurrent program. Run the concurrent program to generate the output in the required format. You can use the method to generate a trace for the concurrent programs.
Navigate to Application Developer -> Concurrent Programs. Query the program. Enable trace by selecting the checkbox
Run the report to generate a trace and generate TKPROF
If you are using Oracle Reports as an independent component to design reports and run using the report server, then follow the below approach to generate the trace.
Write the below code in the Before-report trigger.
srw.do_sql('ALTER SESSION SET sql_trace = true');
srw.do_sql('ALTER SESSION SET tracefile_identifier = report_trace_file_1');
This uses a built-in srw.do_sql package to run alter session command to set sql_trace and tracefile_indentifier.
Write the below code in the after-report trigger to set the trace off.
srw.do_sql('ALTER SESSION SET sql_trace = false');
This creates a trace file at the below location with the filename mentioned in the alter command. Use the below query to find a trace file path.
SELECT value FROM v$parameter WHERE name='user_dump_dest';
Login to Unix or Linux Tier and navigate to the path given by the above query. Run ls *report_trace_file_1* to find out the exact file name. Run tkprof command as shown below.
tkprof report_trace_file_1.trc report_trace_file_1.txt explain=apps/<apps password> sys=no sort=prsela,exeela,fchela
This covert the .trc file is a readable format.
Summary
We covered the method to generate trace and tkpfor for the Oracle Reports(rdf). Go ahead and use it to find the problematic query causing performance issues and tune it.
I hope you liked this article. Please share and subscribe.
