Re: Question about "PL/SQL lock timer" event in Statspack

On May 24, 4:06 pm, Ana Ribeiro <ana.ribe..._at_rbos.com> wrote:
> Hello all,
>
> I am using Statspack to investigate a performance problem in my
> database (Oracle 9.2). In a moment that the database doesn't present
> performance problems, these are the TOP 5 TIMED EVENTS:
>
> Top 5 Timed Events
> ~~~~~~~~~~~~~~~~~~ % Total
> Event Waits Time (s) Ela Time
> -------------------------------------------- ------------ -----------
> --------
> PL/SQL lock timer 2,926 61,078 72.64
> CPU time 14,440
> 17.17
> log file sync 50,705 2,859
> 3.40
> db file parallel write 9,918 1,373
> 1.63
> db file sequential read 381,137 1,055 1.25
>
> -------------------------------------------------------------
> Wait Events for DB: FDMP Instance: FDMP Snaps: 37785 -37786
>
> And in a moment where the database is very slow, these are the events:
>
> Top 5 Timed Events
> ~~~~~~~~~~~~~~~~~~ % Total
> Event Waits Time (s)
> Ela Time
> -------------------------------------------- ------------ -----------
> --------
> PL/SQL lock timer 3,794 52,814 68.51
> CPU time 8,968
> 11.63
> enqueue 1,843 4,956
> 6.43
> db file sequential read 1,979,234 4,046 5.25
> log file sync 93,898 3,105
> 4.03
>
> -------------------------------------------------------------
> Wait Events for DB: FDMP Instance: FDMP Snaps: 37790 -37791
>
> Please, can you tell me how to interpret a Statspack report which
> always has a "PL/SQL lock timer" event as the worst wait event (even
> when the database does not present performance problems?). Should I
> ignore this event and base my further investigations in the following
> events (enqueue, db file sequential read...)?
>
> Many thanks for any help!
> Kind regards,
> Ana

This event is usually a result from calling dbms_lock May be someone who wants to improve performance every month by reducing the number of seconds he is calling dbms_lock.sleep?

--
Sybrand Bakker
Senior Oracle DBA