Re: Cluster growing

raouldekezel_at_hotmail.com (Raoul De Kezel) writes:

1. Question. What was Oracle version when you created this cluster? 7.3.4 or you have made upgrade
2. Advice. 2.1 rebuild your cluster 2.2 don't use it at all

We used to have a huge cluster HIST_CL ;-) it was made of 15 tables. First it started to grow. Oracle gave as TAR322 but didn't help at all (they tell we have to increase block size :-( from 2K )

We made an upgrade but it didn't help

We rebuild this cluster by creating another cluster and copying data table by table (we usually are 24x7 we had to invent a special technique)

We also had problem with another cluster his cluster index begin to corrupt slowly (we begin to get 'NOT FOUND' when looking up by this index) so we split this cluster. Some things become to work slower and we had to reprogram some queries

> Dear very wise sirs, I have a big problem and would
> be extremely grateful if you could help me.
>
> We have a cluster with two clustered tables, as described
> below. To our big surprise, despite daily purges, on site
> databases grow slowly but continuously.
>
> In one of these databases, the cluster contains approximately
> 58000 data blocs. Of these, only 20000 contains rows.
> The others are fully empty.
>
> I performed massive inserts and updates (no deletes)
> in a single session. These statements consumed
> 10000 new blocks. To my surprise, Oracle added an extent.
> Actually, 1765 blocks were added instead of being reused.
>
> It would be very very helpful to me if
>
> - Somebody could point me to why this happens and
> how I can ensure that all empty blocks are reused before a
> new extent is allocated.
>
> - Somebody could explain or give a reference to
> some text which explains precisely how Oracle finds
> free space when it has to insert a new row in a clustered
> table.
>
> Thanks very much in advance
>
> --- Raoul
>
> Database is Personal oracle 7.3.4, block size is
> 8K, default PCTUSED is 40, tablespace HIST_TABLE has
> initial extent = next extent = 10240K, percent
> increase 0. Cluster and tables (names changed)
> are :
>
> CREATE CLUSTER TRCluster (R NUMBER) SIZE 16384
> TABLESPACE HIST_TABLE INITRANS 3 STORAGE
> (FREELISTS 4) PCTFREE 20
> ;
>
> CREATE INDEX TRCluster ON CLUSTER TRCluster
> TABLESPACE HIST_INDEX
> ;
>
> CREATE TABLE TR
> (
> R NUMBER NOT NULL,
> ...
> )
> CLUSTER TRCluster(R)
> ;
>
>
> CREATE TABLE ATR
> (
> R NUMBER NOT NULL DEFAULT 1,
> )
> CLUSTER TRCluster(R)
> ;
>
>

--
Sincerely Yours, Konstantin Kivi, Russia, konst_at_sirena2000.ru Received on Thu Nov 11 1999 - 07:09:46 CST