Last year I have presented a couple of times about the result cache. You can download this presentation from my new Presentations and papers tabpage, by the way. After each of those sessions I received several good questions. To some questions I didn't know the answer, and for some other questions I did, but I hadn't tested it so I wasn't absolutely sure. I promised to address those questions with a separate blogpost, so this post is long overdue, but I finally took some time to investigate them all and here are the results on version 11.2.0.2.
Question 1: If my result cached function is valid, and I recompile the valid function, will my cached results still become invalid?
SQL> exec dbms_result_cache.flush
PL/SQL procedure successfully completed.
SQL> create function f (p_factor in number) return number result_cache
2 is
3 begin
4 return 10 * p_factor;
5 end;
6 /
Function created.
SQL> select f(2) from dual
2 /
F(2)
----------
20
1 row selected.
SQL> select id
2 , type
3 , status
4 , name
5 , namespace
6 from v$result_cache_objects
7 /
ID TYPE STATUS NAME NAMES
---------- ---------- --------- ---------------------------------------- -----
0 Dependency Published RWIJK.F
1 Result Published "RWIJK"."F"::8."F"#fac892c7867b54c6 #1 PLSQL
2 rows selected.
SQL> alter function f compile
2 /
Function altered.
SQL> select id
2 , type
3 , status
4 , name
5 , namespace
6 from v$result_cache_objects
7 /
ID TYPE STATUS NAME NAMES
---------- ---------- --------- ---------------------------------------- -----
0 Dependency Published RWIJK.F
1 Result Invalid "RWIJK"."F"::8."F"#fac892c7867b54c6 #1 PLSQL
2 rows selected.
So the answer is yes. The results always become invalid when compiling a function.
Question 2: What happens when you execute a SQL query -of which the results are cached- that also has a result cached function in its select list?
SQL> exec dbms_result_cache.flush
PL/SQL procedure successfully completed.
SQL> select /*+ result_cache */
2 deptno
3 , f(deptno)
4 from dept
5 /
DEPTNO F(DEPTNO)
---------- ----------
10 100
20 200
30 300
40 400
4 rows selected.
SQL> select id
2 , type
3 , status
4 , name
5 , namespace
6 from v$result_cache_objects
7 /
ID TYPE STATUS NAME NAMES
---------- ---------- --------- ---------------------------------------- -----
1 Dependency Published RWIJK.F
0 Dependency Published RWIJK.DEPT
6 Result Published "RWIJK"."F"::8."F"#fac892c7867b54c6 #1 PLSQL
5 Result Published "RWIJK"."F"::8."F"#fac892c7867b54c6 #1 PLSQL
4 Result Published "RWIJK"."F"::8."F"#fac892c7867b54c6 #1 PLSQL
3 Result Published "RWIJK"."F"::8."F"#fac892c7867b54c6 #1 PLSQL
2 Result Published select /*+ result_cache */ SQL
deptno
, f(deptno)
from dept
7 rows selected.
So, in that case both the SQL query as well as the PL/SQL function will cache its results.
Question 3: If my table is partitioned and I query on only one partition and cache the results, will a DML statement that affects another partition invalidate my result set?
SQL> create table my_partitioned_emp
2 ( empno number(4) primary key
3 , deptno number(2) not null
4 , sal number(4) not null
5 )
6 partition by range(deptno)
7 ( partition p1 values less than (15)
8 , partition p2 values less than (25)
9 , partition p3 values less than (35)
10 , partition p4 values less than (maxvalue)
11 )
12 /
Table created.
SQL> insert into my_partitioned_emp
2 select empno
3 , deptno
4 , sal
5 from emp
6 /
14 rows created.
SQL> begin
2 dbms_stats.gather_table_stats
3 ( ownname => user
4 , tabname => 'my_partitioned_emp'
5 , partname => null
6 , cascade => true
7 );
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> exec dbms_result_cache.flush
PL/SQL procedure successfully completed.
SQL> select /*+ result_cache */
2 sum(sal)
3 from my_partitioned_emp
4 where deptno = 10
5 /
SUM(SAL)
----------
8750
1 row selected.
SQL> insert into my_partitioned_emp
2 values (7777, 40, 1000)
3 /
1 row created.
SQL> commit
2 /
Commit complete.
SQL> select id
2 , type
3 , status
4 , name
5 , namespace
6 from v$result_cache_objects
7 /
ID TYPE STATUS NAME NAMES
---------- ---------- --------- ---------------------------------------- -----
0 Dependency Published RWIJK.MY_PARTITIONED_EMP
1 Result Invalid select /*+ result_cache */ SQL
sum(sal)
from my_partitioned_emp
where deptno = 10
2 rows selected.
So yes, the results will be invalidated, even if the DML is on another partition. The granularity is at table level. I'm not sure whether finer grained dependencies for the result cache would be welcome, because it could imply a significant overhead to manage.
Question 4: Does the result cache work with analytic functions?
SQL> exec dbms_result_cache.flush
PL/SQL procedure successfully completed.
SQL> select /*+ result_cache */
2 empno
3 , sal
4 , sum(sal) over (partition by deptno)
5 from emp
6 /
EMPNO SAL SUM(SAL)OVER(PARTITIONBYDEPTNO)
---------- ---------- -------------------------------
7782 2450 8750
7839 5000 8750
7934 1300 8750
7566 2975 10875
7902 3000 10875
7876 1100 10875
7369 800 10875
7788 3000 10875
7521 1250 9400
7844 1500 9400
7499 1600 9400
7900 950 9400
7698 2850 9400
7654 1250 9400
14 rows selected.
SQL> /
EMPNO SAL SUM(SAL)OVER(PARTITIONBYDEPTNO)
---------- ---------- -------------------------------
7782 2450 8750
7839 5000 8750
7934 1300 8750
7566 2975 10875
7902 3000 10875
7876 1100 10875
7369 800 10875
7788 3000 10875
7521 1250 9400
7844 1500 9400
7499 1600 9400
7900 950 9400
7698 2850 9400
7654 1250 9400
14 rows selected.
SQL> select *
2 from v$result_cache_statistics
3 /
ID NAME VALUE
---------- ---------------------------------------- --------------------
1 Block Size (Bytes) 1024
2 Block Count Maximum 5120
3 Block Count Current 32
4 Result Size Maximum (Blocks) 256
5 Create Count Success 1
6 Create Count Failure 0
7 Find Count 1
8 Invalidation Count 0
9 Delete Count Invalid 0
10 Delete Count Valid 0
11 Hash Chain Length 1
12 Find Copy Count 1
12 rows selected.
One create and one find, so it does work. I could not think of a reason why it wouldn't work, but it doesn't hurt to check.
Question 5: Does fine grained auditing work correctly in combination with the result cache?
The idea behind this question is that with the result cache, your query will skip a lot of work. Will a query that gets its results via the result cache, also skip the auditing part?
SQL> exec dbms_result_cache.flush
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_fga.add_policy
3 ( object_schema => user
4 , object_name => 'emp'
5 , policy_name => 'sal_policy'
6 , audit_condition => 'sal <= 1000'
7 , audit_column => 'sal'
8 );
9 end;
10 /
PL/SQL procedure successfully completed.
SQL> select sal
2 from emp
3 where sal = 800
4 /
SAL
----------
800
1 row selected.
SQL> /
SAL
----------
800
1 row selected.
SQL> select /*+ result_cache */
2 sal
3 from emp
4 where sal <= 800
5 /
SAL
----------
800
1 row selected.
SQL> /
SAL
----------
800
1 row selected.
SQL> select policy_name
2 , scn
3 , sql_text
4 from dba_fga_audit_trail
5 order by scn
6 /
POLICY_NAME SCN
------------------------------ ----------
SQL_TEXT
--------------------------------------------------------------------------------
SAL_POLICY 1382762
select sal
from emp
where sal = 800
SAL_POLICY 1382764
select sal
from emp
where sal = 800
SAL_POLICY 1382766
select /*+ result_cache */
sal
from emp
where sal <= 800
SAL_POLICY 1382768
select /*+ result_cache */
sal
from emp
where sal <= 800
4 rows selected.
Both queries with the result_cache hint are there. So even though the second query got its results from the result cache, the policy recorded the query. And so the answer to this question is "Yes". This is not surprising given the execution plan:
SQL> alter session set statistics_level = all
2 /
Session altered.
SQL> set serveroutput off
SQL> select /*+ result_cache */
2 sal
3 from emp
4 where sal <= 800
5 /
SAL
----------
800
1 row selected.
SQL> /
SAL
----------
800
1 row selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'))
2 /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID fdfbdwgq5vqnn, child number 0
-------------------------------------
select /*+ result_cache */ sal from emp where sal <= 800
Plan hash value: 3956160932
---------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 |
| 1 | RESULT CACHE | fxpb18zb9vn8w87wgw43w8cbyr | 1 | 1 |
|* 2 | TABLE ACCESS FULL| EMP | 0 | 0 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SAL"<=800)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 -
24 rows selected.
Note that I edited the E-rows and A-time columns out of the formatted plan for nicer display.
You can see that the result cache at line 1 prevents the full scan of EMP from executing, but not line 0 -the SELECT-, where the auditing probably takes place.
