Re: Memory Issue

You're seeing something that suggests some type of bug - so almost any random guess MIGHT be the right answer.

Having eliminated the hypothesis that the problem could be caused by frequently repeated execution of this statement, and having failed to find a parent statement we still have the possibility that it's a parsing problem (uses up CPU, demands PGA memory, runs SYS-recurrive statements like the one you've reported), and it's possible that the ASH output is not being updated properly.

You could try re-running the test with 10046 enabled - I wouldn't have wanted to do that while there was a possibility that it might end up with thousands of executions of that statement. You might not find anything useful in it, though, if the session is simply spinning on the CPU. It's possible that a statement will appear in the trace a little before this one that will give you a clue. You might also try with the 10053 trace enabled to see if some statement ends up generating a monstrously huge optimizer trace.

Otherwise you could start working through the steps suggested by Sayan.

Regards
Jonathan Lewis

On Wed, 23 Dec 2020 at 12:13, 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?
>
>
>

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