Shulou(Shulou.com)06/01 Report--

Official document

Https://docs.oracle.com/en/database/oracle/oracle-database/12.2/inmem/concepts-for-the-im-column-store.html#GUID-5A72B48A-8427-41AE-9220-E46042BC90C4

Https://docs.oracle.com/en/database/oracle/oracle-database/12.2/inmem/configuring-the-im-column-store.html#GUID-8844C889-E381-4B77-8A51-7AA6462B14D7

The IM column store encodes data in a columnar format: each column is a separate structure. The columns are stored contiguously, which optimizes them for analytic queries. The database buffer cache can modify objects that are also populated in the IM column store. However, the buffer cache stores data in the traditional row format. Data blocks store the rows contiguously, optimizing them for transactions.

When you enable an IM column store, the SGA manages data in separate locations: the In-Memory Area and the database buffer cache.

The IM column store maintains copies of tables, partitions, and individual columns in a special compressed columnar format that is optimized for rapid scans.

In-Memory Column Store means In-Memory column storage, and each column is a separate structure. Its advantage is that it only needs to access some of the columns of the table, unlike database buffer cache, which stores data in the traditional row format and needs to access all the columns of the table. But traditional database buffer cache can also modify objects that are filled in In-Memory memory.

When the In-Memory column storage feature is enabled, when the database is started, a static memory pool In-Memory Area is allocated in SGA to store user tables stored in In-Memory columns.

In-Memory column storage maintains tables, partitions, and copies of individual columns in a special compressed column format, which is optimized for fast scanning.

Synchronization mechanism of data in In memory memory

Once the table loaded into In memory memory involves DML, you need a mechanism to ensure the consistency of the data in In memory memory, because the modification of the DML statement only modifies database buffer cache and log buffer in memory, how to synchronize these modified data into In memory memory. Oracle ensures data consistency through Transaction journal. If the table modified by the DML statement already exists in In memory memory, record the DML metadata such as table name tablename and row number rowid to transaction journal after the DML is submitted, and identify the SCN of the table in In memory memory as expired. If the later new query needs to access the data of the table in In memory memory, it will be accessed according to the data of the table in In memory memory + transaction journal+database buffer cache.

Of course, if DML statements continue to occur, it will make more and more data in transaction journal, and even most of the data in In memory memory are out-of-date old data, which is very harmful to the performance of in memory queries. Therefore, Oracle defines a threshold staleness threshold. When the proportion of old data in in memory reaches this threshold, the process of Repopulate will be triggered. By default, oracle will check whether the threshold is triggered once every 2 minutes.

Parameters involved in In-Memory column storage

Https://docs.oracle.com/en/database/oracle/oracle-database/12.2/inmem/init-parameters-for-im-column-store.html#GUID-A67ABCAC-C6B9-499E-8AE0-BD7922B239BE

Views involved in In-Memory columnar storage

Https://docs.oracle.com/en/database/oracle/oracle-database/12.2/inmem/views-related-to-im-column-store.html#GUID-2EBF8D9B-FA9E-4D67-8934-5908E6018D4E

Some summaries about In-Memory

1. Two prerequisites for enabling In-Memory column storage at the database level: the MEMORY_TARGET must be set and the parameter greater than 100m must be set and greater than 12.1.0

2. In-Memory column storage can be enabled for tablespaces, tables, partitions and materialized views. When In-Memory column storage is enabled in the current table space, by default, all new tables and materialized views under this table space are enabled with In-Memory column storage, and pre-existing tables under this table space are not affected. When setting a table space to enable In-Memory column storage, the INMEMORY keyword must be preceded by default.

3. Prerequisites for enabling In-Memory column storage at the table level: INMEMORY is specified when create table or alter table is specified

4. Query whether In-Memory column storage is enabled in the table. See whether USER_TABLES.INMEMORY equals' ENABLED', equals ENABLED indicates that it is enabled.

5, the table has been enabled In-Memory column storage does not mean that the data of the table has been automatically loaded into In-Memory memory, only when the instance starts or accesses the object will be loaded into In-Memory memory, if you want to load table data into In-Memory memory immediately, you can force a full table scan or use DBMS_INMEMORY.POPULATE on the table. As long as the PRIORITY level of object In-Memory column storage is not none, the object will be automatically loaded into In-Memory memory when the instance is started or the corresponding PDB of the object is started to check whether the data of the table has entered the In-Memory memory area. See V$IM_SEGMENTS.SEGMENT_NAME. If V$IM_SEGMENTS already exists in a table, the table record disappears in V$IM_SEGMENTS after truncate table, and still exists in V$IM_SEGMENTS after delete table.

6. Version 12.2.0, you can use ILM ADO POLICY to set In-Memory column storage accordingly. ILM ADO POLICY takes effect at the database level, not at the instance level. Information Lifecycle Management (ILM) Automatic Data Optimization (ADO) POLICY information lifecycle management automatic data optimization policy means that you can decide which table, when and when In-Memory columns are stored, and when they will expire. ALTER TABLE TABLE_NAME ILM ADD POLICY SET | MODIFY | NO INMEMORY

7. You can enable In-Memory for only specific field columns of the table, use inmemory to specify these specific fields, and you must use no inmemory to write in the remaining columns. If In-Memory is enabled for field columns, the column type cannot be LONG or LONG RAW column, an out-of-line column (LOB, varray, nested table column), or an extended data type column, and only part of the field columns of a table can be In-Memory enabled. The table cannot be found through USER_TABLES.INMEMORY='ENABLED', but can be checked through V$IM_COLUMN_LEVEL.INMEMORY_COMPRESSION'NO INMEMORY'.

8. Objects that cannot be stored in In-Memory columns are: Indexes, Index-organized tables, Hash clusters, Objects owned by the SYS user and stored in the SYSTEM or SYSAUX tablespace, If you enable a table for the IM column store and it contains any of the following types of columns, then these columns will not be populated in the IM column store:Out-of-line columns (varrays, nested table columns, and out-of-line LOBs), Columns that use the LONG or LONG RAW data types, Extended data type columns

9. If you do not specify the priority priority of inmemory, and the default is none, the access object will be placed in In-Memory memory only when the object is scanned by a full table. Getting the object through an index scan or through rowid will not put the object into In-Memory memory. If the priority level is not none, the object In-Memory is automatically put into memory during database startup, or the object is placed into In-Memory memory according to priority

10. If you do not specify the MEMCOMPRESS compression level of inmemory, the default is MEMCOMPRESS FOR QUERY LOW.

11. If you do not specify DUPLICATE, the default is NO DUPLICATE. Only RAC environment and Oracle Engineered System environment can use DUPLICATE or DUPLICATE ALL. Otherwise, even if you use DUPLICATE or DUPLICATE ALL, it will still be treated as NO DUPLICATE.

12. If you do not specify distribute, the default is auto, and the tables in IM will be distributed among nodes by default. Only RAC environments can use distribute

13. With regard to the difference between populate and repopulate, populate converts the existing data on disk into column format and stores it into In-Memory memory. Repopulate loads new data into In-Memory memory, which can be simply understood as populate initializing full data into In-Memory memory, and repopulate incremental data into In-Memory memory.

Some experimental results

1. Set the tablespace to inmemory

Create a table space or modify a table space to inmemory,inmemory keyword must be preceded by default

SQL > create tablespace tablespace1 datafile'/ u02 Union data inmemory Tablespace 2.dbf' size 100m

ERROR at line 1:

ORA-02180: invalid option for CREATE TABLESPACE

SQL > create tablespace tablespace1 datafile'/ u02 Union data default inmemory Tablespace 2.dbf' size 100m

Tablespace created.

SQL > alter tablespace USERS inmemory

ERROR at line 1:

ORA-02142: missing or invalid ALTER TABLESPACE option

SQL > alter tablespace USERS default inmemory

Tablespace altered.

2. Set the table to inmemory

If create table as mode, inmemory is placed in front of as

Create table table1 (hid number (10)) inmemory

Alter table table2 inmemory

Create table T4 inmemory as select * from T1 Matsumi T4 enables In-Memory column storage

Create table T5 as select * from T1 inmemory;--t5 does not have In-Memory column storage enabled

3. Set the materialized view to inmemory

Create materialized view mview1 inmemory as select * from table1

Alter materialized view mview2 inmemory

4. Some partitions of the partition table are set to inmemory

Table creation is the last two partitions SALES_Q4_2019 and SALES_Q1_2020 do not enable In-Memory column storage. See user_tab_partitions.inmemory, and finally modify the SALES_Q4_2019 partition to enable In-Memory column storage.

CREATE TABLE sales1 (prod_id NUMBER 6, time_id DATE,channel_id varchar2 100)

PARTITION BY RANGE (time_id)

(PARTITION SALES_Q1_2019

VALUES LESS THAN (TO_DATE ('01color APRMYY')) INMEMORY

PARTITION SALES_Q2_2019

VALUES LESS THAN (TO_DATE ('01murJULMULMY') INMEMORY

PARTITION SALES_Q3_2019

VALUES LESS THAN (TO_DATE ('01murOCTMYY')) INMEMORY

PARTITION SALES_Q4_2019

VALUES LESS THAN (TO_DATE ('01MJANMYY') NO INMEMORY

PARTITION SALES_Q1_2020

VALUES LESS THAN (MAXVALUE))

Alter table sales1 modify partition SALES_Q4_2019 inmemory

5. Set the field column to inmemory

In the table table1 created below, only the CREATED_APPID field does not enable In-Memory column storage, and all other columns are enabled.

So as long as some columns of a table are set to inmemory, you must use no inmemory to write in the remaining columns.

Create table table1 as select * from dba_objects

Alter table table1 inmemory (OWNER) no inmemory (CREATED_APPID)

When a database is restarted, all of the data for database objects with a priority level other than NONE are populated in the IM column store during startup.

After the database is restarted, all data from database objects that have a higher priority than NONE are loaded into In-Memory during startup.

Population

The operation of reading existing data blocks from data files, transforming the rows into columnar format, and then writing the columnar data to the IM column store. In contrast, loading refers to bringing new data into the database using DML or DDL.

Population, which transforms existing data on disk into columnar format, is different from repopulation, which loads new data into the IM column store. Because IMCUs are read-only structures, Oracle Database does not populate them when rows change. Rather, the database records the row changes in a transaction journal, and then creates new IMCUs as part of repopulation

The operation of reading existing blocks from a data file, converting rows to column format, and then writing column data to IM column storage. Instead, loading refers to the use of DML or DDL to bring new data into the database.

Population converts existing data on disk to column format, and Population is different from repopulation that loads new data into the IM column store. Because IMCU is a read-only structure, the Oracle database does not populate rows when they are changed. Instead, the database records the row changes in transaction journal and then creates a new IMCU as part of the repopulation

IMCU

An In-Memory Compression Unit (IMCU) is a compressed, read-only storage unit that contains data for one or more columns.

An in-memory compression unit (IMCU) is a compressed read-only storage unit that contains data for one or more columns.

Transaction journal

Metadata in a Snapshot Metadata Unit (SMU) that keeps the IM column store transactionally consistent.

The metadata in the snapshot metadata unit (SMU) enables IM column storage to be transactional consistent.

Every SMU contains a transaction journal. The database uses the transaction journal to keep the IMCU transactionally consistent.

The database uses the buffer cache to process DML, just as when the IM column store is not enabled. For example, an UPDATE statement might modify a row in an IMCU. In this case, the database adds the rowid for the modified row to the transaction journal and marks it stale as of the SCN of the DML statement. If a query needs to access the new version of the row, then the database obtains the row from the database buffer cache.

The database achieves read consistency by merging the contents of the column, transaction journal, and buffer cache. When the IMCU is refreshed during repopulation, queries can access the up-to-date row directly from the IMCU.

Each SMU contains a transaction journal. The database uses transaction journal to keep IMCU transactional consistent.

As when IM column storage is not enabled, the database uses buffer caching to process DML. For example, a UPDATE statement might modify a line in IMCU. In this case, the database adds the row identity of the modified row to the transaction journal and marks it as expired starting with the SCN of the DML statement. If the query needs to access a new version of the row, the database fetches the row from the database buffer cache.

The database achieves read consistency by combining the contents of juxtaposition, transaction journal and buffer cache. When the IMCU is refreshed during repopulation, the query can access the latest rows directly from the IMCU.

Repopulation

The automatic refresh of a currently populated In-Memory Compression Unit (IMCU) after its data has been significantly modified. In contrast, population is the initial creation of IMCUs in the IM column store.

After a major change has been made to the data of the current populated in-memory compression unit (IMCU), it is automatically refreshed. Instead, population is the initial creation of IMCU in the IM column store.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.