Re: Enable 32K Block in 8K Block DB

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 31 Mar 2004 08:43:39 +0000 (UTC)
Message-ID: <c4e0br$mda$1@titan.btinternet.com>

The difference is only likely to be a couple of percent of CPU at max.

I don't have a convenient one at hand, but it's easy to build. Something like the following should do.

9.2, LMT, not ASSM

The values in-line are for the example in a 2K blocksize tablespace, the values after the -- are for a16K blocksize tablespace.

Just check the block dumps to make sure
that the 'colliding values' have actually hit the same block as the other id=1 rows.

create cluster c1 (

    hash_col number(10)
)
single table

hashkeys 15                -- 101

size 128
hash is hash_col
tablespace test_2k -- test_16k
;

create table t1 (

    id number(10) not null,
    descr varchar2(80)
)
cluster c1(id)
;

insert into t1
select rownum, rpad('x',80)
from all_objects where rownum <= 14 -- 100 ;

insert into t1 values (1,'Colliding value');

commit;

rem check CPU usage

declare

    v varchar2
begin

    for i in 1..1000 loop

        select descr into v from t1 where id = 2;     end loop;
end;
/

rem check CPU usage

-- 
Regards

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

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

April 2004 Iceland  http://www.index.is/oracleday.php
June  2004      UK - Optimising Oracle Seminar


"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
news:1080658919.261626_at_yasure...


> Jonathan Lewis wrote:

>

> > Note in-line.

> >

>

>  > I haven't read the paper - but if a lot of the tables use hashing

>  > instead of indexing, then a smaller block size could reduce the CPU

>  > cost of hash-collisions.

>

> Could I trouble you for a simple example demonstrating this?

>

> Thanks.

>

> -- 

> Daniel Morgan

> http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp

> http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp

> damorgan_at_x.washington.edu

> (replace 'x' with a 'u' to reply)

>

Received on Wed Mar 31 2004 - 02:43:39 CST