February 22, 2008 - 10am Central time zone
Reviewer: Michal Kuratczyk from Warsaw, Poland
> Why oracle did not tried times tens on the middleware.
Well, we actually have something like that - it's called Oracle Coherence (formerly Tangosol
Coherence). It is not a SQL database (it's not a database as such at all in fact, because you don't
have any persistent storage out of the box but you can integrate it with RDBMS, Berkeley DB and
whatnot) but very often, when customers ask about Times Ten, Coherence is a better answer. Really
cool product and scalability/performance solution.
Result cahce
February 24, 2008 - 1pm Central time zone
Reviewer: Anil from Dubai
Hi
Oracle 11g new, result cache looks good on the first shot. Immediate test shows it is really fast
if uses appropriately.
But while searching I have seen some not very encouraging reports about it scalability,
http://www.pythian.com/blogs/683/oracle-11g-result-cache-tested-on-eight-way-itanium
What is you take on this..
Rgds
anil
Followup February 24, 2008 - 2pm Central time zone:
well, it is a sort of not real world test isn't it.
Q) why would you use the result cache?
A) to take a complex query and save it's results, you know something that takes 1,000,000 rows and aggregates it into 1,000
So, what do they test with?
SQL> begin
2 for cur in (select /*+ result_cache */ * from t)
3 loop
4 null;
5 end loop;
6 end;
7 /
I'll ask you - would you realistically expect the results of a simple "select * from table" to be realistic here?
would you cache that? (I would not)
How about the results of "select count(*) from t where <condition>" or something more realistic - where you turn MANY ROWS into ONE ROW
Put it another way, I call the server result cache a JIT MV, just in time materialized view.
So, would you ever:
create or replace materialized view mv
enable query rewrite
refresh fast
as
select * from big_table;
would you do that? (no, please say "no")
if you did - would you expect it to be better (no, please say "no")
So, suggestion: go back to their web site and leave a followup and say "hey, if you use this for a query that takes a query from running in a second down to 1/1000th of a second - you know, something we'd really use this for - what do you see then?????"
The server result cache makes sense where a materialized view might have made sense, it is a logical extension of that
for select * from t or select * from t where indexed_column = value, you are not going to see much benefit.
their graphs show that, the red and the blue are equal in the beginning - why would you use the result cache if it didn't do anything for you from the beginning??!?!?
some people dont get it simply
February 25, 2008 - 1am Central time zone
Reviewer: AMIR RIAZ
Hi Tom
just read the above article about resultset cache. I think Alex Fatkulin test case is not valid not because he is doing a simple
select /*+ result_cache */ n from t
but because of the way he has coded the test case. lets go through the test case.
SQL> variable rc refcursor;
SQL> exec open :rc for select /*+ result_cache */ n from t;
PL/SQL procedure successfully completed.
SQL> create or replace procedure rc_fetch(
2 p_rc in sys_refcursor
3 ) is
4 l_n number;
5 begin
6 fetch p_rc into l_n;
7 dbms_output.put_line(to_char(l_n));
8 end;
9 /
he logs into one session and run the
SQL> set serveroutput on
SQL> exec rc_fetch(:rc);
then in another session he runs the same with tracing on and gets the following enqeues
enq: RC - Result Cache: Contention 6 87.15 137.17
first he was not able to understand why he has these waits and went through two more posts. well in my humble opinion these waits are because oracle has to lock the resultset cache when we open the cursor. the purpose is to prevent then invalidation during the fetch if the table data changes. what alex did is he open the cursor but did not close it so the oracle is still considering that he needs more fetchs and did not releases that lock. so RC locks are used for preventing issues like data integrity.
he himself says
So you see, our first session is holding the RC enqueue in exclusive mode. The second session was trying to acquire this enqueue in shared mode until a certain amount of timeouts (six, according to our trace). The good news is that subsequent executions in any other session (including our second session) will not try to acquire RC enqueue again. The bad news is that they will not use Result Cache. As long as our first session has fetched all rows from a refcursor all other sessions will suddenly start to use Result Cache without incrementing the total_req# stat for RC enqueue in the v$enqueue_stat view.
but why oracle used exclusive locks on RC. why they did not used shared locks.
regards
Amir Riaz
result cache
February 25, 2008 - 1pm Central time zone
Reviewer: Anil from Dubai
Tom,
100% agreed, When I read that article completely I realized that the test was not reasonable.
"Wrong use of the feature"
Rgds
Anil
does open cursor lock result set cache
February 27, 2008 - 7am Central time zone
Reviewer: AMIR RIAZ
hi
in above i have written a review but which i think in oracle 11g with resutl set cache when we open
cursor the open cursor locks the result set cache for integrity reasons and if the close cursor
statement is not run oracle 11g keeps a lock on it and that why alxender is getting RC locks. Do
you think my observation is right. i do agree with you that result set cache is like materialize
view and simple select statement cannot shows the true capabilty of result set cache but i want to
add to it that in above test case the cursor was not close on the result set cache and that 's why
he is getting so much waits. do you think i am right?
regards
Amir riaz
Followup February 27, 2008 - 11am Central time zone:
I haven't read the entire paper yet. But as long as a cursor is opened against the result cache - it would have to stay there, yes. But that does not prevent concurrency at all - so not sure what the big deal would be.
that cached result could be invalidated by another session (it wouldn't be used by furture "opens")
that cached result can be accessed by other sessions (until it is invalidated).
so, not sure the relevance.
lets me explain
February 27, 2008 - 1pm Central time zone
Reviewer: AMIR RIAZ
tom
let me explain with a time line. while giving the explanation i am considering the coder did not close the cursor and while open cursor statement has lock the resultset cache. the objective here is to understand weather the concurrency is effected by it or not. i.e oracle locks the result set cursor in share mode or in exclusive mode. suppose a user open cursor at time t1 in session s1 during the result set retival(fetch) at time t2 an update is performed on the table to which result set cache is associated in another session s2. in session s3 a user try to retrieve the same result set from result set cache. so time line will be something like this
t1 ============================== s1
t2 ====== s2
t3 ========================= s3
now if we dont close the cursor in session s1 the result set cache will be there even after update in the session s2 and since session s3 should see the updated data because it queries at time t3 after t2 is committed it has to either wait or query from database and from that its been derived that result set cache should be locked in exclusive mode to prevent other session querying it.
if the result set cache is locked when the cursor is opened then it should be followed by a close statement or we should use implicit cursor as you did in your examples. otherwise same problem will occur as in alexender test case. .
regards
Amir Riaz
Followup February 27, 2008 - 7pm Central time zone:
there can and commonly are multiple copies of the result in the cache based on different points in time.
other sessions are not prevented from querying anything - they will however query the CORRECT CACHED result.
other sessions are not prevented from updating anything - they will however invalidate the current server result cache (which prevents others from hitting it, they will not use what was there - they have to REBUILD the cached result)
Is it necessary?
May 6, 2008 - 5pm Central time zone
Reviewer: Andrew Markiewicz from Madison, WI
I received an email from Oracle with a link proclaiming the virtues of TimesTen in 11g. I'd never heard of this feature.
http://www.oracle.com/newsletters/information-indepth/database-insider/may-08/inmemory.html?msgid=65
20781
What struck me was the apparent need to get the data closer to the application .... in the application server. Why not get the application closer to the data ... in the database?
"By caching performance-critical subsets of an Oracle Database in main memory in the application tier, the software dramatically reduces application response times and increases throughput by relying on memory-optimized algorithms and avoiding communication delays between computers and processes."
I've been reading some information about the TimesTen database. There's a good pdf on OTN. It seems to get some of it's performance benefit by using different optimizer algorithms that require the data to be entirely in memory, thus being able to directly access memory addresses instead of developing plans that may need to be optimized to access disk. Fair enough. I can see that being a benefit. But how much of the performance benefit is gained because of reduced network traffic from the OAS to DB? And if it's all in memory, what about read consistency and concurrency?
http://download.oracle.com/otn_hosted_doc/timesten/703/TimesTen-Documentation/intro.pdf
The documentation says it supports read committed isolation and has multiversioning so readers are not blocked, but they do still allow non-repeatable reads. This is not the way I've come to expect Oracle to work.
"Read committed isolation provides increased concurrency because readers do not
block writers and writers do not block readers. This isolation level is useful for
applications that have long-running scans that may conflict with other operations
needing access to a scanned row. However, the disadvantage when using this
isolation level is that non-repeatable reads are possible within a transaction or
even a single statement (for example, the inner loop of a nested join)."
I don't have any experience with this feature, but my first impression is that it is necessary in response to some poorly performing Java applications residing on an app server that tried to use the database as a non-proprietary black box instead of designing database applications close to the data within the database using plsql.
Andrew
Followup May 7, 2008 - 1am Central time zone:
Times ten is all about "latency".
In a not-in-memory database you typically have
a) disk
b) network
involved - both of those can and will make response times fluctuate. Time ten can remove both of them - so the only thing that will cause a query to really fluctuate response time wise will be CPU and if you determine the cpu required to do query X, you can remove these variations and get a consistent, constant response time.
that is what it is best for. I agree that I would not want to use it to try and fix a middle tier application.
as for "but it allows for non-repeatable read" - so does Oracle in read committed - no difference there. Not sure what you meant by that bit
non-repeatable read
May 7, 2008 - 9am Central time zone
Reviewer: Andrew Markiewicz from Madison, WI
Tom,
This is what I was getting at.
Excerpt from the Time Ten documentation:
"However, the disadvantage when using this
isolation level is that non-repeatable reads are possible within a transaction or
even a single statement (for example, the inner loop of a nested join)."
My understanding from your book "Expert One On One" Chp 3 p. 127 is that Oracle does not do this since it will rebuild the data to the point when the single sql statement began.
"In Oracle, using multi-versioning and read consistent queries, the answer we get from the accounts query is the same in READ COMMITTED as it was in the READ UNCOMMITTED example. Oracle will reconstruct the modified data as it appeared when the query began, returning the answer that was in the database when the query started."
That is different than TimesTen isn't it?
Followup May 8, 2008 - 2am Central time zone:
Sounds like it could be, I haven't had a need to utilize it myself, so I haven't really tested it at all.
got a pointer to the full documentation you are quoting (always best to see things in context)
ResultCache research
May 7, 2008 - 11am Central time zone
Reviewer: Alex Fatkulin from Canada
ok, I will answer this on by one.
Amir Riaz,
"first he was not able to understand why he has these waits and went through two more posts"
I assume you did not read all of my posts. First of all -- I did three additional posts, not two
:). Second -- last three posts are NOT related with the first one, <i>because first one is about
enqueue while the subsequent ones are about latches</i>. When someone is "not able to understand
why" he does more research on the same topic, how completely different topics transformed into "not
able to understand why" is not clear to me. Taking into account that you later did post my own
explanation (which means I did understand the issue) makes your statement even more confusing for
me.
And how the fact that I did not closed the cursor invalidates the test? Does documentation says
anywhere "you have to close your cursor of we will stop you cold dead for 6 seconds on your first
try but not on your second try (but you will not use RC cache)?".
Tom,
I appreciate your feedback. Since you said that you didn't read the entire paper I'll skip right
into the paper's goal.
The research was around two things:
(1) We have only <i>one ResultCache latch</i>
(2) This latch <i>can only be acquired in an exclusive mode</i>, even if you only need to read from
a result cache.
The test cases were selected to put as much pressure on that single latch as possible and, mind
you, I explicitly state that. Nowhere in the paper I am stating that this is a best application for
ResultCache. The goal was to find the limits and implications of a single latch and contribute to a
community by showing the case where RC <i>should not</i> be used and explain why. I am not hiding
how did I get these results nor pretending it was the best use, I simply provided my observations.
The statement that "ResultCache is not appropriate for small things" is not obvious, isn't it? Sort
of saying "don't use Oracle Buffer Cache to cache small tables".
Oracle Documentations says:
"Query retrieval from the query result cache is faster than rerunning the query."
It doesn't mention whether it should be big, small or what "real world application" means. I saw
many real world applications where things were used in a way they were never designed/supposed to
:) And nowhere it says "don't use RC for small queries" as well as nowhere it says "RC is more
appropriate for DWH-like quires". All it says is "running query from result cache is faster".
Followup May 8, 2008 - 3am Central time zone:
... I am not
hiding how did I get these results nor pretending it was the best use,...
that is where I think this research falls short. Rather than putting a damper on the entire thing - maybe demonstrate how it works, where it should be used and (in this rather obvious - you would never use it for this case) when it should not be.
If you print something relatively negative, but your example is so "non-real world", well...?
The result cache would be used to take a query that needs to process a bit of information into a smaller bit of information (sort of like a materialized view) and cut out the processing time. A simple "select * from t" - I don't see that making real world sense - do you?
TimesTen Intro
May 8, 2008 - 9am Central time zone
Reviewer: Andrew Markiewicz from Madison, WI
http://download.oracle.com/otn_hosted_doc/timesten/703/TimesTen-Documentation/intro.pdf
Ch4
P. 36-37
Followup May 12, 2008 - 9am Central time zone:
it would appear that way - the times ten read committed isolation does not provide for consistent read in the way Oracle does.
Real World Sense
May 8, 2008 - 12pm Central time zone
Reviewer: Alex Fatkulin from Canada
"The result cache would be used to take a query that needs to process a bit of information into a
smaller bit of information (sort of like a materialized view) and cut out the processing time. A
simple "select * from t" - I don't see that making real world sense - do you?"
I do see an existence of RESULT_CACHE_MODE = FORCE as a claim "we are good to cache everything",
because if you are not -- why do you have a parameter to cache everything in a first place?
There is a difference between "will not do faster" and "will do slower" and I would tell you that
there is no "real world sense" in why taking something out of a result cache should be slower than
doing the same out of a buffer cache (regardless of the fact how much you "fold" it). In fact,
result cache could be optimized for doing what it is intended to do -- while buffer cache has much
more things to care about. This is what I was expecting from result cache back in 2007 but not what
I have got.
There is also no "real world sense" in why do you have a single latch without shared mode gets
(anyone here care to remember shared pool latch in oracle 7?) and, truth be told, if they did so --
result cache could be on par with a buffer cache in every case and not only "if you do this, that
and there".
Followup May 12, 2008 - 9am Central time zone:
Alex - we do not recommend using that and it is not the default.
but - suppose you have a system whereby every query:
... "The result cache would be used to take a query that needs to process a bit of
information into a smaller bit of information (sort of like a materialized
view) and cut out the processing time. A simple "select * from t" - I don't see
that making real world sense - do you?"
...
satisified that.
I do not agree with your results as posted, you can do with that whatever you like. They are not real world - they cause people to question "why, why did you do that, why not test something we'd actually use"
you did not read all of my posts
May 10, 2008 - 1am Central time zone
Reviewer: Amir Riaz
hi
I have read all of your posts before posting something on this thread and i understand what the enqueue waits are But on the contrary you did not read this thread and just went on. Again you did not gave any logic. just simple replies to defend a wrong test case. here is a cut and past for my first post
SQL> variable rc refcursor;
SQL> exec open :rc for select /*+ result_cache */ n from t;
PL/SQL procedure successfully completed.
SQL> create or replace procedure rc_fetch(
2 p_rc in sys_refcursor
3 ) is
4 l_n number;
5 begin
6 fetch p_rc into l_n;
7 dbms_output.put_line(to_char(l_n));
8 end;
9 /
he logs into one session and run the
SQL> set serveroutput on
SQL> exec rc_fetch(:rc);
then in another session he runs the same with tracing on and gets the following enqeues
enq: RC - Result Cache: Contention 6 87.15 137.17
first he was not able to understand why he has these waits and went through two more posts. well in my humble opinion these waits are because oracle has to lock the resultset cache when we open the cursor. the purpose is to prevent then invalidation during the fetch if the table data changes. what alex did is he open the cursor but did not close it so the oracle is still considering that he needs more fetchs and did not releases that lock. so RC locks are used for preventing issues like data integrity.
he himself says
So you see, our first session is holding the RC enqueue in exclusive mode. The second session was trying to acquire this enqueue in shared mode until a certain amount of timeouts (six, according to our trace). The good news is that subsequent executions in any other session (including our second session) will not try to acquire RC enqueue again. The bad news is that they will not use Result Cache. As long as our first session has fetched all rows from a refcursor all other sessions will suddenly start to use Result Cache without incrementing the total_req# stat for RC enqueue in the v$enqueue_stat view.
but why oracle used exclusive locks on RC. why they did not used shared locks.
then i realised that to be consistent at some point in time oracle have to lock the result set cache in exclusive mode so my second question was. Were there only one result set cache or multiple so i posted this
let me explain with a time line. while giving the explanation i am considering the coder did not close the cursor and while open cursor statement has lock the resultset cache. the objective here is to understand weather the concurrency is effected by it or not. i.e oracle locks the result set cursor in share mode or in exclusive mode. suppose a user open cursor at time t1 in session s1 during the result set retival(fetch) at time t2 an update is performed on the table to which result set cache is associated in another session s2. in session s3 a user try to retrieve the same result set from result set cache. so time line will be something like this
t1 ============================== s1
t2 ====== s2
t3 ========================= s3
now if we dont close the cursor in session s1 the result set cache will be there even after update in the session s2 and since session s3 should see the updated data because it queries at time t3 after t2 is committed it has to either wait or query from database and from that its been derived that result set cache should be locked in exclusive mode to prevent other session querying it.
if the result set cache is locked when the cursor is opened then it should be followed by a close statement or we should use implicit cursor as you did in your examples. otherwise same problem will occur as in alexender test case. .
as tom describes there can be multiple result set caches.you can read the correct reply from that post. So in all cases its your Test case which is wrong and does not even prove what you said above. You are just trying to defend your self on the modesty of Tom. If you really have something PLEASE POST ON THIS THREAD.
May 10, 2008 - 1pm Central time zone
Reviewer: Alex Fatkulin from Canada
Amir Riaz,
I think your entire confusion goes from the fact that you did not realized that I kept the cursor
open on purpose -- to demonstrate my observation against RC enqueue waits.
May 10, 2008 - 2pm Central time zone
Reviewer: Alex Fatkulin from Canada
I have read all of your posts before posting something on this thread and i understand what the enqueue waits are
I still fail to see what you did.
The purpose if this article: http://www.pythian.com/blogs/594/oracle-11g-query-result-cache-and-the-rc-enqueue
...was to solely demonstrate how and when you can wait on RC enqueue because this behavior is nowhere documented. This article has nothing to do with scalability problems discussed in the following articles.
This article:
http://www.pythian.com/blogs/598/oracle-11g-query-result-cache-rc-latches
...introduces you to RC latches (not enqueues) and outlines that the entire RC cache is protected by a single latch without shared mode gets at all. This article is of a totally different matter from the previous one and it uses completely different test cases which were constructed with only one goal -- to put as much pressure on RC latch as possible -- in order to expose the limits. The test cases used to stress RC latch were read-only without anyone trying to modify anything and I also made sure everything can fit in RC cache so there were no age-in and age-out problems as well -- which means I made everything I could think of to allow Oracle get RC latch in a shared mode.
Unfortunately, back then in 2007 I haven't had any appropriate hardware running 11G to really test concurrency so when I got my hands on quad core box I redid my test on it:
http://www.pythian.com/blogs/660/does-oracle-11gs-result-cache-scale-poorly
...but that was not enough to saturate this single latch as well, however, from these test you can already spot how badly RC latch waits were growing as concurrency increases.
And finally:
http://www.pythian.com/blogs/683/oracle-11g-result-cache-tested-on-eight-way-itanium
...where you can see the limits exposed.
The important point about these articles (which you seems to have totally missed or ignored) -- the test case which demonstrated RC enqueue waits has NOTHING to do with questioning scalability. The articles which does question RC scalability are using completely different test cases.
Other members may comment
May 11, 2008 - 2am Central time zone
Reviewer: Amir Riaz
hi
http://www.pythian.com/blogs/594/oracle-11g-query-result-cache-and-the-rc-enqueue
in your article you did not mentioned anywhere that you are keeping the cursor open with the intention of showing RC waits further in your article above you gave the following comments
I¿m going with the simplest example I could think of. You are free to apply that to any real-world situation you can imagine.
variable rc refcursor;
exec open :rc for select /*+ result_cache */ n from t;
on one side you are saying that you are doing this intentionally to demonstrate the RC enq and in your article you mentioned your self you are saying that YOU ARE FREE TO APPLY THAT TO Any real world situation ???
THIS IS BECAUSE YOU DONT KNOW THAT KEEPING THE CURSOR OPEN WILL INTRODUCE RC WAITS.POINTS OUT THAT YOU WERE TAKING THIS EXAMPLE AS A REAL WORLD EXAMPLE AS YOU YOURSELF WRITTEN IN ARTICLE.
further prof in down in the article where you accepted yourself
It¿s a pity that no one bothered to add those enqueues here (probably in the desperate belief that no one will notice their presence). You can discover that any user session connected to a database is holding AE enqueue in shared mode. The purpose of that enqueue is unknown to me for now. RC stands for Result Cache.
clearly states that you dont know what you are doing.
I accept that oracle missed that documentation but its just a documentation bug.
You did not mentioned anywhere what you are trying to prove by keeping the cursor open. You did not mentioned anywhere in the article that you are keeping cursor open intentionally for the purpose of demonstration. Rather you were apply that piece of code to any real world example.
May 11, 2008 - 2am Central time zone
Reviewer: Alex Fatkulin from Canada
Any real world situation ???
Easily. Imagine a report with pagination which is based on aggregated data. You decided to use RC for it. So your user fires up a report and looks at the first page -- he didn't views all the pages yet. Now, if you have a statefull environment -- you will naturally want to keep the cursor open in order to be able to fetch the next page when user will request you to do so. This is also the first user who started to initialize RC this that resultset. Now, if second user will request the same report -- he will be force to wait 2 minutes for RC enqueue timeout.
clearly states that you dont know what you are doing.
I am talking about AE enqueue there -- AE enqueue is not documented anywhere. I hope you always know what are you doing when you see undocumented stuff, because I don't, and you better share with us how you do so.
You did not mentioned anywhere what you are trying to prove by keeping the cursor open. You did not mentioned anywhere in the article that you are keeping cursor open intentionally for the purpose of demonstration. Rather you were apply that piece of code to any real world example.
At the very beginning I am saying:
Way before 11G made its way to production, I joked that this new feature called ¿Query Result Cache¿ will just implicitly create a materialized view, protecting dependent objects with some new enqueue while doing so.
...
Query Result Cache was the first new feature that I put to the test, only to discover that you can spend more than two minutes waiting on¿ guess what."
This is the first paragraph and it clearly says that I was expecting this enqueue and I am going to show you how to see two minutes waiting on it (I hope the "guess what" part wasn't a rocket since).
Sorry, but I don't know how else I can help you but repeat "please read what I am writing properly".
May 11, 2008 - 3am Central time zone
Reviewer: Alex Fatkulin from Canada
YOU ARE FREE TO APPLY THAT TO Any real world situation ???
ok, I think your confusion with that statement is different than I initially thought, so I am going to comment second time on that one.
I suppose you understood it as "every real world case will experience this wait". This is not what I am saying. I am saying "go ahead and apply this testcase to what are you doing and see if you are affected by it".
I think what we really have here is some sort of a language barrier -- you misunderstanding the meanings of words.
Another reason for using Times Ten over Result Cache
May 11, 2008 - 10am Central time zone
Reviewer: Arup Nanda from Danbury, CT, USA
Skipping the discussion on whether the tests done by Alex are real-worldy enough or not; I have
another anwer to the original post - why would you choose timesten over Result Cache.
Tom already gave one compelling reason - the network latency between the app and db servers is a
huge motivation for using TimesTen. the other reason is redundancy. Typically you have several app
servers for a single database server. Using TimesTen on these app servers reduce the need to go the
database - to the buffer or not. One example (yes, real world), one where I am very cloely
associated with - has an application that provides rates information on a webpage, after doing some
lengthy calculations. This application is served by 6 app servers around the world and the database
is at one place - Boston, USA. Using Result Cache will not really help; since the data is fairly
static; but the app server will need to check the result cache every time a query comes for rates.
With up to 100,000 queries per second, that would just cripple the database.
In this case, we used an application server level data cache. Since TT was not available then, we
wrote our own tool. Right now we are doing a conversion to TT to replace our homegrown tool. I
deliberately chose TT even though I knew very well about Result Cache, for this specific reason.
So, the moral is obvious - Result Cache is not a replacement for TT; they both server different
purpose.
resultset cache real world example
May 12, 2008 - 4am Central time zone
Reviewer: daniel
http://www.oracle-developer.net/display.php?id=503
in the performance considerations section
We can see that there is little performance difference between the cached and uncached lookups. In fact, the result cache uses more latches (note the two million hits on the new Result Cache latch), although it is marginally quicker overall. These results are probably to be expected when we consider the work to be done in a single primary key lookup. First, the index and table data will be in the buffer cache and second, the amount of I/O saved by a lookup in the query result cache is minimal.
The best performance gains will be achieved when the result cache is preventing a large amount of database work from being repeated (such as in our aggregate queries earlier). Unfortunately, these types of queries are going to be rare in the result cache as the base tables will probably be frequently updated.
this perhaps what tom and other members are trying to say. A resultset cache is like materialized view. Its will best suit for queries with aggregation as in materialized view and will save the database work.
Perhaps now alex understands why his test case is not the real world. under these conditions all of his posts has flaw so they are not valid
May 12, 2008 - 11am Central time zone
Reviewer: Alex Fatkulin from Canada
Alex - we do not recommend using that and it is not the default.
Can you share a link (if it would be different from this page)?
but - suppose you have a system whereby every query...
This is what we are talking here and now and I am cool with that. But back then in 2007 these "suppose" bits and pieces were not clear.
I disagree on the definition of "real worldliness" of tests as "it have to fold the original query".
1. Even if you cache a query which does nothing but a single row lookups -- RC is still able to serve these request slightly faster than a perfectly sized single-table hash cluster which is a darn good result and this is why I don't see the "folding" factor as a driving condition -- you could cache anything and benefit from anything. Is is a concurrency which kills RC, not the pure speed of getting something out.
2. Why do we have CBO in the database? If processing elimination factor is a principal -- why don't you let CBO make a decision what is good for RC caching and what is not? You do this with regular MVs.
Followup May 12, 2008 - 1pm Central time zone:
just read the reference guide - it is not the default.
You may print whatever you like, however, when you compare a
select * from t uncached
versus
select * from t cached
it makes people go "hmmm", what was the point? I'd never do that.
Sort of like if you tested a materialized view in that fashion. You might find the materialized view to be worse than using the table directly - the rewrite activity and everything else that would have to go on.
If you demonstrated this feature in a meaningful way, we might have a different view of the paper - as it is, it isn't showing something anyone would really do - you can say all you want "but wait, you could do it, nothing stops you - but so what? That doesn't mean anything. People would rather look at something they would actually consider doing, that is all.
we have a CBO in the database to optimize queries, indeed. And every release it gets better at doing that. Not sure I get your point there.
May 12, 2008 - 3pm Central time zone
Reviewer: Alex Fatkulin from Canada
just read the reference guide - it is not the default.
I was talking about "we do not recommend using that" part.
Not sure I get your point there.
The only way we can use RC is through hinting or setting result_cache_mode to force.
Documentation says:
The use of the result cache is database-wide decision
... and that makes sense given that one should escape using hints whether possible, so result_cache_mode=force is suggested as a preferred method of using Result Cache (that's why I became curious where did you read about this being not recommended).
Read further:
OLTP applications can benefit significantly from the use of the result cache
Which is contrary to what we have in reality, no? Isn't OLTP applications are characterized by small queries with high degree of concurrency -- and this is exactly where Result Cache fails short. I understand your position as "Result Cache is mostly for DSS-like queries" and I agree with it. But this is not what documentation says.
My test cases shows exactly this -- if you have something which you are going to execute on a frequent basis with high degree of concurrency (thinking of OLTP, no?)-- Result Cache is going to kill your scalability. You can replace my query with whatever query you like -- OLTP style execution will kill you regardless of the query you are using.
Followup May 13, 2008 - 10am Central time zone:
turnabout: show me where you see us ever recommending it?? negatives are hard to prove sometimes. do you see us saying "hey, this is a good idea, flip this on..."
.... and that makes sense given that one should escape using hints whether possible, ....
that is a patently and entirely FALSE statement. There are good hints (result cache is one of them, all rows is another, dynamic sampling, append - there are many good hints, hints that give the optimizer more lattitude, more information, more options.
... Which is contrary to what we have in reality, no? ...
no, not really. You are thinking the update component - there are many frequently executed and cacheable queries in many (i might even say most) transactional applications
I still think you need a better example, caching a full scan - ??
CRC is different from DRC
May 13, 2008 - 1am Central time zone
Reviewer: Amir Riaz
Hi
from above and many others article on the internet i have seen the same thing. Even at otn the RC articles are questionable. even this article is not based on realism.
http://www.oracle-developer.net/display.php?id=503
why?
this is because Database resultset cache is not design for conncurrency. Its designed for frequently executing queries whose base tables are updatable. Its not designed for lookup tables with huge number of users. we are making test cases on wrong assumptions. so here are few assumptions i developed when design the above system and they should equally apply to RC. They can
Database ResultSet Cache (designed for)
frequently executing queries (but not for large number of users)
the must have some sort of aggregation
behave like materialized view
base tables of the query are updatable.
database resultset cache(not designed for)
huge number of conncurrent users
readonly look up tables
Client ResultSet cache(design for)
huge number of conncurrent users
readonly look up tables
client resultset cache(not designed for)
updatable tables
I have these experiences when i was designing the above system. The problems are virtually the same with RC.
what most papers are doing they are using look up table like considering them readonly and using Database resultset cache which is not design for that purpose or neither it can be designed
if you run your test on Client resultset cache you will see that its highly conncurrent for readonly lookup table.so no problem with CRC.
OLTP applications can benefit significantly from the use of the result cache
is completely true. for oltp lookup readonly tables you can use client resultset cache and which is highly conncurent. for small number of queries which just execute often and are expensive for the database and have updatable base tables you can use database resultset cache. to understand my assumptions you have know how DRC and CRC works
if you made you test case based on these assumption you will see RC is beneficial for the oltp application. also i must say all of these assumptions are based on experience and my understanding of how RC work
regards
Amir Riaz
A better example
May 13, 2008 - 4pm Central time zone
Reviewer: Alex Fatkulin from Canada
do you see us saying "hey, this is a good idea, flip this on..."
I don't know how else one can interpret "it will result in a faster response time for your frequently executed functions and queries" and "it is a database-wide decision". You may say about how appropriate or not something is for Result Cache -- and I hear you. But the guys who wrote the documentation didn't.
I still think you need a better example, caching a full scan - ??
I still think that one should read entire thing before going to comment on it :)
In my scalability test I was not caching a full scan -- I was caching a lookup into a table.
Of course, you will say "this is not a real world use", despite the fact that Performance Tuning Guide is perfectly OK with that. Heck -- you can even see article published in Oracle Magazine:
http://www.oracle.com/technology/oramag/oracle/07-sep/o57plsql.html
...which does exactly the same -- it caches a function which does nothing but single row lookups into a table and you spot "best practices" and "real world" there (and, again, -- this is perfectly legitimate use for this feature given the way it is described in the documentation). Care to see how well it scales (they didn't do scalability tests for whatever reason)?
Followup May 14, 2008 - 10am Central time zone:
sigh, I was talking about the init.ora parameter, the one you say we are recommending to set - which we don't.
did you or did you not lead with a full scan - it is a rather large article and pieced to together in bits. I find it misleading.
May 13, 2008 - 4pm Central time zone
Reviewer: Alex Fatkulin from Canada
this is because Database resultset cache is not design for conncurrency. Its designed for frequently executing queries whose base tables are updatable. Its not designed for lookup tables with huge number of users. we are making test cases on wrong assumptions.
And please tell me -- how would you know that without us did all these test to actually show you that "resultset cache is not design for conncurrency"? You are using the word "wrong assumptions" but you need to understand -- all these tests made these assumptions to be wrong, not the other way around.
check this out
May 15, 2008 - 2am Central time zone
Reviewer: Amir Riaz
Its turning out that resultset cache is perhaps the most misunderstood topic in oracle 11g. However
tom is as usual right. As a proof here is his paper on otn. it is advocating exactly what he is
saying here.
http://www.oracle.com/technology/oramag/oracle/07-sep/o57asktom.html
Followup May 19, 2008 - 11am Central time zone:
well, that is a recursive proof :)
You are using my material to prove me right or wrong...
paper from tom is needed here
May 15, 2008 - 1pm Central time zone
Reviewer: Jayson
here is an other article from alex. He is presistent on his view point and has written another
article. here is the link.
http://www.pythian.com/blogs/1004/oracle-11g-result-cache-in-the-real-world
I think now Tom should write an article explaining what is the real world test case should be like.
with similar graphs and data. because there is so much confusion on this topic even the guru's are
make mistakes so why not prove things in Tom Kyte style with a test case showing what the real
world is?
only proving that you are right
May 20, 2008 - 10am Central time zone
Reviewer: Amir Riaz
just proving that you are right
An old saying...
February 26, 2009 - 2pm Central time zone
Reviewer: DBA from USA
http://www.pythian.com/blogs/598/oracle-11g-query-result-cache-rc-latches#comments
|