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

Whats the value of _kghdsidx_count on that system*?*

On Fri, 1 Jun 2018, 01:55 Chris Taylor, <christopherdtaylor1994_at_gmail.com> wrote:

> Well, I wasn't aware such a thing could occur in a non-memory managed db
> so that's kind of scary.
>
> So, I just confirmed that the only resize operations that occurred were
> the manual ones I did this morning as an attempt to see if the latching
> improved. I *do* think that its related to the subpools related to
> partition maintenance:
>
> These were all mine as purely a troubleshooting step to see if it added
> any relief:
>
> PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
> NOTE
> db_cache_size 2200096997376 2195802030080 2195802030080 COMPLETE 05/31/2018
> 09:07 05/31/2018 09:07 Decreased Buffer Cache by 5GB
> shared_pool_size 274877906944 275951648768 275951648768 COMPLETE 05/31/2018
> 09:08 05/31/2018 09:08 Increase shared pool by 1 GB
> shared_pool_size 275951648768 279172874240 279172874240 COMPLETE 05/31/2018
> 09:09 05/31/2018 09:09 Increase shared pool by 4 GB
> Thanks,
> 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