Re: Memory Issue

First of all, I wonder why in case of PGA issue you didn't analyze it. I would start from top sessions by PGA consumption: https://github.com/xtender/xt_scripts/blob/master/pga_top.sql Then v$process_memory and v$process_memory_detail: https://github.com/xtender/xt_scripts/blob/master/pga/pga_usage_by_sid.sql https://github.com/xtender/xt_scripts/blob/master/pga/details_simple.sql

Then I would use session snapper by Tanel Poder: https://github.com/tanelpoder/tpt-oracle/blob/master/snapper.sql to check what exactly is going on in that sessions. Considering the P2 value of "Library cache: mutex X" I would kill only the session holding that cursor (p1 value).
Then I would analyze that cursor with its bind variables including v$sql_shared_cursor:
https://github.com/xtender/xt_scripts/blob/master/sql_shared.sql

Now since we have no exact details, can you share ashdump for that period? or export dba_hist_active_sess_history

On Wed, Dec 23, 2020 at 3:14 PM Pap <oracle.developer35_at_gmail.com> wrote:

> I see the top_level_sql_id is getting populated with same sql_id and the
> sql_exec_id column is all null along with sql_exec_start for all of those
> ~30K samples. Now i also checked gv$sql and see the last_load_time showing
> as ~2 days back and the EXECUTIONs column showing figure 15, so which means
> even i just consider today's ~30 minutes execution happen for that same sql
> , it still looks lot more for those total ~15 executions.
>
> I tried executing the query manually by passing some sample bind
> values(which again may not be the actual binds which caused the issue). i
> see the query finishing in seconds even with and without rows. So is it
> possible that some specific bind is causing the issue?
>
> On Wed, Dec 23, 2020 at 5:32 PM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>> A couple of thoughts.
>> First - the AWR output looks a little odd - the Total Executions is lower
>> than the Total Parse Calls. Any idea why ? (It may be completely unrelated
>> to your problem, of course).
>>
>> Secondly - this looks like a sys-recursive statement that would be
>> executed while a statement is being parsed, but normally such a statement
>> would only be called a few times because it would be populating the
>> dictionary cache (v$rowcache). There are some bit of the dictionary, that
>> don't get cached, though (parts of views, parts of histograms, maybe) so
>> it's possible that what you're seeing is a strange parsing issue relating
>> to a parent statement. When you see this SQL (sql_id) in the sample there
>> are two other columns you can check: top_level_sql_id, and sql_exec_id.
>> THe former MAY tell you about a statement that is being parsed, the latter
>> may give you an idea of how many times this statement is actually called.
>>
>> My first guess (on a bare minimum of information) would be that you have
>> a statement with a monster IN-list and Oracle is trying to optimize a
>> massive UNION ALL where every branch is accessing a view.
>>
>> Regards
>> Jonathan Lewis
>>
>>
>>
>>
>>
>>
>>
>> ASH / v$active_session_history from your 25 minute run with 25,000
>> samples.
>> One of the columns of
>>
>>
>> On Tue, 22 Dec 2020 at 18:12, 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>
>>>
>>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

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