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> 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