Wednesday, December 2, 2009

Result Cache for Ref Cursor returning PL/SQL codes

According to Oracle documentation if a PL/SQL SP is returning a ref cursor then result cache is not supported. But there's a way to circumvent with somewhat beneficial results.

Below is a package written for the HR sample schema

create or replace package emps_pkg as

type detail is ref cursor;

function getempdetails (options in INTEGER, departname in VARCHAR2, jobtitle in VARCHAR2) return detail;

end;
/


create or replace package body emps_pkg as

function getempdetails(options in INTEGER,departname in VARCHAR2, jobtitle in VARCHAR2) return detail is

employees detail;
main_query VARCHAR2(2000) := 'select /*+ result_cache */ e.* from employees e,departments d,jobs j
where e.department_id = d.department_id and j.job_id = e.job_id ';
begin
if options = 1 then
main_query := main_query ||'and d.department_name=:1';
open employees for main_query using departname;

return employees;

else
main_query := main_query ||'and j.job_title=:1';

open employees for main_query using jobtitle;

return employees;
end IF ;
end;
end;
/


Package body contains some dynamic sql (there's no real logic behind this package only to demonstrate the use of result cache) which is constructed with the result_cache hint.

execute the package code and observe the plan


select emps_pkg.getempdetails(2,'Purchasing','Purchasing Manager') from dual;
select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

Plan hash value: 980169617

------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | RESULT CACHE | a68hth9dfygb5ddg0hr8hnty9b | | | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 6 | 570 | 4 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | JOBS | 1 | 27 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_JOB_IX | 6 | | 0 (0)| |
|* 6 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 6 | 408 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------


Recompile the package body without the result cache hint and observe the plan and it would be

Plan hash value: 980169617

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 6 | 570 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | JOBS | 1 | 27 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_JOB_IX | 6 | | 0 (0)| |
|* 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 6 | 408 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------


The benefit comes from the fact the sql query inside the SP could use the result cache. But the overhead of executing the PL/SQL code is still there which I believe won't be present in "proper" result cached SPs.

Any changes to depending tables will make the cache invalid just like the expected behavior.