Applies to: Oracle database – 184.108.40.206
Mahesh Reddy M
Oracle 12c In-memory option is introduced in 220.127.116.11 patch set. This feature stores copies of tables and partitions, and other database objects in columnar format which is IM Column store. IM Column store is an optional area of the SGA and it is not replacement of buffer cache. Instead, both memory areas can store same data in different formats.
IM COLUMN Store performance Benefits:
If the database objects in IM Column store, database can more perform scans, joins and aggregates much faster than on disk. When In memory will give more performance
Querying small number of columns from large number of columns
Queries that scan large number of rows and applying filters
Queries that apply aggregate data
Queries that join a small table to large table(fact table)
Oracle 12c In Memory option tested on Dell infrastructure.
Operating system: RHEL6.5
SGA Size: 450G
In Memory size: 300G
Database Size: 1TB
I am enabling the inmemory option using inmemory_size parameter.
Sql> alter system set inmemory_size=300g scope=spfile;
It will effect only after instance restart.
We can find the size of inmemory using
SQL> show parameter inmemory;
NAME TYPE VALUE
------------------------ ------------ ------------------
inmemory_force string DEFAULT
inmemory_max_populate_servers integer 30
inmemory_query string ENABLE
inmemory_size big integer 300G
inmemory_trickle_repopulate_servers_ integer 10
optimizer_inmemory_aware Boolean TRUE
After that create tablespace using In Memory attribute
Sql> create tablespace quest datafile '+DATA’ size 10g default inmemory;
We can change any time inmemory parameters using alter statement
Sql> alter tablespace quest DEFAULT INMEMORY MEMCOMPRESS FOR CAPACITY HIGH;
After you can create any tables under this tablespace it will automatically store into IM Column store.
Using BMF7.0 we can load the data into particular tablespace. If we enable inmemory parameter at tablespace level before loading it will take less time compare to without inmemory attribute.
Please refer this link
After loading data, we can use the different compression techniques and priority levels to populate the data into IM Column store. Objects are populated into the IM column store either in a prioritized list immediately after the database is opened or after they are scanned (queried) for the first time.
Here are my runtime results against Dell Infra structure.
We can compress and populate the data into IM Column store using different methods.
The table h_partsupp which has 59.59 GB original data. We can use different compression techniques to populate the table objects into IM Column store and see the compression ratio.
Memcompress for query high
Memcomress for query Low
Memcompress for Capacity low
Memcompress for Capacity high
After that we can raise a query against the table and see the results of performance of inmemory and without In Memory.
Sql> select max (PS_SUPPLYCOST) from h_partsupp;
As per results, In Memory is giving more performance (185X) compare to without In Memory.
Study 2 :
The table h_part which has 29.27Gb original data. We can use different compression techniques to populate the table objects into IM Column store and see the compression ratio.
Sql> select P_NAME from quest.h_part where P_TYPE='SMALL BRUSHED NICKEL';