Re: Performance Brainstorming

From: Nuno Souto <nsouto_at_optushome.com.au.nospam>
Date: Thu, 28 Nov 2002 22:00:55 +1100
Message-ID: <3de5fa29$0$2764$afc38c87@news.optusnet.com.au>

27 Nov 2002 09:01:52 -0800, David Osborne said (and I quote):

What block size do you have?

>
> Other big_table info:
> PCTUSED 40
> PCTFREE 20
try 10 like you said?

> INITRANS 1
set this to >>1. Probably as many as you have db writers.

> MAXTRANS 255
> FREELISTS 16
drop this down to as many db writers.

> Size: The tablespace is LMT with 100M extents. The table is 3.6GB
> with 36 extents. Number of rows is 70,247,110.

Cool.

> There is a primary key on X_ID and ADATE with the index on a LMT with
> 100M extents. Info:
> NOLOGGING
> PCTFREE 10
> INITRANS 2
again, jack up. Or drop/load/rebuild.

> MAXTRANS 255
> PCTINCREASE 0
> FREELISTS 16
drop this.

> FREELIST GROUPS 1
And so on...

>
> Constraints:
>
>
> CONSTRAINT FK_BIG_TABLE FOREIGN KEY (X_ID) REFERENCES ISP (X_ID)
> ON DELETE CASCADE
Is ISP indexed on X_ID? Check that.

>
> Next thoughts:
> 1. Change primary key index to be PARALLEL 4,1.

Don't bother. That's not your problem, I'd say...

> 2. Change pctfree to be 10 on big_table.

Might help, but it won't be a silver bullet...

> 3. Drop indexes before loading and recreate them when complete?

Definitely. Check that ISP is indeed indexed so the constraint check is done as fast as possible. Or consider disabling the constraints if you reckon the quality of data is high.

Again, block size might be a problem. What is it?

-- 
Cheers
Nuno Souto
nsouto_at_optusnet.com.au.nospam

Received on Thu Nov 28 2002 - 05:00:55 CST