Re: Oracle caching
Date: Sat, 28 Jul 2001 02:14:52 GMT
Message-ID: <wep87.414726$p33.8422248_at_news1.sttls1.wa.home.com>
They are correct. If you use bind variables then the same plan will be
executed (even though the results are different - e.g. select ... from
person where ssn=:hv_ssn where hv_ssn=000-00-0000 and then
hv_ssn=999-34-1234)
Every single query you are doing a hard parse. On one system I worked on
the programmers decided not to use bind variables ("It couldn't matter that
much..."). So on a particular benchmark (sans bind variables) a process
would take 26 minutes and peg the CPU at 100% utilization. Basically,
almost no one else could use the system while this process was running.
We changed the system so it used bind variables. Same exact process, same
benchmark no changes in the algorithium. Now it took 6 minutes and had
occaisional cpu peaks of 35%. (average was 5 to 10 %). Now people could use
the system while this other process was running and not notice that it was
running.
Hard parse means that Oracle has to :
If you use bind variables and just keep changing the bind variables and
reexecuting (not closing the cursor, but not reopening them repeatedly; just
You will notice a dramatic performance improvement after the first query.
Also you will dramatically increase your scalability. It has nothing to do
1. Parse the query for correctness.
2. Make sure all the objects exist (tables, columns etc.)
3. Make sure the user issuing the query has rights on the objects that are
in the query etc.
4. It has to build the plan for executing the query (examine indexes,
statistics, etc. cost out each possible plan and choose the cheapest one.)
5. It has to set up memory areas , describe etc.
6. It sends back information that it is ready to give you the data.
1. Hashes the query to see if it has an exact match. That is it compares
the string that you sent over with ones it already has.
2. If it matches then it can skip steps 2,3,4 which are very expensive.
Jim
"Matt Woodworth" <woodworth_at_acm.org> wrote in message
news:a063f3c2.0107270645.731085df_at_posting.google.com...
> 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 Sat Jul 28 2001 - 04:14:52 CEST