Using Oracle Database 11g Release 2 Result Cache in an Oracle RAC Environment

Using Oracle Database 11g Release 2 Result Cache in an Oracle RAC Environment (轉：http://www.oracle.com/technetwork/articles/datawarehouse/vallath-resultcache-rac-284280.html)

By Murali Vallath

Learn about the advantages of this feature for databases in which your data does not change very frequently, such as in a data warehouse or reporting database environment.

Published January 2011

Oracle Real Application Clusters (RAC) is a clustered database solution that provides scalability and availability for business continuum. “Scalability” is a relative term and is based on a simple rule that as the number of user’s accessing the system increases, the RAC configuration should be able to handle that increase. However, it is important to understand that an application can scale in a clustered environment only if that application is also scalable on a single-instance Oracle environment-- unless the application scales on a single server when the number of CPUs increases from 2 to 4 to 6 to 8, it will not scale in a RAC environment. Basically, Oracle RAC cannot perform. magic to fix poorly performing application code. (Availability, in contrast, is ability of the system to provide continuous service when one or more components fail.)

In a scalable system, workload has to be distributed across all nodes in the cluster transparently, bringing a true load balanced environment. To achieve that goal, Oracle RAC architecture allows movement of cached data across instances via the interconnect, which otherwise would have been expensive if physical I/O were required.

For example, Oracle Database's Result Cache feature, which was introduced with Oracle Database 11g Release 1, has received a lot of attention for its ability to cache SQL query and PL/SQL function results in memory. Thus in an Oracle RAC environment, multiple executions of the same query or function can be served directly by a single dataset in the cache, instead of moving that dataset across the interconnect every time it is needed. In this article, you'll learn how this feature works.

What is the Result Cache Feature?

Cache: In the Oracle world, there are so many kinds and flavors of it: library cache, buffer cache, dictionary cache, database cache, keep cache, recycle cache, and so on. Caching of data for better performance has been the goal of the Oracle architecture for a very long time.

When a query is executed for the very first time, the user’s process searches for the data in the database buffer cache. If data is there (because someone else had retrieved this data before), it uses it; otherwise, it performs an I/O operation to retrieve data from the datafile on disk into the buffer cache, and from this data, the final result set is built.

Subsequently, if another query requires the same data set, the process uses the data from the buffer cache to build the result set required by the user. Well, if the buffer cache contains data for reuse, then what’s this new Result Cache? In simple terms, the Result Cache could be called a cache area within a cache, in this case, the shared pool. So, the Result Cache is an area in the shared pool and contains the end results of a query execution.

What does this mean? Let’s examine this through an example; the following query is executed in an Oracle Database 11g Release 2 database:

SELECT OL_NUMBER, SUM (OL_AMOUNT), SUM (OL_QUANTITY) 
FROM   ORDER_LINE OL, ORDERS ORD 
WHERE  OL.OL_O_ID = ORD.O_ID AND 
       OL.OL_W_ID = ORD.O_W_ID AND 
       OL.OL_D_ID = ORD.O_D_ID 
GROUP BY OL_NUMBER;

The output from the tkprof report of the trace file reveals that the query traversed through more than 347,000 rows to provide a final output that contains 300 rows of summary data, which is the result set. 

call     count       cpu    elapsed       disk      query    current        rows
-------       ------      --------      ----------      ----------      ----------      ----------       ----------
Parse        1      0.01       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       21     33.04      96.12     346671     347172          0         300
-------       ------      --------      ----------      ----------      ----------      ----------       ----------
total       23     33.06      96.14     346671     347172          0         300

What if another user would like to execute the exact same query? The user session will have to traverse through all these rows in the buffer cache once again to get the final result of 300 rows. What if there was a method to get directly to those 300 rows the second time and every time going forward? Well, it may be possible every time, as long as we have sufficient buffer to hold this data. 

That’s what the Result Cache is all about. As illustrated in Figure 1, with the Result Cache feature, the 300 rows are moved (Step 3) to the Result Cache section of the shared pool. Subsequently, when the same query is executed, it can find the results in the Result Cache instead of going through those 347,000 rows of data. Isn’t this a neat feature?

Figure 1 Result Cache Behavior

The Result Cache can be managed either on the client side or the server side. A client side Result Cache implementation would require the application to use the Oracle Call Interface (OCI) calls. Comparatively, server side implementation is much simpler. For the discussions of this paper, we will focus on the server side implementation of the Result Cache.

For a server side implementation the same query could be executed with a /*+ RESULT CACHE */ hint or the result_cache_mode parameter could be set to AUTO. Setting this parameter to AUTO moves all query results to the Result Cache section of the shared pool, so the final 300 rows are moved to the Result Cache section of the shared pool for reuse.

When a query with this hint is executed, the database looks in the Result Cache section of the shared pool to determine if the result exists in the cache. If it does exist; the database retrieves the results and returns the data to the user without executing the query.

However, the result might not be cached for the following reasons:

• The query is being executed for the very first time.

• The cache was flushed out because the space allocated was required for another operation due to a limitation set by result_cache_max_result.

• A database administrator executed the dbms_result_cache.flush procedure and the query had to be executed again. Then the final result set was moved and stored in the Result Cache.

NAME                             TYPE        VALUE
------------------------------------               -----------      ----------
client_result_cache_lag          big integer 3000
client_result_cache_size         big integer 0
result_cache_max_result          integer     5
result_cache_max_size            big integer 251680K
result_cache_mode                string      MANUAL
result_cache_remote_expiration   integer     0

The parameters client_result_cache_lag and client_result_cache_size are used to configure the Result Cache at the client side. The other parameters are used for configuring the Result Cache at the server side. 

The size of the Result Cache on the server is determined by two parameters: result_cache_max_result and result_cache_max_size.

By default, the result_cache_max_size parameter is about 0.25% of the

memory_target parameter or 1% of the shared_pool parameter. This parameter can be modified to

control how much is stored in the Result Cache. The result_cache_max_result parameter specifies what percentage of result_cache_max_size a single Result Cache can use. The default value is 5%.

Each result set is identified in the cache using a CACHE_ID, which is a 90-character long string. The CACHE_ID for a query does not match the SQL_ID used to identify the query in the library cache and contained in V$SQL. Unlike the SQL_ID, which is generated for every SQL query executed against an Oracle database, the CACHE_ID is for an area or bucket in the Result Cache section of the shared pool that stores the end result of the query.

If the query mentioned previously is executed with the /*+ RESULT_CACHE */ hint, the following plan is generated. The name assigned to the Result Cache is the CACHE_ID.

--------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                            |   150 |  4950 |       |   126K  (1)| 00:25:17 |
|   1 |  RESULT CACHE           | 8fbjhchhd9zwh7uhn4mv7dhvga |       |       |       |            |          |
|   2 |   HASH GROUP BY         |                            |   150 |  4950 |       |   126K  (1)| 00:25:17 |
|*  3 |    HASH JOIN            |                            |    20M|   649M|    45M|   125K  (1)| 00:25:09 |
|   4 |     INDEX FAST FULL SCAN| ORDERS_I2                  |  2063K|    21M|       |  2743   (1)| 00:00:33 |
|   5 |     INDEX FAST FULL SCAN| IORDL                      |    20M|   432M|       | 87415   (1)| 00:17:29 |
--------------------------------------------------------------------------------------------------------------

There are several views to monitor information related to the Result Cache. The objects that are related to the Result Cache can be obtained from the V$RESULT_CAHCE_OBJECTS view. The following query helps verify the result set contained in the Result Cache for the CACHE_ID named 8fbjhchhd9zwh7uhn4mv7dhvga. 

SQL> SELECT ID, TYPE, CREATION_TIMESTAMP, BLOCK_COUNT, COLUMN_COUNT, PIN_COUNT, ROW_COUNT 
FROM V$RESULT_CACHE_OBJECTS WHERE CACHE_ID='8fbjhchhd9zwh7uhn4mv7dhvga';
ID       TYPE         CREATION_  BLOCK_COUNT COLUMN_COUNT  PIN_COUNT  ROW_COUNT
----------    ----------            ---------       -----------       ------------      ----------      ----------
2        Result     19-JAN-10           1            3          0        300

The main intent of this article is to discuss the Result Cache behavior. in an Oracle RAC environment. So let’s look at the Result Cache in a bit more detail as we move into its behavior. in an Oracle RAC implementation. For those who are new to Oracle RAC, let’s start with some background. 

What is Oracle RAC?

For those new to a clustered environment, Oracle RAC is a technology that comprises two or more instances of an Oracle relational database management system (RDBMS) that share one common physical database. Multiple users can access the data (that is, the same instance of the data) from any instance participating in the cluster and they can view an exact copy of the data.

Figure 2 Oracle RAC Configuration

As illustrated in Figure 2, the following characteristics are unique to an Oracle RAC implementation as opposed to a single-instance configuration:

• Many instances of Oracle Database are running on many nodes. By implementing features such as server pools available in Oracle Database 11g Release 2, requirements for instance to server/node affinity in prior releases are not required, providing opportunities for the dynamic provision of instances based on workload in a clustered environment.

• Many instances share a single physical database.

• All instances have common data and control files. There is only one set of data files and control files irrespective of the number of instances in the cluster.

• Each instance has individual log files and undo segments.

• All instances can simultaneously execute transactions against the single physical database.

• Instances participating in the clustered configuration communicate via the cluster interconnect using cache fusion technology.

• Oracle RAC maintains cache synchronization between instances across the cluster interconnect.

• Each node contains a virtual IP address that is used by user sessions connecting to the database.

• The cluster maintains a single SCAN address that allows the users to dynamically connect to any server that is least loaded. (SCAN addresses are similar to the cluster alias feature that existed in OpenVMS and Tru64 operating systems. These addresses have intelligence that enables them to understand which servers in the cluster are least loaded and place new connections to the least loaded server. Discussions about how the SCAN works is beyond the scope of this document.)

• Each node contains an Oracle Automatic Storage Manager instance, which provides access to the storage infrastructure and provides options, such as the addition and removal of storage when the database is being effectively utilized.

• All nodes participating coordinate with each other for member availability and voting using Oracle Clusterware.

Cache Synchronization

When a user queries data from the database, data is retrieved from the storage subsystem and loaded into the buffer cache, and data is traversed until the final result set is extracted. The final data set is then sent to the user. Subsequently, if another user executes the same query, data is read from the buffer cache and data result sets are returned back to the user. The data is not always readily available. Situations in which there is insufficient buffer space or data is modified by another session require that the data in the buffer be refreshed and reloaded.

In an Oracle RAC environment, when users execute queries from different instances, instead of the DBWR process having to retrieve data from the I/O subsystem every single time, data is transferred (traditionally) over the interconnect from one instance to another. (In Oracle Database 11g Release 2, the new "bypass reader" algorithm used in the cache fusion technology bypasses data transfer when large numbers of rows are being read and instead uses the local I/O subsystem from the requesting instance to retrieve data.) This provides considerable performance benefits, because latency of retrieving data from an I/O subsystem is much higher compared to transferring data over the network. Basically, network latency is much lower compared to I/O latency.

Once data is transferred to the requesting instance, the execution plan then traverses through the rows to extract the actual result set requested by the user.

There are several types of data access patterns that can be implemented in an Oracle RAC environment based on the type of application and database, for example:

• Method 1: Normal query execution—If the query is going against a small subset of data such as in an OLTP implementation, data is accessed locally from storage.

• Method 2: Parallel query execution—If the query is going against a larger set of data such as in a data warehouse implementation, you could consider using the parallel execution and taking advantage of the resources available across multiple instances.

• Method 3: Result cache—If the query performs analytical functions or is a summary table, you could consider using the Result Cache feature in Oracle 11g, where the final results are stored in the Result Cache making the results available to other users in the system.

Let’s illustrate this with the query we used earlier. In Oracle Database 11g Release 2, each of these options use different methods for retrieving data, and in some cases, these methods have changed compared to previous releases of Oracle Database.

To completely understand the behavior. of the Result Cache feature in an Oracle RAC environment, it would be interesting to discuss the first two methods and then indulge in a discussion on Result Cache.

Method 1: Normal Query Execution

In a four-node cluster (Figure 2), when the query is executed in instance one (SSKY1), as illustrated in the 10046 trace output, the query performs in index fast full scan of the two database tables, ORDERS and ORDER_LINE. This operation reads 345,361 blocks of data from disk (physical I/O) and performs another 345,447 logical I/O operations. The final result set is 300 rows that are then sent back to the user.

Note: The 10046 trace can be enabled using - alter session set events '10046 trace name context forever, level 12' and can be disabled using - alter session set events '10046 trace name context off'. The trace output will be generated in the location defined by the parameter user_dump_dest.

SELECT OL_W_ID, OL_D_ID, OL_NUMBER, sum(OL_AMOUNT),sum(OL_QUANTITY) 
FROM
 ORDER_LINE OL, ORDERS ORD WHERE OL.OL_O_ID = ORD.O_ID AND OL.OL_W_ID = 
  ORD.O_W_ID AND OL.OL_D_ID = ORD.O_D_ID GROUP BY OL_NUMBER, OL_W_ID,OL_D_ID

call     count       cpu    elapsed       disk      query    current        rows
-------       ------      --------      ----------      ----------      ----------      ----------       ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       21     18.42      77.41     345361     345447          0         300
-------       ------      --------      ----------      ----------      ----------      ----------       ----------
total       23     18.43      77.42     345361     345447          0         300

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 89  (schema name)

Rows     Row Source Operation
-------     ---------------------------------------------------
300      HASH GROUP BY (cr=345447 pr=345361 pw=0 time=149 us cost=126413 size=4950 card=150)
21349787 HASH JOIN  (cr=345447 pr=345361 pw=0 time=59061480 us cost=125703 size=680920944 card=20633968)
2134685  INDEX FAST FULL SCAN ORDERS_I2 (cr=11248 pr=11219 pw=0 time=258291 us cost=2743 size=22694870 card=2063170)(object id 86234)
21349787 INDEX FAST FULL SCAN IORDL (cr=334199 pr=334142 pw=0 time=47799580 us cost=87415 size=453947296 card=20633968)(object id 86202)

If this query was executed on instance two (SSKY2), the complete operation is performed all over again (the execution plan looks identical), including the physical I/O and the logical operations, before getting the full results of 300 rows. 

call     count       cpu    elapsed       disk      query    current        rows
-------       ------      --------      ----------      ----------      ----------      ----------       ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       21     31.74      74.34     345361     345447          0         300
-------       ------      --------      ----------      ----------      ----------      ----------       ----------
total       23     31.76      74.36     345361     345447          0         300

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 89  (schema name)

Rows     Row Source Operation
-------  ---------------------------------------------------
    300  HASH GROUP BY (cr=345447 pr=345361 pw=0 time=299 us cost=126413 size=4950 card=150)
21349787 HASH JOIN  (cr=345447 pr=345361 pw=0 time=62985040 us cost=125703 size=680920944 card=20633968)
2134685  INDEX FAST FULL SCAN ORDERS_I2 (cr=11248 pr=11219 pw=0 time=490345 us cost=2743 size=22694870 card=2063170)(object id 86234)
21349787 INDEX FAST FULL SCAN IORDL (cr=334199 pr=334142 pw=0 time=42913972 us cost=87415 size=453947296 card=20633968)(object id 86202)
 

Analyzing  the trace outputs from the two instances and the following listed  wait events, we notice that there was no cache synchronization of  data.  As discussed earlier, this is a new situation with Oracle  Database 11g Release 2 RAC (unlike in Oracle Database 10g)  when a local I/O operation (the bypass  readers  algorithm) would be more beneficial, because it avoids transferring  data across the interconnect. 

Method 2: Parallel Query Execution

The entire behavior. of cache synchronization discussed above changes when parallel operations are enabled. Oracle Database 11g Release 2 introduced several new parameters. The parameter of interest for our discussion is PARALLEL_DEGREE_POLICY. The default value of this parameter is MANUAL. Changing this to AUTO causes Oracle RAC to spawn slaves across multiple instances, when possible, to execute this query without the need of parallel query hints. The number of slaves to spawn on one or more instances is automatic and is based on the availability of resources.

There is another parameter that should also be mentioned, PARALLEL_DEGREE_LIMIT. The value for this parameter could be I/O, CPU, or an integer value that specifies the maximum degree of parallelism.

Let’s try these queries again with the parallel degree policy set to AUTO.

call     count       cpu    elapsed       disk      query    current        rows
-------  ------   -------- ---------- ---------- --------- ----------  ----------
Parse       11      0.01       0.02          0          0          0           0
Execute     11     19.17      86.67     181238     190696          0           0
Fetch       21      0.03       8.34          0          0          0         300
-------  ------   -------- ---------- ----------   -------- ---------- ----------
total       43     19.23      95.04     181238     190696          0         300

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 89  (schema name)   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  PX COORDINATOR  (cr=0 pr=0 pw=0 time=0 us)
      0   PX SEND QC (RANDOM) :TQ10003 (cr=0 pr=0 pw=0 time=0 us cost=10094 size=4950 card=150)
     26    HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=10094 size=4950 card=150)
    260     PX RECEIVE  (cr=0 pr=0 pw=0 time=345 us cost=10094 size=4950 card=150)
      0      PX SEND HASH :TQ10002 (cr=0 pr=0 pw=0 time=0 us cost=10094 size=4950 card=150)
      0       HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=10094 size=4950 card=150)
      0        HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=10025 size=680920944 card=20633968)
      0         PX RECEIVE  (cr=0 pr=0 pw=0 time=0 us cost=305 size=22694870 card=2063170)
      0          PX SEND HASH :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=305 size=22694870 card=2063170)
247592           PX BLOCK ITERATOR (cr=1986 pr=1378 pw=0 time=82701 us cost=305 size=22694870 card=2063170)
247592            INDEX FAST FULL SCAN ORDERS_I2 (cr=1986 pr=1378 pw=0 time=38108 us cost=305 size=22694870 card=2063170)(object id 86234)
      0         PX RECEIVE  (cr=0 pr=0 pw=0 time=0 us cost=9713 size=453947296 card=20633968)
      0          PX SEND HASH :TQ10001 (cr=0 pr=0 pw=0 time=0 us cost=9713 size=453947296 card=20633968)
2153776           PX BLOCK ITERATOR (cr=36697 pr=35311 pw=0 time=4484449 us cost=9713 size=453947296 card=20633968)
2153776            INDEX FAST FULL SCAN IORDL (cr=36697 pr=35311 pw=0 time=4067280 us cost=9713 size=453947296 card=20633968)(object id 86202)

In a parallel operation such as this, the query coordinator identified spawns slaves across various instances in the cluster. Each slave process then retrieves subsets of data and brings it back to the instance where the operation was started for consolidation and displays the result set back to the user. 

It’s interesting to note that when the query executed with the parallel option, the cache fusion technology is used to move data over the interconnect to the requesting instances (that is, the new bypass readers algorithm is not used). This is because data is retrieved and collected in smaller numbers compared to the operation discussed previously for method 1.

From the following partial query output, it should be noticed that the PX coordinator process spawns slave processes on all nodes participating in the cluster operations to complete the query execution.

set linesize 140
col NAME FORMAT A28
col VALUE FORMAT 9999999999
break on inst_id on qcsid on server_set
SELECT stat.inst_id,stat.qcsid, stat.server_set, stat.server#, nam.name, stat.val
ue FROM gv$px_sesstat stat, gv$statname nam WHERE stat.inst_id = nam.inst_id AND
stat.statistic# = nam.statistic# AND nam.name = 'physical reads' ORDER BY 1,2,3;
Ins
 ID      QCSID SERVER_SET    SERVER#   NAME                             VALUE
---       ---------- ----------          ---------- ----------------------------                      -----------
  1         76          1          1 physical reads                         0
                                     physical reads                         0
          1083          1          8 physical reads                      1452
                                   9 physical reads                      1300
                                   7 physical reads                      1348
                        2          8 physical reads                     24832
                                   7 physical reads                     24832
                                   9 physical reads                     24448
                                     physical reads                       151
  2         76          1          2 physical reads                         0
          1083          1          5 physical reads                      1226
                                   6 physical reads                      1328
                                   4 physical reads                      1368
                        2          4 physical reads                     29921
                                   5 physical reads                     29176
                                   6 physical reads                     29920
…………………………

Note: For more background on parallel processing, see this white paper. 

Method 3: Result Cache

How is the Result Cache different? We discussed earlier that the Result Cache provides a great benefit because the final results of the query are stored in a separate section of the shared pool and, subsequently, when a user executes the same query, instead of the process having to traverse through all the million rows in the buffer cache, the process bypasses this step and retrieves data from the Result Cache section of the shared pool.

In an Oracle RAC environment, this process is no different when the query is executed multiple times from one instance; results are retrieved from the Result Cache. So is there a difference? Actually, there is and there is not. The difference is when the second instance in the cluster executes the same query with the /*+ RESULT CACHE */ hint. Instead of getting all the rows from the I/O subsystem (as we observed under Method 1), only the results from the Result Cache are transferred.

This is a great benefit in an Oracle RAC environment, reducing interconnect traffic or calls to the I/O subsystem. Then why does the documentation say the Result Cache is local to the instance? Because it is. In an Oracle RAC environment, there is no global Result Cache; the Result Cache is maintained locally within the shared pool of the instance. (See Figure 3.)

Let’s discuss this feature in an Oracle RAC environment step by step through a workshop.

Workshop

Step 1

Let’s check how much buffer has been allocated to the Result Cache section and how much is currently available. The current utilization of the Result Cache section of the shared pool can be determined using the following:

SQL>SET SERVEROUTPUT ON;
SQL>execute DBMS_RESULT_CACHE.MEMORY_REPORT(TRUE);

R e s u l t   C a c h e   M e m o r y   R e p o r t                             
[Parameters]                                                                    
Block Size          = 1K bytes                                                  
Maximum Cache Size  = 251680K bytes (251680 blocks)                             
Maximum Result Size = 12584K bytes (12584 blocks)                               
[Memory]                                                                        
Total Memory = 12784 bytes [0.000% of the Shared Pool]                          
... Fixed Memory = 12784 bytes [0.000% of the Shared Pool]                      
....... Memory Mgr = 200 bytes                                                  
....... Bloom Fltr = 2K bytes                                                   
.......  = 2088 bytes                                                           
....... Cache Mgr  = 5552 bytes                                                 
....... State bjs = 2896 bytes                                                 
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]         

The Result Cache section of the shared pool can also be verified by querying against the v$sgastat view.

SQL> SELECT * FROM gv$sgastat WHERE POOL='shared pool' AND NAME LIKE 'Result%' AND INST_ID =1;

INST_ID  POOL        NAME                                    BYTES             
-------  ----------- ----------------------------------  ----------             
1 shared pool        Result Cache: State Objs                 2896             
1 shared pool        Result Cache: Memory Mgr                  200             
1 shared pool        Result Cache:                            2088             
1 shared pool        Result Cache: Cache Mgr                  5552             
1 shared pool        Result Cache: Bloom Fltr                 2048  

The output above and the output generated by the memory report earlier indicate identical statistics.

From the output above we notice that no memory of the shared pool has been allocated to the Result Cache section (0.000% of the Shared Pool). The Result Cache is allocated from the dynamic memory section of the shared pool.

Indirectly, we could also verify whether there are any objects present by using the following query. The query lists all the objects currently stored in the Result Cache.

SQL> SELECT INST_ID INT, ID, TYPE, CREATION_TIMESTAMP, BLOCK_COUNT, COLUMN_COUNT,
PIN_COUNT, ROW_COUNT FROM GV$RESULT_CACHE_OBJECTS;

Step 2

Let’s execute the query with the /*+ RESULT CACHE */ hint on instance one (SSKY1).

SELECT /*+ RESULT_CACHE */ OL_W_ID, OL_D_ID, OL_NUMBER, sum(OL_AMOUNT),sum(OL_QUANTITY)
FROM
ORDER_LINE OL, ORDERS ORD WHERE OL.OL_O_ID = ORD.O_ID AND OL.OL_W_ID =
  ORD.O_W_ID AND OL.OL_D_ID = ORD.O_D_ID GROUP BY OL_NUMBER, OL_W_ID, OL_D_ID


call     count       cpu    elapsed       disk      query    current        rows
-------  ------  -------- ---------- ---------- ---------- ----------   ----------
Parse        1      0.01       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       21     33.85      97.00     346671     346755          0         300
-------  ------  --------  -------------------- ----------  ----------   ----------
total       23     33.86      97.03     346671     346755          0         300

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 89  (TPCC)

Rows     Row Source Operation
-------  ---------------------------------------------------
    300  RESULT CACHE  8fbjhchhd9zwh7uhn4mv7dhvga (cr=346755 pr=346671 pw=0 time=1046 us)
    300   HASH GROUP BY (cr=346755 pr=346671 pw=0 time=299 us cost=126413 size=4950 card=150)
21541174    HASH JOIN (cr=346755 pr=346671 pw=0 time=84263640 us cost=125703 size=680920944 card=20633968)
2153881     INDEX FAST FULL SCAN ORDERS_I2 (cr=11664 pr=11635 pw=0 time=566756 us cost=2743 size=22694870 card=2063170)(object id 86234)
21541174     INDEX FAST FULL SCAN IORDL (cr=335091 pr=335036 pw=0 time=62691616 us cost=87415 size=453947296 card=20633968)(object id 86202)

The first part of the statistics collected from the 10046 trace event is identical to the non-Result Cache operation. As illustrated in Figure 3, around 340,000 rows are read from the I/O subsystem (Step 1) and traversed through the buffer cache (Step 2) to obtain a result of 300 summary rows, which is then loaded into the Result Cache section of the shared pool (Step 3).

Figure 3 Result Cache Behavior. in an Oracle RAC Environment

Step 3

Let’s check the Result Cache section and see what we can find.

SQL>SET SERVEROUTPUT ON;
SQL>execute DBMS_RESULT_CACHE.MEMORY_REPORT(TRUE);

R e s u l t   C a c h e   M e m o r y   R e p o r t                             
[Parameters]                                                                    
Block Size          = 1K bytes                                                  
Maximum Cache Size  = 251680K bytes (251680 blocks)                             
Maximum Result Size = 12584K bytes (12584 blocks)                               
[Memory]                                                                        
Total Memory = 207000 bytes [0.004% of the Shared Pool]                         
... Fixed Memory = 12784 bytes [0.000% of the Shared Pool]                      
....... Memory Mgr = 200 bytes                                                  
....... Bloom Fltr = 2K bytes                                                   
.......  = 2088 bytes                                                           
....... Cache Mgr  = 5552 bytes                                                 
....... State bjs = 2896 bytes                                                 
... Dynamic Memory = 194216 bytes [0.004% of the Shared Pool]                   
....... verhead = 161448 bytes                                                 
........... Hash Table    = 64K bytes (4K buckets)                              
........... Chunk Ptrs    = 62920 bytes (7865 slots)                            
........... Chunk Maps    = 31460 bytes                                         
........... Miscellaneous = 1532 bytes                                          
....... Cache Memory = 32K bytes (32 blocks)                                    
........... Unused Memory = 23 blocks                                           
........... Used Memory = 9 blocks                                              
............... Dependencies = 2 blocks (2 count)                               
............... Results = 7 blocks                                              
................... SQL     = 7 blocks (1 count)

One execution of the query has changed the memory report. .004% of the dynamic memory section has been allocated to the Result Cache section to store 9 blocks of data. What’s the math behind the 9 blocks of data? Two blocks are allocated to the dependent objects that are part of the query (ORDER_LINE and ORDERS) and 7 blocks are allocated to the result set of the query. There are 5 columns in the result set all having datatype NUMBER and the result has 300 rows. This amounts to about 5.5 blocks of data, which is rounded to 6 blocks; one additional block is used for the query metadata. 

Note: The Result Cache block size should not be confused with the data block size. As indicated in the report, the Result Cache block size is 1K and the database block size is 8K.

A useful view to check on the objects stored in the Result Cache is the V$RESULT_CACHE_OBJECTS view. It provides most of the information contained in the Result Cache, including object dependency and invalidations when the underlying data or object changes.

The following subset of data from the V$RESULT_CACHE_OBJECTS view gives the basic information pertaining to the query and its results. The output lists two dependencies and one result. The result also contains information such as the number of columns and the total number of rows in the result set. 

SQL> SELECT inst_id INT, ID, TYPE, CREATION_TIMESTAMP, BLOCK_COUNT, COLUMN_COUNT, PIN_COUNT, ROW_COUNT FROM GV$RESULT_CACHE_OBJECTS WHERE INST_ID=&&instNum;

INT ID TYPE       CREATION_ BLOCK_COUNT COLUMN_COUNT  PIN_COUNT  ROW_COUNT  
----------------- --------------------------------   ----------  --------- 
1   1  Dependency 09-FEB-10           1          0          0          0 
1   0  Dependency 09-FEB-10           1          0          0          0
1   2  Result     09-FEB-10           7          5          0        300 

The following output lists all the objects used by the query (dependency) and the query itself stored as a result. As discussed in step 1 of this workshop, the area of the Result Cache is identified by an ID called the CACHE_ID. Oracle Database 11g generates the same CACHE_ID every single time for the exact same query irrespective of how many times the query is executed and across how many instances in the cluster it is executed from. 

SQL> SELECT INST_ID INT, ID, TYPE, STATUS, NAME, OBJECT_NO OBJNO,CACHE_ID,INVALIDATIONS INVALS 
FROM GV$RESULT_CACHE_OBJECTS WHERE INST_ID=&&instNum;

INT ID TYPE       STATUS    NAME                                OBJNO CACHE_ID                   INVALS                            
----  --- ----------      --------      --------------------------------                    ------   --------------------------                 ------                            
1   1  Dependency Published TPCC.ORDERS                         86209 TPCC.ORDERS                     0                            
1   0  Dependency Published TPCC.ORDER_LINE                     86201 TPCC.ORDER_LINE                 0                            
1   2  Result     Published SELECT /*+ RESULT_CACHE */ OL_W_ID      0 8fbjhchhd9zwh7uhn4mv7dhvga      0                            
                              ,OL_D_ID,OL_NUMBER,sum(OL_AMOUNT),                                                                                                                                        
                              sum(OL_QUANTITY) FROM ORDER_LINE O                                                                                                                                        
                              L, ORDERS ORD WHERE OL.OL_  

                                                                                                                                                                                    Note: The values found in column OBJECT_NO (OBJNO)correspond to the value of the OBJECT_ID column in the DBA_OBJECTS view. 

Continuing our look at the subsets of data found in the V$RESULT_CACHE_OBJECTS view, the following output lists the current space utilization of the results section for this operation.

SQL> SELECT INST_ID INT,ID,TYPE,BLOCK_COUNT BLKCNT,COLUMN_COUNT CLMCNT,SCAN_COUNT,ROW_COUNT RCNT,ROW_SIZE_MAX RSM,
ROW_SIZE_AVG RSA,SPACE_OVERHEAD SOH,SPACE_UNUSED SUN FROM GV$RESULT_CACHE_OBJECTS WHERE INST_ID=&&instNum;

INT ID TYPE        BLKCNT  CLMCNT RCNT   RSM   RSA   SOH   SUN
------- --------- ------- ------- ----- ----- ----- -----  -----
1   1  Dependency       1       0   0     0     0     0     0
1   0  Dependency       1       0   0     0     0     0     0
1   2  Result           7       5 300    27    26   536    35

Step 4

From the previous output discussed in Step 2 of the workshop, we noticed that .004% of the dynamic memory section of the shared pool was utilized by this operation. The following query provides a detailed report:

INST_ID POOL      NAME                                       BYTES
------- ----------------------------------------------   ----------
1    shared pool  Result Cache: State Objs                   2896
1    shared pool  Result Cache                             194216
1    shared pool  Result Cache: Memory Mgr                    200
1    shared pool  Result Cache:                              2088
1    shared pool  Result Cache: Cache Mgr                    5552
1    shared pool  Result Cache: Bloom Fltr                   2048

We now have an understanding of what happens with the Result Cache on instance one (SSKY1). As observed, this functionality has identical behavior. as in the case of a single-instance implementation. 

One of the great benefits of Oracle RAC is that multiple users can query against the same set of data from multiple instances in the cluster. If a user were to execute the exact same query from either instance 2 (SSKY2), instance 3 (SSKY3), or instance 4 (SSKY4), as discussed previously, depending on the volume of data being retrieved, the data could be either transferred via the interconnect using cache fusion or, as in Oracle Database 11g Release 2, the data could be retrieved from storage locally (using the new bypass readers algorithm). Again, not only does the process have to transfer all the data over the interconnect or load it from storage, the process has to traverse through all the rows available in the buffer cache before the result set is built.

Cruising along with our workshop, let’s execute the query on instance 3 (SSKY3) and observe the details. The great performance benefit of using the Result Cache feature in an Oracle RAC environment illustrated in Figure 3 would be that only the final result set would be transferred between the instances, reducing considerable resources for both the CPU and network. 

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       21      0.00       0.00          0          0          0         300
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       23      0.02       0.03          0          0          0         300

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 89  (TPCC)

Rows     Row Source Operation
-------  ---------------------------------------------------
    300  RESULT CACHE  8fbjhchhd9zwh7uhn4mv7dhvga (cr=5 pr=0 pw=0 time=299 us)
      0   HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=126413 size=4950 card=150)
      0    HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=125703 size=680920944 card=20633968)
      0     INDEX FAST FULL SCAN ORDERS_I2 (cr=0 pr=0 pw=0 time=0 us cost=2743 size=22694870 card=2063170)(object id 86234)
      0     INDEX FAST FULL SCAN IORDL (cr=0 pr=0 pw=0 time=0 us cost=87415 size=453947296 card=20633968)(object id 86202)

From the statistics collected from the 10046 trace event, it should be noticed that there is no data being retrieved locally nor is there any traversing of rows in the buffer cache of the local instance. The execution plan indicates that there was fetch of just the final result set. How and where did this come from? Using the cache fusion algorithm, Oracle Database was able to retrieve the result set from the Result Cache section of the shared pool in instance one and transfer the data over the interconnect. Isn’t this neat? This really cuts down so much on data processing and improves resource utilization. 

The memory structure of the Result Cache section of the shared pool is also identical to the structure found on the instance where the query was executed for the first time. This proves two things: no additional memory or resource is utilized for the second instance, and each Oracle RAC instance maintains its own copy of the result set in its local Result Cache.

R e s u l t   C a c h e   M e m o r y   R e p o r t                             
[Parameters]                                                                    
Block Size          = 1K bytes                                                  
Maximum Cache Size  = 251680K bytes (251680 blocks)                             
Maximum Result Size = 12584K bytes (12584 blocks)                               
[Memory]                                                                        
Total Memory = 208144 bytes [0.004% of the Shared Pool]                         
... Fixed Memory = 13928 bytes [0.000% of the Shared Pool]                      
....... Memory Mgr = 200 bytes                                                  
....... Bloom Fltr = 2K bytes                                                   
.......  = 3232 bytes                                                           
....... Cache Mgr  = 5552 bytes                                                 
....... State bjs = 2896 bytes                                                 
... Dynamic Memory = 194216 bytes [0.004% of the Shared Pool]                   
....... verhead = 161448 bytes                                                 
........... Hash Table    = 64K bytes (4K buckets)                              
........... Chunk Ptrs    = 62920 bytes (7865 slots)                            
........... Chunk Maps    = 31460 bytes                                         
........... Miscellaneous = 1532 bytes                                          
....... Cache Memory = 32K bytes (32 blocks)                                    
........... Unused Memory = 23 blocks                                           
........... Used Memory = 9 blocks                                              
............... Dependencies = 2 blocks (2 count)                               
............... Results = 7 blocks                                              
................... SQL = 7 blocks (1 count)                                

PL/SQL procedure successfully completed.

One execution of the query on instance 3 gives an identical memory structure to that on instance 1; .004% of the dynamic memory section has been allocated to the Result Cache section to store 9 blocks of data. 

Querying the GV$RESULT_CACHE_OBJECTS view, there are two Result Cache sections, one on instance 1 and another on instance 2 , indicating that in an Oracle RAC environment, Oracle Database does not maintain a global Result Cache section. Rather it manages the Result Cache locally within the instance.

SQL> SELECT INST_ID INT, ID, TYPE, STATUS, NAME, OBJECT_NO OBJNO,CACHE_ID,INVALIDATIONS INVALS FROM GV$RESULT_CACHE_OBJECTS;

INT ID TYPE       STATUS    NAME                                OBJNO CACHE_ID                    INVALS
--- -- ---------- --------- ----------------------------------  ------ --------------------------- -----
  3  1 Dependency Published TPCC.ORDERS                         86209 TPCC.ORDERS                     0
  3  0 Dependency Published TPCC.ORDER_LINE                     86201 TPCC.ORDER_LINE                 0
  3  2 Result     Published SELECT /*+ RESULT_CACHE */ OL_W_ID      0 8fbjhchhd9zwh7uhn4mv7dhvga      0
                            ,OL_D_ID,OL_NUMBER,sum(OL_AMOUNT),
                            sum(OL_QUANTITY) FROM ORDER_LINE O
                            L, ORDERS ORD WHERE OL.OL_

  1  1 Dependency Published TPCC.ORDERS                         86209 TPCC.ORDERS                     0
  1  0 Dependency Published TPCC.ORDER_LINE                     86201 TPCC.ORDER_LINE                 0
  1  2 Result     Published SELECT /*+ RESULT_CACHE */ OL_W_ID      0 8fbjhchhd9zwh7uhn4mv7dhvga      0
                            ,OL_D_ID,OL_NUMBER,sum(OL_AMOUNT),
                            sum(OL_QUANTITY) FROM ORDER_LINE O
                            L, ORDERS ORD WHERE OL.OL_
6 rows selected.

There are several factors to observe in the output above: 

• The query has the same CACHE_ID on both instances.

• From the execution plan (step 4), we observed that the number of rows in the Result Cache of instance 3 is identical to instance 1.

• The cluster has four instances; however, the view contains data only when the Result Cache for the instance has been utilized, meaning there are no entries in the Result Cache section for instance 2 and instance 4, respectively. However, when a user executes the identical query on either of these instances, the behavior. observed on instance 3 will be duplicated on these instances.

Will the query keep returning the same result set all the time? What happens when data changes in the underlying objects? What happens to the Result Cache section that contains the results?

Good questions. Let’s continue with our workshop and try to answer some of them.

When data changes in any of the underlying objects, Oracle Database invalidates the result set on all the instances in the cluster, indicating that subsequent execution of the same query will require fresh processing of data to rebuild the result set and store the result in the Result Cache section of the shared pool. 

INT ID TYPE       STATUS    NAME                                OBJNO  CACHE_ID                   INVALS
--- -- ---------- --------- ----------------------------------  ------ -------------------------- ------
  1  1 Dependency Published TPCC.ORDERS                         86209 TPCC.ORDERS                     0
  1  0 Dependency Published TPCC.ORDER_LINE                     86201 TPCC.ORDER_LINE                 1
  1  2 Result     Invalid   SELECT /*+ RESULT_CACHE */ OL_W_ID      0 8fbjhchhd9zwh7uhn4mv7dhvga      0
                            ,OL_D_ID,OL_NUMBER,sum(OL_AMOUNT),
                            sum(OL_QUANTITY) FROM ORDER_LINE O
                            L, ORDERS ORD WHERE OL.OL_

  3  1 Dependency Published TPCC.ORDERS                         86209 TPCC.ORDERS                     0
  3  0 Dependency Published TPCC.ORDER_LINE                     86201 TPCC.ORDER_LINE                 1
  3  2 Result     Invalid   SELECT /*+ RESULT_CACHE */ OL_W_ID      0 8fbjhchhd9zwh7uhn4mv7dhvga      0
                            ,OL_D_ID,OL_NUMBER,sum(OL_AMOUNT),
                            sum(OL_QUANTITY) FROM ORDER_LINE O
                            L, ORDERS ORD WHERE OL.OL_

If the query were executed again to retrieve the new result set from the database, a new row (ID=9) is added to the Result Cache section. Oracle Database retains the invalid result set until the instance is bounced, the Result Cache is flushed, or the data in the Result Cache ages out of the buffer. 

INT ID TYPE       STATUS    NAME                                OBJNO CACHE_ID                   INVALS
--- -- ---------- --------- ----------------------------------  ------ ------------------------- ------
  1  1 Dependency Published TPCC.ORDERS                         86209 TPCC.ORDERS                     0
  1  0 Dependency Published TPCC.ORDER_LINE                     86201 TPCC.ORDER_LINE                 1
  1  9 Result     Published SELECT /*+ RESULT_CACHE */ OL_W_ID      0 8fbjhchhd9zwh7uhn4mv7dhvga      0
                            ,OL_D_ID,OL_NUMBER,sum(OL_AMOUNT),
                            sum(OL_QUANTITY) FROM ORDER_LINE O
                            L, ORDERS ORD WHERE OL.OL_

  1  2 Result     Invalid   SELECT /*+ RESULT_CACHE */ OL_W_ID      0 8fbjhchhd9zwh7uhn4mv7dhvga      0
                            ,OL_D_ID,OL_NUMBER,sum(OL_AMOUNT),
                            sum(OL_QUANTITY) FROM ORDER_LINE O
                            L, ORDERS ORD WHERE OL.OL_

  3  1 Dependency Published TPCC.ORDERS                         86209 TPCC.ORDERS                     0
  3  0 Dependency Published TPCC.ORDER_LINE                     86201 TPCC.ORDER_LINE                 1
  3  2 Result     Invalid   SELECT /*+ RESULT_CACHE */ OL_W_ID      0 8fbjhchhd9zwh7uhn4mv7dhvga      0
                            ,OL_D_ID,OL_NUMBER,sum(OL_AMOUNT),
                            sum(OL_QUANTITY) FROM ORDER_LINE O
                            L, ORDERS ORD WHERE OL.OL_

In the output above, when a few rows are deleted from the ORDER_LINE table, the result row is marked as Invalid and the INVALIDATIONS (INVALS) counter of the GV$RESULT_CACHE_OBJECTS view is incremented. If, subsequently, there were another operation on the ORDER_LINE table, either by adding new rows or by deleting a few more rows, the counter will be incremented again. This indicates how many times the object was invalidated in the Result Cache. 

How long does this remain invalid? Like other cache areas in an Oracle Database instance, the memory management of the Result Cache section is also the same; the invalid results will be moved to the dirty list and will be flushed out of the cache section as new data gets loaded to the cache section.

Limitations of the Result Cache Feature

• The primary limitation of the Result Cache is the fact that the results are stored in cache only. This means when the instance fails or is shut down for maintenance, the data is cleared. If it is highly critical that the result sets are permanently stored in the database, options such as materialized views should be used.

• There are a few types of operations that are not supported when using this feature. For example queries cannot include or use SQL functions such as CURRENT_DATE, CURRENT_TIMESTAMP, LOCAL_TIMESTAMP, SYS_CONTEXT, SYS_GUID, SYS_TIMESTEMP, USERENV, and so on. If they are used, the following type of error occurs:

ERROR  at line 1:
ORA-00904:  "SYS_TIMESTAMP": invalid identifier

• Results or data sets from objects/tables in the SYS or SYSTEM schema cannot be cached using this feature. Data from CURRVAL and NEXTVAL pseudo columns from database sequences cannot be cached using this feature.

Conclusion

The Result Cache feature introduced in Oracle Database 11g Release 1 provides considerable benefits for operations where results sets do not change very often. This feature helps store the final result sets in memory, providing high-speed access to the dataset.

This new feature is very helpful when data in the database does not change very frequently, which invalidates the data result sets, such as in a data warehouse or reporting database environment.