Re: What is a good blocksize to use.

"Ben Brugman" <benbrugman_at_onbekend.nl> wrote in message news:3d92b907.462890_at_news.nl.uu.net...
>
> >
> >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

Ben,

For the Oracle implementation, I'd suggest:

1)    Forget about IOTs. Just put an index on the client_id.
2)   Analyze your schema, with compute statistics.
3)   Forget about block size, for the time being at least.

Hope this helps.
Paul Received on Thu Sep 26 2002 - 16:05:03 CDT