File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/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 Android Security Essentials Live Lessons this week in the Android 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: 3606
    
  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?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Oracle SGA usage
 
Similar Threads
Weblogic CMP Beans
Need an event in a client program
Java discourages intelligent use of database technology: Discuss.
TeamCity and Databases
What are the probable root causes for the oracle row level locks