Re: database using more memory than allocated

From: Jeff Chirco <backseatdba_at_gmail.com>
Date: Tue, 6 Oct 2015 16:45:55 -0700
Message-ID: <CAKsxbLq5gm9UqSGPCN-JhSR1pP5sVHYPSjE=YyjLg_s5j_F+Dw_at_mail.gmail.com>

The next day we were able to figure out that it was a code bug. One of the data sets coming up unfortunately caused an infinite loop in the processes code. :( It was a clob data looping through each row in the file and adding it to an pl/sql object that grew very quickly. Fixed now and safety checks are being put in place to prevent in the future. Thanks for all your help.

Jeff

On Mon, Oct 5, 2015 at 8:01 AM, Dimensional DBA <dimensional.dba_at_comcast.net
> wrote:

> As mentioned earlier with PL/SQL, I had a program recently on a project
> that used clobs in PL/SQL and a single instance of the program would grow
> to 8GB of memory usage as the clob was passed between a simple error
> handling routine.
>
>
>
> Also are you using shared or dedicated sessions for the connection of your
> application. Shared sessions can have the tenedency to buildup and not
> release memory effectively from their last use. (Varies by version and OS).
>
>
>
>
>
> *Matthew Parker*
>
> *Chief Technologist*
>
> *425-891-7934 <425-891-7934> (cell)*
>
> *Dimensional.dba_at_comcast.net <Dimensional.dba_at_comcast.net>*
>
> *View Matthew Parker's profile on LinkedIn*
> <http://www.linkedin.com/pub/matthew-parker/6/51b/944/>
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Tom Dale
> *Sent:* Monday, October 05, 2015 6:25 AM
> *To:* backseatdba_at_gmail.com
> *Cc:* Stefan Koehler; oracle-l_at_freelists.org
> *Subject:* Re: database using more memory than allocated
>
>
>
> Hi Jeff,
>
>
>
> I think you will more likely find its a bug, if you compare oracle
> counters eg v$pgastat and OS view eg top and pmap -x, they may not match
> up
>
>
>
> I had something similar before, A REGEXP_REPLACE and REGEXP_SUBSTR that
> would leak memory until the host hung
>
>
>
> https://community.oracle.com/thread/2465164?start=0&tstart=0
>
>
>
> Tom
>
>
>
>
>
>
>
>
>
>
>
> On Thu, Oct 1, 2015 at 5:29 PM, Jeff Chirco <backseatdba_at_gmail.com> wrote:
>
> Thank you. The interesting thing is that this has been working with 300
> locations just fine for a long time and we haven't made any changes
> recently. Ok actually two weeks ago I changed memory_target from 2gb to
> 4gb.
>
>
>
> On Thu, Oct 1, 2015 at 1:47 AM, Stefan Koehler <contact_at_soocs.de> wrote:
>
> 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

Received on Wed Oct 07 2015 - 01:45:55 CEST