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 its 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.

PLEASE NOTE: That the service names are case sensitive, so make sure you keep them consistent. If your service name is defined all in lower case and then you specify the name in upper case in the ALTER TABLE command, you will not get an error. Remember this command is only changing the INMEMORY attribute on the table. It’s only when we begin populating the table into memory will Oracle discover that the service you specified doesn’t exist, i.e. doesn’t match the case of the service defined.

-- 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;

2 thoughts on “Controlling where objects are populated into memory on RAC”

  1. For other people who happen to find this page…
    Notice that she creates service name in UPPER case, if your services are created in lower case (like we do), then need to change the later table statement also accordingly (otherwise tables will not populate to inmemory at all – but no error message is produced):

    ALTER TABLE products INMEMORY PRIORITY MEDIMUM DISTRIBUTE FOR SERVICE “im1”;

    Tested in 19.6.0

    Reply

    1. Hi Ilmar,

      You make an excellent point regarding the case sensitivity of the service name. If the case doesn’t match, Oracle can’t find the service and therefore doesn’t know where to populate the object. You don’t get an error on the ALTER TABLE command as you are simply updating the INMEMORY attribute of the table at this point, ie. the data dictionary. It is on the actual population into the column store that the error will occur.

      I’ve updated the blog post to reflect this.

      Thanks,
      Maria

      Reply

Leave a Reply to Ilmar Kerm Cancel reply

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

%d bloggers like this: