Home -> Mailing Lists -> Oracle-L -> Re: Sudden occurrence of SHARED_POOL LATCH waits - DB up since 4/3/2018

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

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 5 Jun 2018 11:38:43 +0000
Message-ID: <MM1P123MB084273FD1ACA9D9F5FF0CF61A5660_at_MM1P123MB0842.GBRP123.PROD.OUTLOOK.COM> 







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_at_gmail.com>
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




--



http://www.freelists.org/webpage/oracle-l
Received on Tue Jun 05 2018 - 13:38:43 CEST












Original text of this message