Re: Disable Buffering

"Andrew Allen" <ajallen_at_mailhost.det.ameritech.net> schrieb im Newsbeitrag news:3E5CBDB0.BF9ECE61_at_mailhost.det.ameritech.net...
> "Jörg Brenninkmeyer" wrote:
> >
> > Hello,
> >
> > I want to run performance tests on an Oracle DB using JDBC in order to
> > evaluate different methods of data storation. Since every query should
be
> > repeated a certain number of times for statistical reasons, and many
queries
> > are similar, buffering would really mess up the results. That's why I
want
> > to disable buffering.
> >
> > I already tried to set the size of the buffering space to zero, but
Oracle
> > doesn't let me choose values smaller than 4 MB. Is there a flag to make
> > Oracle not use the cache? Or any other possibility?
> >
>
> You are 180 degrees off. You want production size buffering. Here
> is why.
> 1. In a production system the buffer is never empty. In fact, the
> most commonly accessed blocks will already be there. An empty or no
> buffer will force all queries to read from disk. This does not
> reflect a real situation. What you are doing is like driving a race
> car, on one liter of fuel at a time. One can never win a race if he
> has to keep going into the pit for fuel. Similarly, Oracle can
> never perform well if it keeps having to go to disk.
>
> 2. The target of tuning should be to reduce logical IO. If you
> reduce LIO, buffering and PIO will take care of themselves.
>
> You are deceiving yourself if you think that having no buffer -- in
> Oracle impossible because all activity against data occurs in the
> buffer --- will reveal true performance statistics. What you are
> planning to do is GUARANTEED to make oracle look like the slowest
> database since clay tablets.
>
> The real way to test query performance is to run the query once to
> load the buffer, then run it four or five times again and average
> the wall times.

Ok, you're right, it doesn't make sense to not use the buffer at all and probably isn't even possible. But since I want to do some queries which are based on the same table _independently_ of each other, I must at least empty / override the cache between those queries. Is there a way to do this automatically? Or do I have to do some queries on a completely different table?

Thanks,
 Jörg Received on Wed Feb 26 2003 - 10:36:10 CST