This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Nov 28, 2012 8:18 AM by user500302 Go to original post RSS
  • 15. Re: SQL Causes server to swap and become unresponsive, What is it doing?
    973762 Newbie
    Currently Being Moderated
    Thank you everyone for the help, Oracle support after 14 days, now thinks this might be a bug, not slow block reads.
    So hopefully it will now get fixed or worked around.

    Tom
  • 16. Re: SQL Causes server to swap and become unresponsive, What is it doing?
    Tanel Poder Oracle ACE Director
    Currently Being Moderated
    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:
    $ 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 ]
    Indeed the private memory usage of this process grew to 1-2GB (and then the process crashed with ORA-4030 on my VM).

    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.

    --
    Tanel Poder
    Blog - http://blog.tanelpoder.com
    App - http://voic.ee
  • 17. Re: SQL Causes server to swap and become unresponsive, What is it doing?
    Tanel Poder Oracle ACE Director
    Currently Being Moderated
    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

    Tanel.
  • 18. Re: SQL Causes server to swap and become unresponsive, What is it doing?
    user500302 Newbie
    Currently Being Moderated
    Of all the bugs logged for this issue,
    Bug 9004844 - query using regexp_replace spins in lxregmatch
    and
    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 11.2.0.4, so that it when there will be a solution.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points