In-memory Column Store and Distribute for Service Options in Oracle 12cR2 Database

-

Distribute for Service: A New Feature in (12cR2)


Today, we are going to understand a new feature introduced in Oracle Database 12cR2. The feature is called, Distribute for Service.

What this new feature allows you to do, is to limit the objects/tables getting populated into a in-memory column store in a RAC environment. By Default in a RAC environment, a table or an object is broken up into pieces and each piece is populated into a in-memory column store across the RAC cluster.

If you do not want to allow this, and may be you want to have the application access a specific RAC node, where this table as a whole is populated, then you can use distribute for service feature combined with service specific features of Oracle RAC environment.

Let us consider a 2-node RAC environment which has 3 services. ( IM1, IM2 & IM_ALL )

IM1 runs on only node 1
IM2 runs on only node 2
IM_ALL runs on node1 and node2

There is a global view called, "gv$im_segments", when you query this, you will be able to status of the segments present in in-memory column stores across the nodes of the cluster.

Let us consider 3 tables, 
- Sales
- Customers
- Products

And alter the tables above with the commands as follows. 



Distribute For Service - 12cR2

Customers table has "INMEMORY PRIORTY HIGH" and we are making use of DFS to populate only on RAC node 1.

Products table has "INMEMORY PRIORITY MEDIUM" stating that it has slightly less priority than Customers table and it will be populated entirely on Rac node 2.

Sales table has a Higher Priority too but it is distributed across all the nodes of the RAC Cluster since DISTRIBUTE FOR SERVICE clause is not used. So, part of the table will be on node 1 and part of it will be populated on node 2.

Now, after the above commands are executed let us see the status of the INMEMORY column store across the RAC nodes.

Global View of InMemory Column Stores - Across nodes of RAC Cluster

You may observe that SALES table is distributed on RAC nodes 1 & 2.
CUSTOMERS table is populated completely only on node 1 and PRODUCT is completely populated only on node 2.

Since they are completely populated, MB_NOT_POPULATED shows "0" MB.  The Column values for SALES table in the individual nodes of RAC is self-explanatory. ( as SALES is partially populated on individual nodes ).

But how can you be sure that SALES table is fully populated across multiple nodes, we have to examine another global view called "gv$IM_SEGMENTS_DETAIL".

IM_SEGMENTS_DETAIL
As you can see the total# of blocks that make up the SALES table are "94643". Individual nodes have "BLOCKSINMEM" (blocks in memory) column which sum up to "94643" blocks. Therefore from the above result it is clear that SALES table partially filled in individual nodes is compeletely populated across all the nodes.


In the Next Blog, we will discuss about the following :-
How are the queries using the above tables run on the database. What are the wait events associated with them.

Thanks & Regards
Sai Marlakunta







Comments

Post a Comment

Popular posts from this blog

DBMS_XPLAN.DISPLAY_CURSOR

-
Image
How to Use DBMS_XPLAN.DISPLAY_CURSOR to examine execution plans As you all know we can get to know the execution plan of the optimizer in several different ways. In this post, we are going to examine, DBMS_XPLAN.DISPLAY_CURSOR which shows the actual/running execution plan of a SQL query. DBMS_XPLAN is a package inside SYS schema. DISPLAY_CURSOR is a function inside the package. Let us see how we can use the above to benefit us in fixing problematic and long running queries. SELECT * FROM table(dbms_xplan.display_cursor());  The SQL Statement executed above will pick the SQL that has been last executed in the current session. It returns the SQL_ID, the child Number, the Plan Hash Value, the Execution Plan. The * in the execution plan as indicated will have the predicate information below the execution plan. Sample DBMS_XPLAN.DISPLAY_CURSOR output The Rows column & the Time Column in the above plan are only the estimated Values for each operation as indica...
Read more

IBM Mainframe

-
Image
IBM Mainframes   IBM mainframes are large computer systems produced by IBM since 1952. A typical IBM mainframe computer would look like below:-   IBM zSeries z800 Mainframe Computer From 1950s-60s IBM manufactured and marketed several large computer models known as IBM 700/7000 series. The first generation 700s were based on Vaccum Tubes , while the later, the second generation 7000s used Transistors . These machines established IBM's dominance in   Electronic Data Processing("EDP") .  The mainframe computer costed over 2M USD in mid 1950's.The latest mainframe computers are IBM z10 EC(Enterprise Class) which costs around 1M USD announced on February 26th, 2008.   There were two categories of machines with completely different instruction sets. One dealt with engineering and scientific data and the other set of machines dealt with commercial data. The IBM Mainframe systems are mainly designed for Throughput and Relaibality . They are ...
Read more