Re: What is a good blocksize to use.

You might also look at single-table index clusters (create cluster) in the SQL Ref manuals. Cluster the data by client id. Make the cluster size default to the whole Oracle block.

IOT's need a little more thought regarding what goes into the overflow segment.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______September 24/26, November 12/14

____USA__________November 7/9 (MI), 19/21 (TX)

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html








Paul Brewer wrote in message
<3d937a3e_1_at_mk-nntp-1.news.uk.worldonline.com>...


>"Ben Brugman" <benbrugman_at_onbekend.nl> wrote in message

>news:3d92b907.462890_at_news.nl.uu.net...

>>

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

>

>

>