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

From: Valentin Minzatu <valentinminzatu_at_yahoo.com>
Date: Thu, 24 May 2007 10:37:53 -0400
Message-ID: <f34803$ke7$1@aioe.org>

It looks to me that "enqueue" and "db file sequential read" are responsible for the slow down,as the time spent waiting for "PL/SQL lock timer" and "CPU time" is lower compared to the example where the database is performing fine.

"Ana Ribeiro" <ana.ribeiro_at_rbos.com> wrote in message news:1180015588.942351.211480_at_q75g2000hsh.googlegroups.com... 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 Received on Thu May 24 2007 - 09:37:53 CDT