Re: What is a good blocksize to use.

>
>Ben,
>
>All right. I'll give you some real world advice (which will probably get me
>fired, or thrown off this newsgroup, or at the very least ignored forever):
>Forget about head movement, disk latency and all that physics.
>Get the DESIGN right, and TUNE THE BLOODY SQL THAT THE APPLICATION SUBMITS.
>Then come back to the database and resume fiddling around with the
>parameters.
>
>Sorry, not a rant at you: I've had another bad day at the office with a
>crappily designed application, which is never going to perform well in the
>real world,
>despite all the hardware which will doubtless be thrown at the problem in
>due course.
>
>Regards,
>Paul
>Exhausted DBA
>
>

The design is made, it performs wel.
(It does very wel under SQL-server)

But it does less well under Oracle.
The main reason for this is that data is accessed by an item on which it is clustered in SQL-server. It is not clustered (index organised) in Oracle.
(Clustering in SQL-server does main that all information is stored in a B-tree, the rows reside in the leaf level of the B-tree). (From 40 miljoen plus records on average between 300 and 400 get selected on a 'client_id') Some clients have 3 records some clients have 3000 records. On average a client has 40 records, but the average client which is accessed has as said 300 to 400 records. Offcourse there is an index on the client_id, but even then the table has to be accessed 300 to 400 times. This means in 300 reads from disk and 300 blocks in the cache. Up to now we are working with a Oracle 8 implementation.

For Oracle 9 we are considering using Indexed organised tables, which is effectively the same as a clusterd table in SQL-server. We have had some serious advice against it. But in our situation it might improve performance quite a bit. One of the aspects here is the block size. We do some benchmarking, but we are not capable of trying al types of combinations. So some onderstanding how blocksizes influence the access path and more specific the different caches would help to reduce the number of benchmarks and still get the maximum of information out of the benchmarks.

This whole process is driven by the fact that under SQL-server we get a far better performance than we get under Oracle.

ben

Ben Brugman Received on Thu Sep 26 2002 - 02:57:05 CDT