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
________________________________________
From: Chris Taylor <christopherdtaylor1994@xxxxxxxxx>
Sent: 05 June 2018 05:41
To: Jonathan Lewis
Cc: ORACLE-L
Subject: Re: Sudden occurrence of SHARED_POOL LATCH waits - DB up since 4/3/2018
So I pulled the shared pool stats broken down by allocations in each subpool -
from a script that Tanel Poder provided on one of his posts (I don't have the
link to the script now and I renamed it when I copied it). Thanks again to
Tanel for his great scripts!
Here's the breakdown of the shared pool currently while the partition
maintenance jobs are running. There is NO DML activity on the underlying
tables involved in the partition exchanges but am curious if this might related
to:
Bug 20635353 - High PRTMV Memory Allocations when Inserting into Partitioned
Tables Simultaneously with Partition Maintenance Operations (Doc ID 20635353.8)
But, the table with the high number of partitions and DDL operations doesn't
have any DML being done at the same time so I'm not sure.
Here's the breakdown of the shared pool. I find it interesting that PRTMV has
such large allocations in each subpools:
SUBPOOL NAME SUM(BYTES) MB
------------------------------ -------------------------- ---------- ----------
shared pool (1): PRTMV 1.1870E+10 11320.12
--
//www.freelists.org/webpage/oracle-l
