Oracle Oracle 12c DB in-memory Brief and enable ____oracle

Oracle DB In-memory is pre-installed in Oracle database 12c (12.1.0.2 version) without the need to install additional software or recompile existing database software. This is because the in-memory option is seamlessly integrated into the Oracle database software kernel as a new component of the SGA, so Oracle DB In-memory is also installed if Oracle database is already installed. In-memory Store default is not open, but you can simply through a few steps to open the change function.

A more important point is that the DB in-memory is open at the instance level, and is not automatically stored to In-memory area for objects that need to be manually specified for the in-memory to be stored.

In-memory area is a static pool in which the SGA stores data in a way that is stored in columns. Without this feature, Oracle's data is stored in a row form (except EHCC compression), while in In-memory area the data is stored in a column format, which improves scanning performance in some business scenarios, such as reporting.

The size of the In-memory area is controlled by the parameter inmemory_size, which defaults to 0, indicating that the DB in-memory is disabled.

The inmemory_size parameters that the database is currently using can be viewed through the V$parameter view and also visible in V$sga view. In-memory column is stored in a static pool, so all adjustments to the Inmemory_size parameter will not take effect until the instance is restarted. Also, the size of the In-memory pool is not affected by automatic memory management (AMM). If the DB in-memory feature is enabled, the inmemory_size minimum needs to be set to 100M.

Now let's take a look at the parameters associated with In-memory:

Sql> Show Parameter InMemory

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

Inmemory_clause_default string

Inmemory_force string DEFAULT

Inmemory_max_populate_servers integer 0

Inmemory_query string ENABLE

Inmemory_size Big Integer 0

Inmemory_trickle_repopulate_servers_ Integer 1

Percent

Optimizer_inmemory_aware Boolean TRUE

These 7 parameters, which contain inmemory prefixes, control all aspects of the in-memory function respectively. For example, the Inmemory_query parameter controls whether the user queries at the system and session level can use column-stored data. Other parameters will also be introduced in the following articles.

In the current parameter settings you can see that the Inmemory_size parameter is set to 0, which means that the DB in-memory feature is not enabled and the related space is not allocated in the SGA.

We can also see through V$SGA, In-memory area is not allocated space.

Sql> select Name,value from V$SGA;

NAME VALUE

-------------------- ----------

Fixed Size 2929160

Variable Size 469765624

Database buffers 2600468480

Redo buffers 13844480

The process of opening DB In-memory is as follows:

1. Modify Inmemory_size Parameters:

sql> ALTER SYSTEM SET inmemory_size=1g scope=spfile;

System altered.

Note: The minimum of Inmemory_size is 100M.

2. Check the settings of the SGA parameter to ensure that the database instance can start normally after the Inmemroy_size parameter has been set. If the database uses ASMM, you need to check the sga_target parameters. If you use AMM, you need to check the Memory_target parameters and also check Sga_max_target (or Memory_max_target).

3. Restart database instance

sql> shutdown immediate;

Sql> Startup

4. Check the reset database parameter settings after reboot:

Sql> Show Parameter Inmemory_size

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

Inmemory_size Big Integer 1G

The distribution of In-memory area can also be queried through the V$SGA view:

Sql> select Name,value from V$SGA;

NAME VALUE

-------------------- ----------

Fixed Size 2929160

Variable Size 469765624

Database buffers 1526726656

Redo buffers 13844480

In-memory Area 1073741824

In the In-memory area is divided into two small pool, respectively:

1.1M Pool (IMCU pool)

2.64KB Pool (SMU pool)

The size of the specific allocation we can view through the V$inmemory_area view:

Sql> select Pool,alloc_bytes,used_bytes from V$inmemory_area;

POOL alloc_bytes Used_bytes

-------------------------- ----------- ----------

1MB POOL 854589440 0

64KB POOL 201326592 0

You can see that most of the In-memory area is assigned to 1MB pool. The used_bytes is 0 when the In-memory table is not manually specified.