How to use Result_cache to improve SQL performance? Result_cache examples

In my previous article I have given the information about oracle hints as well as the parallel hints examples. In this article I would like to throw light on one of the caching mechanism which is very much useful in improving SQL performance. The caching plays an important role in SQL performance tuning. There are few SQL statements which will enable the caching. The Result_cache statement is also used to improve SQL query performance. The Oracle server is using caching mechanism to improve the performance of SQL statements.

What you will find in this article?

1. What is Result_cache and where it is used?
2. Example of Result_cache

What is Result_cache and where it is used?

Before we start with Result_cache we will see what exactly the caching mechanism means. The Caching is the technic which is used to store the copy of resource and retrieved when it is actually required. The caching is used to improve the performance of application.

The Important bullet points related to Result_cache :

Point 1 : The Statement Result_cache is used mainly in user defined functions which will cache the result of the function in the oracle memory. It will cache the different parameters,different Parameter values and their results will be stored in the cache.

Example : If you are using result_cache statement for Sum function code and executed 2 statements .

Select Sum(Sal) from Employee where dept_id=100; —Lets consider that result is 2000

Select Sum(Sal) from Employee where dept_id=200; —Lets consider that result is 3000

The both results are cached in oracle memory internally.

Point 2 : The function created with the result cache statement will be stored in shared global area which will be available for any session of your application.

Point 3 : If you are calling function with same Parameters then the result may be fetched from oracle cache rather than it will call oracle function again. This will fasten up the process and execution will do fast.

Point 4: This above process will improve the performance and scalability.

Point 5 : Where to use this?

You might require to use this statement in function which are calling frequently and it is dependent on information that changes infrequently.

Make sure that the exception results are not stored in the cache.

Point 6 : How to use result_cache in user defined functions?

You require to use this clause in function declaration or function definition.

Create or replace function function_name (parameter)

return return_Datatype RESULT_CACHE

is

statements

end function_name;

Point 7 : DBA Parameters

The DBA needs to make changes in the following important parameters before we use this statement,

–parameter shared_pool_size
–parameter result_cache_max_size
–parameter result_cache_mode

Real Life Industry Example :

create or replace function F_get_sum_sal
( dept_id number )
return number result_cache — We have used result cache to cache results
is
v_sal number;
begin
select sum(salary)
into v_sal
from
employees
where department_id =dept_id;
return v_sal;

end;

Lets execute following statements ,

select F_get_sum_sal(10) from dual;

Lets say result is 50000 stored in cache with parameter 10.
select F_get_sum_sal(20) from dual;

Lets say result is 40000 stored in cache with parameter 20.
select get_sum_sal_dept(30) from dual;

Lets say result is 70000 stored in cache with parameter 30.

–now when you do :
select get_sum_sal_dept(10) from dual;

It should be faster because the result is stored in cache, and you will find result is 50000 fetched from cache.

The Result_cache statement is used to improve the performance by fetching the data as fast as possible. If You like this article or if you have any issues with the same kindly comment in comments section.