aspose file tools*
The moose likes Oracle/OAS and the fly likes Oracle SGA usage Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Oracle SGA usage" Watch "Oracle SGA usage" New topic
Author

Oracle SGA usage

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

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?
 
jQuery in Action, 2nd edition
 
subject: Oracle SGA usage