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

I couldn't find anything.

We did bounce the database last night (instead of trying a shared pool flush - as a flush might not have resolved the problem) and the problem is currently resolved. I was unable to turn on the jobs and query the shared pool information directly.

I did find a script from Tanel Poder that will show me the subpool information which I wanted to try while the jobs were running and creating the problem but there wasn't enough time to do this in Production on this busy system :/ Had to take an emergency outage last night around 11:30 PM ET. Would have really liked to dig into this more.

Here's the script from Tanel that seems like it would have been useful:

https://github.com/tanelpoder/tpt-oracle/blob/master/sgastatx.sql

Chris

On Fri, Jun 1, 2018 at 3:25 AM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

>
> Any clues or oddities in v$sgastat - in particular any unfamiliar areas of
> the shared pool that are unusually large.
>
> Regards
> Jonathan Lewis
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Chris Taylor <christopherdtaylor1994_at_gmail.com>
> Sent: 01 June 2018 01:40
> To: GG
> Cc: ORACLE-L
> 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.
>
> Chris
>
>
> On Thu, May 31, 2018 at 1:29 PM, GG <grzegorzof_at_interia.pl<mailto:
> grzegorzof_at_interia.pl>> 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.
> GG
>
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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