New Features of Oracle 11g-Result Cache

1. Result Set Caching description

 

For instructions on the Oracle official website, refer:

7.6 Managing the Server and ClientResult Caches

Http://docs.oracle.com/cd/E11882_01/server.112/e16638/memory.htm#BGBCABED

 

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.

The cache result set data is transparently consistent with all changes on the server. Because the cache contains the corresponding information, the application that uses this function will improve the query performance. In addition, cache-based queries can avoid round-trips between the client and the server when sending queries and obtaining results. The server CPU used to process queries is reduced, improving the scalability of the server.

 

Before using the client to query the cache, determine whether the application will benefit from this function. The client cache is useful when your application generates duplicate result sets, small result sets, static result sets, or frequently executed queries.

 

High-speed cache for client and server results is independent and can be enabled or disabled independently.

 

Note: You can use the client_result_cache_stats $ view or the v $ client_result_cache_stats view to monitor the high-speed cache of client queries.

 

 

Client Cache has the following advantages:

(1) Extend the server-side query cache to the client memory

(2) The performance can be improved by eliminating the round-trip between the client and the server

(3) Use the client memory

(4) Improve server scalability by saving server CPU resources

(5) The result cache is automatically refreshed when the result set on the server is changed.

(6) It is especially suitable for searching tables.

 

 

1.9 use the client to query the cache

 

 

 

You can use the client to query the cache in the following ways:

• Set initialization parameters

-CLIENT_RESULT_CACHE_SIZE

-CLIENT_RESULT_CACHE_LAG

• Use the client configuration file

-OCI_RESULT_CACHE_MAX_SIZE

-OCI_RESULT_CACHE_MAX_RSET_SIZE

-OCI_RESULT_CACHE_MAX_RSET_ROWS

 

Use the client result cache according to the following items:

-Table result cache Mode

-RESULTCACHE prompt in SQL statement

 

 

CLIENT_RESULT_CACHE_SIZE: a non-zero value indicates that the client result cache is enabled. This is the maximum cache size (in bytes) of the result set of each process on the client ). All OCI client processes can reach this maximum value. You can use the OCI_RESULT_CACHE_MAX_SIZE parameter to overwrite this value.

CLIENT_RESULT_CACHE_LAG: the maximum time (in milliseconds) from the last round-trip between the client and the server ), before that, the OCI client performs a round-trip query to obtain all database changes related to queries cached on the client.

 

The client configuration file is optional and can overwrite the cache parameter set in the server initialization parameter file. The parameter value can be included in the sqlnet. ora file.

 

OCI_RESULT_CACHE_MAX_RSET_SIZE/ROWS indicates the maximum size (in bytes/line) of all result sets in the cache for queries by each process ). OCI applications can use application prompts to forcibly execute results to cache storage. This will overwrite the deployment time settings of ALTERTABLE/alter view.

 

The application prompts:

(1) SQL prompt/* + result_cache */, and/* + no_result_cache */

(2) OCIStmtExecute () mode. The content will overwrite the SQL prompt.

 

Note: To use this function, the application must be reconnected to the client library of version 11.1 or later and connected to the server of version 11.1 or later.

 

1.10 PL/SQL function High-speed cache

 

From Oracle Database11g, you can use the PL/SQL cross-segment function result cache mechanism. This high-speed cache mechanism provides a method that is supported by a language and managed by the system. PL/SQL function results can be stored in the shared global region (SGA, this method can be used for each session that runs the application. This high-speed cache mechanism is not only efficient but also easy to use. It can reduce the effort of designing and developing your own high-speed cache and high-speed cache management policies.

 

Oracle Database11g can mark PL/SQL functions, indicating that results should be cached at high speed, so that the next access to call the same parameter value can be searched without re-calculation. This function results in high-speed cache saving a lot of space and time. This is done transparently by using input parameters as search keywords. The cache is for the entire instance, so all sessions that call this function can benefit from it. If you change the results of a given parameter set, you can invalidate the cache entries by constructing them so that they can be correctly re-computed during the next access. This function is particularly useful if the value returned by a function is calculated based on the data selected from the solution-level table.

 

For such usage, the invalid structure is very simple and declarative. The syntax can be included in the source text of PL/SQL functions to request the result of the high-speed cache function and ensure its correctness. The cache can be cleared when the corresponding DML appears in the table list. If the result value is found in the cache when you call the function of the cache result, the cache value is returned immediately without executing the function body.

 

1.11 cache using PL/SQL Functions

 

PL/SQL function Cache Usage:

(1) include the RESULT_CACHE option in the function declaration part of the package or function definition.

(2) You can select a RELIES_ON clause to specify any tables or views on which function results depend.

 

For example:

Create or replace function productName

(Prod_id NUMBER, lang_id VARCHAR2)

RETURN NVARCHAR2

RESULT_CACHE RELIES_ON (product_descriptions)

IS

Result VARCHAR2 (50 );

BEGIN

SELECT translated_name INTO result

FROM product_descriptions

WHERE product_id = prod_id AND language_id = lang_id;

RETURN result;

END;

 

 

Instructions for use:

(1) If the execution of the function causes an unhandled exception error, the exception results will not be stored in the cache.

(2) The function body that executes the cache results in the following situations:

-The session on this database instance uses these parameter values to call this function for the first time. The cache results for these parameter values are invalid.

-When any database object specified in the RELIES_ON clause defined by the function changes, the cache results will become invalid.

-The cache results of these parameter values are out of date. If the system requires more memory, it may discard the earliest cache value.

-This function bypasses the cache.

(3) This function should not have any negative effect.

(4) This function should not depend on session-specific settings.

(5) The function should not depend on the session-specific application context.

 

1.12 PL/SQL function High-speed cache: Note

 

PL/SQL function High-speed cache is not applicable to the following situations:

(1) A function is defined in a module with the caller permission or in an anonymous block.

(2) functions are table functions transmitted using pipelines.

(3) This function contains the OUT or in out parameters.

(4) This function contains the following types of IN parameters: BLOB, CLOB, NCLOB, ref cursor, set, object, or record.

(5) The return type of the function is BLOB, CLOB, NCLOB, REFCURSOR, object, record, or a set that contains an unsupported return type.

1 2 Next Page