Re: Shared Pool Issues possibly caused by In-Memory
Date: Thu, 10 Mar 2016 10:34:45 +0800
Message-ID: <CABx0cSUk5QOxFN4Kj4bbqJyA8eZaoKupsYwN_ekkyQ4=FUVsDA_at_mail.gmail.com>
Andy,
Thanks, yes I reviewed that link, found it a little lacking in specifics about exactly how to size shared pool with respect to in-memory. I guess this is not specific to in-memory, but just a case of monitoring shared pool usage and adjusting parameters where necessary. I can see currently (in memory disabled) shared pool size is around 6GB. From checking dba_hist_resize_ops, I can see that when we were using in-memory shared pool grew to around 28GB(!), and buffer cache was shrunk down to 512MB (!). This is using 50GB in-memory area on a fairly lightly loaded system.
I think best option is to re-enable in-memory, and closely monitor shared pool for excessive growth, probably will work with support on that.
Herald,
Thanks again found that on Oracle Support website, but seems a bit lacking
in details, may work with support to see if relevant. Note this is RAC,
but we only ever fire up one node.
Regards, Patrick
On 10 March 2016 at 02:45, Andy Rivenes <andy.rivenes_at_oracle.com> wrote:
> Hi Patrick,
>
> When using Database In-Memory there is additional space required from the
> shared pool that is used to allocate additional locks to keep in-memory
> data in sync with the row store. Please see MOS note 1903683.1 for more
> details.
>
> Regards,
>
> Andy Rivenes
> Database In-Memory Product Manager
> Oracle
>
>
>
> On Mar 9, 2016, at 2:29 AM, Patrick Jolliffe <jolliffe_at_gmail.com> wrote:
>
> Exadata 12.1.0.2.0
>
> Our test instance we had some issues with the shared pool. Analysis shows
> that the issue started with lots of entries similar to the following:
> ORA-04031: unable to allocate 2875560 bytes of shared memory ("shared
> pool","unknown object","sga heap(3,0)","ktmc_dlm_lck")
> ORA-04031: unable to allocate 2872832 bytes of shared memory ("shared
> pool","unknown object","sga heap(1,0)","ktmc_dlm_lck")
> ORA-04031: unable to allocate 2872832 bytes of shared memory ("shared
> pool","unknown object","sga heap(1,0)","ktmc_dlm_lck")
> ORA-04031: unable to allocate 2690496 bytes of shared memory ("shared
> pool","unknown object","sga heap(1,0)","ktmc_dlm_lck")
> ORA-04031: unable to allocate 2917536 bytes of shared memory ("shared
> pool","unknown object","sga heap(4,0)","ktmc_dlm_lck")
>
> After an hour or so, the problem seemed to the 'spread' to other areas of
> the shared pool, messages such as the following:
> ORA-04031: unable to allocate 40 bytes of shared memory ("shared
> pool","unknown object","KGLH0^405b049a","kglHeapInitialize:temp")
> ORA-04031: unable to allocate 40 bytes of shared memory ("shared
> pool","select /*+ index(idl_char$
> i...","KGLH0^5c4b4dd2","kglHeapInitialize:temp")
> ORA-04031: unable to allocate 480 bytes of shared memory ("shared
> pool","UPDATE SYS.AQ <http://sys.aq/>$_SCHEDULES
> SET...","KGLH0^16fdfe24","kkslpkp:kksclitval")
> ORA-04031: unable to allocate 720 bytes of shared memory ("shared
> pool","unknown object","KGLH0^206fd69a","kkscs")
>
> Have been doing some research, it appears ktmc relates to "IM Txn Block".
>
> https://dmitryremizov.wordpress.com/2015/10/19/a-new-im-related-oracles-diagnostic-events/
> This makes sense as we have been researching InMemory option.
> I am guessing the ktmc_dlm_lock allocation is some kind of mechanism to
> ensure read-consistency.
> My guess at the moment are that the size of the memory requested for this
> purpose, around 3MB, is more than shared pool is designed to handle.
> (Can anybody weigh in if I am wrong here? What about optimizer parsing
> huge chunk of SQL, would that require large chunk of memory like this?)
>
> My guess is that the fact that this memory cannot be allocated causes
> existing allocated chunks to be moved around, subsequently causing issues
> elsewhere.
> Am going to have to re-read the section in Jonathan Lewis's excellent
> "Oracle Core" book to see if this bears any relationship to reality.
>
> In the mean-time, anybody want to chime in with thoughts/observations?
>
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 10 2016 - 03:34:45 CET