Memory Issue
Date: Tue, 22 Dec 2020 23:41:31 +0530
Message-ID: <CAEjw_fh5si2tGa_r6-DcPJ1onStS74mWdjuOC01CpyWxABN-0A_at_mail.gmail.com>
Hello Friends, this is version 11.2.0.4 database. I am struggling to understand the reason behind sudden memory saturation in one of our hosts causing multiple databases to be brought to its knee. The host memory utilization went up from ~30% to 100%(~500GB) within ~3-4hrs. After digging into this we found it's one of the databases whose pga was bumped to almost ~500+ GB and the oem was filled with concurrency wait (Library cache: mutex X) with the below sql being highlighted was singled out as the one.We end up killing all the processes related top this query and few others after which the other databases in that host then came up to speed. And also this query was showing the program as "Jdbc Thin client".
However in the AWR i don't see any oddity i.e. this below query was not showing in the top in those parse or version count sections in the AWR. And in the dba_hist_active_sess_history the flags "in_parse" ,"in_sql_execution","IN_HARD_PARSE" were all showing as "N" throughout the issue period.
So wanted to check if anybody else has encountered such an issue because this sql seems to be a normal one (or say some recursive sql). So if there are any chances of memory leak or any related stuff? Also i saw there were not many sessions running this query , so wondering how come this query can cause such drastic memory consumption?
SELECT obj#, type#, ctime, mtime, stime, status,dataobj#, flags,oid$,spare1,spare2
FROM obj$
WHERE owner# = :1 AND name = :2
AND namespace = :3 AND (remoteowner = :4 OR remoteowner IS NULL AND :4 IS NULL) AND (linkname = :5 OR linkname IS NULL AND :5 IS NULL)
AND (subname = :6 OR subname IS NULL AND :6 IS NULL)
*Section of AWR:-*
[image: image.png]
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Dec 22 2020 - 19:11:31 CET