Oracle SQL

    Tag Archives: result_cache

    RESULT_CACHE: run-time dependency tracking

    Posted on July 5, 2015 by Sayan Malakshinov Posted in documentation, oracle, result_cache Leave a comment

    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]
    As you can see, there is only dependency on public synonym V$DATABASE, but not real base fixed X$-tables.
    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]
    The results were cached and the dependencies do not include system objects.
    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]
    Note that there is no “RESULT CACHE” line.
    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]

    result_cache undocumented oracle

    A function gets called twice if the result_cache is used

    Posted on July 5, 2015 by Sayan Malakshinov Posted in curious, oracle, PL/SQL, result_cache Leave a comment

    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!

    result_cache undocumented oracle

    Deterministic functions, result_cache and operators

    Posted on March 31, 2014 by Sayan Malakshinov Posted in deterministic functions, oracle, result_cache Leave a comment

    In previous posts about caching mechanism of determinstic functions I wrote that cached results are kept only between fetch calls, but there is one exception from this rule: if all function parameters are literals, cached result will not be flushed every fetch call.
    Little example with difference:

    SQL> create or replace function f_deterministic(p varchar2)
      2     return varchar2
      3     deterministic
      4  as
      5  begin
      6     dbms_output.put_line(p);
      7     return p;
      8  end;
      9  /
    SQL> set arrays 2 feed on;
    SQL> set serverout on;
    SQL> select
      2     f_deterministic(x) a
      3    ,f_deterministic('literal') b
      4  from (select 'not literal' x
      5        from dual
      6        connect by level<=10
      7       );
    
    A                              B
    ------------------------------ ------------------------------
    not literal                    literal
    not literal                    literal
    not literal                    literal
    not literal                    literal
    not literal                    literal
    not literal                    literal
    not literal                    literal
    not literal                    literal
    not literal                    literal
    not literal                    literal
    
    10 rows selected.
    
    not literal
    literal
    not literal
    not literal
    not literal
    not literal
    not literal
    

    As you can see, ‘literal’ was printed once, but ‘not literal’ was printed 6 times, so it was returned from cache 4 times.

    Also i want to show the differences in consistency between:
    1. Calling a function with determinstic and result_cache;
    2. Calling an operator for function with result_cache;
    3. Calling an operator for function with deterministic and result_cache;

    In this example I will do updates in autonomouse transactions to emulate updates in another session during query execution:

    Tables and procedures with updates

    drop table t1 purge;
    drop table t2 purge;
    drop table t3 purge;
    
    create table t1 as select 1 id from dual;
    create table t2 as select 1 id from dual;
    create table t3 as select 1 id from dual;
    
    create or replace procedure p1_update as
      pragma autonomous_transaction;
    begin
       update t1 set id=id+1;
       commit;
    end;
    /
    create or replace procedure p2_update as
      pragma autonomous_transaction;
    begin
       update t2 set id=id+1;
       commit;
    end;
    /
    create or replace procedure p3_update as
      pragma autonomous_transaction;
    begin
       update t3 set id=id+1;
       commit;
    end;
    /
    

    [collapse]

    Variant 1

    create or replace function f1(x varchar2) return number result_cache deterministic
    as
      r number;
    begin
       select id into r from t1;
       p1_update;
       return r;
    end;
    /
    

    [collapse]

    Variant 2

    create or replace function f2(x varchar2) return number result_cache
    as
      r number;
    begin
       select id into r from t2;
       p2_update;
       return r;
    end;
    /
    create or replace operator o2
    binding(varchar2)
    return number
    using f2
    /
    

    [collapse]

    Variant 3

    create or replace function f3(x varchar2) return number result_cache deterministic
    as
      r number;
    begin
       select id into r from t3;
       p3_update;
       return r;
    end;
    /
    create or replace operator o3
    binding(varchar2)
    return number
    using f3
    /
    

    [collapse]

    Test:

    SQL> set arrays 2;
    SQL> select
      2     f1(dummy) variant1
      3    ,o2(dummy) variant2
      4    ,o3(dummy) variant3
      5  from dual
      6  connect by level<=10;
    
      VARIANT1   VARIANT2   VARIANT3
    ---------- ---------- ----------
             1          1          1
             2          1          1
             2          1          1
             3          1          1
             3          1          1
             4          1          1
             4          1          1
             5          1          1
             5          1          1
             6          1          1
    
    10 rows selected.
    
    SQL> /
    
      VARIANT1   VARIANT2   VARIANT3
    ---------- ---------- ----------
             7         11         11
             8         11         11
             8         11         11
             9         11         11
             9         11         11
            10         11         11
            10         11         11
            11         11         11
            11         11         11
            12         11         11
    
    10 rows selected.
    

    We can see that function F1 returns same results every 2 execution – it is equal to fetch size(“set arraysize 2”),
    operator O2 and O3 return same results for all rows in first query execution, but in the second query executions we can see that they are incremented by 10 – it’s equal to number of rows.
    What we can learn from that:
    1. The use of the function F1 with result_cache and deterministic reduces function executions, but all function results are inconsistent with query;
    2. Operator O2 returns consistent results, but function is always executed because we invalidating result_cache every execution;
    3. Operator O3 works as well as operator O2, without considering that function is deterministic.

    All tests scripts: tests.zip

    consistency deterministic functions pl/sql pl/sql functions result_cache

    When oracle invalidates result_cache function results without any changes in objects on which depends

    Posted on July 30, 2012 by Sayan Malakshinov Posted in documentation, oracle, result_cache Leave a comment

    On our production servers we have simple function with result_cache, like this:

    create or replace function f_rc(p_id number) return number result_cache
    is
      ret number;
    begin
      select t.val into ret from rc_table t where t.id=p_id;
      return ret;
    exception 
      when no_data_found then 
         return null;
    end;
    /
    

    And oracle invalidates its results very frequently without any changes in table or function. I found only 2 cases when oracle invalidates result_cache results without any changes in table:
    1. “select for update” from this table with commit; (strictly speaking, select-for-update is DML and it changes blocks, so it’s ok)
    2. deleting rows from parent table that has no child records if there is unindexed foreign key with “on delete cascade”.
    I test it on 11.2.0.1, 11.2.0.3, on solaris x64 and windows. Test cases for this i will show below.
    But none of them can be the cause of our situation: we have no unindexed fk, and even if i lock all rows with “select for update”, it still does not stop invalidating.
    In what other cases this happens? Am I right that the oracle does not track any changes, but the captures of the locks and “commits”?
    Continue reading→

    invalidation oracle undocumented behaviour result_cache

    Simple Android Oracle client

    Get it on Google Play

    About us

    photo Sayan Malakshinov

    Oracle ACE Associate Oracle ACE Associate
    Oracle performance tuning expert
    Russia / Moscow / Transmedia Dynamics
    photo Bair Malakshinov

    Ph.d candidate
    Senior Oracle Developer
    Poland / Cracow / Luxoft

    Popular Posts

    • Differences between integer(int) in SQL and PL/SQL 0 comments
    • Deterministic function vs scalar subquery caching. Part 1 8 comments
    • Amazing optimization of getting distinct values from the index, and TopN for each of them 4 comments
    • SQL*Plus tips #6: Colorizing output 4 comments
    • SQL*Plus tips #5: sql_text/sql_fulltext formatting(sql beatifier) 13 comments
    • SQL*Plus tips. #1 5 comments
    • A couple of well-known but often forgotten things for PL/SQL developers 2 comments
    • SYS_OP_MAP_NONNULL is in the documentation now 0 comments
    • SQL*Plus tips #4: Branching execution 0 comments
    • Oracle 12c: Lateral, row_limiting_clause 3 comments

    Recent Posts

    • Top-N again: fetch first N rows only vs rownum
    • Docker with Oracle database: install patches automatically
    • Top N biggest tables (with lobs, indexes and nested table)
    • “Collection iterator pickler fetch”: pipelined vs simple table functions
    • SQL*Plus tips #8: How to read the output of dbms_output without “serveroutput on”

    Email Subscription

    Recent Comments

    • SQL*Plus 256 Colours in terminal | EDUARDO CLARO on SQL*Plus tips #6: Colorizing output
    • A simple SQL*Plus parameter parser | EDUARDO CLARO on SQL*Plus tips. #1
    • Sayan Malakshinov on “Collection iterator pickler fetch”: pipelined vs simple table functions
    • Frits on “Collection iterator pickler fetch”: pipelined vs simple table functions
    • SQL*Plus tips #8: How to read the output of dbms_output without 'serveroutput on' - SSWUG.ORG on SQL*Plus tips #8: How to read the output of dbms_output without “serveroutput on”
    • Adaptive serial direct path read decision ignores object statistics since 12.1 - SSWUG.ORG on Adaptive serial direct path read decision ignores object statistics since 12.1
    • Oracle issues after upgrade to 12.2 - SSWUG.ORG on Oracle issues after upgrade to 12.2
    • Ampersand instead of colon for bind variables - SSWUG.ORG on Ampersand instead of colon for bind variables
    • Евгений Бабин on Oracle issues after upgrade to 12.2
    • Oracle SQL | How even empty trigger increases redo generation on Triggers and Redo: changes on 12.2

    Blogroll

    • Alex Fatkulin
    • Alexander Anokhin
    • Andrey Nikolaev
    • Charles Hooper
    • Christian Antognini
    • Coskan Gundogar
    • David Fitzjarrell
    • Igor Usoltsev
    • Jonathan Lewis
    • Karl Arao
    • Mark Bobak
    • Martin Bach
    • Martin Berger
    • Neil Chandler
    • Randolf Geist
    • Richard Foote
    • Riyaj Shamsudeen
    • Tanel Poder
    • Timur Akhmadeev
    • Valentin Nikotin

    Categories

    Aggregated by OraNA Aggregated by OraFAQ

    Meta

    • Log in
    • Entries RSS
    • Comments RSS
    • WordPress.org
    ©Sayan Malakshinov. Oracle SQL