In memory Column Store
----------------------
1) Stores objects in memory in the new columnar
format.
2) This format enable scan / joins / aggregate to be
processed very fast when compared to traditional on disk formats.
3) Query can be done faster without indexes.
3) Beneficial for applications with few columns
returning many rows, rather than few rows with more columns.
4) The dba has to define which objects to be stored
in memory such as hot tables / partitions and more precisely frequently
accessed columns.
Advantages.
1) Queries run a lot faster, all data can be
populated in in-memory in compress columnar format, no index is used,
queries ran at least 100x faster.
2) DMLS are faster, alanytics indexes can be
eliminated. All but the indexes need for referential integrity
(primary key) can be replaced with scans of
the IM column store representation of the table when the tables fits in IM column store. 75% of indexes on an
inmemroy table can be removed which speed up dml statement.
3) When the rows in table are updated the
correscponding entries in the IMCU are marked stale. The row version recorded
in the journal is constructed from the buffer cache and is unaffected by the
subset of IMCU. IMCU synchronization is performed by IMCO/SMCO bg process with
the updated rows populated in the transactional journal.
4) Arbitary adhoc queries run faster since the table
behaves as if all columns are indexed.
5) Fully supports RAC and Multitenant architecture.
In Row format -> The tables which are accessed
frequent by otlp style queries operating on few rows returning many columns are
good candidate for buffer cache. These table not necessarily defined in
in-Memory.
In Dual format -> The tables which are frequently
accessed by analytical style queries operating on many rows and retruning only
few columns are good candidate for in-memory segments. if only some columns of
table are defined inmemroy then the queries that select any non-in memory will
fetch the data from the buffer cache and the fetch of columns which defined in
in-memory are fetched from IM column store. Any fetch by rowids will be done
using buffer cache. Any dml performed on these objects will be executed via
buffer cache.
1) Parameter inmemory_size (k,m or g)
2) It is a static parameter (instance restart
required).
3) Not managed by LRU , when there is no space in
IMcS the objects are partially storted in IMCS.
4) DBA can set priority on objects to define which
in memory object should be populated on priority.
5) Set the value of inmemory_size to a sum of all
the estimated size of in memory tables.
6) Use dbms_compression to use how well the objects
can be compressed.
7) Cluster table and IOT are not supported.
When the queries do not perform through the IM
column store even though the tables are defined as in memory segments
find whether non inmemory columns exists in these
in-memory tables using the view V$im_column_level view.
This views displays the tables with column defined
in memory and no inmemory.
Eg.
SQL> show parameter inmemory;
NAME TYPE
VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default string
inmemory_force string DEFAULT
inmemory_max_populate_servers integer 0
inmemory_query string ENABLE
inmemory_size big integer 0
inmemory_trickle_repopulate_servers_ integer
1
percent
optimizer_inmemory_aware boolean TRUE
SQL> alter system set inmemory_size=200M
scope=spfile;
System altered.
SQL> startup force;
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2932632 bytes
Variable Size 851443816 bytes
Database Buffers 4194304 bytes
Redo Buffers 5455872 bytes
In-Memory Area 209715200 bytes <<---------------------------- New
Database mounted.
Database opened.
SQL> create table emp inmemory as select * from
scott.emp where 1 =2;
Table created.
SQL> select table_name, INMEMORY ,
INMEMORY_PRIORITY, INMEMORY_COMPRESSION from dba_tables where table_name =
'EMP' AND owner = 'MANZOOR';
TABLE_NAME
INMEMORY INMEMORY INMEMORY_COMPRESS
-------------------- -------- --------
-----------------
EMP
ENABLED NONE FOR QUERY LOW
SQL> begin
2 for i in 1..50000 loop
3 insert into emp values (i,
dbms_random.string('U',10), 'SOFTWARE',5000,sysdate-1000,i+10000,i+30,25);
4 end loop;
5 commit;
6* end;
SQL> /
PL/SQL procedure successfully completed.
SQL> create table emp_no_memory as select * from
emp;
Table created.
SQL> select * from V$inmemory_area;
POOL ALLOC_BYTES USED_BYTES
POPULATE_STATUS CON_ID
-------------------------- ----------- ----------
-------------------------- ----------
1MB POOL 166723584 0 DONE 0
64KB POOL 25165824 0 DONE 0
SQL> select count(*) from manzoor.emp;
COUNT(*)
----------
50000
SQL> select count(*) from emp_no_memory;
COUNT(*)
----------
50000
SQL> select * from V$inmemory_area;
POOL ALLOC_BYTES USED_BYTES
POPULATE_STATUS CON_ID
-------------------------- ----------- ----------
-------------------------- ----------
1MB POOL 166723584 3145728 DONE 0
64KB POOL 25165824 131072 DONE 0
Let us see the xplan plan for both.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID
g59vz2u4cu404, child number 0
-------------------------------------
select count(*) from emp
Plan hash value: 2083865914
----------------------------------------------------------------------------
| Id |
Operation | Name |
Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
| 1 (100)| |
| 1 | SORT AGGREGATE | |
1 | | |
| 2 | TABLE ACCESS INMEMORY FULL| EMP | 1
| 1
(0)| 00:00:01 |
----------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID
596rjca765gt0, child number 0
-------------------------------------
select count(*) from emp_no_memory
Plan hash value: 2238429888
----------------------------------------------------------------------------
| Id |
Operation | Name | Rows
| Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | |
107 (100)| |
| 1 | SORT AGGREGATE | | 1 | |
|
| 2 | TABLE ACCESS FULL| EMP_NO_MEMORY | 50000
| 107
(0)| 00:00:01 |
----------------------------------------------------------------------------
As seen from the above we could see that the cost of
the table access which is
in inmemory is very very less.
SQL> select * from emp where empno = 34335;
EMPNO
ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
34335 XBMLHMYMXT
SOFTWARE 5000 23-DEC-12 44335
34365 25
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID
drqx25zfzux31, child number 0
-------------------------------------
select * from emp where empno = 34335
Plan hash value: 3956160932
-----------------------------------------------------------------------------------
| Id |
Operation | Name |
Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
| | 1 (100)| |
|* 1 | TABLE ACCESS INMEMORY FULL| EMP | 1
| 87 | 1
(0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -
inmemory("EMPNO"=34335)
filter("EMPNO"=34335)
SQL> select * from emp_no_memory where empno =
45637;
EMPNO
ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
45637
ELDUEJWRCK SOFTWARE 5000
23-DEC-12 55637 45667 25
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID
9rfcnzy63yf0w, child number 0
-------------------------------------
select * from emp_no_memory where empno = 45637
Plan hash value: 1132349240
-----------------------------------------------------------------------------------
| Id |
Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | |
| 107 (100)| |
|* 1 | TABLE ACCESS FULL| EMP_NO_MEMORY | 1 |
49 | 107 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -
filter("EMPNO"=45637)
Lets create index
SQL> create index idex_empno on emp_no_memory
(empno);
Index created.
SQL> select * from emp_no_memory where empno =
36473;
EMPNO
ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
36473
GSIDDJHDYK SOFTWARE 5000
23-DEC-12 46473 36503 25
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID
9zxx5fd7wwybt, child number 0
-------------------------------------
select * from emp_no_memory where empno = 36473
Plan hash value: 3709436832
-----------------------------------------------------------------------------------------------------
| Id |
Operation |
Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | |
| 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED|
EMP_NO_MEMORY | 1 | 49 |
2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDEX_EMPNO |
1 | | 1
(0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
access("EMPNO"=36473)
-- In memory cost is less even then the index is
created.
SQL> delete from emp where empno = 46789;
1 row deleted.
SQL> commit;
Commit complete.
SQL_ID
9ayzquqb8aryw, child number 0
-------------------------------------
delete from emp where empno = 46789
Plan hash value: 161811703
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | DELETE
STATEMENT | |
| | 1 (100)| |
| 1 | DELETE | EMP |
| | | |
|* 2 | TABLE ACCESS INMEMORY FULL| EMP | 1
| 13 | 1
(0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
inmemory("EMPNO"=46789)
filter("EMPNO"=46789)
-- The dml scans the inmemroy.
SQL> update emp set ename = 'MANZOOR' WHERE empno
between 5000 and 10000;
5001 rows updated.
SQL> commit;
Commit complete.
update emp set ename = 'MANZOOR' WHERE empno between
5000 and 10000
Plan hash value: 1494045816
------------------------------------------------------------------------------------
| Id |
Operation | Name |
Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | UPDATE
STATEMENT |
| | |
1 (100)| |
| 1 | UPDATE | EMP |
| | | |
|* 2 | TABLE ACCESS INMEMORY FULL| EMP | 1
| 20 | 1
(0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
inmemory(("EMPNO">=5000 AND "EMPNO"<=10000))
filter(("EMPNO">=5000 AND "EMPNO"<=10000))
SQL> update emp_no_memory set ename = 'AHAMED'
where empno between 5000 and 10000;
5001 rows updated.
SQL> commit;
Commit complete.
SQL_ID
1h4pkrmzy8n1j, child number 0
-------------------------------------
update emp_no_memory set ename = 'AHAMED' where
empno between 5000 and 10000
Plan hash value: 3139015418
-----------------------------------------------------------------------------------
| Id |
Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | UPDATE
STATEMENT | | |
| 51 (100)| |
| 1 | UPDATE | EMP_NO_MEMORY | |
| | |
|* 2 | INDEX RANGE SCAN| IDEX_EMPNO |
5002 | 80032 | 13 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
access("EMPNO">=5000 AND "EMPNO"<=10000)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID
4c8dx35s30z5h, child number 0
-------------------------------------
select hiredate , count(*) from emp group by
hiredate
Plan hash value: 4067220884
------------------------------------------------------------------------------------
| Id |
Operation | Name |
Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT |
| | |
6 (100)| |
| 1 | HASH GROUP BY | |
1 | 9 | 6
(17)| 00:00:01 |
| 2 | TABLE ACCESS INMEMORY FULL| EMP | 1
| 9 | 5
(0)| 00:00:01 |
------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID
abgw2x09uzr9j, child number 0
-------------------------------------
select hiredate , count(*) from emp_no_memory group
by hiredate
Plan hash value: 2385702130
------------------------------------------------------------------------------------
| Id |
Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | |
| 109 (100)| |
| 1 | HASH GROUP BY | | 39 |
312 | 109 (2)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP_NO_MEMORY | 50000
| 390K| 107
(0)| 00:00:01 |
------------------------------------------------------------------------------------
SQL> alter table emp no inmemory;
Table altered.
SQL> alter table emp inmemory;
Table altered.
SQL> alter table emp inmemory priority critical;
Table altered.
SQL> alter table manzoor.emp no inmemory (sal);
Table altered.
SQL> alter table manzoor.emp no inmemory (job,
mgr);
Table altered.
SQL> select owner, table_name, COLUMN_NAME ,
INMEMORY_COMPRESSION from V$im_column_level where table_name = 'EMP';
OWNER TABLE_NAME COLUMN_NAME INMEMORY_COMPRESSION
------------------------------- --------------------
------------------------------- --------------------------
MANZOOR EMP EMPNO DEFAULT
MANZOOR EMP ENAME DEFAULT
MANZOOR EMP JOB NO INMEMORY
MANZOOR EMP MGR NO INMEMORY
MANZOOR EMP HIREDATE DEFAULT
MANZOOR EMP SAL NO INMEMORY
MANZOOR EMP COMM DEFAULT
MANZOOR EMP DEPTNO DEFAULT
8 rows selected.
SQL> alter table manzoor.emp inmemory (sal, job,
mgr);
Table altered.
SQL> select owner, table_name, COLUMN_NAME ,
INMEMORY_COMPRESSION from V$im_column_level where table_name = 'EMP';
OWNER TABLE_NAME COLUMN_NAME INMEMORY_COMPRESSION
------------------------------ --------------------
------------------------------- --------------------------
MANZOOR EMP EMPNO DEFAULT
MANZOOR EMP ENAME DEFAULT
MANZOOR EMP JOB DEFAULT
MANZOOR EMP MGR DEFAULT
MANZOOR EMP HIREDATE DEFAULT
MANZOOR EMP SAL DEFAULT
MANZOOR EMP COMM DEFAULT
MANZOOR EMP DEPTNO DEFAULT
8 rows selected.
-->
SQL> select segment_name, bytes from dba_segments
where segment_name = 'EMP' AND OWNER = 'MANZOOR';
SEGMENT_NAME BYTES
------------------------------ ----------
EMP 4194304
SQL> select SEGMENT_NAME, BYTES, INMEMORY_SIZE,
BYTES_NOT_POPULATED, INMEMORY_PRIORITY from V$im_segments where segment_name =
'EMP';
SEGMENT_NAME BYTES INMEMORY_SIZE
BYTES_NOT_POPULATED INMEMORY
------------------------------ ----------
------------- ------------------- --------
EMP 4194304 3276800 0 CRITICAL
The bytes shows the size occupied in the disk space,
in_memory size shows the size which is occupied by the
table in the In memory strore, the bytes not
populted is showing the bytes which is not populated yet in the in memory.
Here the bytes not populated is showing as 0 which
means full table is in the in memory store.
Priority - NONE, LOW, MEDIUM , HIGH , critical
- This is to manage how the data has to be populated
on the IMCs, if it is none then only when the table is queried the data will be
populated to the IMCS.
- Low / medium / high / critical ( will be populated
on the IMCs cycle which is of 2 mins), also when the IM is full the segment
with higher priority will be retained in the IMCs.
SQL> alter table manzoor.emp_no_memory inmemory;
Table altered.
SQL> select table_name, INMEMORY_PRIORITY ,
INMEMORY_COMPRESSION from dba_tables where table_name = 'EMP_NO_MEMORY';
TABLE_NAME
INMEMORY INMEMORY_COMPRESS
-------------------- -------- -----------------
EMP_NO_MEMORY
NONE FOR QUERY LOW
-- The default priority is NONE
SQL> select SEGMENT_NAME, BYTES, INMEMORY_SIZE,
BYTES_NOT_POPULATED, INMEMORY_PRIORITY from V$im_segments where segment_name =
'EMP_NO_MEMORY';
no rows selected
-- Not populated yet, change the priority.
SQL> alter table manzoor.emp_no_memory inmemory
priority low;
Table altered.
SQL> exec dbms_lock.sleep(120);
PL/SQL procedure successfully completed.
SQL> select SEGMENT_NAME, BYTES, INMEMORY_SIZE,
BYTES_NOT_POPULATED, INMEMORY_PRIORITY from V$im_segments where segment_name =
'EMP_NO_MEMORY';
SEGMENT_NAME BYTES INMEMORY_SIZE
BYTES_NOT_POPULATED INMEMORY
------------------------------ ----------
------------- ------------------- --------
EMP_NO_MEMORY 4194304 3276800 0 LOW
--> We can see that now the data is popluated in
2 mins cycle.
SQL> alter table emp_no_memory inmemory priority
high;
Table altered.
SQL> exec dbms_lock.sleep(120);
PL/SQL procedure successfully completed.
SQL> select SEGMENT_NAME, BYTES, INMEMORY_SIZE,
BYTES_NOT_POPULATED, INMEMORY_PRIORITY from V$im_segments;
SEGMENT_NAME BYTES INMEMORY_SIZE
BYTES_NOT_POPULATED INMEMORY
-------------------- ---------- -------------
------------------- --------
EMP_NO_MEMORY 4194304 3276800 0 HIGH
EMP
394264576 99549184 0 CRITICAL
Large segments are not populated to IM within one
shot, The SMCO and Wnnn bg process populate the segment progressively.
Table eviction only happens when a table is disabled
for the IM column store using alter table command. Since
Alter is ddl all the dependent cursors will get
invalidated.
IM column store compression
Use memcompress for defining compress level.
No memcompress -> No compression at all.
Memcompress for query high / low -
Uses a compression algorition to fit for better
query performance than the space using in IMCS, where clause predicates can be applied
directly to the data .
Memcompress for capacity low –
Lower space used in in memory column store, for
capacity low is balance for high compression
and query performance.
Memcompress for catacity high –
Provide high level of compression and thus provide
high storage in in memory. This required
lot more cpu as data compressed for capacity high
must be decompressed to a certain degree before
where clause predicated can be applied.
Memcompress for DML
Below is the default value of compression level
which is for query low.
SQL> select table_name, INMEMORY_COMPRESSION from
dba_tables where owner = 'MANZOOR' AND TABLE_NAME like 'EMP%';
TABLE_NAME INMEMORY_COMPRESS
--------------------------- -----------------
EMP_NO_MEMORY FOR QUERY LOW
EMP FOR QUERY LOW
SQL> alter table manzoor.emp inmemory memcompress
for query high;
Table altered.
SQL> alter table manzoor.emp_no_memory inmemory
memcompress for capacity high;
Table altered.
SQL> select table_name, INMEMORY_COMPRESSION from
dba_tables where owner = 'MANZOOR' AND TABLE_NAME like 'EMP%';
TABLE_NAME INMEMORY_COMPRESS
------------------------------ -----------------
EMP_NO_MEMORY FOR CAPACITY HIGH
EMP FOR QUERY HIGH
SQL> select SEGMENT_NAME, BYTES, INMEMORY_SIZE,
BYTES_NOT_POPULATED, INMEMORY_PRIORITY from V$im_segments;
no rows selected
SQL> select * from V$inmemory_area;
POOL ALLOC_BYTES USED_BYTES
POPULATE_STATUS CON_ID
-------------------------- ----------- ----------
-------------------------- ----------
1MB POOL 166723584 0 DONE 0
64KB POOL 25165824 0 DONE 0
-- Its not listing anything ???
When the compression level is updated, the
information is updated in the data dictionary with the new segment level
compression, also the priority will be set as NONE, The IMCU data repopulation
with the new compression algorithm/level will be updated only when the segment
is moved / manually populated.
When none of the actions are used, the new
compression level are only reflected in new populated data.
SQL> select table_name, INMEMORY_PRIORITY,
INMEMORY_COMPRESSION from dba_tables where owner = 'MANZOOR' AND TABLE_NAME
LIKE 'EMP%';
TABLE_NAME INMEMORY_PRIORITY INMEMORY_COMPRESS
------------------------------
------------------------------ -----------------
EMP_NO_MEMORY NONE FOR CAPACITY HIGH
EMP NONE FOR QUERY LOW
SQL> select count(*) from emp;
COUNT(*)
----------
6399872
SQL> select * from V$inmemory_area;
POOL ALLOC_BYTES USED_BYTES
POPULATE_STATUS CON_ID
-------------------------- ----------- ----------
-------------------------- ----------
1MB POOL 166723584 0 POPULATING 0
64KB POOL 25165824 0 POPULATING 0
-- Status showing as populating.
SQL> /
POOL ALLOC_BYTES USED_BYTES
POPULATE_STATUS CON_ID
-------------------------- ----------- ----------
-------------------------- ----------
1MB POOL 166723584 13631488 POPULATING 0
64KB POOL 25165824 262144 POPULATING 0
SQL> /
POOL ALLOC_BYTES USED_BYTES
POPULATE_STATUS CON_ID
-------------------------- ----------- ----------
-------------------------- ----------
1MB POOL 166723584 96468992 DONE 0
64KB POOL 25165824 851968 DONE 0
SQL> select SEGMENT_NAME, BYTES, INMEMORY_SIZE,
BYTES_NOT_POPULATED, INMEMORY_PRIORITY from V$im_segments;
SEGMENT_NAME BYTES INMEMORY_SIZE
BYTES_NOT_POPULATED INMEMORY
-------------------- ---------- -------------
------------------- --------
EMP
394264576 97320960 0 NONE
SQL> alter table manzoor.emp inmemory memcompress
for query low;
Table altered.
SQL> select SEGMENT_NAME, BYTES, INMEMORY_SIZE,
BYTES_NOT_POPULATED, INMEMORY_PRIORITY from V$im_segments;
no rows selected
SQL> alter table emp inmemory memcompress for
query high priority critical;
Table altered.
SQL> alter table emp_no_memory inmemory
memcompress for capacity high priority high;
Table altered.
SQL> select table_name, INMEMORY_PRIORITY,
INMEMORY_COMPRESSION from dba_tables where owner = 'MANZOOR' AND TABLE_NAME
LIKE 'EMP%';
TABLE_NAME INMEMORY_PRIORITY INMEMORY_COMPRESS
------------------------------
------------------------------ -----------------
EMP_NO_MEMORY HIGH FOR CAPACITY HIGH
EMP CRITICAL FOR QUERY HIGH
SQL> exec dbms_lock.sleep(120);
PL/SQL procedure successfully completed.
SQL> select * from V$inmemory_area;
POOL ALLOC_BYTES USED_BYTES
POPULATE_STATUS CON_ID
-------------------------- ----------- ----------
-------------------------- ----------
1MB POOL 166723584 0 DONE 0
64KB POOL 25165824 0 DONE 0
Now again we have reset the compression level with
the prioirty level as well, so the data is populated automatically (2 mins
cycle)
You can use compression advisor to find out how much
space is required in IMCS for a particular level
of compression.
SQL> alter table manzoor.emp no inmemory;
Table altered
SQL> alter table manzoor.emp_no_memory no
inmemory;
Table altered.
SQL> select * from V$inmemory_area;
POOL ALLOC_BYTES USED_BYTES
POPULATE_STATUS CON_ID
-------------------------- ----------- ----------
-------------------------- ----------
1MB POOL 166723584 0 DONE 0
64KB POOL 25165824 0 DONE 0
Lets check how much space is required in in memroy
area for query high level for emp_no_memory table.
set serveroutput on
declare
blkcnt_uncmp pls_integer;
blkcnt_cmp
pls_integer;
row_uncmp
pls_integer;
row_cmp
pls_integer;
cmp_ratio
number;
comptype_str varchar2(100);
begin
DBMS_COMPRESSION.GET_COMPRESSION_RATIO (
scratchtbsname => 'READ_ONLY_TBLS',
ownname => 'MANZOOR',
objname => 'EMP_NO_MEMORY',
subobjname => NULL,
comptype =>
dbms_compression.comp_inmemory_query_high,
blkcnt_cmp => blkcnt_cmp,
blkcnt_uncmp => blkcnt_uncmp,
row_cmp => row_cmp,
row_uncmp => row_uncmp,
cmp_ratio => cmp_ratio,
comptype_str => comptype_str,
subset_numrows => 10000,
objtype => 1
);
dbms_output.put_line('Block count uncompressed = ' || blkcnt_uncmp);
dbms_output.put_line('Block count compressed = ' || blkcnt_cmp);
dbms_output.put_line('Row count Per block uncompressed = ' ||
row_uncmp);
dbms_output.put_line('Row count Per Block compressed = ' || row_cmp);
dbms_output.put_line('Compression Ratio = ' || cmp_ratio);
dbms_output.put_line('Compression type = ' || comptype_str);
end;
/
Block count
uncompressed = 368
Block count compressed = 0
Row count
Per block uncompressed = 135
Row count
Per Block compressed = 0
Compression Ratio = 1.3
Compression type = "In-memory
Memcompress Query High"
PL/SQL procedure successfully completed.
Compression ratio is 1.3 Percent , hece the IM
column store space is needed about 1 Byte for 1.3 byte in disk space.
SQL> alter table manzoor.emp_no_memory inmemory
memcompress for query high priority high;
Table altered.
SQL> exec dbms_lock.sleep(120);
PL/SQL procedure successfully completed.
SQL> select SEGMENT_NAME, BYTES, INMEMORY_SIZE,
BYTES_NOT_POPULATED, INMEMORY_PRIORITY, BYTES/INMEMORY_SIZE "COMPRESSION
RATIO" from V$im_segments;
SEGMENT_NAME BYTES INMEMORY_SIZE
BYTES_NOT_POPULATED INMEMORY_PRIORITY Compression Ratio
-------------------- ---------- -------------
------------------- ------------------------------ -------------------
EMP_NO_MEMORY 4194304 2228224 0 HIGH 1.88235294
-- Compression ratio can be caluated using above
query.
Highest compression ratio the best , lowest the worst
compression ratio.
SQL> show parameter inmem;
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
_inmemory_populate_wait boolean FALSE
inmemory_clause_default string
inmemory_force string DEFAULT
inmemory_max_populate_servers integer 1
inmemory_query string ENABLE
inmemory_size big integer 200M
inmemory_trickle_repopulate_servers_ integer 1
percent
optimizer_inmemory_aware boolean TRUE
inmemory_clause_default - we can use this parameter
to assign default in memory clause for new table.
SQL> alter system set inmemory_clause_default =
'inmemory memcompress for query low priority low';
System altered.
SQL> show parameter inmemory_clause_default;
NAME TYPE VALUE
------------------------------------ -----------
-----------------------------------------------
inmemory_clause_default string inmemory memcompress for query low
priority low
SQL> select table_name, INMEMORY_PRIORITY,
INMEMORY_COMPRESSION from dba_tables where table_name = 'TEST_TBL';
TABLE_NAME INMEMORY_PRIORITY INMEMORY_COMPRESS
------------------------------
------------------------------ -----------------
TEST_TBL NONE FOR QUERY LOW
SQL> create table test_tbl (id number) inmemory;
Table created.
SQL> select table_name, INMEMORY_PRIORITY,
INMEMORY_COMPRESSION from dba_tables where table_name = 'TEST_TBL';
TABLE_NAME INMEMORY_PRIORITY INMEMORY_COMPRESS
------------------------------
------------------------------ -----------------
TEST_TBL LOW FOR QUERY LOW
Inheritance , can be set in tablespace level / table
level / partition level / column level.
-- In memory tables do not requied analytics indexes
any more, so drop it if there is any.
--- Prodedure
-->
Mark the analytic style index invisible.
-->
confirm workload no longer befefits of those indexes.
-->
Drop indexes.
Note : Just like exadata storage indexes , the IMCS
records the minimum and maximum values for each of the imus or extents.
SQL> select owner, segment_name, bytes,
INMEMORY_SIZE , BYTES_NOT_POPULATED from V$im_segments;
OWNER SEGMENT_NAME BYTES INMEMORY_SIZE
BYTES_NOT_POPULATED
-------------------- -------------------- ----------
------------- -------------------
MANZOOR
EMP
1610612736 337575936 100442112
SQL> show parameter inmemory_query;
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
inmemory_query string ENABLE
SQL> set timing on;
SQL> select max(SAL), max(comm), max(hiredate)
from emp;
MAX(SAL)
MAX(COMM) MAX(HIRED
---------- ---------- ---------
60000 50030 23-DEC-12
Elapsed: 00:00:02.47
select max(SAL), max(comm), max(hiredate) from emp
Plan hash value: 2083865914
------------------------------------------------------------------------------------
| Id |
Operation | Name |
Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
| | 2019 (100)| |
| 1 | SORT AGGREGATE | |
1 | 18 | | |
| 2 | TABLE ACCESS INMEMORY FULL| EMP |
6399K| 109M| 2019
(3)| 00:00:01 |
------------------------------------------------------------------------------------
14 rows selected.
select b.name, a.value from V$sesstat a, V$statname
b where upper(b.name) like 'IM%' and a.value > 0 and a.sid = 60 order by 2
SQL> alter session set inmemory_query = DISABLE;
Session altered.
Elapsed: 00:00:00.05
SQL> select max(SAL), max(comm), max(hiredate)
from emp;
MAX(SAL) MAX(COMM) MAX(HIRED
---------- ---------- ---------
60000 50030 23-DEC-12
Elapsed: 00:00:19.05
-- We could see that difference on the query
performance, query which executes on IM is very faster when compared to the
query
which executes again buffer cache.
IMCU pruning
SQL> select * from emp where empno = 43334;
EMPNO
ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
43334
DKOXTSQXFR SOFTWARE 5000
23-DEC-12 53334 43364 25
43334
DKOXTSQXFR SOFTWARE 5000
23-DEC-12 53334 43364 25
43334
DKOXTSQXFR SOFTWARE 5000
23-DEC-12 53334 43364 25
43334
DKOXTSQXFR SOFTWARE 5000
23-DEC-12 53334 43364 25
Below is the IMCU columnar format, as we could see
that the first line represnets the minimum and the maximum value found in that
particular column , so for eg. when we search for a value in the where clause
predicate that value (eg. 43334 as per above) will be compared in the col-1 (eg
empno column) to match whether this value is falling between the min and the
max value if it is not there then the entire IMCU will be skipped thus save
lots of IO'S.
imcu-1
COL-1 col-2 col-3
400-1000 C-J 01-Jan-2010 - 23-Feb-2012
456 C 01-Jan-2010
876 F 25-Mar-2011
987 H 19-Dec-2011
1000 J 23-Feb-2012
iMCU Pruning applies to queries using predicates
filters on single column such as (<>,=, <=, >=, <, >, null,
not null, like, substring).
You can find the min value and max values of the
columns in the IMCU's using below query
SQL> desc emp;
Name Null? Type
----------------- -------- ------------
EMPNO NUMBER(10)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> select column_number, MINIMUM_VALUE,
MAXIMUM_VALUE from V$im_col_cu a, dba_objects b where a.objd = b.data_object_id
and b.object_name = 'EMP'
2 group by column_number, MINIMUM_VALUE ,
MAXIMUM_VALUE order by 1
3 /
COLUMN_NUMBER MINIMUM_VALUE MAXIMUM_VALUE
------------- ------------------------------
------------------------------
1
C102 C305642B
1
C102 C306
1
C114 C3056446
2
41414147554447584D44
5A5A5A55444541474455
2
41414147554447584D44
5A5A5A59535750414447
2
414142564D43444E534E
5A5A5A5348564C475145
3
534F465457415245 534F465457415245
4
C233 C233
5
78700C170B070F
78700C170B0735
6
C3020102 C306642B
6
C3020102 C307
6
C3020114 C3066446
7
C120 C3056449
7
C120 C306011F
7
C132 C3056464
8
C11A C11A
16 rows selected.
select b.name, a.value from V$sesstat a, V$statname
b where b.name in
('IM scan segments minmax eligible','IM scan CUs
pruned','IM scan CUs optimized read','IM scan CUs predicates optimized')
and a.value > 0 and a.sid = 35 order by 2;
Optimizer will favor the IM only when there is no
index the column predicate or if there is only multiple column index available.
SQL> SELECT TABLE_NAME FROM DICT WHERE TABLE_NAME
LIKE 'V$IM%' OR TABLE_NAME LIKE 'V$%INMEM%';
TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
V$IM_COLUMN_LEVEL
V$IM_COL_CU
V$IM_HEADER
V$IM_SEGMENTS
V$IM_SEGMENTS_DETAIL
V$IM_SEG_EXT_MAP
V$IM_SMU_CHUNK
V$IM_SMU_HEAD
V$IM_TBS_EXT_MAP
V$IM_USER_SEGMENTS
V$INMEMORY_AREA
V$SCHEDULER_INMEM_MDINFO
V$SCHEDULER_INMEM_RTINFO
If there is heavy dml activity and then the immeory
option is changed then it will take time to populate, in that case you can
re-org the table to populate the table faster.
In RAC,
You can distribute the table accorss RAc nodes, eg.
if a 2 node cluster you can say distribute auto which mean oracle will
automatically distibute some values to one node and the other to the next node.
You can also distribute using BY ROWID RANGE, BY PARTITION
or BY SUBPARTITION .
No duplicate -> no duplicate of data will be kept
in the im store. i.e. only one copy will be there. (Usefull for larger tabls)
Duplicate
-> duplicate will be kep in the im store. i.e you will be having 2
copies. each in 1 node. (Usefull for smaller tables)
Data pump
Exports the inmemory attributes of the table as
well.
While importing we can keep or does not keep
->
impdp transform=inmemory:Y|n
->
impdp transform=inmemory_clause:"NO INMEMROY"
