Re: Memory Issue

The issue happened a bit before so no details in v$ views. But I have captured the ASH(gv$active_session_history) dump of that sql. And it's coming around ~1.2MB , i tried sending it twice but somehow i think it's not going through because of the attachment or something i am not sure. Is there any other way to pass it on?

And the big threat is this query can submit to the database at any time which we are not sure of , and we are not able to get the exact culprit (i.e. probably the exact top_level_sql_id which submit this recursive query), so is there any other way to put a track on this so that we would be alerted before hand.

I do see a job as below was showing up in the OEM top activity section during that time, not sure if the recursive sql can be part of this parent?

DECLARE   job BINARY_INTEGER := :job;

  next_date TIMESTAMP WITH TIME ZONE := :mydate;

  broken BOOLEAN := FALSE;

  job_name VARCHAR2 (30) := :job_name;

  job_subname VARCHAR2 (30) := :job_subname;

  job_owner VARCHAR2 (30) := :job_owner;

  job_start TIMESTAMP WITH TIME ZONE := :job_start;

  job_scheduled_start TIMESTAMP WITH TIME ZONE := :job_scheduled_start;

  window_start TIMESTAMP WITH TIME ZONE := :window_start;

  window_end TIMESTAMP WITH TIME ZONE := :window_end;

  chain_id VARCHAR2 (14) := :chainid;

  credential_owner VARCHAR2 (30) := :credown;

  credential_name VARCHAR2 (30) := :crednam;

  destination_owner VARCHAR2 (30) := :destown;

  destination_name VARCHAR2 (30) := :destnam;

  job_dest_id VARCHAR2 (14) := :jdestid;

  log_id NUMBER := :log_id;

BEGIN   BEGIN    DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (15, TRUE);   END;   :mydate := next_date;

  IF broken

  THEN    :b := 1;

  ELSE    :b := 0;

  END IF; END; On Wed, Dec 23, 2020 at 6:02 PM Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> 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