By Franck Pachot
.
Oracle In-Memory is an hybrid solution: an In-Memory Column Store in addition to the traditional Row Store.
In the IMCS, data is stored in IMCU (In-memory compression units) and metadata is in SMU (Snapshot Metadata Units)
In the row store, data is stored in datafile extents and metadata is stored in the dictionary (and in datafile header since Locally managed Tablespaces).
Let’s see how they map to eachothers.
Create table In Memory
I’m creating a 2 million rows table in a LMT tablespace with autoallocate extent size.
create table DEMO inmemory tablespace USERS as select rownum num,mod(rownum,10) ten from xmltable('1 to 2000000');
and as in the previous post, I ensure that it’s populated in memory.
V$IM_SMU_HEAD
I can check the the IM Compression units from the SMU headers that show each IMCU with number of rows:
SQL> select objd,tsn,startdba,extent_cnt,block_cnt,load_scn,itl_cnt,total_rows,invalid_rows from V$IM_SMU_HEAD;
OBJD TSN STARTDBA EXTENT_CNT BLOCK_CNT LOAD_SCN ITL_CNT TOTAL_ROWS INVALID_ROWS
---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ------------
107958 6 1572875 22 5 941292582 255 491079 0
107958 6 1573890 8 126 941292582 255 561354 0
107958 6 1574914 8 126 941292582 255 524160 0
107958 6 1575938 7 126 941292582 255 423407 0
You see that they reference extent information (DBA is Data Block Address – file number and block id).
It’s not obvious here, but it’s relative DBA, with relative file number, and this is why you also have the tablespace number to absolutely identify a block in the database.
DBA_EXTENTS and V$IM_TBS_EXT_MAP
The Columns Store IMCUs are mapped to Row Store extents. One extent can populate several IMCUs but one IMCU contains data from only one extent. The mapping is available in V$IM_TBS_EXT_MAP
I show you how I join it to DBA_EXTENTS, using the START_DBA and END_DBA (and using dbms_utility to convert DBA_EXTENTS relative file number and block id to DBA). I get the data object id from DBA_OBJECTS:
SQL> break on imcu_addr skip 1 duplicates
SQL> select * from
(
select segment_name,file_id,block_id,blocks,bytes/1024/1024 MBytes
,dbms_utility.make_data_block_address(relative_fno,block_id) extent_start_dba
,dbms_utility.make_data_block_address(relative_fno,block_id+blocks-1) extent_end_dba
,(select data_object_id from dba_objects where owner=user and object_name='DEMO' and object_type='TABLE') DATAOBJ
from dba_extents where owner=user and segment_name='DEMO'
) e join (
select start_dba,end_dba,dataobj,to_char(imcu_addr,'0XXXXXXXXXXXXXXX') imcu_addr,len/1024/1024,to_char(smu_addr,'0XXXXXXXXXXXXXXX') smu_addr from V$IM_TBS_EXT_MAP
) i on ( e.dataobj=i.dataobj and (i.start_dba between e.extent_start_dba and e.extent_end_dba) and (i.end_dba between e.extent_start_dba and e.extent_end_dba) )
order by 1,2,3;
SEGMENT FILE_ID BLOCK_ID BLOCKS MBYTES _START_DBA _END_DBA DATAOBJ START_DBA END_DBA DATAOBJ IMCU_ADDR LEN/1024/1024 SMU_ADDR
------- ------- -------- ------ ------ ---------- -------- ------- --------- ------- ------- ----------------- ------------- -----------------
DEMO 5 1572872 8 .0625 1572872 1572879 107958 1572875 1572879 107958 0000000067FFFDD8 7 0000000349F44770
DEMO 5 1572880 8 .0625 1572880 1572887 107958 1572880 1572887 107958 0000000067FFFDD8 7 0000000349F44770
DEMO 5 1572888 8 .0625 1572888 1572895 107958 1572889 1572895 107958 0000000067FFFDD8 7 0000000349F44770
DEMO 5 1572896 8 .0625 1572896 1572903 107958 1572896 1572903 107958 0000000067FFFDD8 7 0000000349F44770
DEMO 5 1572904 8 .0625 1572904 1572911 107958 1572905 1572911 107958 0000000067FFFDD8 7 0000000349F44770
DEMO 5 1572912 8 .0625 1572912 1572919 107958 1572912 1572919 107958 0000000067FFFDD8 7 0000000349F44770
DEMO 5 1572920 8 .0625 1572920 1572927 107958 1572921 1572927 107958 0000000067FFFDD8 7 0000000349F44770
DEMO 5 1572928 8 .0625 1572928 1572935 107958 1572928 1572935 107958 0000000067FFFDD8 7 0000000349F44770
DEMO 5 1572936 8 .0625 1572936 1572943 107958 1572937 1572943 107958 0000000067FFFDD8 7 0000000349F44770
DEMO 5 1572944 8 .0625 1572944 1572951 107958 1572944 1572951 107958 0000000067FFFDD8 7 0000000349F44770
DEMO 5 1572952 8 .0625 1572952 1572959 107958 1572953 1572959 107958 0000000067FFFDD8 7 0000000349F44770
DEMO 5 1572960 8 .0625 1572960 1572967 107958 1572960 1572967 107958 0000000067FFFDD8 7 0000000349F44770
DEMO 5 1572968 8 .0625 1572968 1572975 107958 1572969 1572975 107958 0000000067FFFDD8 7 0000000349F44770
DEMO 5 1572976 8 .0625 1572976 1572983 107958 1572976 1572983 107958 0000000067FFFDD8 7 0000000349F44770
DEMO 5 1572984 8 .0625 1572984 1572991 107958 1572985 1572991 107958 0000000067FFFDD8 7 0000000349F44770
DEMO 5 1572992 8 .0625 1572992 1572999 107958 1572992 1572999 107958 0000000067FFFDD8 7 0000000349F44770
DEMO 5 1573120 128 1 1573120 1573247 107958 1573122 1573247 107958 0000000067FFFDD8 7 0000000349F44770
DEMO 5 1573248 128 1 1573248 1573375 107958 1573250 1573375 107958 0000000067FFFDD8 7 0000000349F44770
DEMO 5 1573376 128 1 1573376 1573503 107958 1573378 1573503 107958 0000000067FFFDD8 7 0000000349F44770
DEMO 5 1573504 128 1 1573504 1573631 107958 1573506 1573631 107958 0000000067FFFDD8 7 0000000349F44770
DEMO 5 1573632 128 1 1573632 1573759 107958 1573634 1573759 107958 0000000067FFFDD8 7 0000000349F44770
DEMO 5 1573760 128 1 1573760 1573887 107958 1573762 1573887 107958 0000000067FFFDD8 7 0000000349F44770
DEMO 5 1573888 128 1 1573888 1574015 107958 1573890 1574015 107958 00000000686FFDD8 8 0000000349F54770
DEMO 5 1574016 128 1 1574016 1574143 107958 1574018 1574143 107958 00000000686FFDD8 8 0000000349F54770
DEMO 5 1574144 128 1 1574144 1574271 107958 1574146 1574271 107958 00000000686FFDD8 8 0000000349F54770
DEMO 5 1574272 128 1 1574272 1574399 107958 1574274 1574399 107958 00000000686FFDD8 8 0000000349F54770
DEMO 5 1574400 128 1 1574400 1574527 107958 1574402 1574527 107958 00000000686FFDD8 8 0000000349F54770
DEMO 5 1574528 128 1 1574528 1574655 107958 1574530 1574655 107958 00000000686FFDD8 8 0000000349F54770
DEMO 5 1574656 128 1 1574656 1574783 107958 1574658 1574783 107958 00000000686FFDD8 8 0000000349F54770
DEMO 5 1574784 128 1 1574784 1574911 107958 1574786 1574911 107958 00000000686FFDD8 8 0000000349F54770
DEMO 5 1574912 128 1 1574912 1575039 107958 1574914 1575039 107958 00000000626FFDA8 7 0000000339F44710
DEMO 5 1575040 128 1 1575040 1575167 107958 1575042 1575167 107958 00000000626FFDA8 7 0000000339F44710
DEMO 5 1575168 128 1 1575168 1575295 107958 1575170 1575295 107958 00000000626FFDA8 7 0000000339F44710
DEMO 5 1575296 128 1 1575296 1575423 107958 1575298 1575423 107958 00000000626FFDA8 7 0000000339F44710
DEMO 5 1575424 128 1 1575424 1575551 107958 1575426 1575551 107958 00000000626FFDA8 7 0000000339F44710
DEMO 5 1575552 128 1 1575552 1575679 107958 1575554 1575679 107958 00000000626FFDA8 7 0000000339F44710
DEMO 5 1575680 128 1 1575680 1575807 107958 1575682 1575807 107958 00000000626FFDA8 7 0000000339F44710
DEMO 5 1575808 128 1 1575808 1575935 107958 1575810 1575935 107958 00000000626FFDA8 7 0000000339F44710
DEMO 5 1575936 128 1 1575936 1576063 107958 1575938 1576063 107958 00000000620FFDA8 6 0000000339F34710
DEMO 5 1576064 128 1 1576064 1576191 107958 1576066 1576191 107958 00000000620FFDA8 6 0000000339F34710
DEMO 5 1576192 128 1 1576192 1576319 107958 1576194 1576319 107958 00000000620FFDA8 6 0000000339F34710
DEMO 5 1576320 128 1 1576320 1576447 107958 1576322 1576447 107958 00000000620FFDA8 6 0000000339F34710
DEMO 5 1576448 128 1 1576448 1576575 107958 1576450 1576575 107958 00000000620FFDA8 6 0000000339F34710
DEMO 5 1576576 128 1 1576576 1576703 107958 1576578 1576703 107958 00000000620FFDA8 6 0000000339F34710
DEMO 5 1576704 128 1 1576704 1576831 107958 1576706 1576764 107958 00000000620FFDA8 6 0000000339F34710
I used sqlplus ‘break’ in order to separate the 4 IMCUs
If you have partitioned tables, then you should add the tablespace number in the join.
I have 45 extents following the auto extent size algorithm: 16x64KB extents totalizing 1MB, then 29x1MB extents.
The ‘length’ is the size in the IMCS, compressed. Total is 28MB here.
This mapping will help the re-population of a single IMCU: only the mapped extents have to be full scanned. And this is the way we can know which part of the table is populated or not.
Currently, In-Memory Column store can be populated only from tables stored in the database, so the mapping with extents makes sense: full scan some extents in order to populate one IMCU. It’s possible that in future release we can populate IM from external tables. That would be a great feature for ETL datawarehouse loading. We will see then how it is mapped to the source. I hope to ear about that at OOW15.