Controlling where objects are populated into memory on RAC

Last week I spent some time beta testing Oracle Database 12c RAC on docker (more on this in a future post) and decided to take a trip down memory lane and play around with Oracle Database In-Memory.

One of the things I looked at was the new FOR SERVICE sub-clause of the INMEMORY DISTRIBUTE clause.

ALTER TABLE customers INMEMORY PRIORITY HIGH DISTRIBUTE FOR SERVICE IM1;

This new sub-clause allows you to control exactly where an object (table or partition) is populated into memory in a RAC environment based on where a database service is allowed to run. If the service is stopped then the objects distributed for that service are automatically removed from the IM column store(s).

If you recall each RAC node has it’s own In-Memory column store (IM column store). By default, when a table is populated into memory in a RAC environment it will be automatically distributed across all of the IM column stores in the cluster. That is to say, a piece of the table will appear in each RAC node, effectively making the IM column store a shared-nothing architecture.

This is not always desirable, especially if you only run certain applications or workloads on a subset of RAC node.

The video below demonstrates how to use the new syntax and what gv$ performances views you should look at to monitor what is going on in the IM column store in a RAC environment. You’ll find a copy of the script I used in the video at the bottom of this post.

-- Step1 configure the database services
srvctl ADD service -db orcl -service IM1 –preferred “rac1”
 
srvctl ADD service -db orcl -service IM2 -r preferred “rac2”
 
srvctl ADD service -db orcl -service IM_ALL -r preferred “rac1,rac2”
 
srvctl START service -db orcl -service "IM1,IM2,IM_ALL" 
 
srvctl STATUS service -db orcl
 
-- Step 2 Add INMEMORY attributes to the SALES, CUSTOMERS and PRODUCTS tables
 
ALTER TABLE customers INMEMORY PRIORITY HIGH DISTRIBUTE FOR SERVICE IM1;
 
ALTER TABLE products INMEMORY PRIORITY MEDIMUM DISTRIBUTE FOR SERVICE IM2;
 
ALTER TABLE sales INMEMORY PRIOIRTY HIGH;
 
-- Step 3 Check the content of the In-Memory column store
 
SELECT * 
FROM   gv$inmemory_area;
 
SELECT v.inst_id, 
       v.owner, 
       v.segment_name name, 
       v.populate_status, 
       SUM(v.bytes_not_populated)/1024/1024 MB_not_populated 
FROM   gv$im_segments v 
GROUP  BY v.inst_id,  v.owner, v.segment_name, v.populate_status; 
 
SELECT m.inst_id, 
       m.blocksinmem, 
       m.datablocks 
FROM   gv$im_segments_detail m, 
       user_objects o 
WHERE  m.dataobj = o.object_id 
AND    o.object_name = 'SALES'; 
 
-- Step 4 create a new session
-- Run query and capture the session level statistics before and after the query
 
SELECT t1.name, t2.value 
FROM v$sysstat t1, v$mystat t2 
WHERE t1.name IN ('table scans (long tables)',
                  'table scans (IM)',
                  'session logical reads',
                  'session logical reads - IM',
                  'IM scan rows', 
                  'IM scan segments disk')
AND t1.statistic# = t2.statistic# 
ORDER BY t1.name;
 
SELECT c.cust_city, 
       p.prod_name, 
       SUM(s.amount_sold) 
FROM   sales s, 
       customers c, 
       products p 
WHERE  s.cust_id = c.cust_id 
AND    s.prod_id = p.prod_id 
GROUP  BY c.cust_city, p.prod_name; 
 
SELECT t1.name, t2.value 
FROM v$sysstat t1, v$mystat t2 
WHERE t1.name IN ('table scans (long tables)',
                  'table scans (IM)',
                  'session logical reads',
                  'session logical reads - IM',
                  'IM scan rows', 
                  'IM scan segments disk')
AND t1.statistic# = t2.statistic# 
ORDER BY t1.name;
 
-- Step 5 Repopulate the tables so they are all on the same node
 
ALTER TABLE products INMEMORY PRIORITY MEDIMUM DISTRIBUTE FOR SERVICE IM1;
 
ALTER TABLE sales INMEMORY PRIOIRTY HIGH DISTRIBUTE FOR SERVICE IM1;
 
-- Step 6 Check the content of the In-Memory column store
 
SELECT v.inst_id, 
       v.owner, 
       v.segment_name name, 
       v.populate_status, 
       SUM(v.bytes_not_populated)/1024/1024 MB_not_populated 
FROM   gv$im_segments v 
GROUP  BY v.inst_id,  v.owner, v.segment_name, v.populate_status; 
 
-- Step 7 Create a new session and rerun the query and check the session statistics before and after 
 
SELECT t1.name, t2.value 
FROM v$sysstat t1, v$mystat t2 
WHERE t1.name IN ('table scans (long tables)',
                  'table scans (IM)',
                  'session logical reads',
                  'session logical reads - IM',
                  'IM scan rows', 
                  'IM scan segments disk')
AND t1.statistic# = t2.statistic# 
ORDER BY t1.name;
 
SELECT c.cust_city, 
       p.prod_name, 
       SUM(s.amount_sold) 
FROM   sales s, 
       customers c, 
       products p 
WHERE  s.cust_id = c.cust_id 
AND    s.prod_id = p.prod_id 
GROUP  BY c.cust_city, p.prod_name; 
 
SELECT t1.name, t2.value 
FROM v$sysstat t1, v$mystat t2 
WHERE t1.name IN ('table scans (long tables)',
                  'table scans (IM)',
                  'session logical reads',
                  'session logical reads - IM',
                  'IM scan rows', 
                  'IM scan segments disk')
AND t1.statistic# = t2.statistic# 
ORDER BY t1.name;
 
-- Step 8 stop the service and check that all of the objects were removed from the IM column store
 
srvctl stop service -db main -service "IM1"
 
SELECT v.inst_id, 
       v.owner, 
       v.segment_name name, 
       v.populate_status, 
       SUM(v.bytes_not_populated)/1024/1024 MB_not_populated 
FROM   gv$im_segments v 
GROUP  BY v.inst_id,  v.owner, v.segment_name, v.populate_status;
This entry was posted in Database In-Memory, Oracle Database 12c New Features and tagged , . Bookmark the permalink.

Leave a Reply

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