CODEWITHSUNDEEP
sqloracle-databaseperformancecaching
Andrew Klimov
Andrew Klimov

Reputation: 124

Oracle RESULT_CACHE hint

Respective all!

Can I expect from v$result_cache_objects , that when I have my query with RESULT_CACHE hint included, I should see a non-zero value in row_count column of v$result_cache_objects for my query?

Like this:

Select /*+ RESULT_CACHE*/ order_id, order_name 
from orders

i would like to issue

select row_count from v$cache_memory_objects 
  where cache_id = <cache id of my query>

and see a non-zero value for row_count.

Environment: Oracle 18c XE

Params:

result_cache_mode               MANUAL
result_cache_max_size           8060928
result_cache_max_result         5
result_cache_remote_expiration  0

I think that row_count equal to non-zero is very significant to make me believe that RESULT_CACHE is works. If you have another criterias to make developer believe that RESULT_CACHE is works , please point me.

Thanks in advance, Andrew.

Upvotes: 0

Views: 3204

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21063

Well the first thing to check in v$result_cache_objects is the STATUS is must be Published to be able to use the cached result.

The ROW_COUNT is greater than zero, but is not as interesting, simple saying the cached result has N rows.

The more relevant is the column SCAN_COUNT giving the number of times the cached result was used.

A total overview of the cache usage may be obtained in the view v$result_cache_statistics providing following measures (above other information):

Create Count Success
Find Count
Invalidation Count

Basically while using a cache you are trying to see the Find Count (i.e. using the cached result) much higher than the Create Count Success (i.e. query performed and the result was cached)

but...

the generall problem with the RESULT_CACHE in Oracle (and this is the reason why it was introduced so late and is only marginaly used) is that

  • performant queries work fine even without cache and sometimes when cached need more elapsed time to complete due to internal overhead of the cache.

  • non-performant queries profit from the cache, but the question appears immediately, why the application is repeatedly sending the identical query...

Upvotes: 1

Related Questions