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@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@xxxxxxxxx>
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
