Sunday, February 27, 2011

Questions about the result cache

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.

Friday, February 25, 2011

SQL Masterclass in Tallinn, Estonia

On June 9 and 10, I'll be doing a SQL Masterclass seminar for Oracle University in Tallinn, Estonia.

You can find the details here.

Tuesday, February 15, 2011

A tip, a conference, an extension and a challenge

This post contains four unrelated notes.

First a small SQL*Plus tip. I really like to know with which user I am connected to which database, so in my login.sql script I used to have this section:

define gname=idle
column global_name new_value gname
select lower(user) || '@' ||
substr(global_name,1,decode(dot,0,length(global_name),dot-1)) global_name
from (select global_name,instr(global_name,'.') dot from global_name);
set sqlprompt '&gname> '

I saw this piece of code in Tom Kyte's book Expert Oracle Database Architecture and I have used it ever since. The downside from using this adjusted prompt, is that the first line isn't properly aligned anymore with lines 2 and further, as you can see by this example:

rwijk@ORA11202> select ename
2 from emp
3 where deptno = 10
4 /

ENAME
----------
CLARK
KING
MILLER

3 rows selected.

I read a tip on Oracle-L, which I now use. Instead of "set sqlprompt '&gname> '", I now use "host title &gname". This adjusts the title bar of my SQL*Plus window with the user@database information, and leaves the prompt at "SQL> ", thus leaving my SQL statements aligned while I'm still able to see my connect string all the time.

***

On March 22, the Dutch Oracle usergroup OGh organizes their second APEX day. Again we have a great lineup of speakers. You can read about it here. Due to the great response, we had to scale up the event. Now there is room for 250 people instead of our originally planned 150. And be sure to bring your manager with you. He or she doesn't want to miss the APEX at work track where customers will tell how they have successfully implemented parts of their business processes with APEX. I'm especially looking forward to the Northgate story as this is a huge APEX project.

***

Today there was good news for future Oracle searches on Google. On their blog they announced a Personal Blocklist Extension. Unfortunately it currently doesn't work for the Dutch version of Chrome. The following quote from the Google blog is very promising though:

If installed, the extension also sends blocked site information to Google, and we will study the resulting feedback and explore using it as a potential ranking signal for our search results.


***

The Northern California Oracle Users Group (NoCOUG) has issued their Second International SQL Challenge. You can read about it here in their journal. SQL commands for creating the required data are available here. Initially, it may look like insufficient explanation is given to solve the problem, but that's part of the fun. If you like SQL and puzzles, then you should definitely give this one a go.

***