Re: Sudden occurrence of SHARED_POOL LATCH waits - DB up since 4/3/2018

Thanks Jonathan - your input is greatly appreciated.

Regards,
Chris

On Tue, Jun 5, 2018 at 6:38 AM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

>
> The title of the SR/Bug need not be a reflection of the real problem. I
> think it's what the person reporting the problem saw, and doesn't
> necessarily reflect cause and effect correctly. You, for example, might
> raise an SR suggesting the "Large PRTMV related to shared pool latch waits"
> based on your observations - the person raising the SR may simply have said
> "we get 4031 and lots of PRTMV and we're inserting into this table where
> we're exchanging partitions."
>
> There's a detail in the notes if you drill down that says something about
> a memory leak on exchange - some memory apparently being pinned but there
> being no-one pinning it. This leak could be purely about exchanging
> partitions. In the case of the SR this resolved to ORA-04031; in your case
> (because your shared pool is enormous) it could be that the memory chains
> from the leaks are now so long that it takes a huge amount of time walking
> them to find freeable memory - and a session has to hold the shared pool
> latch while doing so -- hence your perception of the problem.
>
> If you search MoS for PRTMV there is another bug that says this memory
> area is still growing in 12.2 even though the bug is supposed to be fixed.
>
> I've just run up a little test in 12.2.0.1 - create partitioned table,
> create simple table, exchange table with one partition, and keep repeating
> exchange. The PRTMV becomes quite large (relatively speaking), and the
> Oracle is busy kicking loads of stuff out of the shared pool (so free
> memory increases) even though there appears to be lots of free memory to
> handle the task. This isn't conclusive in any way, of course, but it does
> show that you can end up allocating memory to PRTMV when you wouldn't
> necessarily expect to.
>
> Regards
> Jonathan Lewis
>

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