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

From: Jonathan Lewis <>
Date: Fri, 1 Jun 2018 08:25:10 +0000
Message-ID: <MM1P123MB0842FCFEA99A970CBC80EEC0A5620_at_MM1P123MB0842.GBRP123.PROD.OUTLOOK.COM>

Any clues or oddities in v$sgastat - in particular any unfamiliar areas of the shared pool that are unusually large.

Jonathan Lewis

From: <> on behalf of Chris Taylor <> Sent: 01 June 2018 01:40
To: GG
Subject: Re: Sudden occurrence of SHARED_POOL LATCH waits - DB up since 4/3/2018

Yeah I can't run that SQL either without locking up the shared pool on the db.


On Thu, May 31, 2018 at 1:29 PM, GG <<>> wrote: Hi,
 You've got quite large shared pool, it is true that quering x$ksmsp is not recommended due to excesive latch activity, but Oracle uses this query to narrow down the shared pool issues:

SELECT * FROM ( SELECT ksmchidx subpool, 'DURATION:'||ksmchdur duration, 'CMNT: '||ksmchcom cmnt, ksmchcls type, SUM(ksmchsiz) total_size, COUNT(*) allocations, 'AVG:' avg,AVG(ksmchsiz) average_size, MIN(ksmchsiz) min_size, 'MAX:' max,MAX(ksmchsiz) max_size FROM x$ksmsp GROUP BY ksmchidx, ksmchdur, ksmchcom, ksmchcls ORDER BY SUM(ksmchsiz)) WHERE (ROWNUM < 21 AND total_size > 10000 AND cmnt NOT LIKE 'CMNT:free%') OR cmnt LIKE 'CMNT: free%'order by 3,4,1,2 /

Your problem could be related to subpool defragmentation which seems to be often the case with exchange partition (generally partition maintenance?).

And indeed the shared pool can grow like crazy even when auto memory management is inactive (_target parameters = 0), it is documented SGA Re-Sizes Occurring Despite AMM/ASMM Being Disabled (MEMORY_TARGET/SGA_TARGET=0) (Doc ID 1269139.1)

Check the resize ops like Tim said via query on : V$MEMORY_RESIZE_OPS / V$SGA_RESIZE_OPS Regards.

Received on Fri Jun 01 2018 - 10:25:10 CEST

Original text of this message