Re: Shared Pool Issues possibly caused by In-Memory

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