Follow up ： Chapter one Oracle Database In-Memory Relevant concepts (IM-1.1)

This article is IM The architecture of column storage （ On ） piece

2 In-Memory Column storage architecture

In-Memory Column store （IM Column store ） Store tables and partitions in a column format optimized for fast scanning . Oracle The database uses a complex architecture to manage data in both column and row formats .

Two formats ： Columns and rows

Enable IM When storing Columns ,SGA Manage data in a separate location ：In-Memory Area and database buffer cache （Buffer Cache）.

IM Column storage encodes data in column format ： Each column is a separate structure . These columns are stored continuously , They optimize analysis queries . Database buffer cache （buffer cache ） You can modify objects , It can also be in IM Objects populated in the column store . however , Buffer cache （buffer cache ） Store data in traditional row format . Data blocks store rows consecutively , Optimize their transactions .

The following figure illustrates the difference between row based storage and column storage .

chart 2-1 Columns and row based storage

This section creates the following topics ：

• In-Memory Area Column data in In-Memory Area contain IM Optional for column storage SGA Components .
• Database buffer cache （Buffer Cache） Row data in No matter what IM Whether column storage is enabled or disabled , Database buffer cache （buffer cache） All store and process data blocks in the same way . buffer I / O It has exactly the same function as buffer pool .

In-Memory Column data in area

In-Memory Area Is included IM Optional for column storage SGA Components .

This section contains the following topics ：

• In-Memory Area Size In-Memory Area from INMEMORY_SIZE Initialization parameter control . By default ,In-Memory Area The size is 0, It means IM Column storage is disabled .
• In-Memory Area Memory pool in （Memory Pools） In-Memory Area It is divided into sub pools of column data and metadata .

In-Memory The size of the area

The memory area consists of INMEMORY_SIZE Initialization parameter control . By default , The size of the memory area is 0, It means IM Column storage is disabled .

To enable the IM Column store , Please put In-Memory Area Set to at least 100 MB. The size is shown in V$SGA in .

from SGA_TARGET Subtract... From initialization parameter settings In-Memory Area Value . for example , If you will SGA_TARGET Set to 10 GB, And if you will INMEMORY_SIZE Set to 4 GB, be SGA_TARGET Set up 40％ Assign to In-Memory Area . The figure below illustrates this relationship .

chart 2-2 INMEMORY_SIZE and SGA_TARGET Parameters

And SGA Other components of （ Include buffer cache and shared pool） Different ,In-Memory Area Size is not controlled by automatic memory management . When buffer cache or shared pool When more memory is needed , The database will not shrink automatically In-Memory Area , Or when there is not enough memory space , increase In-Memory Area . You can only adjust... Manually INMEMORY_SIZE Initialization parameters to increase In-Memory Area Size .

from Oracle Database 12c Release 2（12.2） Start , have access to ALTER SYSTEM Statements are added dynamically INMEMORY_SIZE . When the following conditions are met , The database allocates additional memory ：

• SGA There is free memory available in .
• INMEMORY_SIZE The new size of is at least... Larger than the current setting 128 MB. notes : You cannot use ALTER SYSTEM To reduce INMEMORY_SIZE.

VINMEMORY_AREA and VSGA The view immediately reflects the changes .

In-Memory Area Memory pool in （Memory Pools）

In-Memory Area Sub pool for column data and metadata .

In-Memory area It is subdivided into the following sub pools ：

• Column data pool This sub pool stores IMCU, It contains column data . V$INMEMORY_AREA.POOL The column identifies this sub pool as 1MB POOL, As the sample 2-1 Shown .
• Metadata pool This sub pool stores information about residing in IM Metadata of objects in the column store . V$INMEMORY_AREA.POOL The column identifies this sub pool as 64KB POOL, As the sample 2-1 Shown .

chart 2-3 Sub pool in memory area

The database uses an internal heuristic algorithm to determine the relative size of the two sub pools . Database will In-Memory Area Most of the space in is allocated to the column data pool （1 MB pool）.

notes :

Oracle The database automatically determines the size of the sub pool . You cannot change the space allocation .

Example 2-1 V$INMEMORY_AREA View

This sample query V$INMEMORY_AREA View to determine each sub pool （ Include sample output ） Amount of memory available in ：

COL POOL FORMAT a9
COL POPULATE_STATUS FORMAT a15
SSELECT POOL, TRUNC(ALLOC_BYTES/(1024*1024*1024),2) "ALLOC_GB",
        TRUNC(USED_BYTES/(1024*1024*1024),2) "USED_GB",
        POPULATE_STATUS
FROM    V$INMEMORY_AREA;

POOL      ALLOC_GB   USED_GB    POPULATE_STATUS
--------- ---------- ---------- ---------------
1MB POOL  7.99       0          DONE
64KB POOL 1.98       0          DONE

In-Memory area The current size of is V$SGA In the view, I see ：

SELECT NAME, VALUE/(1024*1024*1024) "SIZE_IN_GB"
FROM   V$SGA 
WHERE  NAME LIKE '%Mem%';

NAME                 SIZE_IN_GB
-------------------- ----------
In-Memory Area       10

In this example , The memory allocated to the sub pool is 9.97 GB, and In-Memory Area The size is 10 GB. The database uses a small percentage of memory for internal management structures .

Database buffer cache （Buffer Cache） Row data in

No matter what IM Whether column storage is enabled or disabled , Database buffer cache （buffer cache） All store and process data blocks in the same way . buffer I / O It has exactly the same function as buffer pool .

IM Allow storage in columns SGA In traditional line format （ Buffer cache ） Fill in data at the same time as column format . The database transparently stores OLTP Inquire about （ For example, the primary key search ） Send to buffer cache , And analysis and report query to IM Column store . When extracting data ,Oracle The database can also read data from two memory areas in the same query .

notes :

In the execution plan ,TABLE ACCESS IN MEMORY FULL The operation is expressed in IM Access some or all of the data in the column store .

Double format （dual-format） Architecture does not increase memory requirements . Buffer cache （buffer cache） Optimized to run at a much smaller size than the size of the database .

The following figure shows an example IM Column store . The database will... In traditional row format sh.sales The table is stored on disk . SGA Store data in column format in IM Column storage , And stored in the database buffer cache in row format （buffer cache） in .

chart 2-4 IM Column store

IM Column storage supports persistence , Pile organization chart （heap-organized tables） Each disk data format . The column format does not affect the format of the data stored in the data file or buffer cache , It won't affect undo Data and online redo logging .

Databases are handled in the same way DML modify , Whether enabled or not IM Column store , By updating the buffer cache （buffer cache）、 online redo Journal and undo Table space . however , The database uses internal mechanisms to track changes , And ensure IM Column storage is consistent with the rest of the database . for example , If sales The table is filled in IM Column storage , And if the application updates sales The lines in the , Then the database automatically makes IM Column store sales Table copies keep transactions consistent . visit IM Queries stored in columns are always cached against the access buffer （buffer cache） Your query returns the same result .

（ This chapter is not finished , See the next chapter ,IM Series of ： Chapter two ：IM Column storage architecture （ in ）（IM-2.2））

Shandong Oracle User group (Shandong Oracle User Group), abbreviation ：SDOUG, Is a full of vitality 、 Young non-profit organizations , It aims to provide an exchange platform for technology lovers in Jinan and surrounding areas .SDOUG Organize offline technology sharing activities from time to time , Promote local and surrounding IT Technological development 、 Help technology enthusiasts improve themselves . Share technology 、 Share happiness ,SDOUG On the road .