Share

Oracle Database 12c In‐memory feature for Siebel Reporting and MIS Queries Optimization

Siebel database is optimized all DML are working fine with awesome performance but still we receive complaints when we fetch reports (few columns but fetch huge number of rows). In this article we will explain you the behavior of OLTP database & New Oracle 12c feature of In-memory to optimize Reporting & MIS queries output.

Siebel uses OLTP (Online Transaction Processing ) databases .  OLTP Databases contains data in the Row format. This provides optimized DML operations.

Row format allows quick access to all of the columns in a record since all of the data for a given record are kept together either in the database buffer cache or on disk storage.




Reporting and MIS type queries require just the opposite data model – few columns but fetch huge number of rows. A column format is ideal for analytics, as it allows for faster data retrieval when only a few columns are selected but the query accesses a large portion of the data set.

 

The Database In-Memory feature was introduced in Oracle 12c version 12.1.0.2  and provides the ability to easily perform real-time data analysis together with real-time transaction processing without any application change or re-design.



Oracle Database In-Memory feature provides the best  by allowing data to be simultaneously populated in both an in-memory row format “the database buffer cache” and a new in memory column format.

 

Note :- This portion of memory is taken from the SGA already allocated to the database.

 

We need to first enable the In-Memory memory area to  Enable this parameter we need to restart Database

 

Steps :-

SQL> alter system set inmemory_size=1024m scope=spfile;

SQL> startup;

ORACLE instance started.

Total System Global Area xxxxx bytes

Fixed Size                             xxxx bytes

Variable Size                      xxxxx bytes

Database Buffers              xxxxx bytes

Redo Buffers               xxxxxxxx bytes

In-Memory Area        xxxxxxxx bytes

Database mounted.

Database opened.

 

The INMEMORY attribute can be specified on a tablespace, table, (sub) partition, or materialized view.

SQL> alter table XYZ inmemory;

 

Note – At this stage since the tables have not been accessed, they have not been populated in the IM column store memory.



SQL> select segment_name, INMEMORY_SIZE , populate_status from v$im_segments;

SQL> ALTER TABLE customers INMEMORY PRIORITY CRITICAL;

 

Let us now execute the query and note the Explain Plan

 

SQL> select * from table(dbms_xplan.display_cursor());

 

The In-Memory column store has now been populated with XYZ tables.

 

SQL> select segment_name, INMEMORY_SIZE , populate_status from v$im_segments;

 

Compare and check plan with Disable In-Memory column store

 

Note the difference in the Explain Plan with In-Memory option disabled

 

SQL> alter session set inmemory_query=disable;

SQL> select * from table(dbms_xplan.display_cursor());

 

 

 

Leave A Reply

Your email address will not be published. Required fields are marked *

error: Content is protected !!