Database freezes for 25 min

ENVIRONMENT: Oracle 8.1.6.2.0, Solaris 2.7, Sun E6500, 14 CPUs, 8 GB RAM APPLICATION: the application reads incoming files (10-30 files per min,
typical file size 5-10K) from INPUT directory, registers each file in 2 database tables (1 record per file in each table), converts them in different format (in C code, Oracle not involved), and puts them in OUTPUT directory. There are several (4-12) workers called "bf55" running in parallel doing this processing. In addition, one "matcher"
processes small number of records that require matching (combining several records from different files into one record). The matcher reads/updates the same two tables as "bf55" and it has exclusibe use of 3-rd table that it inserts/updates. Once a day (at night) cron job purges records from these 3 tables. I described the application in details to show that it is very simple and the volume of changes is small. It does not use any fancy stuff: no partitioning, no Java, no bitmap indexes, no clusters. It could probably run on Oracle 7.0.

PROBLEM: From time to time the database almost completely freezes: while I can connect and run *some* queries (small config.tables, V$SESSION, V$PROCESS, V$SESSION_EVENT), other queries hung, as well as things like "alter system checkpoint",
"alter system switch logfile", "shutdown immediate". The only solution
seemed to be to "shutdown abort". Then I found that the database
"unfreezes" after 20 - 25 min, so it could be that it experienced
these
freezes for a while, but no-one noticed.

There is nothing wrong on UNIX level - this box runs 5 other instances (different applications), and they all OK. The load is moderate (5), and 'sar' shows that it is not overloaded.

The database itself is quite small (3 GB), has small block size (2K), and huge buffer cache (350,000 blocks - 700,00 MB). Log files were 50M (reduced to 10M), log buffer 1.8 M. Log switches happen 20 - 300 times per day,
depending on the volume of the input. Looks like these freezes tend to happen during periods of heavy load.

I called Oracle support, but their main advice was to run certain queries during the freeze period - most of them simply hung.

Any idea what causes these freezes?
Thanks,
Sev Received on Sat Mar 23 2002 - 00:12:10 CST