Re: Finding information about object for LOCK TYPE = UL
- From: Tim Gorman <tim@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 15 Feb 2005 06:54:21 -0700
You've done some excellent analysis, but a call to one of the procedures in
the DBMS_LOCK package does not have to be associated with any objects at
all. It could be present simply because of the need for some sort of
synchronization somewhere in the program logic. So, I think it is possible
that you've gone as far as you can in this direction...
I would suggest moving in the other direction, toward the session itself.
What are the values associated with lines starting with the phrase
"APPNAME". These are calls to DBMS_APPLICATION_INFO used to set the columns
MODULE and ACTION, and those values could be useful. In Oracle E-Biz, most
programs set MODULE to the ConcProgram name, I believe...
Also, searching backwards for the phrase "PARSING IN CURSOR #169" would be
useful, to find the SQL statement associated with this wait event...
Hopefully, you've acquired "Optimizing Oracle Performance" from Millsap and
Holt, published by O'Reilly, for information about the contents of sql trace
files?
Keep up the good work...
on 2/15/05 6:21 AM, New DBA at new_dba_on_the_block@xxxxxxxxx wrote:
> Hi,
>
> After tracing a slow running transaction, I noticed
> that most of the time was spent waiting on enqueue.
> After drilling down further I saw the following line
> multiple times in the trace files.
>
> WAIT #169: nam='enqueue' ela= 3021648 p1=1431044102
> p2=1073754670 p3=0
>
> So from P1 I can make out that the LOCK TYPE is 'UL'
> and requested mode is 6 (Exclusive). So I query
> DBMS_LOCK_ALLOCATED matching p2 with LOCKID.
>
> So far so good. Since I have little information about
> user lock through DBMS_LOCK I'm stuck.
>
> I see name = 'ORA$DR_DML_ 02954: 000' in the
> DBMS_LOCK_ALLOCATED for LOCKID = 1073754670 (p2 from
> the trace file).
>
> Now my question is how to find the object for which
> the lock was requested from this information. Which
> table or view can help me in finding this information.
>
> Oracle Apps 11.5.8 DB 9.2.0.5
>
> Regards
> New DBA
--
//www.freelists.org/webpage/oracle-l
Other related posts:
