| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: next extent allocation hangs -- need help -- Update with solution
We just tested DBMS_SPACE_ADMIN.TABLESPACE_MIGARATE_TO_LOCAL on a clone of our production database that we verified suffered from the same latency during extent allocation -- 7 minutes for an index in the affected tablespace to allocate a next extent.
Interesting that subsequent extent allocations of the same or any index in the same tablespace was 2 seconds. We found that after inital DB startup the latency returned to 7 minutes for the initial allocation in that tablespace, with subsequents at 2 sec. The behavior we've seen on production is that after one of these 7 minute "hanging" eposiodes, extents can be allocated at about 2 secs. until so much time passes and then another 7 minute episode. We wonder if the free extent data must be caching out.
Anyway -- to the point: In our test on the clone database, migrating to LOCAL while to 1 minute and 8 sec. (the tablespace size is 171GB comprised of 21 datafiles separated and striped across sets of 8 disks on an EMC Symmetrix 8730). After the migration, new extent allocation within the affected tablespace took only .80 seconds! Quite an improvement from 7 minutes!
Our only concern now is getting to the bottom of the reports one sees on the web and MetaLink that folks have export errors after having used the migration routine. Other folks have found that the data dictionary does not get updated properly and have found that migrating to local, migrating back to dictionary and then forward once again to local seems to be the work-around.
Anyone heard of this?
Thanks very much to Mark and Connor for responding to this group and my emails. You helped me greatly not only with this problem, but many others to come with the incredible content on your websites.
"Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message
news:3D73C688.51E0_at_yahoo.com...
> Ric Sullivan wrote:
> >
> > 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 --
> > >
> > >
>
>
>
 |
 |