Can a Oracle 10G process on HP-UX 11i Grow to have an RSS (memory) of more than SGA size (11GB)?

[Alzhy]

Or is this a BUG with 10G?

On 2 occasions now, a 64GB partition on a SUperDOme running Oracle 10G (on 11i v1.0) ran out of memory because there was a badly formed SQL query that accoriding to my DBA was the culprit in an Oracle Process growing to 11GB..

Is this indeed possible?

[Murat SULUHAN]

Hi Nelson

Did you checked SGA_MAX_SIZE

From oracle ref:
Oracle can start instances underconfigured and allow the instance to use more memory by growing the SGA components, up to a maximum of SGA_MAX_SIZE.

For instance you can configure SGA_MAX_SIZE is 20 GB but your SGA initial size can be 10 GB. Dynamic SGA structure allows you to reconfigure your SGA components up to 20 GB

But have to check your SQL statements as your DBA sayed
Best Regards
Murat

[TwoProc]

Murat,

The problem is that the area that is growing is probably in the PGA space- which isn't shared memory. This would be in maxdsize_64bit (just plain old data area).

Nelson,
I'd say it's entirely possible if your maxdsiz_64bit is large enough. What is it set to?

My guess is the bad code was doing huge memory allocations, not just simple "code runs." If I had to guess a scenario , I'd guess at dynamic arrays initialized out the wazoo, and done reiteratively or recursively - and probably event based on data which lead the code off on a never ending trail of work. In fact, I've seen that very thing, but not off to the large landholdings that you're talking about.

Programmers love arrays, and when they find out that they can take their array making "skills" (never used malloc a day in their lives, yet have been doing programming for 20 years!) into Oracle space, well there's practically a tragic love affair worthy of a movie of the week script right there on the spot.

John

[Alzhy]

maxdsiz_64bit 0x40000000 - 0X40000000

Our maxdize_64bit is "only" 1 GB....

SGA is just 8GB.

Is it possible to declare very very large arrays on a SQL (pl/sql?) program?

Remember -- this is Resident Memory (RSS) that we saw... an oracle foreground process -- oraclemaindb process...

[TwoProc]

I don't see it. If your limit is 1G, and it wasn't shared memory.

Did you see it go to 11G, or is this what the DBA is telling you?

Can you make very large arraays in plsql? Yes, I've had developers here do it, and I've had to correct them to manage the issue.

Of course, what does "very large" mean? I don't know of a limit of how large you can make an array, but there might (and probably should) be. But, you can't think in terms of just dimensioned size, but of course, you must consider the size of each row, or object row (whichever the case).

[Alzhy]

TwoProc.. I saw it...

oracledbmain process was 11GB in size and growing when I asked the user who had a SQL Navigator session top cease and desist.

[TwoProc]

Amazing,

I've just looked over my server, and I can't find a thing remotely that large. The biggest I've got anywhere is 163 MB (which is huge). I don't know how it got that large with kernel memory restricting that. Unless it grew in the text segment - but I just can't see how.

I'm looking at one of the 163 MB ones right now (from Glance), and as I suspected, 131 MB of that is from the data area. There is a lot more from the shared RSS and the shared mem RSS , but that's not counting against this process.

The next time it happens see if you can open it up in glance from the "Processes" list, and then Look at the "Process Memory Regions" and see where that memory is being allocated from. See if you can get a quick look at it and maybe a screen shot before you have to kill it.

Unless, it was showing up in "Other RSS" or "Private RSS" - but I don't what segment that would be coming from. When I look down the list of a process, I do see being consumed, and it pulls 32K from both RSS and VSS equally. So, is uarea a container for program overhead that grow crazy, like recursion? From what I know, those things should be in the stack segment, and I don't think you've got your stack size so large that it would hold 11G of entrance and exit states.

Sorry, but looking more into example Oracle connections here, I don't see anything that will help.

If you have it happen again, a snapshot of the runaway process' memory regions would be helpful.

[Don Morris_1]

UAREAS are kernel thread stacks. You get more of them if you spawn more threads, but a Uarea itself is very fixed in size, it doesn't grow dynamically.

User thread stacks (maxssiz_64bit or pthread stacks allocated using mmap()) might do this -- but I'd be surprised if maxdsiz_64bit was 1Gb and maxssiz_64bit was 16Gb or something.

So that leaves: Shared objects (SysV shmem, MAP_SHARED mmap, shared libraries) or MAP_PRIVATE mmap's [which are not affected by maxdsiz()].

I certainly wouldn't call this impossible, but couldn't really tell you what was done where without at least the virtual object (by type) layout of the process at the time, sorry.