• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Oracle SGA usage

 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
reply
    Bookmark Topic Watch Topic
  • New Topic