IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
Hi everyone, Today i try to explain Oracle Database 12c In-Memory.
In-Memory is a very important subject for Database and DWH.
Oracle DB to store data in row format at the physical level. Due to the characteristics of transactional systems, it is an advantage to keep the data physically in this way. Because OLTP systems may want to access all the column information about a record at the same time.
However, the same situation does not seem to perform well for OLAP systems. Generally, DWH and reporting environments are systems that process an entire table or a whole set of records rather than dealing with a single record, and tend to produce analytical operations on specific major columns instead of using all columns of records.
Generally, DWH and reporting environments are systems that process an entire table or a whole set of records rather than dealing with a single record, and tend to produce analytical operations on specific major columns instead of using all columns of records.
Because of this characteristic of OLAP systems, keeping data in row format in database has a negative effect on data access and query performance of these systems.
With Oracle 12c database, it is aimed to increase the analytical query performance of these tables by keeping the column base in memory together with the In-Memory option.
If we take this development a little bit, Oracle will keep both the column base and row based columns of the table or table to be imported into in-memory (Oracle Dual Architecture).
Doesn’t keeping the data to be imported into oracle in-memory both row-based and column-based format does not require 2 times more memory?
Oracle’s statement on this subject is that it will not consume twice as much memory!
Why column format?
- Only used in-memory
- Does not need to be recovered because it is kept on disk in a row format
- Access to only required column data
In-Memory And Data Processing
- Queries that use TEMP tablespace can cause slowdown
- Use parallel queries
- Increase PGA_AGGREGATE_TARGET
How To Activate
- ALTER SYSTEM SET inmemory_size=30G scope=spfile;
- We need to restrat the database
To load tables in in-memory ALTER TABLE TABLE_NAME INMEMORY;
To view tables in IN-MEMORY SELECT OWNER, SEGMENT_NAME, INMEMORY_SIZE, BYTES, BYTES_NOT_POPULATED, POPULATE_STATUS FROM v$im_segments;
What are IN-MEMORY Restrictions?
- Sys objects in the SYSTEM and SYSAUX tablespace cannot be stored in in-memory
- Clustered table
- Columns of data type long
Column / Partition Based In-Memory
- ALTER TABLE TABLE_NAME INMEMORY NO INMEMORY (emp_id)
- ALTER TABLE TABLE_NAME MODIFY PARTITION emp_no NO INMEMORY
- ALTER TABLE TABLE_NAME INMEMORY NO INMEMORY
Finally, a brief summary of in-memory, There are no sql restrictions, No need to transfer or change data.
See you in next article..
Do you want to learn Oracle Database for Beginners, then read the following articles.
https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/
