When running queries in oracle with oracle parallel query, oracle spins up multiple parallel query processes to process the query. Each parallel query process gets its own database session. So when we turn trace on the session oracle creates multiple trace files in the udump directory. Here are the steps that i went through to gather a tkprof output of all those files for a query (Or for anything you run in the same session).
Login to sqlplus from where you are going to run your parallel query.
Setup a client id for the session
exec dbms_session.set_identifier(‘px_test’);alter session set events=’10046 trace name context forever,level 1′;
Run your sql query (That uses parallel query)
Quit from sqlplus
Find all your trace files and move it to a different directory
Identify your user_dump_directory
sqlplus / as sysdbaSQL> sho parameter user_dump_directory
Locate all your trace files (There will be one for the main session and then 1 each for all the parallel query processes used)
cd /u01/udumpmkdir tmp2find . -name ‘*.trc’ -mmin 5
The find command above finds and lists all the trace files that have been updated in the last 5 minutes (Change mmin to your time period within which you want to list trace files for).
move the files created by your session to the subdirectory named tmp2
Now cd tmp2
Remove all the files in tmp2 which are not the sessions trace or one of the parallel query slaves. This simplifies the trcsess command you need to run. Or else you can list all of your trace files by name in your trcsess command.
trcsess output=prog9.trc clientid=px_test *.trctkprof prog9.trc prog9.out sort=exeela sys=no
