oracle 11g caching techninque

Question

0.00/5 (No votes)

See more:

my select query has 4 table joining,whenever i cache using result_cache it does not work, if i used single table select it works fine,
EX1:SELECT /*+ RESULT_CACHE */ id from tab1 it works fine
but for another query like this
ex2:select /*+ RESULT_CACHE */ ORDERDET*(SELECT COL1,COL2,COL3,COL4
FROM TAB1,TAB2,TAB3,TAB4)ORDERDET
THIS ex2 DOES WORK WHILE CACHING IN ORACLE 11G

Posted 11-Jan-15 17:39pm

Member 10809057

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2015-01-11T17:54:00

Solution 1

You should provide more data why you think the caching isn't working. One cause could be that the data in the tables have been changed thus invalidating the cached result.

In overall, server side caching is rarely needed because the server is caching data block in-memory anyway. The benefit from query specific caching is often so small and situation specific that it would make sense to put the effort to access plan optimization instead.

Posted 11-Jan-15 17:54pm

Wendelius

Comments

Member 10809057 12-Jan-15 0:05am

BUT QUERY IS NOT STORED IN V$RESULT_CACHE_OBJECT TABLE

Wendelius 12-Jan-15 0:32am

If I'm looking correctly, the example 2 contains a subquery as far as I know the subquery results aren't cached if that's what you're looking for.

Also the query contains cartesian joins which most likely means that the size of the result is big. Have a look at the following parameter. Perhaps they provide more information:
- RESULT_CACHE_MAX_SIZE
- RESULT_CACHE_MAX_RESULT

Member 10809057 12-Jan-15 1:22am

HOW MUCH SIZE NEED TO ALLOCATE FOR 3L RECORDS FOR RESULT_CACHE_MAX_SIZE

Wendelius 12-Jan-15 1:32am

That depends greatly on the records. However, as I said if you don't have specific justification for result caching I would let the database to handle the caching based on the data blocks. Just make sure that the size of your db buffer is large enough and concentrate on the actual plans of the queries.