Re: Memory Issue

No. Actually as i got to know, we killed all the underlying processes and then the memory utilization was brought back to normal. But, do you mean to say , it might be taking a bad path(which unfortunately, we have not captured and also this sql plan is not showing up in display_AWR too). And due to that bad path , say a FTS on OBJ$ it can cause memory starvation. And that bad path might be the cause of bad stats for OBJ$. Correct me if wrong.

But i do see the last_analyzed is for OBJ$ showing 5-dec-2020 with the num_rows showing ~109K, which seems accurate. So i was suspecting if any bugs around memory leak related to this query?

On Wed, Dec 23, 2020 at 12:34 AM Nitin Saxena <ntnsxn7_at_gmail.com> wrote:

> 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