posted 11 years ago
We have a 24/7 application at one of our clients which uses the client's database. There are several other apps running on the same database. In the last 24 hours, the DB crashed three times due to SGA running out (actually, we got ORA-04031; I don't know whether that is due to fragmentation or too much allocations). The DB keeps crashing several hours after restart. It is a two-node RAC and has a physical standby set up. I don't know anything about that DB configuration. v$version gives:
It is also noteworthy that although the failure firstly occurred this Friday before noon, there was a holiday in our country and the DB load was smaller than usual for normal working day.
The cause was not yet pinned down. We've been asked to review our application for possible causes. As far as I know, the most common reason for this error is not using bind variables (we do use binds; in some queries binds are not used to help optimize them, but these are run only a handful times each hour) and some very large packages (we do have a few packages, but nowhere near large; the largest has a few tens of KB).
We have one DB link to another Oracle instance. Our application runs periodically a few queries to scan for changes (including queries using the DB link), but these touch only around five tables. We have some 300 triggers (three - insert, update, delete - on each of about 100 tables), but these triggers call stored procedures in a package and only a handful of these tables are actually active. Furthermore, these things have been unchanged for months.
Are there other things worth reviewing?