Re: Memory Issue

Can this memory choke be caused by an "os thread startup" event from a Streams replication session?

Something was not adding up as because the number of Average active sessions per ~15minute for the recursive query during that interval was showing up as <5 during this memory issue period which lasted for ~17hours. Which seems to be quite low. I fetched the data using the below query from dba_hist_active_sess_history. And also I tried executing those recursive queries manually with few binds in loop 100 times and those came within seconds.

Then we found something interesting in OEM top activity just before the issue started. There was a single session lying in the database from SYS with module as "Streams" and program as "oracle_at_PRODDB1 (QMNC)", though this session was not showing as top consumer and even not coming as in blocking sessions against any of those recursive queries which were consuming all those memory, but it's almost seems like, this is the one which made the recursive queries to suffer from "Library cache: mutex X".

That same streams session was active all through that ~17hrs duration and if I aggregate the wait events in dba_hist_active_sess_history , it comes as below. So I am trying to interpret these but not quite able to translate how this one session can cause those recursive queries to crawl. Is this streams session waitevent can cause library cache latches all over and thus impacting the parsing of the query Or say this recursive queries? As because this session has logged null sql_id and has logged ~5MB as the pga_allocated in dba_hist_active_sess_history.

*Stats from the one Streams session for those ~17hours for which it was active:-*

EVENT SESSION_STATE COUNT(*)
  ON CPU 24894
os thread startup WAITING 15273
Streams AQ: qmn coordinator waiting for slave to start WAITING 969 enq: PV - syncstart WAITING 218
latch: shared pool WAITING 125
process diagnostic dump WAITING 78
enq: PR - contention WAITING 14
cleanup of aborted process WAITING 6
latch free WAITING 1

On Wed, Dec 23, 2020 at 8:29 PM Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> I've got you exported ASH. Btw, it would be much easier if you use
> pastebin or gist.github to post such data and just provide links to the
> files.
> To be honest the only interesting thing I see in your ASH data is "V8
> Describe Any" in "Top level call name", which should be OCIDescribeAny().
> Also only 716 rows from total 3146 were in "waiting", all other 2430 snaps
> (~80%) were "On CPU". So I would start with snapping session call stack, at
> least 30-40 "oradebug short_stack".
> From those 716 rows, 715 were "library cache: mutex X" and one "latch: row
> cache objects"
> And 132 rows with Blocking session status=valid with blocking
> sid=808,serial=37715.
>
>
> --
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning engineer
> Oracle ACE Associate
> http://orasql.org
>

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