OracleServerResultCache personal practice summary

Server Cache introduction and working principles

The Oracle 11g version provides a new independent shared memory area to cache result sets that are frequently used by applications. We call it Server Result Cache. OLAP applications can greatly improve performance by using ServerResult Cache. Simply put, Server Result Cache allows a query Result set/function return value to be cached in the memory. If subsequent queries/functions attempt to obtain the same Result set, oracle will read data directly from the cache to avoid unnecessary I/O.

Server Result Cache is a memory area that exists in SGA. Furthermore, ServerResult Cache exists in a memory area of the SharedPool, which means that the cached content can be shared by different sessions. As shown in, Server Result Cache can be subdivided into two types. One is SQL Query Result Cache, which is used to Cache SQL Query results. The other is PL/SQL function result cache, which is used to cache the results returned by PL/SQL functions.

 

The working principle of Server Result Cache is that when a query is executed, the database will first check whether there are cached results in the Cache area. If yes, the database will directly read the results from the Cache, instead of executing a query. If the required results are not cached, the database executes the query, returns the results, and caches the results for other subsequent queries. When you repeatedly execute a query or function, the database will obtain the result from the cache, which greatly reduces the response time. The cached result becomes invalid when the dependent data object changes.

SQL Query Result Cache

The general implementation method is to add RESULT_CACHE hint to the SQL query statement. In addition, you can set the database parameter RESULT_CACHE_MODE (MANUAL, FORCE, DEFAULT) or Table annotation. Among these methods, the hint method has the highest priority, followed by TableAnnotation, and finally the RESULT_CACHE_MODE parameter. Let's take a look at the running status of SQL Query Result Cache. Run the following SQL statement (the tables used by SQL and functions are from the HR Schema of Oracle .), This SQL statement attempts to calculate the average salary of each department.

explain plan for select /*+ result_cache +*/    department_id, avg(salary)     from hr.employees    group by department_id;SELECT plan_table_output FROM table(DBMS_XPLAN.DISPLAY());

From the above execution plan, we can clearly see that the Result Cache is used. Oracle uses the dictionary table V $ RESULT_CACHE_OBJECTS to store the information of these cache result sets.

select TYPE,STATUS,NAME,CACHE_ID from V$RESULT_CACHE_OBJECTS where UPPER(status) = 'PUBLISHED';

Based on the above output, we can see that Oracle maintains the underlying object on which this SQL query depends. Once the underlying object changes, Oracle automatically sets the cached query result set to invalid.

However, Oracle detects that changes in the underlying object can only be performed on the table object level. It cannot precisely locate the changes in the data that affect the cached SQL query results. For example, start another Session and execute the following statement:

update employees set first_name = 'Matt',last_name = 'Zhang' where employee_id = 100;commit;

We only updated the name of a certain employee, which is not closely related to the average wage statistics query. Then, go back to the V $ RESULT_CACHE_OBJECTS dictionary table and we will find that the originally cached query result has become Invalid.

In application scenarios, Results Cache SQL queries must be executed repeatedly. SQL query requires a large amount of data to be retrieved and a small part of data that meets the requirements is returned. Especially when SQL queries contain grouping, computing, and user-defined functions, such as Group by, Count, and Average. The data for SQL queries does not change frequently. Limits

If the SQL query contains the following conditions, the query results are not cached.

Temporary table dictionary table Non-deterministic PL/SQL functions (for the same/group parameter values, the returned results of the function are changed. View the Oracle Deterministic keywords) Curval and Nextval pseudo functions SYSDATE, SYS_TIMESTAMP, CURRENT_DATE, CURRENT_TIMESTAMP, LOCAL_TIMESTAMP, USERENV, SYS_CONTEXT, and SYS_QUID.

Here is a simple example. SYSDATE is used as the Where condition to query the result to see if it can be cached by Oracle.

Select/* + result_cache + */department_id, avg (salary) from hr. employees where hire_date> (sysdate-365*8) group by department_id; first, it indicates that the query results of this SQL statement are certainly returned.

Let's take a look at the execution plan of this SQL statement to see if it has been cached.

explain plan for select /*+ result_cache +*/     department_id, avg(salary)     from hr.employees    where hire_date > (sysdate - 365*8)    group by department_id;   SELECT plan_table_output FROM table(DBMS_XPLAN.DISPLAY());   

Obviously, the SQL query with sysdate is not cached by Oracle.

PL/SQL Function Result Cache

Function Result Cache and Query Result Cache share a ResultCache memory area. Function-based result set cache must be added with the RESULT_CACHE keyword through the display declaration. The method is as follows:

create or replacefunction is_manager (p_emp_id IN employees.employee_id%TYPE)RETURN BOOLEANRESULT_CACHEIS    manager_count NUMBER;BEGIN    DBMS_OUTPUT.PUT_LINE('Manager status confirmation in progress.');    SELECT  COUNT(1)    INTO    manager_count    FROM    departments    WHERE   MANAGER_ID = p_emp_id;    IF manager_count > 0 THEN         RETURN (TRUE);    ELSE         RETURN (FALSE);    END IF;    END is_manager;

The following anonymous block is used to execute the above functions.

set serveroutput on;declarel_emp_id NUMBER;beginl_emp_id := 99;IF is_manager(p_emp_id => l_emp_id) THEN DBMS_OUTPUT.PUT_LINE('I am a manager.');ELSEDBMS_OUTPUT.PUT_LINE('I am not a manager.');END IF;end;

The output result after execution is

anonymous block completedManager status confirmation in progress.I am not a manager.

After the execution, let's look back at the Oracle dictionary table.

select rco.type,rco.status,rco.name,rco.cache_id,rco.cache_key,rco_dependency.type dependent_type,rco_dependency.status dependent_status,rco_dependency.name dependent_name from V$RESULT_CACHE_OBJECTS rcojoinV$RESULT_CACHE_DEPENDENCY rcdon rcd.result_id = rco.idjoin V$RESULT_CACHE_OBJECTS rco_dependencyonrcd.depend_id = rco_dependency.id

We can see that Oracle caches a result set. The dependent objects of the stored results are the Departments table and the Is_manager function. That is, if any of these two objects changes, Oracle will set the corresponding Function Result Cache as invalid.

When the same function is executed by different sessions on the Oracle Instance, if the input parameter values are identical, Oracle will attempt to obtain the result directly from the cache. This means that for the Cache Result of the function, different combinations of parameter values correspond to different Result sets. We tried to execute the above function twice. The first input parameter is l_emp_id: = 99;, then the second input parameter is l_emp_id: = 100; then Oracle will cache two returned results, and the dependent objects of these two results are the same.

RELIES_ON clause

We can see that Oracle dependency management has found the underlying object that a cached function result set depends on. However, when a function becomes complex, even if Oracle can help us maintain this dependency, it will be effort-consuming. A simple method is to explicitly specify the objects on which a function depends. This keyword is the RELIES_ON clause.

create or replacefunction is_manager (p_emp_id IN employees.employee_id%TYPE)RETURN BOOLEANRESULT_CACHE RELIES_ON (departments)IS    manager_count NUMBER;BEGIN    DBMS_OUTPUT.PUT_LINE('Manager status confirmation in progress.');    SELECT  COUNT(1)    INTO    manager_count    FROM    departments    WHERE   MANAGER_ID = p_emp_id;    IF manager_count > 0 THEN         RETURN (TRUE);    ELSE         RETURN (FALSE);    END IF;    END is_manager;

Application scenarios

Databases use frequently-used functions, and the data on which these functions depend seldom changes. At the same time, the function itself cannot meet the following conditions:

When the function is executed in the way of Invoker rights (using the user permission to call the function) (implemented using the AUTHID clause ). By default, the function is executed in Definer rights (using the user permission to define the function ). When a function has an OUT or in out parameter, the IN parameter of the function is BLOB, Collection, Object, or Record. The RETURN data type of the function is BLOB, Ref Cursor, Object, or any of the above types. Note: before deciding whether a function needs to be cached, check the following precautions for caching. It is not applicable when the function changes data during execution. If such a function is cached, no subsequent function calls will be executed. Obviously, your data update operation will not happen at this time. In addition to data query, functions such as calling system functions such as UTL_MAIL () are not applicable to specific functions. This parameter is not used when some Session-level default settings are used during function execution. For example, if the TO_CHAR function is called to convert the format of the date type, if the target conversion format is not specified, the setting of the NLS_DATE_FORMAT parameter of the Session will be used. Because the Function Result Cache is shared across sessions, once cached, a Session may return incorrect results.

Summary

In addition to the Oracle Server Result Cache, Oracle also provides the Oracle Client Result Cache. This function caches the Result set in the Client's memory instead of the Server. If you are interested, refer to the Oracle documentation. I personally don't use much.