1.IMC初始化设置
show parameter inmemory
alter system set inmemory_size=4g scope=spfile;
2.查询在imc中的objects
V$IM_SEGMENTS,v$IMC_SEGMENTS
3.查看表的imc属性
SELECT table_name, cache, inmemory_priority,
inmemory_distribute,inmemory_compression
FROM user_tables ;
4.查看IMC中对象,也可监控数据加载进度
set lines 1000 pages 1000
col owner for a15
col segment_name for a20
col inmemory_size for 99999999
col bytes for 99999999
col POPULATE_STATUS for a15
col INMEMORY_COMPRESSION for a20
select owner,segment_name,
sum(inmemory_size)/1024/1024 as "Mem_Size",
sum(bytes)/1024/1024 as "Disk_Size",
sum(BYTES_NOT_POPULATED)/1024/1024 as "BYTES_NOT_POPULATED",
POPULATE_STATUS
from v$im_segments
group by owner,segment_name,
POPULATE_STATUS,INMEMORY_COMPRESSION,INMEMORY_PRIORITY
order by 5 desc;
5.Put table in IMC
Examples:alter table user2.orders inmemory memcompress for query priority high;
--Then You could "Full scan the table:"
select /*+ full(s) noparallel (s )*/ count(*) from user2.orders s;
--分区表
alter table ima.sales1 modify partition SALES_2014 INMEMORY
--物化视图
ALTER MATERIALIZED VIEW mv_users INMEMORY;
--表空间tablespace
ALTER TABLESPACE tbs1 DEFAULT INMEMORY
MEMCOMPRESS FOR CAPACITY HIGH
PRIORITY LOW;
create table par_tab_for1
(id varchar2(20) not null primary key,
ud varchar2(20) not null,
da varchar2(30),
tc varchar(20)
)
partition by range(id)
(
partition p1 values less than (100) tablespace RECO INMEMORY memcompress for query priority medium,
partition p2 values less than (200) tablespace RECO1 INMEMORY memcompress for capacity low priority low,
partition p3 values less than (300) tablespace RECO2
);
6.小表放入IMC
>>Small table (default value is smaller than 64k) cannot be popluated into memory
alter system set "_inmemory_small_segment_threshold" = 0 scope=both;
7.将表从IMC中移除
alter table XXX no inmemory;
8.查看隐含参数
col NAME for a33
col KSPPITY for 99
col VALUE for a15
SELECT x.indx+1,
ksppinm as NAME,
ksppity,
ksppstvl as VALUE,
ksppstdf as isdefault
FROM x$ksppi x,
x$ksppsv y
WHERE x.indx = y.indx
AND x.inst_id = USERENV('Instance')
and upper(ksppinm) like '%INMEMORY%';
9.查看IMC中一个表的CU的数量
col OBJECT_NAME for a20
set linesize 1000 pagesize 1000
select object_name, count(*) from v$im_col_cu , dba_objects
where objd = object_id
and owner = 'TPCH'
and column_number = 1
group by object_name
order by 1;
10.Disable IMC query
ALTER SESSION set inmemory_query = enable;
ALTER SESSION set inmemory_query =disable;
11.select * from v$inmemory_area;