You Asked

Hi Team,

can you provide some information regarding in-memory database ?

As per my understanding i see that it works in same way of keeping object in buffer cache like keep pool etc.
in-memory is also provide same functionality .So , why i should go for in-memory rather than sticking to traditional buffer cache

Regards,
Krishna

and Connor said...

Here's a nice interview about it in plain terms

http://www.oracle.com/technetwork/issue-archive/2015/15-jan/o15asktom-2398997.html

and here is a more detailed look

https://www.oracle.com/us/assets/lad-2015-ses16248-pedregal-2604873.pdf

but if you want a "TL;DR":

Buffer cache - we read blocks into memory as those blocks are on disk, ie, in a row-format, so it is optimized for processing *rows* as quickly as possible.

In-memory - we read blocks into memory, and totally reformat them - we compress them, convert them into a column-based format, so it is optimized for reading large chunks of column data quickly.

Most analytic style queries rarely want the *rows* back from a database. They want to aggregate (sum, min, max, etc) huge numbers of rows for particular columns. A compressed, column-based format is ideal for that.

Rating

(2 ratings)

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

Comments

Product documentation link

Rajeshwaran, Jeyabal, February 13, 2018 - 5:40 am UTC

Also the in-memory Guide from Production documentation explains the dual format architecture in detail at these below links.
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/inmem/intro-to-in-memory-column-store.html#GUID-4D7DF329-6C4F-4717-8A79-386D33BB773C
then
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/inmem/in-memory-column-store-architecture.html#GUID-FF2499C5-C582-496A-B547-CFE49C84185E

Memory compression different at each column sets

Rajeshwaran, Jeyabal, March 18, 2019 - 12:28 pm UTC

Team,

was reading about enabling a subset of columns for inmemory column store at this below link.
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/inmem/populating-objects-in-memory.html#GUID-128BA141-FB78-4828-B3EE-6FDAB6E23E2E

the below demo was from Oracle 18c.
having a memory compression different for each columns - shows the details at V$IM_COLUMN_LEVEL but not in V$IM_SEGMENTS - is that right? please clarify.

demo@PDB1> create table t
  2  as
  3  select *
  4  from big_table ;

Table created.

demo@PDB1> alter table t
  2     inmemory memcompress for query( owner,object_id )
  3     inmemory memcompress for dml ( object_type)
  4     inmemory memcompress for query high( object_name )
  5     inmemory memcompress for capacity low( created )
  6     inmemory memcompress for capacity high( status )
  7     no inmemory( duplicated, sharded ) ;

Table altered.

demo@PDB1> select count(*) from t;

  COUNT(*)
----------
   1000000

demo@PDB1> show parameter inmemory_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_size                        big integer 512M
demo@PDB1> exec dbms_session.sleep(120);

PL/SQL procedure successfully completed.

demo@PDB1> select * from v$im_segments;

no rows selected
demo@PDB1> col owner format a10
demo@PDB1> col column_name format a20
demo@PDB1> select  owner,segment_column_id,column_name, inmemory_compression
  2  from v$im_column_level
  3  order by 2 ;

OWNER      SEGMENT_COLUMN_ID COLUMN_NAME          INMEMORY_COMPRESSION
---------- ----------------- -------------------- --------------------------
DEMO                       1 OWNER                FOR QUERY LOW
DEMO                       2 OBJECT_NAME          FOR QUERY HIGH
DEMO                       3 SUBOBJECT_NAME       DEFAULT
DEMO                       4 OBJECT_ID            FOR QUERY LOW
DEMO                       5 DATA_OBJECT_ID       DEFAULT
DEMO                       6 OBJECT_TYPE          FOR DML
DEMO                       7 CREATED              FOR CAPACITY LOW
DEMO                       8 LAST_DDL_TIME        DEFAULT
DEMO                       9 TIMESTAMP            DEFAULT
DEMO                      10 STATUS               FOR CAPACITY HIGH
DEMO                      11 TEMPORARY            DEFAULT
DEMO                      12 GENERATED            DEFAULT
DEMO                      13 SECONDARY            DEFAULT
DEMO                      14 NAMESPACE            DEFAULT
DEMO                      15 EDITION_NAME         DEFAULT
DEMO                      16 SHARING              DEFAULT
DEMO                      17 EDITIONABLE          DEFAULT
DEMO                      18 ORACLE_MAINTAINED    DEFAULT
DEMO                      19 APPLICATION          DEFAULT
DEMO                      20 DEFAULT_COLLATION    DEFAULT
DEMO                      21 DUPLICATED           NO INMEMORY
DEMO                      22 SHARDED              NO INMEMORY
DEMO                      23 CREATED_APPID        DEFAULT
DEMO                      24 CREATED_VSNID        DEFAULT
DEMO                      25 MODIFIED_APPID       DEFAULT
DEMO                      26 MODIFIED_VSNID       DEFAULT
DEMO                      27 ID                   DEFAULT

27 rows selected.

demo@PDB1>

March 20, 2019 - 4:02 am UTC

You have set the column metadata, but you still need to say "Yes, this table is in-memory"

SQL> create table t
  2      as
  3      select d.*
  4      from dba_objects d, ( select 1 from dual connect by level <= 20 );

Table created.

SQL>
SQL> alter table t
  2         inmemory memcompress for query( owner,object_id )
  3         inmemory memcompress for dml ( object_type)
  4         inmemory memcompress for query high( object_name )
  5         inmemory memcompress for capacity low( created )
  6         inmemory memcompress for capacity high( status )
  7         no inmemory( duplicated, sharded ) ;

Table altered.

SQL>
SQL> select count(*) from t;

  COUNT(*)
----------
   1564040

1 row selected.

SQL> select owner,segment_name,bytes,bytes_not_populated,populate_status from v$im_segments;

no rows selected

SQL> select  owner,segment_column_id,column_name, inmemory_compression
  2  from v$im_column_level
  3  order by 2 ;

OWNER      SEGMENT_COLUMN_ID COLUMN_NAME                    INMEMORY_COMPRESSION
---------- ----------------- ------------------------------ --------------------------
MCDONAC                    1 OWNER                          FOR QUERY LOW
MCDONAC                    2 OBJECT_NAME                    FOR QUERY HIGH
MCDONAC                    3 SUBOBJECT_NAME                 DEFAULT
MCDONAC                    4 OBJECT_ID                      FOR QUERY LOW
MCDONAC                    5 DATA_OBJECT_ID                 DEFAULT
MCDONAC                    6 OBJECT_TYPE                    FOR DML
MCDONAC                    7 CREATED                        FOR CAPACITY LOW
MCDONAC                    8 LAST_DDL_TIME                  DEFAULT
MCDONAC                    9 TIMESTAMP                      DEFAULT
MCDONAC                   10 STATUS                         FOR CAPACITY HIGH
MCDONAC                   11 TEMPORARY                      DEFAULT
MCDONAC                   12 GENERATED                      DEFAULT
MCDONAC                   13 SECONDARY                      DEFAULT
MCDONAC                   14 NAMESPACE                      DEFAULT
MCDONAC                   15 EDITION_NAME                   DEFAULT
MCDONAC                   16 SHARING                        DEFAULT
MCDONAC                   17 EDITIONABLE                    DEFAULT
MCDONAC                   18 ORACLE_MAINTAINED              DEFAULT
MCDONAC                   19 APPLICATION                    DEFAULT
MCDONAC                   20 DEFAULT_COLLATION              DEFAULT
MCDONAC                   21 DUPLICATED                     NO INMEMORY
MCDONAC                   22 SHARDED                        NO INMEMORY
MCDONAC                   23 CREATED_APPID                  DEFAULT
MCDONAC                   24 CREATED_VSNID                  DEFAULT
MCDONAC                   25 MODIFIED_APPID                 DEFAULT
MCDONAC                   26 MODIFIED_VSNID                 DEFAULT

26 rows selected.

SQL> alter table t inmemory;  <================

Table altered.

SQL> select count(*) from t;

  COUNT(*)
----------
   1564040

1 row selected.

SQL> select owner,segment_name,bytes,bytes_not_populated,populate_status from v$im_segments;

OWNER      SEGMENT_NA      BYTES BYTES_NOT_POPULATED POPULATE_STAT
---------- ---------- ---------- ------------------- -------------
MCDONAC    T           248389632           248389632 STARTED

1 row selected.

SQL> /

OWNER      SEGMENT_NA      BYTES BYTES_NOT_POPULATED POPULATE_STAT
---------- ---------- ---------- ------------------- -------------
MCDONAC    T           248389632                   0 COMPLETED

1 row selected.

SQL> select  owner,segment_column_id,column_name, inmemory_compression
  2  from v$im_column_level
  3  order by 2 ;

OWNER      SEGMENT_COLUMN_ID COLUMN_NAME                    INMEMORY_COMPRESSION
---------- ----------------- ------------------------------ --------------------------
MCDONAC                    1 OWNER                          FOR QUERY LOW
MCDONAC                    2 OBJECT_NAME                    FOR QUERY HIGH
MCDONAC                    3 SUBOBJECT_NAME                 DEFAULT
MCDONAC                    4 OBJECT_ID                      FOR QUERY LOW
MCDONAC                    5 DATA_OBJECT_ID                 DEFAULT
MCDONAC                    6 OBJECT_TYPE                    FOR DML
MCDONAC                    7 CREATED                        FOR CAPACITY LOW
MCDONAC                    8 LAST_DDL_TIME                  DEFAULT
MCDONAC                    9 TIMESTAMP                      DEFAULT
MCDONAC                   10 STATUS                         FOR CAPACITY HIGH
MCDONAC                   11 TEMPORARY                      DEFAULT
MCDONAC                   12 GENERATED                      DEFAULT
MCDONAC                   13 SECONDARY                      DEFAULT
MCDONAC                   14 NAMESPACE                      DEFAULT
MCDONAC                   15 EDITION_NAME                   DEFAULT
MCDONAC                   16 SHARING                        DEFAULT
MCDONAC                   17 EDITIONABLE                    DEFAULT
MCDONAC                   18 ORACLE_MAINTAINED              DEFAULT
MCDONAC                   19 APPLICATION                    DEFAULT
MCDONAC                   20 DEFAULT_COLLATION              DEFAULT
MCDONAC                   21 DUPLICATED                     NO INMEMORY
MCDONAC                   22 SHARDED                        NO INMEMORY
MCDONAC                   23 CREATED_APPID                  DEFAULT
MCDONAC                   24 CREATED_VSNID                  DEFAULT
MCDONAC                   25 MODIFIED_APPID                 DEFAULT
MCDONAC                   26 MODIFIED_VSNID                 DEFAULT

26 rows selected.

SQL>

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Product documentation link

Memory compression different at each column sets

More to Explore

Administration

In- Memory option versus buffer cache

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Product documentation link

Memory compression different at each column sets

More to Explore

Administration