next extent allocation hangs -- need help

From: Ric Sullivan <rsullivan_at_austin.rr.com>
Date: Sat, 31 Aug 2002 15:22:25 GMT
Message-ID: <Ra5c9.380283$q53.12576565@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; Received on Sat Aug 31 2002 - 10:22:25 CDT