1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

When result cache functions status is changed to bypass?

Discussion in 'SQL PL/SQL' started by preston, Apr 28, 2012.

  1. preston

    preston Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    80
    I write the following code but the status of result cache is always new.

    SQL> exec DBMS_RESULT_CACHE.flush;

    PL/SQL procedure successfully completed.

    SQL> create or replace function plch_func
    2 return number
    3 result_cache
    4 is
    5 v_name varchar2(128);
    6 v_status varchar2(9);
    7 begin
    8 for i in 0..20 loop
    9 select name,
    10 status
    11 into v_name,
    12 v_status
    13 from v$result_cache_objects
    14 where type = 'Result';
    15 dbms_output.put_line('Result ' || v_name || ' status is ' || v_status || ' (after ' || i || ' seconds).');
    16 dbms_lock.sleep(1);
    17 end loop;
    18 return 50;
    19 end;
    20 /


    Result "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 status is New (after 0 seconds).
    Result "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 status is New (after 1 seconds).
    Result "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 status is New (after 2 seconds).
    Result "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 status is New (after 3 seconds).
    Result "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 status is New (after 4 seconds).
    Result "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 status is New (after 5 seconds).
    Result "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 status is New (after 6 seconds).
    Result "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 status is New (after 7 seconds).
    Result "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 status is New (after 8 seconds).
    Result "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 status is New (after 9 seconds).
    Result "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 status is New (after 10 seconds).
    Result "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 status is New (after 11 seconds).
    Result "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 status is New (after 12 seconds).
    Result "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 status is New (after 13 seconds).
    Result "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 status is New (after 14 seconds).
    Result "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 status is New (after 15 seconds).
    Result "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 status is New (after 16 seconds).
    Result "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 status is New (after 17 seconds).
    Result "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 status is New (after 18 seconds).
    Result "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 status is New (after 19 seconds).
    Result "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 status is New (after 20 seconds).


    Do you know why?
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Which release of Oracle are you using? I suspect 11.1 as 11.2.0.3 reports:

    Code (SQL):
    SQL> SELECT plch_func FROM dual;
     
     PLCH_FUNC
    ----------
            50
     
    RESULT "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 STATUS IS NEW (after 0 seconds).
    RESULT "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 STATUS IS NEW (after 1 seconds).
    RESULT "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 STATUS IS NEW (after 2 seconds).
    RESULT "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 STATUS IS NEW (after 3 seconds).
    RESULT "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 STATUS IS NEW (after 4 seconds).
    RESULT "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 STATUS IS NEW (after 5 seconds).
    RESULT "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 STATUS IS NEW (after 6 seconds).
    RESULT "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 STATUS IS NEW (after 7 seconds).
    RESULT "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 STATUS IS NEW (after 8 seconds).
    RESULT "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 STATUS IS NEW (after 9 seconds).
    RESULT "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 STATUS IS NEW (after 10 seconds).
    RESULT "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 STATUS IS Bypass (after 11 seconds).
    RESULT "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 STATUS IS Bypass (after 12 seconds).
    RESULT "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 STATUS IS Bypass (after 13 seconds).
    RESULT "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 STATUS IS Bypass (after 14 seconds).
    RESULT "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 STATUS IS Bypass (after 15 seconds).
    RESULT "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 STATUS IS Bypass (after 16 seconds).
    RESULT "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 STATUS IS Bypass (after 17 seconds).
    RESULT "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 STATUS IS Bypass (after 18 seconds).
    RESULT "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 STATUS IS Bypass (after 19 seconds).
    RESULT "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 STATUS IS Bypass (after 20 seconds).
    SQL>
     
    All of this is irrelevant as V$RESULT_CACHE_OBJECTS is owned by SYS and is a view based on a SYS-owned table; objects in the SYS and SYSTEM schemas are not placed into the result cache thus the status of those query results will either be NEW or BYPASS.

    Even if you include a query from the EMP table in this function the function output will be the same.
     
  3. preston

    preston Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    80
    SQL> select * from v$version;

    BANNER
    --------------------------------------------------------------------------------

    Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
    PL/SQL Release 11.1.0.6.0 - Production
    CORE 11.1.0.6.0 Production
    TNS for 64-bit Windows: Version 11.1.0.6.0 - Production
    NLSRTL Version 11.1.0.6.0 - Production

    SQL> show parameter _result_cache_timeout;

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    _result_cache_timeout integer 1
    SQL>


    I executed in sys account and normal user account but although my result cache timeout is 1, status didn't change. Do you know why?? And also in what situations it change? Because in ur example it is changed.

    Thanks
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I hate to ask but did you read my previous reply? I am running 11.2.0.3 (clearly stated in the response);the output is the default behavior for that release. Also I explained why your query results did not end up in the results cache; I shall repost it here in hopes you will read it:

    All of this is irrelevant as V$RESULT_CACHE_OBJECTS is owned by SYS and is a view based on a SYS-owned table; objects in the SYS and SYSTEM schemas are not placed into the result cache thus the status of those query results will either be NEW or BYPASS.
     
  5. preston

    preston Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    80
    I did read your answer. But I guess I didn't express myself. I created the above function in different user accounts. (I mean sys and normal user accounts) However, the result is always same. I have never ever seen bypass status. And I am asking why??? Although my _result_cache_timeout = 1, but I havent seen the bypass status.

    Thanks
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I'll state this again -- I ran this on Oracle 11.2.0.3 -- you reported running 11.1.0.6. Behavior has changed between the two releases. And the result is always the same because the OBJECT you are querying is owned by SYS and objects in the SYS and SYSTEM schemas are never loaded into the results cache.
     
  7. preston

    preston Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    80
    I also state again. I create a new user called makaraci and I logged in this user and create the same object and execute it, and the reuslt is'nt change. Do u understand now?

    Thanks
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I understand fully and it matters not WHO does the query. IT matters WHO owns the objects being queried and the object being queried is owned by SYS and objects OWNED by SYS and SYSTEM are never loaded into the results cache regardless of the user running the query.
     
  9. preston

    preston Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    80
    No I said the object in not owned by sys or system. It is owned by normal user . the user name is makaraci. Ok?
     
  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    From the Oracle documentation:

    "
    7.6.4.2 Additional Requirements for the Result Cache

    You cannot cache results when the following objects or functions are in a query:
    • Temporary tables and tables in the SYS or SYSTEM schemas
    • Sequence CURRVAL and NEXTVAL pseudo columns
    • SQL functions CURRENT_DATE, CURRENT_TIMESTAMP, LOCAL_TIMESTAMP, USERENV/SYS_CONTEXT (with non-constant variables), SYS_GUID, SYSDATE, and SYS_TIMESTAMP"
     
  11. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It isn't the owner of the FUNCTION it's the owner of the TABLES/VIEWS being queried IN the function. V$RESULTS_CACHE_OBJECTS is owned by SYS and the results will never be cached.
     
  12. preston

    preston Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    80
    When you execute this function after 10 seconds the status is changed? so how is it changed then?

    RESULT "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 STATUS IS NEW (after 10 seconds).
    RESULT "SYS"."PLCH_FUNC"::8."PLCH_FUNC"#9689ba467a19cd19 #1 STATUS IS Bypass (after 11 seconds).

    ???
     
  13. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It is the DEFAULT behavior of 11.2.0.3. I've stated this many times in this thread. You are NOT running 11.2.0.3 and the behavior has changed in 11.2.0.3 relative to 11.1.0.6.
     
  14. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The optimizer has changed in 11.2.0.3 and Oracle now bypasses the results cache after 10 seconds of generating NEW status; it makes no sense for Oracle to visit the results cache when it knows the results will never be loaded. 11.1 wasn't that smart in the optimizer department.