This post is just a compilation of the links to other people’s articles and short descriptions about new SQL PLAN OPERATIONS and HINTS with a couple little additions from me.
Continue reading
Monthly Archives: July 2015
RESULT_CACHE: run-time dependency tracking
As you know, since 11.2 “relies_on” clause was deprecated and oracle tracks dependencies at runtime now.
Test function and tables
create or replace function f_without_deps(p_tab varchar2) return varchar2 as res varchar2(30); begin execute immediate 'select '''||p_tab||''' from '||p_tab||' where rownum=1' into res; return res; end; / create table a as select 'a' a from dual; create table b as select 'b' b from dual; create view v_ab as select a,b from a,b;
[collapse]
And it works fine with normal tables:
v_ab
SQL> exec :p_tab:='v_ab'; PL/SQL procedure successfully completed. SQL> call DBMS_RESULT_CACHE.flush(); Call completed. SQL> select/*+ result_cache */ f_without_deps(:p_tab) result from dual; RESULT ---------- v_ab 1 row selected. SQL> select name,bucket_no, id,type,status,pin_count,scan_count,invalidations from v$result_cache_objects o order by type,id; NAME BUCKET_NO ID TYPE STATUS PIN_COUNT SCAN_COUNT INVALID ----------------------------------------------------------------- ---------- --- ---------- --------- ---------- ---------- ------- XTENDER.F_WITHOUT_DEPS 1579 0 Dependency Published 0 0 0 XTENDER.V_AB 3127 2 Dependency Published 0 0 0 XTENDER.B 778 3 Dependency Published 0 0 0 XTENDER.A 464 4 Dependency Published 0 0 0 select/*+ result_cache */ f_without_deps(:p_tab) result from dual 1749 1 Result Published 0 0 0
[collapse]
But don’t forget that the result_cache also caches such functions with the objects, that usually should not be cached, and such objects will not be listed in the result_cache dependencies list:
v$database
SQL> exec :p_tab:='v$database'; PL/SQL procedure successfully completed. SQL> call DBMS_RESULT_CACHE.flush(); Call completed. SQL> select/*+ result_cache */ f_without_deps(:p_tab) result from dual; RESULT ---------- v$database 1 row selected. SQL> select name,bucket_no, id,type,status,pin_count,scan_count,invalidations from v$result_cache_objects o order by type,id; NAME BUCKET_NO ID TYPE STATUS PIN_COUNT SCAN_COUNT INVALID ----------------------------------------------------------------- ---------- --- ---------- --------- ---------- ---------- ------- XTENDER.F_WITHOUT_DEPS 772 0 Dependency Published 0 0 0 PUBLIC.V$DATABASE 1363 2 Dependency Published 0 0 0 select/*+ result_cache */ f_without_deps(:p_tab) result from dual 2283 1 Result Published 0 0 0 3 rows selected.
[collapse]
SYS.OBJ$
SQL> exec :p_tab:='sys.obj$'; PL/SQL procedure successfully completed. SQL> call DBMS_RESULT_CACHE.flush(); Call completed. SQL> select/*+ result_cache */ f_without_deps(:p_tab) result from dual; RESULT ---------- sys.obj$ 1 row selected. SQL> select name,bucket_no, id,type,status,pin_count,scan_count,invalidations from v$result_cache_objects o order by type,id; NAME BUCKET_NO ID TYPE STATUS PIN_COUNT SCAN_COUNT INVALID ----------------------------------------------------------------- ---------- -- ---------- --------- ---------- ---------- ------- XTENDER.F_WITHOUT_DEPS 3922 0 Dependency Published 0 0 0 select/*+ result_cache */ f_without_deps(:p_tab) result from dual 3753 1 Result Published 0 0 0 2 rows selected.
[collapse]
We easily check that the queries with any table in SYS schema or with sysdate,systimestamp,current_date,current_timestamp,dbms_random will not be cached:
SYS tables
SQL> select/*+ result_cache */ current_scn result from v$database;
RESULT
----------
##########
1 row selected.
SQL> select name,bucket_no, id,type,status,pin_count,scan_count,invalidations from v$result_cache_objects o order by type,id;
no rows selected
SQL> explain plan for select/*+ result_cache */ * from sys.obj$;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 2311451600
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 87256 | 7328K| 296 (1)| 00:00:04 |
| 1 | TABLE ACCESS FULL| OBJ$ | 87256 | 7328K| 296 (1)| 00:00:04 |
--------------------------------------------------------------------------
[collapse]
And even if create own tables in SYS schema(don’t do it ๐), they will not be cached :
SYS.V_AB
SYS> create table a as select 'a' a from dual; SYS> create table b as select 'b' b from dual; SYS> create view v_ab as select a,b from a,b; SYS> grant select on v_ab to xtender; XTENDER> explain plan for select/*+ result_cache */ * from sys.v_ab; PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------- Plan hash value: 215283502 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 6 | 4 (0)| 00:00:01 | | 1 | MERGE JOIN CARTESIAN| | 1 | 6 | 4 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL | A | 1 | 3 | 2 (0)| 00:00:01 | | 3 | BUFFER SORT | | 1 | 3 | 2 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL | B | 1 | 3 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2)
[collapse]
But sys_context and userenv will be cached successbully:
sys_context
SQL> explain plan for select/*+ result_cache */ sys_context('userenv','os_user') from dual;
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 1388734953
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | RESULT CACHE | 267m2hcwj08nq5kwxcb0nb2ka8 | | | |
| 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; attributes=(single-row); parameters=(sys_context);
name="select/*+ result_cache */ sys_context('userenv','os_user') from dual"
14 rows selected.
[collapse]
userenv
SQL> explain plan for select/*+ result_cache */ userenv('instance') from dual;
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 1388734953
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | RESULT CACHE | dxzj3fks1sqfy35shbbst4332h | | | |
| 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; attributes=(single-row); parameters=(sys_context);
name="select/*+ result_cache */ userenv('instance') from dual"
[collapse]
A function gets called twice if the result_cache is used
Recently I showed simple example how result_cache works with non-deterministic functions and observed strange behaviour: a function gets fired once in the normal query, but twice with the result_cache hint.
Moreover, only third and subsequent query executions return same cached results as second query execution.
I didn’t want to investigate such behavior, just because 1) we should not cache the results of non-deterministic functions and 2) it doesn’t matter if we use deterministic functions.
But later I was asked to explain this, so this post is just a short description with test case.
Look at the simple function that just returns random values:
create or replace function f_nondeterministic(p int:=100) return int as res number; begin res:=round(dbms_random.value(0,p)); return res; end;
SQL> exec dbms_result_cache.flush;
PL/SQL procedure successfully completed.
SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;
NONDETER
----------
481
SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;
NONDETER
----------
689
SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;
NONDETER
----------
689
with result_cache_statistics
SQL> exec dbms_result_cache.flush;
PL/SQL procedure successfully completed.
SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;
NONDETER
----------
481
SQL> select name,value from v$result_cache_statistics where name in ( 'Create Count Success', 'Find Count');
NAME VALUE
-------------------------------------------------- ----------
Create Count Success 1
Find Count 0
SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;
NONDETER
----------
689
SQL> select name,value from v$result_cache_statistics where name in ( 'Create Count Success', 'Find Count');
NAME VALUE
-------------------------------------------------- ----------
Create Count Success 1
Find Count 1
SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;
NONDETER
----------
689
SQL> select name,value from v$result_cache_statistics where name in ( 'Create Count Success', 'Find Count');
NAME VALUE
-------------------------------------------------- ----------
Create Count Success 1
Find Count 2
SQL> select name,bucket_no, id,type,status,pin_count,scan_count,invalidations from v$result_cache_objects o;
NAME BUCKET_NO ID TYPE STATUS PIN_COUNT SCAN_COUNT INVALIDATIONS
-------------------------------------------------- ---------- ---------- ---------- --------- ---------- ---------- -------------
XTENDER.F_NONDETERMINISTIC 552 0 Dependency Published 0 0 0
select/*+ result_cache */ f_nondeterministic(1000) 2102 1 Result Published 0 2 0
nondeter from dual
[collapse]
As you can see, second execution returns different result than first one.
If we change this function:
create or replace function f_nondeterministic(p int:=100) return int
as
res number;
begin
res:=round(dbms_random.value(0,p));
dbms_output.put_line('fired! ('||res||')');
return res;
end;
and repeat this test-case:
SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;
NONDETER
----------
943 -- << (2)
1 row selected.
fired! (607) -- << (1)
fired! (943) -- << (2)
SQL> /
NONDETER
----------
607 -- << (1)
1 row selected.
SQL> /
NONDETER
----------
607 -- << (1)
1 row selected.
SQL> /
NONDETER
----------
607 -- << (1)
1 row selected.
we will see that there were 2 function executions: first result was cached, and the second was fetched!