Oracle Database Discussions

When I run this query, I consistently get at least 5 hits:

select event, (max(time_waited))/100 time
from v$active_session_history
where wait_time = 0
group by event
having (max(time_waited))/100 >3600
order by 2 desc;

example output:
db file sequential read 45417.97
db file parallel write 31327.45
db file parallel read 27123.28
db file scattered read 24249.53
log file sequential read 6120.68
log file parallel write 6023.67
local write wait 3669.11

time_waited is documented as being in centiseconds, so the results above are in seconds.

From the oracle reference on v$active_session_history says:
"If SESSION_STATE = WAITING, then the time that the session actually
spent waiting for that EVENT. This column is set for waits that were in
progress at the time the sample was taken.
If a wait event lasted for more than a second and was caught waiting in
more than one session sample row, then the actual time spent waiting for
*+that wait event+* will be populated in the last of those session sample rows.
At any given time, this information will not be available for the latest
session sample."

This doesn't seem right. The end users would certainly howl at me if a single sequential read ever took more than an hour. Does anybody know whether the phrase "*that wait event*" can be correctly interpreted as the time for a single wait on a single block all at one time? Sessions in JDE Enterprise One are shared by an application front end among many tasks. Is this the total this session waited for that particular block through many different tasks by many users? What is the granularity of this number?

A small subset of the queries in our system need to always run in 30 seconds. I can filter them based on module, client-id, etc. so I can identify the sessions and sql_id's of interest. When I put a monitor in place, I sometimes find sessions which appear to have waited 10 minutes or more on a single sequential read.

Does anybody have a better explanation of the contents of active session history?

Edited by: ca107680 on Jul 27, 2009 1:54 PM