Re: Memory Issue

From: Pap <>
Date: Wed, 23 Dec 2020 17:43:34 +0530
Message-ID: <>

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 <> 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 <> wrote:
>> Hello Friends, this is version 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]
>> <>

Received on Wed Dec 23 2020 - 13:13:34 CET

Original text of this message