[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 11
Carlos IjalbaIT Systems DirectorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
sample:
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
0
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Carlos IjalbaIT Systems DirectorAuthor Commented:
OK, thanks I will look at them next time it start to go slow, Geert.
0
Carlos IjalbaIT Systems DirectorAuthor Commented:
Markgeer, thanks will look at the frmweb processes
0
Carlos IjalbaIT Systems DirectorAuthor Commented:
Thanks to both, so far I am using your recomendations to monitor the server.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.