Re: Oracle caching

Jim,

Thank you for your help. I've thought about using bind variables but we may have more permutations than you're guessing (over 100). With that many queries we'll probably lose the benefits of using bind variables anyway. (Just a guess)

The oracle experts around here seem to think that the execute portion of the query will run faster if the same explain plan is followed. Even if different records are retrieved. Have you heard of that? Any thoughts?

Thanks in advance.

"Jim Kennedy" <kennedy-family_at_home.com> wrote in message news:<VW487.413654$p33.8417768_at_news1.sttls1.wa.home.com>...
> 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
Received on Fri Jul 27 2001 - 16:45:12 CEST