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

What's New in Oracle 12C: In-Memory

In-Memory starts at 12C. The newly added memory area in SGA enables table data to be stored in columns.

In-Memory does not replace the traditional Buffer Cache, both of which exist in SGA.

The SGA is a dynamic area, and the In-Memory size is static and requires manual maintenance by DBA.

Column storage data and row storage data have their own advantages and disadvantages and are suitable for different scenarios.

Column storage performs better when accessing multiple rows and fewer columns.

Let's enable inmemory for performance testing.

A database version is 19C (equivalent to 12.2.0.3 version)

SQL > select banner_full from v$version

BANNER_FULL

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0-Production

Version 19.3.0.0.0

Second, check whether inmemory is enabled

SQL > show parameter inmemory_size

NAME TYPE VALUE

-

Inmemory_size big integer 0

3. Create test data

SQL > conn cjc/cjc@cjcpdb

SQL > create table T1 as select * from dba_objects

SQL > select count (*) from T1

COUNT (*)

-

72482

Enable in-memory (instance level)

SQL > show parameter inmemory_size

NAME TYPE VALUE

-

Inmemory_size big integer 0

SQL > alter system set inmemory_size=300M scope=spfile

SQL > shutdown immediate

SQL > startup

ORACLE instance started.

Total System Global Area 1287650440 bytes

Fixed Size 9145480 bytes

Variable Size 759169024 bytes

Database Buffers 201326592 bytes

Redo Buffers 3436544 bytes

In-Memory Area 314572800 bytes

Database mounted.

Database opened.

SQL > show parameter inmemory_size

NAME TYPE VALUE

-

Inmemory_size big integer 300M

Five performance comparison

5.1 collect T1 table statistics

SQL > EXEC DBMS_STATS.GATHER_TABLE_STATS ('CJC','T1',estimate_percent= > 100m cascade = > TRUE)

PL/SQL procedure successfully completed.

View the execution plan

SQL > set autotrace on

5.2 enable inmemory for table T1

SQL > conn sys/oracle@cjcpdb as sysdba

Connected.

SQL > alter table cjc.t1 inmemory

-disable alter table cjc.t1 no inmemory

5.3 collect statistics

SQL > EXEC DBMS_STATS.GATHER_TABLE_STATS ('CJC','T1',estimate_percent= > 100m cascade = > TRUE)

5.4 View the execution plan

SQL > set autotrace on

SQL > select count (object_id) from T1

COUNT (OBJECT_ID)

-

72481

When inmemory is enabled in T1 table, consistent gets is reduced from 1412 to 2 and Cost is reduced from 392to 16. The performance is improved obviously.

6 inquire about relevant information

-V$INMEMORY_AREA, V$IM_SEGMENTS, V$IM_COLUMN_LEVEL

SELECT NAME, VALUE / (1024 * 1024 * 1024) "SIZE_IN_GB"

FROM V$SGA

WHERE NAME LIKE'% Mem%'

SELECT 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

SELECT OWNER

SEGMENT_NAME

Bytes

INMEMORY_SIZE

POPULATE_STATUS

BYTES_NOT_POPULATED

FROM V$IM_SEGMENTS

SELECT table_name

Segment_column_id

Column_name

Inmemory_compression

FROM v$im_column_level

Seven points for attention

1 inmemory_size cannot be less than 100m

SQL > startup

ORA-64353: in-memory area size cannot be less than 100MB

2 specify that the table should collect statistics in time when inmemory is enabled or disabled

Otherwise, the information in the execution plan is inaccurate.

For more information, please refer to the official documentation.

Https://docs.oracle.com/en/database/oracle/oracle-database/19/inmem/intro-to-in-memory-column-store.html#GUID-BFA53515-7643-41E5-A296-654AB4A9F9E7

Database In-Memory GuideIntroduction to Oracle Database In-Memory

Welcome to follow my Wechat official account "IT Little Chen" and learn and grow together!

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.