Enormous wait times!

[Dorian]

Hi everyone,

When I ran the following query on a database that has been up for one month I was shocked:

select sum(time_waited)/360000 "hours waited since startup"
from v$system_event
where event not like 'SQL*Net%' and event not like '%timer' and event not like '%ipc%';

The query returned a total wait time around 70 hours! Almost 65 hours come from the 'db file sequential read' event.

Do these figures look normal or does this database have a serious I/O problem? (Or am I missing some essential point?)

Thanks!
Dorian

[slimdave]

Not possible to tell really without knowing how much work was actually done in this time period. Is there an apparant performance problem in a particular application operation, or at particular times of the day/week/month?

[abhaysk]

WW04_PRO_DBA>  select INSTANCE_NAME, STARTUP_TIME, Sysdate - STARTUP_TIME "Num Of Days Since It Started"
  2  from v$instance;

INSTANCE_NAME    STARTUP_T Num Of Days Since It Started
---------------- --------- ----------------------------
ww04             14-FEB-04         23.95717592592592593

1 row selected.

Elapsed: 00:00:00.07
WW04_PRO_DBA> ed
Wrote file afiedt.buf

  1  select
  2    event,
  3    round((time_waited)/360000/24) "days waited since startup"
  4  from
  5    v$system_event
  6  where
  7    event like 'db%' and
  8*   round((time_waited)/360000/24) > 0
WW04_PRO_DBA> /

EVENT                                    days waited since startup
---------------------------------------- -------------------------
db file sequential read                                         24
db file scattered read                                          10

2 rows selected.

Elapsed: 00:00:00.07
WW04_PRO_DBA>

We have hell lotta Batch Jobs .. and hell lotta Reporting Web Pages that acces data .. and u can see the effect .. 24 days ( nearly 576 hours ) of wait time in only 23.7 days !!

But still we dont have performance issues ..

Abhay.

[paladin]

You said:
> The query returned a total wait time around 70 hours! Almost 65 hours come from the 'db file sequential read' event
> on a database that has been up for one month

Okay. You can't really determine if this is this good or is this bad without more information.

In the case of db file sequential read event waits, this refers to waiting for disk I/O when doing indexed accessing.

There are several things to look for, though.
(1) Run IOSTAT to see if you have unbalanced drive access - see if most of your activity is going through one I/O controller to a limited number of disks? If so, which datafiles are on those disks? Which tables are in those datafiles?
(2) Run Statspack (15 minute durations) for peak periods and look for SQL doing high physical read activity (or perhaps you have a lot of write activity).
(3) Is the DBBC set up for good pool usage, and is it big enough? Not having the DBBC configured well will drive more accesses from disk rather than from RAM.

Etcetera.

[Dorian]

ok, thanks.
this database has no apparent performance problems, that was why I was so surprised.
In fact we could say that even if some Oracle process is waiting for something it does not mean that other processes can't be going on. I overlooked that.

thanks,
Dorian