Re: Oracle Server Hanging

Hi Sharon,

Boy, there's not a lot to go on.

The simple answer is that is could just about be anything but some things you could look at investigating include:

• Check out the alert log, anything untoward
• Check out the wait related events (eg. v$session_wait, v$system_event, v$sysstat, etc.) and suz out the major events that's keeping Oracle waiting
• Check out and ensure that the SGA is not suffering from paging/swapping related issues
• Check out the execution plans of your most expensive statements (depending on DB version and available tools there are various ways to determine this, v$sqlarea is a good place to start) and suz out if anything untoward springs out
• All the already mentioned can be determined by running a STATSPACK report over an appropriate period (check the doco for how to install, get statistical snapshots and generate reports with statspack)
• Ensure all tables/indexes etc are analyzed or dbms_stats(ed)
• "Intermittingly hanging" is often an indicator of problems with the 'protection' part of the DB. Check to make sure that the log buffer is not stressed out, that the redo logs have plenty of time to complete checkpoints, that the slow archiving of redo logs is not preventing log switches from occurring (again v$system_event should highlight these, look for events that contain 'log file switch' )

I'm sure others will add to this list but the above should get you started and will determine what your issues are.

Good Luck

Richard

"Sharon" <sharon_mccaughan_at_dell.com> wrote in message news:37ce7eff.0211120316.55afde5e_at_posting.google.com...
> I have a server which has Oracle 8i running on it. Recently the schema
> was upgraded. The applications were tested prior to going back into
> production. There were no problems the next day but from the following
> working day the server has been intermitingly hanging. Can anybody
> give me some pointers where to start troubleshooting. I come from a
> SQL background and am not familiar with Oracle. The page file was
> moved from the C: to the D: since then but this has enhanced
> performance as the page file was running out of space on the C:.
> many thanks.
Received on Tue Nov 12 2002 - 08:51:34 CST