[12c] inmemory 사용

1. enable inmemory

ALTER SYSTEM SET INMEMORY_FORCE=OFF;

ALTER SYSTEM SET INMEMORY_FORCE=DEFAULT;

alter system set inmemory_size=1G scope=spfile;  (<--> ALTER SYSTEM RESET INMEMORY_SIZE;)

shutdown immediate;

startup;

show parameter inmemory;

2. enable/disable inmemory query ( opt가 고려 )

-- System level

ALTER SYSTEM SET INMEMORY_QUERY=DISABLE;

ALTER SYSTEM SET INMEMORY_QUERY=ENABLE;

-- Session level

ALTER SESSION SET INMEMORY_QUERY=DISABLE;

ALTER SESSION SET INMEMORY_QUERY=ENABLE;

3. 사용

1) 테이블

CONN test/test@pdb1

CREATE TABLE im_tab (

  id  NUMBER

) INMEMORY;

CREATE TABLE noim_tab (

  id  NUMBER

) NO INMEMORY;

CREATE TABLE default_tab (

  id  NUMBER

);

ALTER TABLE IM_TAB NO INMEMORY;

ALTER TABLE NOIM_TAB INMEMORY MEMCOMPRESS FOR CAPACITY LOW;

ALTER TABLE DEFAULT_TAB INMEMORY PRIORITY HIGH;

COLUMN table_name FORMAT A20

SELECT table_name,

       inmemory,

       inmemory_priority,

       inmemory_distribute,

       inmemory_compression,

       inmemory_duplicate  

FROM   user_tables

ORDER BY table_name;

2) 컬럼

CREATE TABLE im_col_tab (

  id   NUMBER,

  col1 NUMBER,

  col2 NUMBER,

  col3 NUMBER,

  col4 NUMBER

) INMEMORY

INMEMORY MEMCOMPRESS FOR QUERY HIGH (col1, col2)

INMEMORY MEMCOMPRESS FOR CAPACITY HIGH (col3)

NO INMEMORY (id, col4);

CONN sys@pdb1 AS SYSDBA

SELECT table_name,

       segment_column_id,

       column_name,

       inmemory_compression

FROM   v$im_column_level

WHERE  owner = 'TEST'

and    table_name = 'IM_COL_TAB'

ORDER BY segment_column_id;

CONN test/test@pdb1

ALTER TABLE im_col_tab 

NO INMEMORY (col1, col2)

INMEMORY MEMCOMPRESS FOR CAPACITY HIGH (col3)

NO INMEMORY (id, col4);

CONN sys@pdb1 AS SYSDBA

SELECT table_name,

       segment_column_id,

       column_name,

       inmemory_compression

FROM   v$im_column_level

WHERE  owner = 'TEST'

and    table_name = 'IM_COL_TAB'

ORDER BY segment_column_id;

3) MV

CONN test/test@pdb1

CREATE TABLE t1 AS

  SELECT * FROM all_objects;

CREATE MATERIALIZED VIEW t1_mv INMEMORY 

  AS SELECT * FROM t1;

SELECT table_name,

       inmemory,

       inmemory_priority,

       inmemory_distribute,

       inmemory_compression,

       inmemory_duplicate  

FROM   user_tables

WHERE  table_name = 'T1_MV';

ALTER MATERIALIZED VIEW t1_mv

  INMEMORY MEMCOMPRESS FOR CAPACITY HIGH PRIORITY HIGH;

SELECT table_name,

       inmemory,

       inmemory_priority,

       inmemory_distribute,

       inmemory_compression,

       inmemory_duplicate  

FROM   user_tables

WHERE  table_name = 'T1_MV';

ALTER MATERIALIZED VIEW t1_mv NO INMEMORY;

SELECT table_name,

       inmemory,

       inmemory_priority,

       inmemory_distribute,

       inmemory_compression,

       inmemory_duplicate  

FROM   user_tables

WHERE  table_name = 'T1_MV';

4) 테이블스페이스

CONN sys@pdb1 AS SYSDBA

CREATE TABLESPACE new_ts

   DATAFILE '/u01/app/oracle/oradata/CDB1/datafile/pdb1/pdb1_new_ts.dbf' SIZE 10M 

   DEFAULT INMEMORY;

SELECT tablespace_name, 

       def_inmemory,

       def_inmemory_priority,

       def_inmemory_distribute,

       def_inmemory_compression,

       def_inmemory_duplicate

FROM   dba_tablespaces

ORDER BY tablespace_name;

ALTER TABLESPACE new_ts

  DEFAULT INMEMORY MEMCOMPRESS FOR CAPACITY HIGH;

SELECT tablespace_name, 

       def_inmemory,

       def_inmemory_priority,

       def_inmemory_distribute,

       def_inmemory_compression,

       def_inmemory_duplicate

FROM   dba_tablespaces

ORDER BY tablespace_name;

ALTER TABLESPACE new_ts

  DEFAULT NO INMEMORY;

SELECT tablespace_name, 

       def_inmemory,

       def_inmemory_priority,

       def_inmemory_distribute,

       def_inmemory_compression,

       def_inmemory_duplicate

FROM   dba_tablespaces

ORDER BY tablespace_name;

4. 관련 view

v$im_column_level

v$im_segments

v$im_user_segments

select display_name, value

from v$mystat m, v$statname n

where m.statistic# = n.statistic#

  and display_name like 'IM scan%';

SELECT OWNER, SEGMENT_NAME, INMEMORY_PRIORITY, INMEMORY_COMPRESSION FROM V$IM_SEGMENTS;