[Oracle Forms 10] Aplication server hangs

We have an Oracle Forms 10 server, that hangs up from time to time, and we end up rebooting the server to fix it.

I don't have experience with this product, can someone help on where to find the logs, and where to look at  for resolving application freezes and hangs?
LVL 10
Carlos IjalbaSenior SysadminAsked:
Who is Participating?
Mark GeerlingsDatabase AdministratorCommented:
Which operating system do you have for this Application server?  We have Oracle Forms10 application servers running on Linux and we occasionally see some "frmweb" processes in the Linux "top" command that consume 99% of a CPU, but never finish.  And, we do not see a corresponding session for these in our database server when this happens.

I wrote a combination of:
1.  a shell script that I run on our application server to check for these by running "top" and capturing the output of "top" in a text file.
2. a PL\SQL stored procedure that gets called via SQL*Plus which I launch from the shell script, to read the "top" output and check for problem sessions.
3. a custom table in the database to hold details on the "frmweb" processes for a few minutes.
4. a "cron" job to run my shell script every five minutes to check for these.  The shell script then kills the problem processes if they were also consuming 99% of a CPU at the prior run (five minutes ago).

If I was a master shell script writer, it might be possible to do all of this in a shell script, but I'm not an expert on writing shell scripts - I just know some of the basics.  I'm much more proficient at writing PL\SQL.

If you use Linux (or Solaris or any *NIX o/s) I could post the source code for my shell script and PL\SQL procedure, if you are interested in them.
Geert GOracle dbaCommented:
application hang usually indicates slow querries
by restarting the server, the query stops ...

you'd need to look at AWR reports to see what querries are slow

or the manual way:
on the database list the querries in v$sql by one of these columns disk_reads, buffers_gets, executions or application_wait_time
select * from v$sql order by buffer_gets desc;

check the rate per execution: buffer_gets/executions

if you can run a query on the database at the time of the problem, run this:
select s.sid, s.sql_hash_value, sw.event, sw.seq#, s.last_call_et, substr(sql.sql_text, 1, 120)
  from v$sql     	 sql,
       v$session  s,
       v$session_wait sw
 where sql.hash_value(+) = s.sql_hash_value
   and sw.sid = s.sid
   and sw.event <> 'SQL*Net message from client'    -- dit is
   and sw.event <> 'rdbms ipc message'              -- om de onbelangrijke
   and sw.event <> 'pmon timer'                     -- uit
   and sw.event <> 'smon timer'                     -- te
   and sw.event <> 'pipe get'                       -- sluiten
   and sw.event <> 'PL/SQL lock timer'
   and sw.event <> 'jobq slave wait'
   and sw.event not like 'Streams AQ%'
 group by s.sid, s.sql_hash_value, sw.event, sw.seq#, s.last_call_et, substr(sql.sql_text, 1, 120)
 order by sid

Open in new window

this query should give you the actual querries which are busy

get the complete query with using the sql_hash_values from above query
select * from v$sql where sql_hash_value = ###hash_value
Carlos IjalbaSenior SysadminAuthor Commented:
OK, thanks I will look at them next time it start to go slow, Geert.
Carlos IjalbaSenior SysadminAuthor Commented:
Markgeer, thanks will look at the frmweb processes
Carlos IjalbaSenior SysadminAuthor Commented:
Thanks to both, so far I am using your recomendations to monitor the server.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.