How to enable trace with BIND variables and WAITs for a concurrent program?

In this link, we had mentioned about tracing a concurrent program, but the drawback of this method is it doesnt yield information about the BIND variable values. So in this post we will talk about it.

Scenario

X submitted a concurrent request and its running for a long time and never completes. X approached you to see what is the problem with the concurrent request.

Steps

1. Inform X to logout from his ERP session.
2. Login as sysadmin user
3. System Administrator -> Profile (System)
4. Values: User = X, Profile = Initialization SQL Statement – Custom (you can also mention Initialization%Custom)
5. Under user field enter the following

begin fnd_ctl.fnd_sess_ctl(”,”,’TRUE’,'TRUE’,'LOG’,'ALTER SESSION SET EVENTS=”10046 TRACE NAME CONTEXT FOREVER, LEVEL 12” TRACEFILE_IDENTIFIER=”[ANY_IDENTIFIER]“‘);end;

[ANY_IDENTIFIER] = Any value to identify the trace file.

LEVEL 12 – Both Binds and Waits
LEVEL 8 – Only WAITS
LEVEL 4 – Only BIND Variables

All above levels will include the sql statements being executed and not only the binds and waits.

6. If tracing had been enabled at the concurrent program level as mentioned in this link, please disable it by removing the check box. [IMPORTANT]. If you have trace enabled for the concurrent program, then it will take precedence for the profile option.

7. Kill / cancel the long running concurrent request (if its still running)
8. Request X to login to ERP and instruct X to submit the concurrent request again and logout. Nothing else. This will ensure we capture the trace information specific to the concurrent request and not for other actions.

At this stage, you will find a trace file generated at $ORACLE_HOME/admin/SID_Hostname/udump with [ANY_IDENTIFIER] as part of it.

Inside the trace file you will be able to see information about the bind variable values being passed to and used by the concurrent program.

Eg:

Bind#0
oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=00 csi=00 siz=8 off=0
kxsbbbfp=9fffffffbf2e88d8 bln=07 avl=07 flg=09
value=”[BIND VARIABLE VALUE]“

Bind#1
oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=00 csi=00 siz=8 off=0
kxsbbbfp=9fffffffbf2e88f8 bln=07 avl=07 flg=09
value=”[BIND VARIABLE VALUE]“

Note: Make sure you remove the profile option value for X after the job is done, else every action of X will generate a trace file in udump directory.

Happy Trouble shooting.

cheers,
OraclePitStop