Re: Oracle caching

Actually, if you use tkprof you will probably see the reason the query runs faster the second time is that it is not having to do a hard parse. You need to use bind variables and not keep rebuilding the query. (there can only be so many permutations). See the Oracle Application Developer's Guide.

The way caching works is that Oracle always reads in a whole database block at a time. When you issue a query as you retrieve rows the entire block is cached in memory. Of course, before it reads it from disk it checks to see if it has it in memory. If so then it reads it from there. (in the init.ora file the db_block_buffers controls how much caching. Each block buffer represents a database block NOT an amount of ram in bytes. So if your database block size (also indicated in the init.ora usually) is 8192 bytes and db_block_buffers=100 (rather low) then you are using 8192 * 100 = 819,200 bytes of memory for cache.)

They use an LRU method for aging out blocks. (Yes, I know you can define buffer pools and assign different database_block_buffer amounts to them etc. but let's not confuse him)

Jim

"Matt Woodworth" <woodworth_at_acm.org> wrote in message news:a063f3c2.0107260951.1d8ab148_at_posting.google.com...
> 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
Received on Fri Jul 27 2001 - 05:08:37 CEST