Re: Memory Issue
Date: Wed, 23 Dec 2020 14:17:12 +0530
Message-ID: <CAEjw_fjEA_4qU3toeAad-OrJVAfBAjJdHyrO8vFZ1VgUhpFOrw_at_mail.gmail.com>
We were able to capture the plan for that query. And we see, as it started, the memory consumption on the host started to increase but luckily, it finished within ~25minutes so things went normal too. Sql monitor is not captured but we did see in ASH it was active for full ~25minutes execution and i there was ~3200 samples/entries in gv$active_session_history logged, most of it are "ON CPU" apart from few which are showing the same "CONCURRENCY"/Library cache: mutex X. Doesn't this look bad as in any case, this recursive query should not take this much time? and again for all these samples in ASh the sql_plan_line_id is showing as "0" which points to the SELECT part only.
Should we trace(~10046) it for sometime to see when it executes in the database to see the issue?
--------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
--------------------------------------------------------------------------------------|
0 | SELECT STATEMENT | | | | 4 (100)|
||* 1 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 86 | 4
(0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | I_OBJ2 | 1 |
| 3 (0)| 00:00:01 |
On Wed, Dec 23, 2020 at 12:50 AM Pap <oracle.developer35_at_gmail.com> wrote:
> 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-lReceived on Wed Dec 23 2020 - 09:47:12 CET