Re: next extent allocation hangs -- need help

Sorry, I failed to mention -- this database is over 400GB and the tablespace in question is about 180GB, making it more difficult to expor/import.

"Ric Sullivan" <rsullivan_at_austin.rr.com> wrote in message news:hswc9.385920$q53.12821418_at_twister.austin.rr.com...
> Thanks Mark. We're looking into that option now. This DB opertates under
> high availability 24x7 requirements (300,000 Users) and we are looking at
> the DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL package as a temporary
 fix
> unitl we can get the tablspaces exported/imported in about 3 weeks.
>
> It seems the use of the bitmap verus the the FET$ table in the C_TS#
 cluster
> is much more appropriate for tracking free extents given the way it
 appears
> that Oracle queries for free extents. What we're concerned about are the
> reports from people who say they have had trouble exporting/importing
 after
> having migrated thei tablespaces -- as well as reports that the data
> dictionary is left with a less-than-accuarate picture of the tablespace
> parameters after migration.
>
> Have you ever used this package or have any thoughts on its use?
>
> Thanks very much for replying.
>
> Ric
>
> "Mark D Powell" <mark.powell_at_eds.com> wrote in message
> news:178d2795.0209011332.2981a7da_at_posting.google.com...
> > "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 - 17:57:30 CDT