Re: Finding information about object for LOCK TYPE = UL

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_at_yahoo.com 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

--
http://www.freelists.org/webpage/oracle-l