This content has been marked as final. Show 18 replies
The V$ views (with the exception of V$OSSTAT) will show you only what Oracle thinks is happening, not the reality what really happens in a process.
So, the UGA/PGA numbers show you close to real memory usage only when Oracle itself maintains these properly. If due to a bug or some external factor memory is allocated without updating these counters, then you'll see discrepancy between what Oracle thinks it's using vs real memory usage. So snapper showed no memory growth as these "pga/uga memory bytes" counters were not updated by Oracle properly.
But when standard OS tools say there is a memory shortage, I would believe the OS tools as opposed to Oracle which is just another application on top of the OS.
You correctly did the stack profiling, so that it should be evident roughly where the looping and memory leak is happening.
I would first have ran pmap -x on the process (after TOP showed the huge memory usage). I reproduced the problem with your script and ran pmap:
Indeed the private memory usage of this process grew to 1-2GB (and then the process crashed with ORA-4030 on my VM).
$ pmap -x 15616 | egrep -e "zero|anon|heap|stack" 00440000 8 0 0 rwx-- [ anon ] 004a7000 16 8 4 rwx-- [ anon ] 004c0000 100 4 4 rwx-- [ anon ] ... 12364000 5120 0 0 rwx-- [ anon ] 128f8000 220164 164 148 rwx-- [ anon ] 7ef83000 933888 337528 337528 rwx-- [ anon ] <<<<----------------- bffae000 144 52 40 rwx-- [ stack ]
And interestingly this is reported as "anon" mem (allocated using the old-fashioned malloc/brk), not the new real-free memory allocation (mmap of /dev/zero).
It's a bug.
Blog - http://blog.tanelpoder.com
App - http://voic.ee
I did a metalink search with the top functions reported in the stack traces ... didn't find an exact match (you don't always get lucky), but here's one with similar stack trace, causing a session to hang. No memory issues reported though, but you may have a variation of this bug (or some completely different bug in the same code):
Bug 8579113 : UPDATE HANGS USING REGEXP_REPLACE ON LOB COLUMN
Of all the bugs logged for this issue,
Bug 9004844 - query using regexp_replace spins in lxregmatch
Bug 9134741 - ora-07445 [kghfrmrg()+1260] create table as select
both of which are marked as duplicates of bug 8579113,
both say FIX BY 12.1 and 18.104.22.168, so that it when there will be a solution.