Re: next extent allocation hangs -- need help

"Ric Sullivan" <rsullivan_at_austin.rr.com> wrote in message news:<Ra5c9.380283$q53.12576565_at_twister.austin.rr.com>...
> AIX 4.3.3, Oracle 8.1.7
>
> After dropping and rebuilding 3 large indexes we found that subesquent
> extent allocation into the tablespace of these indexes now can take up to 6
> minutes in some cases -- causing inserts into the indexed tables to hang.
>
> Oracle support has been unable to assist and we need to see if there is a
> solution short of totally re-building the database (export/import).
>
> We traced the processes that cause this latency and they always appear to be
> holding a share lock on the SYS.TS$ table -- part of the SYS.C_TS# cluster
> involving SYS.FET$ as well.
>
> The TS$ table has only 50 rows in it. The FET$ table has only 18456 rows.
> A query we found run that component queries of the DBA_FREE_SPACE_COALESCED
> view can take up to 6 minutes to run on this database, while on other
> databases it returns almost instantaneously.
>
> Below is a sample of one such query. Any advice would be greatly
> appreciated.
>
> select ts#, count(*) extents_coalesced, sum(length) blocks_coalesced
> from rams.fet$_test a
> where not exists (
> select * from rams.fet$_test b
> where b.ts#=a.ts# and
> b.file#=a.file# and
> a.block#=b.block#+b.length)
> group by ts#
>
> We tried building and populating a test model of the C_TS# cluster with
> larger SIZE and storage parameters to see if the problem is just due to a
> badly designed cluster, but the performance is only a little better.
>
>
> CREATE CLUSTER test.c_ts#_test(ts# NUMBER)
> SIZE 32256
> STORAGE
> (INITIAL 1720320
> NEXT 1720320
> PCTINCREASE 0
> MINEXTENTS 2
> MAXEXTENTS unlimited)
> PCTFREE 10
> PCTUSED 70
> INITRANS 10
> MAXTRANS 255;
Ric, I take it that this tablespace is Dictionary managed. Instead of rebuilding the entire database give some thought to just rebuilding the one tablespace as Locally managed probably using uniform extents. This should avoid the need to access the cluser to find and mark space as used for these objects.

HTH -- Mark D Powell -- Received on Sun Sep 01 2002 - 16:32:53 CDT