Re: next extent allocation hangs -- need help

"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 --

Ric, I haven't used the dbms_space_admin package, but I have seen several posts on metalink by people who did or wanted to and it seems to work fine. (It seems people seem to expect the package to reformat the existing object extents when it just creates the bitmap)

The only dictionary information problem I am aware of is that dba_Tables/indexes show the initial extent to be what was requested on the create rather than the physical allocation used in the tablespace.  If your test db is still dictionary managed and is only a fraction of production this can be a plus. But it does mean that you cannot just use the dba_table/index numbers as is for certain functions.

HTH -- Mark D Powell -- Received on Tue Sep 03 2002 - 08:45:32 CDT