Re: Database freezes for 25 min

Hi,

two things can be done:

• Statspack reports should be collected on a ongoing basis. If the sytem has a hang look afterwards for the top wait events in the statspack reports
• Do a systemstate dump

Regards
Stefan

Vsevolod Afanassiev wrote:

> 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 - 01:30:31 CST