Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Maria Colgan

Thanks for the question, Mikhail.

Asked: February 28, 2017 - 3:49 am UTC

Last updated: February 28, 2017 - 5:35 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Hello team,
I have noticed that In-Memory area has static pools with fixed amount of memory allocated to them.
Here is an example:
SQL> select * from v$inmemory_area;


POOL                        ALLOC_BYTES   USED_BYTES POPULATE_STA       CON_ID
-------------------------- ------------ ------------ ------------ ------------
1MB POOL                    25477251072  25445793792 POPULATING              0
64KB POOL                    6422724608     43122688 POPULATING              0

It means that 20 per cent of the inmemory_size parameter was allocated to the 64KB pool that is pretty much free in that database. As a result, 6GB of the In-Memory area is not used at all.
As a downside, I actually got the "Insufficient memory to populate table to inmemory area" messages in the alert.log without any evidence in v$inmemory_area/v$inmemory_segments, which is probably due to Bug 18549042 - Out of memory status is not shown in view V$IM_SEGMENTS (Doc ID 18549042.8) because I am still using DBBP 12.1.0.2.161018 in this database and the January 2017 DBBP is to be installed soon.
I was wondering what the reason for such memory allocation is. I mean that setting 64KB pool to 20 per cent of total In-Memory area looks like a complete waste of memory in this environment. I understand that maybe in some environments under some different conditions it would be appropriate, but it might also have been done in such a way when Oracle dynamically adjust the pools as needed according to the database activity.

and we said...

To populate a table into the In-Memory column store, we need two things:

a) the columnar compressed data from the table itself stored in the 1MB Pool
b) metadata about the table and its compressed data stored in the 64KB Pool

We also reverse space in 64KB Pool to help keep the IM column store in sync with the row store and other internal operations. The size of the 64KB pool is calculated based a heuristics, which factors in a number of things including the total size of the In-Memory column store.

There's an excellent blog post that describes this in more detail here

https://blogs.oracle.com/In-Memory/entry/what_is_an_in_memory



Is this answer out of date? If it is, please let us know via a Comment