New Feature of Oracle11g -- ResultCache (result cache) Description

You can enable explicit cache for query result sets and query fragments in the database memory. Dedicated memory buffers stored in the Shared Pool

You can enable explicit cache for query result sets and query fragments in the database memory. Dedicated memory buffer stored in the Share Pool

1. Result Set Caching description

For instructions on the Oracle official website, refer:

7.6 Managing the Server and ClientResult Caches

1.1 Overview

You can enable explicit cache for query result sets and query fragments in the database memory. The dedicated memory buffer stored in the Share Pool can be used to store and retrieve cache results. After the data in the database object accessed by the query is modified, the query results stored in the cache become invalid.

Although the SQL query cache can be used for any query, it is most suitable for statements that require access to a large number of rows but only return a small part of them. Most data warehouse applications are in this situation.

Note:

(1) Each node in the RAC configuration has a dedicated result cache. The cache results of one instance cannot be used by another instance. However, failure may affect multiple instances. To process all synchronization operations related to the SQL query result cache between RAC instances, you need to use a dedicated RCBG process for each instance.

(2) Through parallel query, the whole result can be cached at high speed (in RAC, the cache is executed on the query Coordination Program instance ), however, a single parallel query process cannot use the cache.

In short:

• Cache query or query block results for future reuse.

• Express cache can be used across statements and sessions unless the cache is out of date.

• Advantages:

-Scalability

-Reduce memory usage

• Applicable statement:

-Access Multiple rows

-Return a few rows

1.2 set high-speed cache for SQL query results

The query optimization program sets the management result cache mechanism based on the RESULT_CACHE_MODE parameter in the initialization parameter file.

You can use this parameter to determine whether the optimizer automatically sends the query results to the results cache. You can set the RESULT_CACHE_MODE parameter at the system and session level.

The parameter values can be AUTO, MANUAL, and FORCE:

(1) When set to AUTO, the optimizer will determine which results are stored in the cache based on repeated operations.

(2) when it is set to MANUAL (default value), you must use the RESULT_CACHE prompt to specify to store specific results in the cache.

(3) When set to FORCE, all results will be stored in the cache.

Note: For AUTO and FORCE settings, if the statement contains the [NO _] RESULT_CACHE prompt, the prompt takes precedence over the parameter settings.

1.3 manage high-speed cache of SQL query results

You can modify multiple parameter settings in the initialization parameter file to manage the database's SQL query results for high-speed cache.

By default, the database allocates memory for the result cache in the Share Pool in the SGA. The size of the memory allocated to the result cache depends on the memory size of the SGA and the memory management system. You can set the RESULT_CACHE_MAX_SIZE parameter to change the memory allocated to the result cache. If you set the result cache value to 0, the result cache is disabled. The value of this parameter is rounded to the maximum multiple of 32 KB which does not exceed the specified value. If the value obtained by rounding is 0, this function is disabled.

You can use the RESULT_CACHE_MAX_RESULT parameter to specify the maximum amount of cache that can be used for any result. The default value is 5%, but any 100 cent ratio between 1 and one hundred can be specified. This parameter can be implemented at the system and session level.

You can use the RESULT_CACHE_REMOTE_EXPIRATION parameter to specify the time (in minutes) for retaining the results of objects dependent on the remote database ). The default value is 0, indicating that the results of remote objects are not cached at high speed.

Setting this parameter to a non-zero value may generate outdated information. For example, when the remote table used in the result is changed in the remote database.

Use the following initialization parameters for management:

1. RESULT_CACHE_MAX_SIZE

-This parameter sets the memory allocated to the result cache.

-If you set the value to 0, the result cache is disabled.

-The default value depends on other memory settings (0.25% of memory_target, 0.5% of sga_target, or 1% of shared_pool_size)

-The value cannot be greater than 75% of the Shared Pool.

2. RESULT_CACHE_MAX_RESULT

-Set the Maximum Cache speed for a single result.

-The default value is 5%.

3. RESULT_CACHE_REMOTE_EXPIRATION

-Set the expiration time of the cache result based on the remote database object.

-The default value is 0.

1.4 Use Result_Cache prompt

If you want to cache the query results and set the RESULT_CACHE_MODE initialization parameter to MANUAL, you must explicitly specify the RESULT_CACHE prompt in the query. This introduces the ResultCache operator in the query execution plan. When a query is executed, the ResultCache operator caches the search results to check whether the query results exist in the cache. If yes, the result is directly retrieved from the cache. If the query result does not exist in the cache, the query is executed. Results are returned as outputs and stored in the Results cache.

If you set the RESULT_CACHE_MODE initialization parameter to AUTO or FORCE and do not want to store the query results in the result cache, you must use the NO_RESULT_CACHE prompt in the query. For example, if the value of RESULT_CACHE_MODE in the initialization parameter file is FORCE and you do not want to use the result cache for the EMPLOYEES table, you need to use the NO_RESULT_CACHE prompt.

Note: The [NO _] RESULT_CACHE prompt should be given priority over parameter settings.

1.5 Use the DBMS_RESULT_CACHE package

The DBMS_RESULT_CACHE package provides statistics, information, and operators to manage the memory allocation of the high-speed cache of query results. You can use the DBMS_RESULT_CACHE package to perform multiple operations, such as viewing the cache status (OPEN or CLOSED), retrieving statistics about the cache memory usage, and refreshing the cache.

For example, to view memory allocation statistics, use the following SQL process:

SQL> set serveroutput on

SQL> executedbms_result_cache.memory_report

The DBMS_RESULT_CACHE package is used:

(1) Manage the cache memory allocation of query results

(2) view the cache status:

SELECTDBMS_RESULT_CACHE.STATUS from dual;

(3) retrieve the cache memory usage statistics:

EXECUTEDBMS_RESULT_CACHE.MEMORY_REPORT;

(4) Delete all existing results and clear the cache:

EXECUTEDBMS_RESULT_CACHE.FLUSH;

(5) invalidate the cache results dependent on the specified object:

EXECDBMS_RESULT_CACHE.INVALIDATE ('jfv ', 'mytab ');

1.6 view SQL results cache dictionary Information

(G) V $ RESULT_CACHE_STATISTICS: lists various cache settings and memory usage statistics.

(G) V $ RESULT_CACHE_MEMORY: lists all memory blocks and corresponding statistics.

(G) V $ RESULT_CACHE_OBJECTS: lists all objects (cache results and dependencies) and their attributes.

(G) V $ RESULT_CACHE_DEPENDENCY: lists the dependency details and dependencies between cache results.

1.7 high-speed cache of SQL query results: Note

For any function used in function-based indexes compiled by the user, the DETERMINISTIC keyword must be used to declare that the function always returns the same output value for any specified input parameter value set.

(1) the clear operation can be run only when the cache is not in use. to refresh the operation, you must disable or disable the cache.

(2) variable values are used to parameterize the cache results. Only cache results with the same variable value can be found. That is to say, different values or variable names will cause cache miss.

Note:

1. Disable result caching for queries that contain the following content:

-Temporary table or dictionary table

-Uncertain PL/SQL Functions

-CURRVAL and NEXTVAL Sequences

-SQL functions such as current_date, sysdate, and sys_guid

2. DML/DDL on the remote database does not expire the cache results.

3. High-speed cache flash-back query.

4. the cache does not automatically release the memory.

-It will continue to grow until the maximum size is reached.

-DBMS_RESULT_CACHE.FLUSH clears the memory.

5. bind variables

-The cache results will be parameterized using variable values.

-Only cache results with the same variable values can be found.

6. cache results are not generated in the following cases:

-The query is based on non-current data versions (read consistency is enforced)

-The current session has unfinished transaction processing in the queried table.

1.8 OCI client query high-speed cache

In Oracle Database11g, you can use the Oracle call interface (OCI) client to query the cache and enable the cache for the query result set in the client memory.