Re: File open wait

> You should compare values of V$SESSTAT taken at different times - you will
> see values for '% gets' and 'session logical reads' increase. This implies
> that you are burning CPU accessing blocks that are already cached. (And
> for sure, your BCHR will be close to 100%!! ;-)

Yep, v$sesstat is a good way to check whether session is actually succeeding to do something.

Just one point is that if you'r sesstat values aren't increasing, this doesn't automatically mean that your session isn't doing anything. If the session gets into some endless loop and doesn't ever hit a location in code which updates session statistics, you won't see any changes in v$sesstat. Although you're still using CPU.

Also a small addition to my previous reply to this thread - if v$session_wait says the session WAITED, then it means that now your session *wants* to be on CPU, it doesn't necessarily have to be burning CPU. It might be in CPU runqueue for long time before gets scheduled at all (if your system is CPU-starved). This is one case where sql trace/oracle event interface might show large inconsistencies between actual response time and CPU + wait time.

Tanel.

--
http://www.freelists.org/webpage/oracle-l