I consider myself to be an average PL/SQL programmer with perhaps an
entry level understanding of larger Oracle issues. I've been tasked
to learn more about Oracle's caching in order to understand our load
test results.
Without boring you with too much extra info, here's the background:
We've built a web portal-like app that displays ordering information
that is relevant to you. A module could have status info, billing
info, shipping info, etc. all on one screen. Each of the modules has
a query that's built on the fly based on your user information and the
preferences that you've selected. It's unusual to return more than
100 rows. However, the queries can be mildly complicated (20-100 lines
long).
During our load testing we found that as the queries ran the second,
third, etc. time they ran much faster. Of course, this is because of
caching. As we changed the user preferences (and therefore the query)
it would run slow until it became cached.
So here's the question part: How does caching work? Is it based
solely on the explain plan? What if the second query is different but
it selects a subset of the first query? Does it benefit from a
different query that selected the same records?
Is there a record cache and a statement cache. Based on the query
description above, what step would you think takes the most time: 1.
statement parsing, 2. the execute, 3. returning the records. (Have I
acurately broken down the many many phases into the major one?)
Anything you want to teach me about Oracle caching would be great.
I've been studying for two days and I haven't gotten to far yet.
Thanks in advance
Received on Thu Jul 26 2001 - 19:51:15 CEST
