Re: Enqueue Diagnosis

From: Brian Peasland <dba_at_remove_spam.peasland.com>
Date: Wed, 20 Aug 2003 13:17:36 GMT
Message-ID: <3F4374F0.290CDEB1@remove_spam.peasland.com>

In your trace file, you should see something like the following when you have committed:

PARSING IN CURSOR #1 len=27 dep=0 uid=29 oct=2 lid=29 tim=11214896289140 hv=4778
51071 ad='2912b71c'
insert into test values (1)
END OF STMT
PARSE
#1:c=10000,e=298250,p=0,cr=4,cu=0,mis=1,r=0,dep=0,og=4,tim=11214896289124 EXEC
#1:c=0,e=13298,p=0,cr=1,cu=7,mis=0,r=1,dep=0,og=4,tim=11214896302649

PARSING IN CURSOR #1 len=6 dep=0 uid=29 oct=44 lid=29 tim=11214897727258 hv=3867
936055 ad='29d0f168'
commit
END OF STMT
PARSE
#1:c=0,e=1058,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=11214897727234 XCTEND rlbk=0, rd_only=0
EXEC #1:c=0,e=571,p=0,cr=0,cu=1,mis=0,r=0,dep=0,og=4,tim=11214897728082

From the above, you can clearly see that I've got a COMMIT. In that section where I issued a COMMIT, you can also see the line that states:

XCTEND rlbk=0, rd_only=0

This is the transaction end. Notice that rlbk=0 which means that a rollback was not done. In other words, a commit was done. Contrast that to the following:

PARSING IN CURSOR #1 len=27 dep=0 uid=29 oct=2 lid=29 tim=11214902043975 hv=9990
69658 ad='291298fc'
insert into test values (2)
END OF STMT
PARSE
#1:c=0,e=1637,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=11214902043954 EXEC #1:c=0,e=593,p=0,cr=1,cu=2,mis=0,r=1,dep=0,og=4,tim=11214902044829

PARSING IN CURSOR #1 len=8 dep=0 uid=29 oct=45 lid=29 tim=11214903652489 hv=3505
8394 ad='2920ceb4'
rollback
END OF STMT
PARSE #1:c=0,e=638,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=11214903652472 XCTEND rlbk=1, rd_only=0

Here, you can see that I did a rollback and on the line with XCTEND, I have rlbk=1, which means that I did, in fact, do a rollback.

If there is only one COMMIT in your trace file, then only one commit has been performed.

HTH,
Brian

Matt wrote:
>
> Brian,
>
> Thanks for the advice. I just have one more thing to clarify.....
>
> I have a trace file for a session that incurred a 4 minute enqueue
> wait (TX 6)on a very small table (50 rows 2 columns). The hit rate on
> this table is quite low.
>
> When analyzing the trace file with TRCANLZR, I picked up on the fact
> that over a period of 10 minutes the session only commited once
> despite the fact that there were over 30 DML statements in the trace
> file.
>
> This may be the cause of my enqueue conflict, but before I point the
> finger at the application (Peoplesoft HR) I want to make sure that I
> have my facts right.
>
> Are all commits shown in the trace file (I.E. explicit and implicit
> commits). It seems quite unusual that this session is committing so
> infrequently.
>
> The Peoplesoft makes use of a TP monitor as a middle tier which should
> act as the point of transaction control. Are there different
> monitoring requirements for tracking commits/transactions in these
> kinds of environments.
>
> Thanks again
>
> Matt

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"

Received on Wed Aug 20 2003 - 08:17:36 CDT