Re: Disable Buffering

"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.

-- 
AjA