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

On May 24, 4:06 pm, Ana Ribeiro <ana.ribe... (AT) rbos (DOT) com> wrote:
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

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 (DOT) 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