11 Deploying IM Column Stores in Oracle RAC
This chapter explains how to enable IM column stores in an Oracle Real Application Clusters (Oracle RAC) environment, and configure objects for population.
This section contains the following topics:
11.1 Overview of Database In-Memory and Oracle RAC
Every Oracle RAC node has its own In-Memory (IM) column store. By default, populated objects are distributed across all IM column stores in the cluster.
Oracle recommends that you size the IM column stores equally on every Oracle RAC node. If an Oracle RAC node does not require an IM column store, then set the INMEMORY_SIZE
parameter to 0
.
It is possible to have completely different objects populated on every node, or to have larger objects distributed across all of the IM column stores in the cluster. On Oracle Engineered Systems, it is also possible for the same objects to appear in the IM column store on every node. The distribution of objects across the IM column stores in a cluster is controlled by two subclauses to the INMEMORY
attribute: DISTRIBUTE
and DUPLICATE
.
In an Oracle RAC environment, an object that only has the INMEMORY
attribute specified is automatically distributed across the IM column stores in the cluster. You can use the DISTRIBUTE
clause to specify how an object is distributed across the cluster. By default, the type of partitioning used (if any) determines how the object is distributed. If the object is not partitioned, then it is distributed by rowid range. Alternatively, you can specify the DISTRIBUTE
clause to override the default behavior.
On an Oracle Engineered System, you can duplicate or mirror populated objects across the IM column stores in the cluster. This technique provides the highest level of redundancy. The DUPLICATE
clause controls how an object is duplicated. If you specify only DUPLICATE
, then one mirrored copy of the data is distributed across the IM column stores in the cluster. To duplicate the entire object in each IM column store, specify DUPLICATE ALL
.
Note:
When you deploy Oracle RAC on a non-Engineered System, the DUPLICATE
clause is treated as NO DUPLICATE
.
11.1.1 Multiple IM Column Stores
In Oracle RAC, each database instance has its own IM column store.
Conceptually, the IM column store in Oracle RAC environment uses a shared-nothing architecture. On each database instance, you size and manage the IM column stores separately. The database instances do not use Cache Fusion to transfer IMCUs back and forth.
Figure 11-1 IM Column Stores in an Oracle RAC Database
This figure shows a two-node Oracle RAC cluster. Each instance has a separately configured IM column store.
![Description of Figure 11-1 follows Description of Figure 11-1 follows](img/inmem_3v_030.png)
Description of "Figure 11-1 IM Column Stores in an Oracle RAC Database"
Oracle recommends that you set the size of the IM column stores on every Oracle RAC node to an equal value. For example, you might assign every IM column store 100 GB of memory. For any node that does not require an IM column store, set the INMEMORY_SIZE
initialization parameter on this node to 0.
Figure 11-2 Three-Node Oracle RAC Database with Two IM Column Stores
In this example, instance 1 and instance 2 both have IM column stores. Instance 3 does not require an IM column store, so the INMEMORY_SIZE
initialization parameter on this node is set to 0.
![Description of Figure 11-2 follows Description of Figure 11-2 follows](img/inmem_3v_026.png)
Description of "Figure 11-2 Three-Node Oracle RAC Database with Two IM Column Stores"
See Also:
-
Oracle Database Reference for more information about the
INMEMORY_SIZE
initialization parameter -
Oracle Real Application Clusters Administration and Deployment Guide for an introduction to Oracle RAC
11.1.2 Distribution and Duplication of Columnar Data in Oracle RAC
When INMEMORY
is specified, the DISTRIBUTE
and DUPLICATE
keywords control the distribution of objects.
Oracle RAC provides multiple distribution options. You can have different objects populated on each node, or have larger objects distributed across all IM column stores in the Oracle RAC cluster. You can also have the same objects populated in the IM column store on every node (Oracle Engineered Systems only).
Note:
If a table is currently populated in the IM column store, and if you change any INMEMORY
attribute of the table other than PRIORITY
, then the database evicts the table from the IM column store. The repopulation behavior depends on the PRIORITY
setting.
This section contains the following topics:
11.1.2.1 Distribution of Columnar Data in Oracle RAC
The DISTRIBUTE
clause of INMEMORY
controls how table data in the IM column store is distributed across Oracle RAC instances.
When the default option of AUTO
is set, the Oracle RAC instances distribute data automatically. While populating a segment, Space Management Slave Processes (Wnnn) processes attempt to put an equal amount of data on each instance. Distribution depends on access patterns and object size. Alternatively, you can manually specify how the database must distribute partitions, subpartitions, or rowid ranges across instances.
Equal data distribution is important for performance. The goal is for parallel query processes to work on equal data set sizes so that they all finish in the minimum amount of time. If data distribution is skewed, then a long-running process delays the completion of the query.
If an Oracle RAC instance fails, then the IMCUs on the failed instance are unavailable. Consequently, a query that needs data stored in the inaccessible IMCUs must read it from somewhere else: the database buffer cache, flash storage, disk, or mirrored IMCUs in other IM column stores.
The DBA_TABLES.INMEMORY_DISTRIBUTE
column indicates how IMCUs are distributed. When the AUTO
option is set, the column value is AUTO-DISTRIBUTE
.
Example 11-1 Default Distribution
This example shows the database distributing a sales
table that contains only partitions: sales_2013_pt
and sales_2014_pt
. The database automatically places the sales_2013_pt
partition in Instance 1, and sales_2014_pt
in Instance 2.
Figure 11-3 Automatic In-Memory Distribution in Oracle RAC
![Description of Figure 11-3 follows Description of Figure 11-3 follows](img/cncpt_vm_387.png)
Description of "Figure 11-3 Automatic In-Memory Distribution in Oracle RAC"
This section contains the following topics:
See Also:
-
Oracle Real Application Clusters Administration and Deployment Guide to learn how to manage the IM column store on Oracle RAC
-
Oracle Database SQL Language Reference to learn more about the
DISTRIBUTE
clause -
Oracle Database Reference to learn about the Wnnn background processes
11.1.2.1.1 Distribution by Partition
You can use the DISTRIBUTE BY PARTITION
clause to distribute data in partitions to different Oracle RAC instances.
This technique is ideal for hash partitions. For example, to distribute partitions in the orders
table equally, you could partition by hash on the order_id
column. As shown in the following figure, Oracle Database distributes partitions among four instances by hashing on the order_id
column.
Figure 11-4 Distributing Partitions by Hash
![Description of Figure 11-4 follows Description of Figure 11-4 follows](img/inmem_3v_028.png)
Description of "Figure 11-4 Distributing Partitions by Hash"
This technique is suitable for other partitioning schemes when the partitions are uniformly accessed. The DISTRIBUTE BY PARTITION
clause also supports partition-wise joins.
Note:
If your partitioned strategy results in a large data skew, that is, one partition is much larger than the others, then Oracle recommends that you override the default distribution (BY PARTITION
) by manually specifying DISTRIBUTE BY ROWID RANGE
.
See Also:
-
Oracle Database SQL Language Reference to learn more about the
DISTRIBUTE BY PARTITION
subclause -
Oracle Database VLDB and Partitioning Guide for an introduction to partitioned tables
11.1.2.1.2 Distribution by Subpartition
In tables with a composite partitioning scheme, you can use the DISTRIBUTE BY SUBPARTITION
clause to distribute data in subpartitions to different instances.
This technique is ideal for hash subpartitions. For example, to distribute partitions in the orders
table equally, you could partition by range on the order_date
column and by hash on the order_id
column.
Figure 11-5 Distributing Partitions by Range and Subpartitions by Hash
![Description of Figure 11-5 follows Description of Figure 11-5 follows](img/inmem_3v_027.png)
Description of "Figure 11-5 Distributing Partitions by Range and Subpartitions by Hash"
This technique is suitable for other partitioning schemes when the subpartitions are uniformly accessed. The DISTRIBUTE BY PARTITION ... SUBPARTITION
clause also supports partition-wise joins.
See Also:
-
Oracle Database VLDB and Partitioning Guide to learn more about composite partitioning
-
Oracle Database SQL Language Reference to learn more about the
DISTRIBUTE BY PARTITION ... SUBPARTITION
subclause
11.1.2.1.3 Distribution by Rowid Range
You can use the DISTRIBUTE BY ROWID RANGE
clause to distribute data in specific ranges of rowids to different Oracle RAC instances.
This technique distributes IMCUs by uniform hash on the first rowid. For example, Oracle Database might distribute rows 1-105 in the orders
table to one database instance, rows 106-121 to a different instance, and so on.
The rowid distribution technique is most useful for nonpartitioned tables. However, if your partitioned strategy results in a large data skew, for example, one partition is much larger than the others, then Oracle recommends overriding the default distribution (BY PARTITION
) by manually specifying DISTRIBUTE BY ROWID RANGE
.
See Also:
Oracle Database SQL Language Reference to learn more about the DISTRIBUTE BY ROWID RANGE
subclause
11.1.2.2 Duplication of Columnar Data in Oracle RAC
The DUPLICATE
clause controls how an Oracle RAC database duplicates columnar data across Oracle RAC instances.
Note:
The DUPLICATE
clause is only available with Oracle RAC on an Oracle Engineered System. When Oracle RAC does not run on an Oracle Engineered System, the DUPLICATE
clause is functionally equivalent to NO DUPLICATE
.
To provide IM column store fault tolerance, you may choose to mirror the IMCUs. With IMCU mirroring, the same IMCU resides in multiple IM column stores. This technique is analogous to storage mirroring.
Figure 11-7 Duplication of IMCUs in Oracle RAC
![Description of Figure 11-7 follows Description of Figure 11-7 follows](img/inmem_3v_025.png)
Description of "Figure 11-7 Duplication of IMCUs in Oracle RAC"
Using the DUPLICATE
clause to mirror data at the tablespace or object (table, partition, or subpartition) level provides the following benefits:
-
Provides fault tolerance because if one node fails, then the mirrored columnar data is accessible from a different node
-
Improves performance because queries can access data locally, thus avoiding buffer cache or disk access
For example, in a star query, the fact table might be partitioned, whereas the dimension tables use
DUPLICATE ALL
. In this scenario, all joins take place fully on the local nodes. -
Enhances manageability because you can duplicate a subset of objects
For example, you can duplicate this year’s partitions while leaving others partitions from the same table not duplicated.
A disadvantage of IMCU mirroring is that when an object is duplicated n times, its memory requirements increase by a factor of n. For example, a 500 MB table that is duplicated in 4 instances occupies a total of 2000 MB of memory.
This section contains the following topics:
11.1.2.2.1 DUPLICATE Clause in Oracle RAC
The DUPLICATE
clause specifies that the database maintain a copy of every IMCU in a second database instance. Thus, the same segment is populated in exactly two Oracle RAC instances.
For each object, one IMCU is primary. A secondary IMCU resides on a different database instance. The database can use either copy to satisfy a query. If the database instance with the primary copy of the IMCU fails, then the database can use the surviving IMCU to satisfy the query.
For example, you might specify DUPLICATE
for the partition sales_q1_2014
. The IM column stores in instance 1 and instance 2 both have an identical copy of the data. If instance 1 terminates, then the IM column store on instance 2 can satisfy requests for sales_q1_2014
.
See Also:
-
Oracle Database SQL Language Reference to learn more about the
DUPLICATE
clause
11.1.2.2.2 DUPLICATE ALL Clause in Oracle RAC
The DUPLICATE ALL
clause specifies that every In-Memory object is mirrored on every database instance.
This setting provides the highest level of redundancy and provides linear scalability because queries can execute completely within a single node. For example, every IMCU for the sales
table is populated in the IM column store in instance 1, instance 2, and instance 3. Thus, any database instance can retrieve the data requested by a query of sales
.
A consequence of the DUPLICATE ALL
clause is that the DISTRIBUTE
subclause has no application because all IMCUs for the object are distributed. You specify duplication at the object level, which means that not all objects in the IM column store required the DUPLICATE ALL
clause.
The primary advantages of the DUPLICATE ALL
technique are:
-
High availability
When you use
DUPLICATE ALL
clause for all In-Memory objects, an Oracle RAC database with n instances can sustain n-1 Oracle RAC instance failures. If you need take one database instance out of service for maintenance, then critical data is available in at least one IM column store. The only scenario in which all data is inaccessible is a failure of all database instances in the cluster. -
Performance of star queries
If queries join smaller dimension tables to a large partitioned fact table, then you can use
DUPLICATE ALL
to mirror dimension tables in every Oracle RAC instance. The fact table is distributed by partition or subpartition. In this strategy, the IM column store in every database instance has the data necessary for a star join. This technique is analogous to a partition-wise join because the entire dimension table is populated in every IM column store.
See Also:
Oracle Database SQL Language Reference to learn more about the DUPLICATE ALL
clause
11.1.2.2.3 NO DUPLICATE Clause in Oracle RAC
The default NO DUPLICATE
clause specifies that the database maintain only one copy of an object.
For example, a three-node Oracle RAC database might store the 2012 partition of a sales table in instance 1, the 2013 partition in instance 2, and the 2014 partition in instance 3. Each table partition resides in exactly one database instance.
If an Oracle RAC node does not duplicate the columnar data, then the columnar data on the failed node is not available in the IM column store on the cluster. Queries issued against missing data do not fail. Instead, queries access the data either from the database buffer cache or permanent storage, which may negatively affect performance. If the node remains down for some time, and if space exists in the surviving IM column stores, then Oracle RAC populates the missing objects or pieces of the objects on the remaining nodes in the cluster.
See Also:
Oracle Database SQL Language Reference to learn more about the NO DUPLICATE
clause
11.1.3 Parallelism in Oracle RAC
A database instance must access the IMCUs in the IM column store in which they reside. Population and access of IM column stores in Oracle RAC must occur in parallel so that all IM column stores are accessible from any instance.
This section contains the following topics:
11.1.3.1 Serial and Parallel Queries in Oracle RAC
Database In-Memory in Oracle RAC is a shared-nothing architecture. Unless at least one parallel server process runs on every active instance, a query is not guaranteed to access all necessary data from the IM column stores.
A serial query that runs on one node in an Oracle RAC database cannot access IMCUs in the other IM column stores. For example, a serial query running on instance 1 requests a full scan of sales
. Some sales
partitions are populated in the IM column store in instance 1, whereas the others are populated in the IM column store in instance 2. The query can only access the IMCUs in the IM column store on instance 1: the remaining data must come from on-disk storage.
The situation is similar when parallel execution is enabled, but the PARALLEL_DEGREE_POLICY
initialization parameter is not set to AUTO
. The query coordinator runs on the database instance where the query executes. The PQ processes send data to the coordinator. In this case, the database starts multiple PQ processes. However, unless the DOP is greater than or equal to the number of IM column stores containing IMCUs populated for objects referenced in the query, not all data will be accessed from the IM column store. When Auto DOP is not enabled, you must ensure that the DOP is at least as great as the IM column stores with IMCUs for the populated objects in the query.
Both serial and parallel queries can perform an In-Memory Dynamic Scan (IM dynamic scan) and use the lightweight thread infrastructure. The parallel execution infrastructure co-exists with the new thread infrastructure, which is dynamically managed by Oracle Database Resource Manager (Resource Manager). A table scan process can be either a foreground process in a serial query or a parallel server process in a parallel query. When a parallel query performs an IM dynamic scan, every table scan process can own a pool of threads.
See Also:
-
Oracle Database VLDB and Partitioning Guide to learn more about parallel queries in Oracle RAC
-
Oracle Database Reference to learn more about the
PARALLEL_DEGREE_POLICY
initialization parameter
11.1.3.2 Auto DOP in Oracle RAC
With Automatic Degree of Parallelism (Auto DOP), the optimizer performs a cost-based calculation to determine the degree of parallelism for a SQL statement.
Enable Auto DOP by setting the PARALLEL_DEGREE_POLICY
initialization parameter to AUTO
. When the optimizer parses a SQL statement, it estimates the execution time. It checks this estimate against the setting of the PARALLEL_MIN_TIME_THRESHOLD
initialization parameter, which is automatically set when the IM column store is enabled. The optimizer then makes the following cost-based decision:
-
If the estimated time is less than
PARALLEL_MIN_TIME_THRESHOLD
, then the statement executes serially. -
If the estimated time is greater than
PARALLEL_MIN_TIME_THRESHOLD
, then the statement executes in parallel.The optimizer calculates the degree of parallelism based on resource requirements. The calculation is limited by the
PARALLEL_DEGREE_LIMIT
initialization parameter and, if configured, the Database Resource Manager.
When using IM column stores in an Oracle RAC environment, the goal is to avoid disk or buffer cache access. To this end, you must guarantee that at least one parallel server process runs on every active database instance. Auto DOP is the recommended way to achieve this goal.
Note:
If you do not use Auto DOP, then you must ensure that the DOP is greater than or equal to the number of IM column stores containing the IMCUs required by the query.
Auto DOP guarantees an adequate distribution of processes because every shared pool stores metadata that indicates where all the IMCUs are located, how large they are, and so on. The same map is in every shared pool. No matter where the query originates in the cluster, the parallel query coordinator is aware of the home location (instance of residence) of the IMCUs.
For example, the PQ coordinator knows that the sales partitions for 2016 are in instance 1, whereas the partitions for 2015 are in instance 2. If a query running on instance 1 requests both 2015 and 2016 partitions, then the query coordinator uses the home location to determine which IM column stores to access. If the DOP has been set sufficiently high, then the coordinator automatically starts PQ processes on both instances, and the processes send the requested data back to the query coordinator.
See Also:
Oracle Database VLDB and Partitioning Guide to learn about Auto DOP11.1.4 FastStart Area in Oracle RAC
The FastStart area is shared across all Oracle RAC nodes. This feature enables maximum sharing and reusability across the cluster.
Only one copy of an IMCU resides in the FastStart area. For example, if DUPLICATE ALL
is specified for an object in a four-node cluster, then four copies of the object exist in the IM column stores. However, the database saves only one copy to the FastStart area.
Any database instance in an Oracle RAC cluster can use an IMCU in the FastStart area. This feature improves performance of instance restarts in an Oracle RAC environment.
For example, the sales
table might have three partitions: sales_2014
, sales_2015
, and sales_2016
, with each partition populated in a different instance. An instance failure occurs, with one instance unable to restart. If sufficient space is available in the IM column stores, then the surviving instances can read the IMCUs that were previously populated in the inaccessible instance. Thus, all three sales
table partitions are available to applications.
See Also:
-
Oracle Database SQL Language Reference to learn more about the
DUPLICATE ALL
clause
11.2 Configuring In-Memory Services in Oracle RAC
A service represents a set of instances. In Oracle RAC, you can use services to direct connections or applications to a subset of nodes in the cluster.
This section contains the following topics:
See Also:
Oracle Real Application Clusters Administration and Deployment Guide to learn more about services in Oracle RAC
11.2.1 Instance-Level Service Controls
In Oracle RAC, the population and access of IM column stores must occur in parallel so that all IM column stores are accessible from any database instance.
The PARALLEL_INSTANCE_GROUP
initialization parameter restricts parallel query operations to the specified service. For example, if three out of four database instances in a cluster have an IM column store, then you might create a service named dbmperf
and use PARALLEL_INSTANCE_GROUP
to assign these three instances to this service. You can then restrict all client connections to the dbmperf
service. Parallel operations spawn parallel execution processes only on the instances defined in the service.
Figure 11-8 Assigning a Subset of Instances to a Service
![Description of Figure 11-8 follows Description of Figure 11-8 follows](img/inmem_3v_018.png)
Description of "Figure 11-8 Assigning a Subset of Instances to a Service"
See Also:
Oracle Database Reference to learn more about thePARALLEL_INSTANCE_GROUP
initialization parameter
11.2.2 Object-Level Service Controls
For an individual object, the INMEMORY ... DISTRIBUTE
clause has a FOR SERVICE
subclause that limits population to the database instance where this service can run.
The PARALLEL_INSTANCE_GROUP
initialization parameter controls segments at the service level, where a service represents one or more instances. In contrast, INMEMORY ... DISTRIBUTE FOR SERVICE
controls distribution at the segment level. For example, you can configure an INMEMORY
object to be populated in the IM column store on instance 1 only, or on instance 2 only, or in both instances.
The DISTRIBUTE FOR SERVICE
options are:
-
DEFAULT
- IfPARALLEL_INSTANCE_GROUP
is set, then the object is populated in all database instances that have an IM column store specified inPARALLEL_INSTANCE_GROUP
. IfPARALLEL_INSTANCE_GROUP
is not set, then the object is populated in all instances that have an IM column store.Specifying
FOR SERVICE
is equivalent to specifyingFOR SERVICE DEFAULT
. -
ALL
- The database populates the object in all instances that have an IM column store.Note:
If
PARALLEL_INSTANCE_GROUP
is not set, thenDEFAULT
andALL
are functionally equivalent. -
service_name - As part of its duties, IMCO triggers the removal of the object from the database instances assigned to the previous service, and populates it into the instances assigned to the new service.
When redistributing segments, the database does the minimum work necessary. For example, service
dbmperf
is assigned to instance 1 and instance 2. Thesales
partitions are evenly distributed between instance 1 and instance 2. You add instance 3 to this service. The database only populates IMCUs in instance 3 and then removes them from instance 1 or instance 2 when necessary for even distribution. Some IMCUs remain in their original location. -
NONE
- IMCO removes the object from the IM column stores of the currently specified services.
If the object has a PRIORITY
value other than NONE
, then Wnnn processes populate the object during the next IMCO cycle after the DDL executes or the service starts. If the object has PRIORITY
set to NONE
, however, then the object is only populated during a full table scan. The scan triggers In-Memory population on all database instances on which the specified service for the table is active and not blocked. Note that this service can be different from the scan of the issuing service.
If a service used for In-Memory population stops, then the database removes the segment from the IM column stores represented by this service. In this respect, stopping the service is like shutting down the instances. The INMEMORY
attributes of this object do not change. If the service starts again, then the database populates the object according to its INMEMORY
attributes. To remove an object from the IM column store, specify NO INMEMORY
in a DDL statement.
You can combine DUPLICATE
with DISTRIBUTE FOR SERVICE
. For example, you might specify that an object use DUPLICATE ALL
for service dbmperf
, which is assigned to three nodes out of four. In this case, the IM column store on each of these three nodes has a copy of the object.
See Also:
-
Oracle Database SQL Language Reference to learn more about the
DISTRIBUTE FOR SERVICE
subclause
11.2.3 Benefits of Services for Database In-Memory in Oracle RAC
The combination of services and DUPLICATE
enables you to control node access and In-Memory population.
Benefits of services include the following:
-
Rolling patches and upgrades
Suppose you set up an Oracle RAC service to direct client queries to the instances that contain an IM column store. If you use the
DUPLICATE
clause, then you can selectively remove an instance without affecting query response time. This approach assumes that sufficient resources exist on the other instances in the service to handle the workload of the removed instance.For example, in a four-node cluster, you could remove each node in turn, patch it, and then make it available again. The IMCUs for the temporarily inaccessible node are available on at least one other node, depending on whether you use the
DUPLICATE
orDUPLICATE ALL
clause. Thus, application access to columnar data remains uninterrupted. -
Application affinity
You can restrict application access to a single node based on service name. For example, service
dbmperf1
is restricted to node 1, servicedbmperf2
is restricted to node 2, and so on. When an application connects to a specific service and submits a parallel query, the query uses processes on the nodes belonging to the same service. For example, an application that connects to servicedbmperf1
only uses processes on node 1.Applications can coexist in an Oracle RAC database independently and access columnar data. Completely different objects can be populated in each node. For example, you could direct an HR application to service
dbmperf1
, and direct a sales history application to servicedbmperf2
.
See Also:
-
Oracle Database SQL Language Reference for
DUPLICATE
semantics
11.2.4 Configuring an In-Memory Service for a Subset of Nodes: Example
This task explains how to assign an In-Memory service to a subset of nodes in an Oracle RAC database.
The goal is the following:
-
Create IM column stores on a subset of nodes in a RAC database
-
Define a service to allow access to only the nodes that have an IM column store
Assumptions
This task assumes the following:
-
The Oracle RAC database named
dbmm
has four instances:dbm1
,dbm2
,dbm3
, anddbm4
. See "Figure 11-8". -
All instances except
dbm4
haveINMEMORY_SIZE
set to a nonzero value. -
You want to add a service named
dbmperf
and assign it to the three nodes that have an IM column store. -
You want to populate the
sales
table in the IM column stores attached to the service.
To configure an In-Memory service for a subset of nodes:
-
Create a service that represents the three nodes running IM column stores.
On the operating system command line, use the
srvctl
command using the following form:srvctl add service -db db_name –s service_name -preferred "instance_names"
For example, enter the following command:
srvctl add service –db dbmm –s dbmperf –preferred "dbm1, dbm2, dbm3"
-
Start the service.
For example, to start the
dbmperf
service, use the following command:srvctl start service -db dbmm -service "dbmperf"
-
Create a net service name for a connection to the service.
For example, update the
tnsnames.ora
file as follows:DBMPERF = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = host_name) (PORT = listener_port)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DBMPERF) ) )
-
Assign the
INMEMORY
attribute to the tables that you intend to populate, using theDISTRIBUTE FOR SERVICE
subclause.For example, alter
sales
as follows:ALTER TABLE sales INMEMORY DISTRIBUTE FOR SERVICE "dbmperf";
The preceding statement uses the default
PRIORITY
setting ofNONE
for thesales
table. Therefore, population of this table occurs on demand rather than automatically. -
To populate the
sales
table, connect to thedbmperf
service, and then initiate a full scan of the table.For example, force a full scan by querying
sales
as follows:SELECT /*+ FULL(s) */ COUNT(*) FROM sales s;
See Also:
-
Oracle Database SQL Language Reference to learn more about the
INMEMORY DISTRIBUTE FOR SERVICE
clause ofALTER TABLE
-
Oracle Database Administrator’s Guide to learn more about the
srvctl
utility -
Oracle Database Net Services Administrator's Guide to learn more about net service names