In Memory Database Concept….!!!!
As we are discussing the Oracle 12c new features In Memory is most popular feature of Oracle 12c.
Memory optimization is always my favorite topic since I started working on Oracle. Optimization means whole idea to wonder around SGA and tune to gain max DB performance.
And here tuning means what most used data should be read from memory instead of from hard disk.
So well tuned database almost 95% data request find required data already in MEMORY.
Now let’s talk about data storage raw / column format
Generally there are two types of databases OLTP & DSS
OLTP: Which operates on few rows but many columns and it works best on row format.
DSS: Accessing few columns of many rows, works best on the column format.
How it will be if we get both OLTP and DSS technique in single product
So here it is Oracle 12c In Memory based on dual format data store.
Generally data are stored on disk in row format only and whenever data requested for read / writes, they will be loaded into traditional Row Store (SGA – Buffer Cache). And whenever data requested for only read operation they will be populated into new In Memory Column Store. So this population includes transformation from row to columnar format.
So it means whenever transaction includes INSERTS, UPDATES or DELETES with commit, new data will be immediately and simultaneously appear row store and the In Memory Column Store. So both the store format transactionally consistent.
And most imp this approach doesn’t require more memory.
Advantages of this approach:
- There is no need to modify / change application. All existing applications run unchanged in new architecture.
- There is no need to modify the database. Oracle 12c In Memory option can be implemented without Database migration or table reorganization.
- There are no limits for database or table sizes. The Oracle Database 12c In- Memory option can be used with databases and systems of any size.
- Therefore there is no need to change the infrastructure. The new In Memory feature can be implemented on existing hardware.
This In Memory option is compatible with other features such as table compression, table encryption, and table partitioning also with RAC & Data Guard.
Now how to use this feature ….!!!!
This is very easy to use
Assign the value to new initialization parameter inmemory_size to define the size of the In Memory column store.
SQL> ALTER SYSTEM SET inmemory_size= 10G scope=spfile;
And now select table that you want to be available in te In Memory column store:
SQL> ALTER TABLE T1 INMEMORY;
And it’s done …. Ready to move!!!!
Note: As a static pool any changes to INMEMORY_SIZE will not take effect until DB instance restarted.
In Memory must have minimum size of 100MB.
INMEMORY attribute can be specified on a tablespace , table , (sub)partitioned or materialized view.
If enable for tablespace level then all tables and materialized views of respective tablespace will be enable for the IN MEMORY column store by default.
Above statement doesn’t change or populate table data into In Memory column store.
It just tells the DB that you want the table data to be available In Memory column store at certain point in time.
But point in time means On Demand OR during DB startup time.
Here on Demand means table populated into In Memory column store whenever they are refer by any query.
OR DBA can define this job should executed during the Db startup.
SQL> ALTER TABLE TAB1 INMEMORY PRIORITY CRTICAL;
So let us discus bout this priority criteria !!!!
CRITICAL: Object is populated immediately after the DB opened.
HIGH: If space remains available in IN MEMORY column store then next objects will be this one.
MEDIUM: These objects will be populated after CRITICAL & HIGH.
LOW: After CRITICAL , HIGH & MEDIUM.
NONE: Objects will be populated after they are scanned for the first time.
Following objects cannot be populated in to IN MEMORY
- Any objects owned by SYS user and stored in SYSTEM or SYSAUX tablespace.
- INDEX organized tables (IOTs).
- Clustered tables.
- LONG data types also not supported.
- LOBs also not supported.
IN MEMORY Compression….!!!!
Compression is considered only as a space-saving mechanism. However, data populated into the IM column store is compressed using a new set of compression algorithms that not only help to save space but also improve query performance. The new Oracle In-Memory compression format allows queries to execute directly against the compressed columns. This means all scanning and filtering operations will execute on a much smaller amount of data. Data is only decompressed when it is required for the result set.
As I am in SAP partner company always heard SAP doing research on this / that but thought let us discuss what Oracle research team doing considering SAP HANA’s market. So as I am working on SAP HANA let us discus about difference between Oracle Exadata & SAP HANA in next article.
Hope this article helped to you. I am expecting your suggestions/feedback.
It will help to motivate me to write more articles….!!!!
Thanks & Regards,
Samadhan
https://samadhandba.wordpress.com/
“Key for success, always fight even knowing your defeat is certain….!!!!
samadhandba 