Shulou(Shulou.com)06/01 Report--

This article shows you how to use the oracle result cache result set cache, which is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Result set caching

Caching is the most common technology used to improve performance in computer systems. Hardware and software are widely used. Oracle databases are no exception. For example, data file blocks are cached in buffer cache, dictionary information is cached in dictionary cache, and cursors are cached in library cache. The result set cache is also available in oracle11G.

How result set caching works

The oracle database engine provides three types of result set caching:

Server result set cache, also known as query result cache, is a server-side cache, which is used to store query result sets.

Pl/sql function result set cache is a server-side cache, which is used to store the result set returned by pl/sql function.

The client-side result set cache is a client-side cache that stores query result sets.

Server result set cache

The server result set cache is used to avoid repeated execution of queries. Simply put, the query is executed for the first time, and its result set is stored in the shared pool. Then when the same query is executed later, the result set is extracted directly from the result set cache without recalculation. Note that the two queries are considered equal. So the same result set can be used. In addition, if binding variables appear, then their values must be the same. This is necessary because it is obvious that the bound variable is input as a parameter and passed to the query. Therefore, different binding variable values will have different result sets. Note that the result set cache is stored in a shared pool, and all connected sessions for a specified instance can share the same cache entry.

The following is an example. Notice that the result_cache prompt is specified during the query to enable result set caching. The first execution took 1.04 seconds. You can see that the operation result cache in the execution plan determines that result set caching is enabled for the query. In the execution plan, starts clearly states that all operations must be performed at least once. It is necessary to perform all the actions in the plan because this is the first time to execute this query. Therefore, the result set cache does not contain result sets.

SQL > alter session set statistics_level=all;SQL > SELECT / * + result_cache * / 2 p.prod_category, c.country_id, 3 sum (s.quantity_sold) AS quantity_sold, 4 sum (s.amount_sold) AS amount_sold 5 FROM sh.sales s, sh.customers c, sh.products p 6 WHERE s.cust_id = c.cust_id 7 AND s.prod_id = p.prod_id 8 GROUP BY p.prod_category, c.country_id 9 ORDER BY p.prod_category, c.country_id Line 81 has been selected. Time spent: 00: 00: 01.04SQL > select * from table (dbms_xplan.display_cursor (null,null,'all allstats')) PLAN_TABLE_OUTPUT----SQL_ID g9sxqz9qgutu7 Child number 0--SELECT / * + result_cache * / p.prod_category, c. Maxim (s.quantity_sold) AS quantity_sold, sum (s.amount_sold) ASamount_sold FROM sh.sales s, sh.customers c, sh.products p WHEREs.cust_id = c.cust_id AND s.prod_id = p.prod_id GROUP BYp.prod_category C.country_id ORDER BY p.prod_category C.country_idPlan hash value: 1866882273 Muhashi Muhashi PLANCHELECTUTUTUTUTUTUTUTUTMAIL- - -| Id | Operation | Name | Starts | E-Rows | E-Bytes | Cost (% CPU) | E-Time | Pstart | Pstop | A-Rows | A-Time | OMem | 1Mem | Oamp 1max M |- - -| 0 | SELECT STATEMENT | | 1 | 985 (100) | 81 | 00.01 | | | 1 | RESULT CACHE | cu8pf4s7jn9d05yda1swvwpd7y | 1 | 81 | 00 SORT GROUP BY 00.01 | 2 | SORT GROUP BY | 1 | 68 | 3808 | 985 (7) | 00:00:12 | | | 0 | 0 | 73728 | 73728 | * 3 | HASH JOIN | | 1 | 968 | 54208 | 984 (7) | 00:00:12 | 0 | 00Vera 00.01 | 940K | 940K | | 4 | VIEW | | | index$_join$_003 | 1 | 72 | 1512 | 3 (34) | 00:00:01 | 0 | 00001 | * 5 | HASH JOIN | | 1 | | | 0 | 00 INDEX FAST FULL SCAN 00.01 | 1269 K | 1269 K | 6 | INDEX FAST FULL SCAN | PRODUCTS_PK | 1 | 72 | 1512 | 1 (0) | 00:00:01 | 0 | 00 INDEX FAST FULL SCAN | 00.01 | 7 | INDEX FAST FULL SCAN | PRODUCTS_PROD_CAT_IX | | | 1 | 72 | 1512 | 1 (0) | 00:00:01 | 0 | 00 VIEW | VW_GBC_9 | 1 | 968 | 33880 | 981 (7) | 00:00:12 | | 0 | 000.00 HASH GROUP BY | | 9 | HASH GROUP BY | | 0 | 968 | 26136 | 981,981 (7) | 00:00:12 | 0 | 00 | 00001 | 56m | 4744K | | * 10 | HASH JOIN | | | 1 | 918k | 23m | 941m | 941 (3) | 00:00:12 | 0 | 00VOGULAR 00.01 | 3056K | 1398K | | 11 | TABLE ACCESS FULL | CUSTOMERS | 1 | 55500 | 541K | 406K (1) | 00:00:05 | 0 | 00VOVOLOR 00.01 | | | 12 | PARTITION RANGE ALL | | 1 | 918K | 14m | 530m | 530m (3) | 00:00:07 | 1 | 28 | 0 | 00lv 00.01 | | 13 | TABLE ACCESS FULL | SALES | 28 | 918K | 14m | 530m (3) | 00:00: | 07 | 1 | 28 | 0 | 00, 00, 00, 00, 00.01 |- -

The second execution took only 0.03 seconds. The starts column shows the number of times all operations were performed in the execution plan. RESULT CACHE is held once. Other operations were not performed. In other words, the result set stored in the result set cache is directly used.

SQL > SELECT / * + result_cache * / 2 p.prod_category, c.country_id, 3 sum (s.quantity_sold) AS quantity_sold, 4 sum (s.amount_sold) AS amount_sold 5 FROM sh.sales s, sh.customers c, sh.products p 6 WHERE s.cust_id = c.cust_id 7 AND s.prod_id = p.prod_id 8 GROUP BY p.prod_category, c.country_id 9 ORDER BY p.prod_category, c. Time spent: 00: 00: 00.03SQL > select * from table (dbms_xplan.display_cursor (null,null,'all allstats')) PLAN_TABLE_OUTPUT----SQL_ID g9sxqz9qgutu7 Child number 0--SELECT / * + result_cache * / p.prod_category, c. Maxim (s.quantity_sold) AS quantity_sold, sum (s.amount_sold) ASamount_sold FROM sh.sales s, sh.customers c, sh.products p WHEREs.cust_id = c.cust_id AND s.prod_id = p.prod_id GROUP BYp.prod_category C.country_id ORDER BY p.prod_category C.country_idPlan hash value: 1866882273 Muhashi Muhashi PLANCHELECTUTUTUTUTUTUTUTUTMAIL- - -| Id | Operation | Name | Starts | E-Rows | E-Bytes | Cost (% CPU) | E-Time | Pstart | Pstop | A-Rows | A-Time | OMem | 1Mem | Oamp 1max M |- - -- | 0 | SELECT STATEMENT | | 1 | 985 (100) | 162 | 00001 | 00.01 | | 1 | RESULT CACHE | cu8pf4s7jn9d05yda1swvwpd7y | 1 | 162 | 00 SORT GROUP BY 00.01 | 2 | SORT GROUP BY | 0 | 68 | 3808 | 985 (7) | 00:00:12 | | | 0 | 73728 | 73728 | * 3 | HASH JOIN | | 0 | 968 | 54208 | 984 (7) | 00:00:12 | 0 | 00 | 00Vera 00.01 | 940K | 940K | | 4 | VIEW | index$ | _ join$_003 | 0 | 72 | 1512 | 3 (34) | 00:00:01 | 0 | 0000 join$_003 00.01 | * 5 | HASH JOIN | | 0 | | | 0 | 00 INDEX FAST FULL SCAN 00.01 | 1269 K | 1269 K | 6 | INDEX FAST FULL SCAN | PRODUCTS_PK | 0 | 72 | 1512 | 1 (0) | 00:00:01 | 0 | 00 INDEX FAST FULL SCAN | 00.01 | | 7 | INDEX FAST FULL SCAN | | | 0 | 72 | 1512 | 1 (0) | 00:00:01 | 0 | 00 VIEW | VW_GBC_9 | 0 | 968 | 33880 | 981 (7) | 00:00:12 | 0 | 00 | : 00PUR 00.01 | | 9 | HASH GROUP BY | | 0 | 968 | 26136 | 981 (7) | 00:00:12 | 0 | 00RV 00.01 | 56m | 4744K | | * 10 | HASH JOIN | | | 0 | 918K | 23m | 941m | 941 (3) | 00:00:12 | 0 | 00VOGULAR 00.01 | 3056K | 1398K | | 11 | TABLE ACCESS FULL | CUSTOMERS | 0 | 55500 | 541K | 406K (1) | 00:00:05 | 0 | 00PULV 00.01 | | 12 | | | PARTITION RANGE ALL | | 0 | 918K | 14m | 530m | 530m (3) | 00:00:07 | 1 | 28 | 0 | 00VRV 00.01 | | 13 | TABLE ACCESS FULL | SALES | 0 | 918K | 14m | 530m (3) | 00:00:07 | 1 | | | 28 | 0 | 000.00 | 00.01 |-| -

Pay attention to a name cache ID in the execution plan, which is related to the operation result cache. If you know the cached ID, you can query the v$resul_cache_objects view to display information about the cached data. The following query shows that the cached result set has been published (in other words, it can be used). The view information shows how the cached result set was created, how long it took to create it, how many rows of records were stored and how many times it was referenced. Other views that provide information about caching result sets include vstores, results, cachets, dependencies, books, cachets, memory and v$result_chace_statistics.

SQL > SELECT status, creation_timestamp, build_time, row_count, scan_count 2 FROM v$result_cache_objects 3 WHERE cache_id = 'cu8pf4s7jn9d05yda1swvwpd7y' STATUS CREATION_TIMESTAMP BUILD_TIME ROW_COUNT SCAN_COUNT--Published 2013-7-5 9:21:26 94 81 3

To ensure the consistency of the results (that is, whether the result set is the same depends on whether it comes from the cache or from the database calculation). Whenever the object referenced in the query changes, the cache entries that depend on these tables will be invalid. That's the way it is, even if the real change doesn't happen. For example. Even a select * for update statement followed by a commit commit causes cached entries that depend on the select table to become invalid.

There are some dynamic initialization parameters that control the server result set cache:

Result_cache_max_size: specifies the total amount of memory used for result set caching in the shared pool in byte. If it is set to 0, this feature will be disabled. The default value is a value greater than 0, which is derived from the shared pool. Memory allocation is dynamic, so initialization parameters can only specify its upper limit. You can use the following query to display the amount of memory currently allocated:

SQL > SELECT name, sum (bytes) FROM v$sgastat WHERE name LIKE 'Result Cache%' GROUP BY rollup (name) NAME SUM (BYTES)-Result Cache 161680Result Cache: Bloom Fltr 2048Result Cache: Cache Mgr 208Result Cache: Memory Mgr 200Result Cache: State Objs 2896 167032 Shows that the value of resulr_cache_max_size is 15424KSQL > show parameter result NAME TYPE VALUE -client_result_cache_lag big integer 3000client_result_cache_size big integer 0result_cache_max_result integer 5result_cache_max_size big integer 15424Kresult_cache_mode string MANUALresult_cache_remote_expiration integer 0

Result_cache_mode: specify when to use result set caching. You can set it to manual manually, which is the default or to force. When using manual, the result set cache is used only when the result_cache prompt is specified. When using force, result set caching is used for all queries that do not use no_result_cache prompts. Because in most cases we only use result set caching for a small number of queries, set this parameter to its default value of manual to enable it by specifying result_cache in the query when it is only needed.

Result_cache_max_result: specifies the percentage of result_cache_max_size server result set cache size occupied by a single result set cache. Its default value is 5. 5. Its value can be any number from 0 to 100. The result set cache that exceeds this limit will be invalid.

Result_cache_remote_expiration: specifies the validity time (in minutes) of the result set based on the remote object. This is necessary because the invalidated result set cache based on these remote objects cannot be executed when remote objects have changed. These result sets become invalid only after the valid time specified by the initialization parameter. The default value of this parameter is 0, which means that query caching based on remote objects is disabled.

Initialization parameters result_cache_max_size and result_cache_max_result can only be modified at the system level. Other result_cache_mode

And result_cache_remote_expiration can be modified at the session level.

Note: setting the result_cache_remote_expiration parameter to a number greater than 0 will result in outdated result set caching. So only if you really understand the impact of this can you set it to greater than 0.

There are several obvious limitations to using the result set cache:

Queries use non-deterministic functions, sequences and temporary tables are result sets that are not cached

The result set will not be cached when the query violates read consistency. For example, when a result set is created through a session, the referenced table is experiencing the result of a large number of transactions

Sets are not cached.

The result set of a query that references a data dictionary view is not cached.

Dbms_result_cache package

You can use the dbms_result_cache package to manage the result set cache, which provides the following programs to manage:

Bypass: temporarily disables or enables result set caching at the session or system level.

Flush: clear all objects from the result set cache

Invalidate: invalidates all result set caches that depend on the specified database object

Invalidate_object: invalidate a single result set cache entry

Memory_report: generate a memory usage report

Status: displays the status of the result set cache.

For example:

SQL > select dbms_result_cache.status from dual; STATUS----ENABLED

Pl/sql function result set cache

The pl/sql function result set cache is similar to the server result set cache, but it supports the pl/sql function and shares the same memory structure as the server result set cache. Its purpose is to store the value returned by the function in the result set cache (only the value returned by the function, the value returned by the procedure cannot be cached). Functions that obviously use different input values are cached in the result set cache with different cache entries. A function that enables result set caching is shown in the following example. To enable pl/sql function result set caching, specify the result_ cache clause. You can also specify the relies_ on clause to specify which table the function depends on to return the result.

SQL > CREATE OR REPLACE FUNCTION f 2 RETURN NUMBER 3 RESULT_CACHE RELIES_ON (t) IS 4 l_ret NUMBER; 5 BEGIN 6 SELECT count (t.indi_id) INTO l_ret FROM impl_chenzhou.bs_insured t; 7 RETURN lemorette; 8 END; 9 / Function created

In the following example, the function will be called 2480625 times f without using the result set cache (by using the bypass procedure to temporarily disable the result set cache) for a total of 4.69 seconds

SQL > execute dbms_result_cache.bypass (bypass_mode = > TRUE, session = > TRUE); SQL > select count (f) from impl_chenzhou.bs_insured t; COUNT (F)-2480625Elapsed: 2480625 times in the following example the function will be called f using the result set cache 0.32 seconds SQL > execute dbms_result_cache.bypass (bypass_mode = > FALSE, session = > TRUE) SQL > select count (f) from impl_chenzhou.bs_insured t COUNT (F)-2480625Elapsed: 00VOULARO 00.32

Note: if the relies_on clause is not specified or contains an error message, the result set cache will not become invalid when the functional dependent object is modified. As a result, outdated result sets may appear.

There are some limitations to using the result set of pl/sql functions, and the following functions cannot use the result set cache:

Functions that use out and / or in out arguments

Defines a function that uses caller permissions

Pipelined table function

Call a function from an anonymous block

Functions that use in parameters or return values have the following types of functions: LOB,REF CURSOR, objects, and records

Client result cache set

The client result set cache uses the client cache to store the result set of the query. Its purpose and work is similar to server-side result set caching. Compared with the server-side implementation, there are two important differences. First, it avoids the need to execute sql statements back and forth between the client / server. This is a great advantage. The failure of the second result set is based on a polling mechanism, so consistency is not guaranteed.

In order to implement this polling client must periodically perform database calls to check the database engine to see if its result set cache has become invalid. In order to minimize the overhead of polling, each time the client makes a database call for other reasons, it checks the validity of the result set cache. In this way, database calls that are used to invalidate cached result sets can be avoided. Enables the client to continuously perform "normal" database calls.

Although it is a client-side cache, it should be enabled on the server side. There are some parameters to control the client cache:

Client_result_cache_size: specifies the maximum memory size of the result set cache that can be used by each client process in byte. If it is set to 0, which is also the default, then this feature will be disabled. This initialization parameter is static and can only be modified at the instance level. After the modification, the instance must be restarted before it takes effect.

Client_result_cache_lag: specifies the maximum time interval between two database calls in milliseconds. That is, it specifies how long an invalid result set can remain in the client cache. The default value is 3000. This initialization parameter is static and can only be modified at the instance level. After the modification, the instance must be restarted before it takes effect.

Oci_result_cache_max_size: overrides the initialization parameter client_result_cache-size. Note, however, that if result set caching is disabled on the server, it will not be activated.

Oci_result_cache_max_rset_size: specifies the maximum amount of memory that can be used by a single result set cache in byte.

Oci_result_cache_max_rset_rows: specifies the maximum number of returned rows that can be stored in a single result set cache.

When to use

When you encounter performance problems caused by a program performing the same operation repeatedly, you can either reduce the frequency of execution or reduce the response time of the operation. Ideally, do both. However, sometimes (for example, because the application code cannot be modified) you can only implement the latter. In order to reduce the response time, a variety of optimization techniques can be used, if the requirements can not be met, then we can only use advanced optimization techniques such as result set cache. Basically, two conditions must be met for the effective use of result set caching. First, the same data is queried more frequently than modifying data. Second, there should be enough memory to store the result set.

In most cases, result set caching cannot be enabled for all queries. In fact, most of the time, only specific queries can benefit from result set caching, while in other cases, result set caching is just a pure extra overhead and may even use cache overload. Also remember that server-side caches are shared for all sessions, so their access is synchronized (they also become a string processing point like all shared resources). Therefore, the result set is cached only when the real query requests them. That is, result_cache prompts are specified in queries only when they are really needed to improve performance.

Server-side result set caching cannot completely avoid the extra overhead of executing a query. This means that if a query performs the least logical reads (not physical reads) for each row without using the result set cache, the performance of using the result set cache will not improve much. Keep in mind that both the cache and the result set cache are stored in the same shared memory.

The pl/sql function result set cache is especially useful for functions that are often used in sql statements. In fact, this function is called once for each row that is processed or returned, and the input parameters have only a few different values. However, this function is often called frequently from pl/sql and can use the result set cache.

Because of consistency issues, client caching should only be used on read-only or mainly read tables.

Finally, it is important to note that the server and client result set caches can be used together. However, for queries executed by the client, you cannot choose to use only the server result set cache instead of the client result set cache. In other words, both result set caches are used.

The above is how to use oracle result cache result set caching. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.