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

Posted on November 12, 2008. Filed under: DBA/Admin Tips, Diagnostics, R12, TroubleShooting | Tags: , , |

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

Advertisements

Make a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

2 Responses to “How to enable trace with BIND variables and WAITs for a concurrent program?”

RSS Feed for OraclePitStop – Lets share some knowledge Comments RSS Feed

A very useful tip again. – Thanks.

enabled trace for custom concurrent request. trace is not getting generated for custom concurrent request.

Sreedhar


Where's The Comment Form?

Liked it here?
Why not try sites on the blogroll...

%d bloggers like this: