Re: Re: database using more memory than allocated

to add to Stefan's answer: in 11G the PGA can overshoot up to 200%. You should allow for a minum of 100% additional demand by calculating memory parameters. On critical production systems 200% would be best.

Regards

Lothar

----Ursprüngliche Nachricht----
Von : contact_at_soocs.de
Datum : 01/10/2015 - 10:47 (CEDT)
An : oracle-l_at_freelists.org, backseatdba_at_gmail.com Betreff : Re: database using more memory than allocated

Hi Jeff,

> Why would the database use more memory that has been set to use.

Because memory_target does not control everything - for example structures like PL/SQL collections and PL/SQL tables. Oracle 12c has some official functionality to limit these as well, but you are still on 11g. Oracle 11g only got some undocumented event to do this (10261 - http://www.oracle.com/technetwork/database/features/availability/exadata-consolidation-522500.pdf).

Tanel Poder has written some great blog post about drilling down into PGA memory issues: http://blog.tanelpoder.com/2014/03/26/oracle-memory-troubleshooting-part-4-drilling-down-into-pga-memory-usage-with-vprocess_memory_detail/

You can crosscheck it when you have reached the 250 locations. The problem descriptions sounds like some of these locations are causing this (memory leak?) or software/data bug?

Best Regards
Stefan Koehler

Freelance Oracle performance consultant and researcher Homepage: http://www.soocs.de
Twitter: _at_OracleSK

> Jeff Chirco <backseatdba_at_gmail.com> hat am 1. Oktober 2015 um 03:05 geschrieben:
>
> We are having some major memory issues with one of our databases today. I have the following parameter set.
>
> memory_max_target = 8g
> memory_target=4g
>
> 11.2.0.4 EE database on Windows Server 2008R2
>
> So the database is only supposed to use 4gb but it got up to 84gb before it took down the server. We think we found the application that caused
> but not the database reason. We have 300 external sites that push us data every minute. We didn't realize for half the day that it wasn't working.
> Around noon apparently it started working and I was able to see that the Windows service for that database was allocated 84gb before it killed the
> entire server.
> Once we discovered that this service started working we decided to allow each location to upload 1 at a time, then 5, 10, 25, everything was fine
> until we got to 250 locations. Then all of a sudden the memory jump up really fast to 40gb before I manually stopped the database and we stopped
> all external loads.
> Why would the database use more memory that has been set to use. I have been trying to go through ASH and AWR but it is hard to get a good picture
> because we keep on having to restart the database. Tomorrow morning we are going to work on it again.
> I am really lost on this so if anybody can offer any suggestions I would appreciate it.
>
> I was going to create a SR but apparently Oracle is having an unplanned downtime for their site. Maybe this is a bigger issue that I thought :)
>
 

--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l