Re: Memory Issue

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>
>

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