Why is the result cache always disabled in Oracle 11g

All we need is an easy explanation of the problem, so here it is.

I’m trying to enable the result cache in Oracle 11g. I have used Enterprise Manager to change the result_cache_max_size and result_cache_max_result values to ‘1M’ and ‘100’ respectively (maybe these aren’t optimal settings, but I’m just trying to get it to work!).

After I make these changes I use the following query to see if the changes have really been made:

select * from V$PARAMETER where LOWER("NAME") like '%result_cache%';

I can see that both have been set correctly. However, when I run queries with the result_cache hint, I can see from the Autotrace that the cache isn’t being used. If I then run the following query:

SELECT dbms_result_cache.status() FROM dual;

I can see that it’s status is ‘DISABLED’. Restarting the database just makes the result_cache_max_size parameter go back to 0 (disabled).

How can I enable the result cache?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Are you using Enterprise Edition? It looks like this is a EE feature:

http://docs.oracle.com/cd/B28359_01/license.111/b28287/editions.htm

CTRL + F the docs and search for result cache and supported editions.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply