To produce a consolidated formatted trace file from multiple trace files generated by all clients for a single service, the combination of trcsess and TKPROF utilities is used. The trcsess utility consolidates trace files based on specified criteria such as session, client identifier, or service name. This results in a single trace file that combines the desired tracing information. Next, TKPROF is used to format the output of the trace file generated by trcsess, providing a readable summary of the trace, including execution counts, execution times, and SQL statement text along with execution plans.

Steps:

Use trcsess to combine trace files:

Command: trcsess output=consolidated.trc service=your_service_name *.trc

Use TKPROF to format the consolidated trace file:

Command: tkprof consolidated.trc output.txt explain=user/password sys=no sort=prsela,fchela

References:

Oracle Database Performance Tuning Guide, 19c

Oracle Database Utilities, 19c