Re: Query Performance issue

From: Lok P <>
Date: Thu, 31 Dec 2020 23:28:36 +0530
Message-ID: <>

Yes Jonathan, the count(*) from v$open_cursor for all those 6- session_ids is coming out to be ~15K. So it really appears to be an application code not closing the cursor issue.

If i sum(work_area_size) for that sql_id in v$sql_workarea_active, its coming out to be ~37GB, but we have pga_aggregate_target set as ~15GB(out of which ~5GB is total pga allocated in v$pgastat), so not sure if the set global memory bound(i.e. ~1MB) can be retrieved from this one , rather it seems to be derived from the number of work area i.e. ~13K here. Correct me if wrong.

Another thing i noticed the , the actual_mem_used and max_mem_used column in v$sql_workarea_active are all populated as Zero for this sql_id, so does this mean that in reality those are actually not consuming any physical memory from the server, rather its just making the calculation of "global memory bound" wrong and thus fooling optimizer to use less memory endup spilling more to temp? or in other words this can't result in any memory starvation like scenario?

On Thu, Dec 31, 2020 at 6:57 PM Jonathan Lewis <> wrote:

> That looks as if it may be the source of the problem - and MAY be
> corroborating my latest interpretation that it's the number of active
> workareas rather than the total volume of space allocated that results in
> the change in the global memory bound.
> If you haven't already killed those sessions then you could
> select sid, count(*) from v$open_cursor group by sid -- or something a
> little more sophisticated
> to check if the hundreds of workareas are linked to cursor that still
> appear to be open.
> You could also select sum(workarea_size) for that SQL_ID from
> v$sql_workarea_active to see if including that value in my previous
> suggestion for estimating the global memory bound gets you close to the
> current values.
> If you do see 13,000 open_cursors for that statement then you've probably
> got an application error not closing cursors; if you don't then you may
> have found an Oracle bug that is losing track of that workarea.
> Regards
> Jonathan Lewis
> On Thu, 31 Dec 2020 at 13:03, Lok P <> wrote:
>> Thank You Jonathan.
>> Something strange in v$sql_workarea_active, its showing total ~13198
>> entries out of which ~13184 are belong to one sql query. I was not able to
>> search that sql_id in v$session as that sql was not running at this moment
>> which is clear from v$session. But then i think the only option left is to
>> backtrack through the SID which is there in v$sql_workarea_active
>> associated to that sql, i saw 6 distinct SID's and all of those sessions
>> are now stays INACTIVE(event sql * net message from client) with
>> logon_time showing as 9th Nov. And all of these entries in
>> v$sql_workarea_active are on operation_id- 1 and operation_type as "GROUP
>> BY (HASH)". Not sure if we hit any bug here, as because the query plan from
>> AWR below looks normal(a WITH clause query). And also the
>> dba_hist_active_sess_history is logging the pga_allocated against these ~6
>> sessions as ~10 to ~15MB too, so it got unnoticed.
>> I see from dba_hist_sqlstat , this sql used to run ~10-15 times/day and
>> now recently running max ~100-200 times/day with avg execution time as
>> ~1millisec/execution. So not sure what has happened wrong with it for any
>> of the executions. and it can keep spawningg so much work area. But it
>> seems that we have to kill these sessions to bring things back to normal.
>> Hoping it will not appear again.

Received on Thu Dec 31 2020 - 18:58:36 CET

Original text of this message