Oracle 11g release 1 gave the option of caching function results. It is done using the optional reference of mentioning RESULT_CACHE keyword. The values of these function calls are stored in system global area (SGA). It will have one result for every unique combination of parameter of passed. These are available for every session. Thus this helps in greatly reducing the execution time of an often repeated complex process inside a function which gets same repeated input values. This will increase performance in high concurrent applications.
In Oracle 11g Release 1
It is mentioned, the best candidate for result caching is function that is invoked frequently and depend on information that changes infrequently or never. Consider the following function.
CREATE OR REPLACE
FUNCTION get_sal (p_emp_id in hr.employees.employee_id%type)
RETURN NUMBER
RESULT_CACHE
IS
lv_sal_rtn hr.employees.salary%type;
BEGIN
select salary
into lv_sal_rtn
from hr.employees
where employee_id = p_emp_id;
RETURN lv_sal_rtn;
EXCEPTION
WHEN others then
RETURN 0;
END;
Note: the function execution values are stored in SGA by explicitly mentioning RESULT_CACHE. And the downside of this is, at times you can get wrong return values. This is because the values are already cached in memory and not calculated again. Consider the following example.
SQL> select employee_id as id
2 , salary
3 , get_sal(employee_id)
4 from hr.employees
5 where employee_id = 100
6 /
ID SALARY GET_SAL(EMPLOYEE_ID)
---------- ---------- --------------------
100 24000 24000
Elapsed: 00:00:00.01
SQL> update hr.employees
2 set salary = salary *1.1
3 where employee_id = 100
4 /
1 row updated.
Elapsed: 00:00:00.14
SQL> commit
2 /
Commit complete.
SQL> select employee_id as id
2 , salary
3 , get_sal(employee_id)
4 from hr.employees
5 where employee_id = 100
6 /
ID SALARY GET_SAL (EMPLOYEE_ID)
---------- ---------- --------------------
100 26400 24000
Elapsed: 00:00:00.00
Even though the second time execution virtually ran in no time, it returned wrong value. Oracle 11g release 1 introduced another clause RELIES_ON to overcome this downside. By this, you can mention objects on what the function values relies on.
CREATE OR REPLACE
FUNCTION get_sal (p_emp_id in hr.employees.employee_id%type)
RETURN number
RESULT_CACHE RELIES_ON (hr.employees)
If we execute the function this time, after updating the base table, you can see that the function returns correct value. The value is not taken from system global area.
SQL> update employees
2 set salary = salary *1.1
3 where employee_id = 100
4 /
1 row updated.
Elapsed: 00:00:00.00
SQL> select employee_id as id
2 , salary
3 , get_sal(employee_id)
4 from employees
5 where employee_id = 100
6 /
ID SALARY GET_SAL (EMPLOYEE_ID)
---------- ---------- --------------------
100 29040 29040
Elapsed: 00:00:00.01
In Oracle 11g Release 2
As you can see, mentioning objects in RELIES_ON gets dirty fast and quick once the function becomes complex. With Oracle 11g release 2, the database automatically detects all data sources that are queried and relied on for caching. The over head you mentioning the objects in RELIES_ON are done away in release 2.
Result Caches in Oracle RAC Environment Are No Longer Private
In the previous section, we have discussed about function result caches. The changes dint stop there. We will discuss in this section more about it. Very often, when architects implement Oracle database, they implement it as real application clusters (RAC).
As of release 11.1, each database instance in an Oracle RAC environment had a private function result cache, available only to sessions on that instance. If a required result was missing from the private cache of the local instance, the body of the function ran to compute the result, which was then added to the local cache. The result was not retrieved from the private cache of another instance.
For Release 11.2, each database instance manages its own local result cache, but the local result cache is no longer private—sessions attached to remote database instances can access its contents. If a required result is missing from the result cache of the local instance, the result might be retrieved from the local cache of another instance, instead of being locally computed.
Using Result Cache Table Annotations
Table data also can be cached for better throughput. You can use table annotations to control result caching. Although the point to note is table annotations are in effect only for the whole query, not for query segments. The primary benefit of these annotations is avoiding the necessity of adding result cache hints to queries at the application level.
A table annotation has a lower precedence than a SQL hint. Thus, you can override table and session settings by using hints at the query level. Permitted values for the RESULT_CACHE table annotation are DEFAULT and FORCE. In DEFAULT mode, the results are not cached.
While creating the table, we need to mention as
SQL> CREATE TABLE sales (...)
2 RESULT_CACHE (MODE DEFAULT);
The RESULT_CACHE clause is optional and you can skip it while creating it too. Now, what we do is, we alter the table and force it cache the results.
SQL> ALTER TABLE sales
2 RESULT_CACHE (MODE FORCE);
Then this query which uses only a portion of the table will benefit from the RESULT_CACHE.
SQL> SELECT prod_id, SUM(amount_sold)
2 FROM sales
3 GROUP BY prod_id
4 HAVING prod_id=136;
If you want to over ride this, you can pass the hint NO_RESULT_CACHE. The compiler will understand and over ride the less precedent RESULT_CACHE defined at the table level.
SQL> SELECT /*+ NO_RESULT_CACHE */ *
2 FROM sales
3 ORDER BY time_id DESC;
Check the documentation for more details.
Posted by SREE GURUPARAN 
Posted by SREE GURUPARAN 
Posted by SREE GURUPARAN 
