Database hang!!!

[nansbrain]

Hi all,
Consider the following scenario.

In a production environment, the database is functioning properly for a couple of weeks/months. One fine morning, you receive complaints that the response is very, very slow and almost still!

What are the probable reasons for the database to go dead, considering that you have a stable network?

Nanda.

[brassd]

If you are in archivelog mode, check the destination for the archive logs is not full. The alert log will tell you if the archiver cannot archive one of the online logs.

Dave

[shawish_sababa]

1. check OS level data (cpu, zombi, io ...). i would start there. gust ps -l or top.
2. check db memory functions like buffer chach hit, io and stuff like that.
3. check for problems in datafiles like fragmentation.
4. if you are useing raid, check there.

and a lot more.

[LND]

investigate wait events first

[pando]

well do you analyze your objects at all durng that *2* months?
if you have I would trace a session and check the sql trace

[Halo]

Try to bounce the db. Also check for network bottlenecks. Optionally, run the utlb/estat.sql

[hinermak]

Halo,

There is no single correct answer for your problem and the vast majority of what could be wrong is far to long to list or summarize.

I would like to speak with you further about your slowness issue. I'm available for on-site consulation. Please contact me at hinermak@datainteraction.com.

Ken

[mhaller]

There is a bulletin on metalink.oracle.com, Diagnosing Database Hanging Issues, that describes the methods and tools used in diagnosing database hanging issues and performance issues.