Re: Oracle caching

You guys are miles ahead of me with the programming but depending on the amount of data it may be good to increase SGA memory...

"Jim Kennedy" <kennedy-family_at_home.com> wrote in message news: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 :
> (for a detailed explanation see the Oracle OCI programmer's guide. It
 gives
> a nice flowchart etc.)
> 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.
>
> If you use bind variables and just keep changing the bind variables and
> reexecuting (not closing the cursor, but not reopening them repeatedly;
 just
> once.) Then Oracle has to:
> 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.
>
> You will notice a dramatic performance improvement after the first query.
> Also you will dramatically increase your scalability. It has nothing to
 do
> with caching.(which is important)
> 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 Mon Jul 30 2001 - 16:09:29 CEST