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.
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
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.
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.| Instance 1 (SSKY1) | Instance 2 (SSKY2) | ||
| Event waited on | Times | Event waited on | Times |
| -------------------------------- | Waited | ------------------------------ | Waited |
| ges message buffer allocation | 466 | ges message buffer allocation | 135 |
| library cache lock | 2 | library cache pin | 2 |
| row cache lock | 14 | row cache lock | 18 |
| SQL*Net message to client | 21 | SQL*Net message to client | 21 |
| Disk file operations I/O | 3 | Disk file operations I/O | 1 |
| os thread startup | 1 | os thread startup | 1 |
| KSV master wait | 2 | KSV master wait | 2 |
| ASM file metadata operation | 1 | ASM file metadata operation | 1 |
| db file sequential read | 6 | db file sequential read | 6 |
| db file parallel read | 1 | db file parallel read | 96 |
| db file scattered read | 2762 | db file scattered read | 2667 |
| asynch descriptor resize | 1 | asynch descriptor resize | 1 |
| gc current grant busy | 206 | ||
| gc cr block 2-way | 50 | gc cr block 2-way | 21 |
| SQL*Net message from client | 21 | SQL*Net message from client | 21 |
| gc cr multi block request | 112 | ||
| gc cr block 3-way | 22 |
Sidebar: Comparing 11g Release 2 with 10g Release 2On a similar four-node Oracle RAC cluster with Oracle Database 10g Release 2, when the previous query is executed, there is a considerable amount of cache synchronization between the instances in the cluster. Instance 1: The execution plan generated from the query in a 10g Release 2 database indicates that the query did about 214,682 disk I/O operations and 42,907,485 logical I/O operations to produce the result set of 300 rows. 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 248.08 288.49 214682 42907485 0 300 -------------------------------------------------------------------- total 23 248.09 288.51 214682 42907485 0 300 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 462 (TPCC) Rows Row Source Operation ------- --------------------------------------------------- 300 HASH GROUP BY (cr=42907485 pr=214682 pw=0 time=288494487 us) 21349787 NESTED LOOPS (cr=42907485 pr=214682 pw=0 time=320320093 us) 21349787 INDEX FULL SCAN IORDL (cr=207908 pr=208355 pw=0 time=85453357 us)(object id 616250) 21349787 INDEX UNIQUE SCAN ORDERS_I1 (cr=42699577 pr=6327 pw=0 time=156657073 us)(object id 616287) Instance 2: The execution plan from executing the query on the second instance indicates that while the logical I/O operations remain the same, there is almost no disk I/O. Then how did the data get into the database buffer cache? As illustrated in the wait event statistics below, this happens over the private interconnect using the cache fusion technology. 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 246.53 271.06 3 42907483 0 300 ------------------------------------------------------------------- total 23 246.54 271.07 3 42907483 0 300 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 462 (TPCC) Rows Row Source Operation ------- --------------------------------------------------- 300 HASH GROUP BY (cr=42907483 pr=3 pw=0 time=271060491 us) 21349787 NESTED LOOPS (cr=42907483 pr=3 pw=0 time=320262423 us) 21349787 INDEX FULL SCAN IORDL (cr=207907 pr=3 pw=0 time=85413649 us)(object id 616250) 21349787 INDEX UNIQUE SCAN ORDERS_I1 (cr=42699576 pr=0 pw=0 time=146357573 us)(object id 616287)
Note: The gc current block 2-way wait event occurs when the block is currently not in the buffer cache of the local instance but is available on another instance (holder) and the block needs to be transferred over the interconnect to the requesting instance performing a two-hop operation. Applying this to the discussion above, the block was not in the buffer cache of instance 2 (requestor). However, since a previous user executed this query on instance 1 (holder), the blocks had to be transferred via the interconnect to instance 2. Note: The gc current block 3-way wait event occurs when the block is currently not in the buffer cache of the local instance (requestor) but is available on another instance (holder) but the block was mastered on a third instance and had to perform 3 hops before the requesting instance received the block. Irrespective of the number of instances in the cluster, this is the maximum number of hops that can occur before the requestor receives the block. Comparing the logical I/O operations between Oracle Database 11g Release 2 and Oracle Database 10g Release 2, it is obvious that there is a high amount of logical I/O in Oracle Database 10g Release 2 compared to Oracle Database 11g Release 2. This is a result of the improvements that have been incorporated into the Oracle Database 11g Release 2 database optimizer. |
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.
------- ------ -------- ---------- ---------- --------- ---------- ----------
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)
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 1Let’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 Objs = 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)
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
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 Objs = 2896 bytes ... Dynamic Memory = 194216 bytes [0.004% of the Shared Pool] ....... Overhead = 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;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.Note: The values found in column OBJECT_NO (OBJNO)correspond to the value of the OBJECT_ID column in the DBA_OBJECTS view.
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 Objs = 2896 bytes ... Dynamic Memory = 194216 bytes [0.004% of the Shared Pool] ....... Overhead = 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.
- 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.
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.
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.
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.
No comments:
Post a Comment