Home > Default > Result Cache Oracle 11gR2

Result Cache Oracle 11gR2

November 30Hits:0
Advertisement
Hi all,
Currently I have some problems with result cache, or maybe I don't understand this feature properly.
I'm trying to switch off the bypass mode, and I'm not able to do this:
SQL> select dbms_result_cache.status from dual;
STATUS
BYPASS
SQL> exec dbms_result_cache.bypass(FALSE);
PL/SQL procedure successfully completed.
SQL> select dbms_result_cache.status from dual;
STATUS
BYPASS
SQL> show parameter result
NAME TYPE VALUE
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
result_cache_max_result integer 5
result_cache_max_size big integer 0
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
SQL> alter system set result_cache_max_size=2M scope=both;
System altered.
SQL> show parameter result
NAME TYPE VALUE
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
result_cache_max_result integer 5
result_cache_max_size big integer 0
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
SQL> alter system set result_cache_max_size=2M scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 523108352 bytes
Fixed Size 1337632 bytes
Variable Size 465569504 bytes
Database Buffers 50331648 bytes
Redo Buffers 5869568 bytes
Database mounted.
Database opened.
SQL> show parameter result
NAME TYPE VALUE
client_result_cache_lag big integer 3000
client_result_cache_size big integer 1G
result_cache_max_result integer 5
result_cache_max_size big integer 0
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
SQL> select dbms_result_cache.status from dual;
STATUS
BYPASS
SQL> set serveroutput on;
SQL> exec dbms_result_cache.memory_report
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 0 bytes (0 blocks)
Maximum Result Size = 0 bytes (0 blocks)
[Memory]
Total Memory = 9440 bytes [0.004% of the Shared Pool]
... Fixed Memory = 9440 bytes [0.004% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
PL/SQL procedure successfully completed.
SQL>
Is there something what I missed?
Thanks for any advices.
Regards,
Piotr

Answers

A little bit more theory:
The result cache resides in the Shared pool. Because the database was managing its memory automatically (parameter memory_target was set) and no one was working with this environment, the database didn't allocate yet enough space for shared pool to give some space for result cache. I had to set min value for shared pool to force the database to automatically allocate some space to shared pool during start up and never make shared pool smaller than specified value.
Hope that this explanation help other people :)
Regards,
Petrus

Read other 8 answers

Tags:

Related Articles

  • Result Cache Oracle 11gR2November 30

    Hi all, Currently I have some problems with result cache, or maybe I don't understand this feature properly. I'm trying to switch off the bypass mode, and I'm not able to do this: SQL> select dbms_result_cache.status from dual; STATUS BYPASS SQL> ex

  • Query result caching on oracle 9 and 10 vs indexingOctober 11

    I am trying to improve performance on oracle 9i and 10g. We use some queries that take up to 30 minutes to execute. I heard that there are some products to cache query results. Would this have any advantage over using indexes or materialized views? D

  • Oracle 11g result cache and TimesTenOctober 11

    Oracle 11g has introduced the concept of result cache whereby the result set of frequently executed queries are stored in cache and used later when other users request the same query. This is different from caching the data blocks and exceuting the q

  • Oracle 11g/R2 Query Result Cache - Incremental UpdateOctober 11

    Hi, In Oracle 11g/R2, I created replica of HR.Employees table & executed the following statement (+Although using SUM() function is non-logical in this case, but just testifying the result+) STEP - 1 SELECT      /+ RESULT_CACHE */ employee_id, first_

  • Function result Cache in oracle 11GNovember 30

    Hi, i am reading the following article and trying to reproduce same set of statements to learn about function result cache. http://www.oracle.com/technology/oramag/oracle/07-sep/o57asktom.html Details about my output: SQL> create or replace function

  • Result Cache in oracle 11g release 2.0.2.0November 30

    All, Few months back I was trying Result Cache feature of 11g release 2.0.1.0. That time oracle mentioned that they have 3 modes to use this feature by setting result_cache_mode paramter to AUTO, FORCE or MANUAL. With AUTO mode (result_cache_mode=AUT

  • Oracle result cache and functionsNovember 30

    Hi All, I am on 11.2 in Linux. I want to use Oracle's result cache to cache results of (user defined) functions, which we use in SELECT commands. My question is, does result caching work for deterministic and non-deterministic functions ? Just curiou

  • Does OCI client result cache and oracle UCP work together?November 30

    I'm using Oracle 11.2.1.0. I've set up FCF and enabled OCI result cache on server side, so on client side, I'm using UCP for failover. this is my datasource configuration: <Resource name="jdbc/MyPool" auth="Container" factory="

  • Oracle 11g Result CacheNovember 30

    Hi, I have read that there is SGA component called Result Cache. According to documentation: The result cache buffers query results. If a query is run that already has results in the result cache, the database returns results from the result cache in

  • Possible result cache problem 11GR1;;Plz Help me :)November 30

    Hello, I just finished installing Oracle 11g Enterprise Edition on my laptop,under windows XP 32 Bit, Just run to test the result cache ,It seems the result cache does not work for me ; Please see the demonstration below and help me to undertsand: (I

Copyright (C) 2019 wisumpire.com, All Rights Reserved. webmaster#wisumpire.com 14 q. 0.862 s.