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

_kghdsidx_count=7

I'll have to look up that parameter as I'm not familiar with it.

Chris

On Thu, May 31, 2018 at 2:23 PM, Stefan Knecht <knecht.stefan_at_gmail.com> wrote:

> 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