Re: Memory Issue

Hi,

Have you tried gathering fixed_objects stats to see if that helps .I saw this issue during upgrade where it preupgrade took longer to complete and was stuck on this query.

Thanks
Nitin

On Tue, Dec 22, 2020 at 10:12 AM Pap <oracle.developer35_at_gmail.com> wrote:

> 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]
> <https://us.v-cdn.net/6032257/uploads/35FPPSKFLLVS/image.png>
>

-- 
Cheers
Nitin

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