Oracle Caching

Friends,

     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

PS Forgive me if this is a double posting. I tried to post before but google/deja seemed to work oddly. Received on Thu Jul 26 2001 - 14:01:02 CDT