Re: What is a good blocksize to use.

On Fri, 27 Sep 2002 08:20:59 +0100, "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

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

We did look into clustering, but the spread of data is very skewed. about 10 of the clients are responsible for 80-90 percent of the data. So most clients only have a few rows. About 10 percent of the clients have about a few hundred rows. Only a very few clients have thousands of rows.

Using clustering and estimating the average size of a cluster (number of clients in the cluster) probably would not work because at a cluster factor of 10 clients per cluster, a lot of clusters would be empty or almost empty, and a lot of clusters would have a lot over overflow because those clusters contain a very large client or several large clients.

We did not try this, because of the above assumptions. If there is something wrong with the above reasoning, please inform me.

ben

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

Ben Brugman Received on Fri Sep 27 2002 - 03:07:00 CDT