Re: next extent allocation hangs -- need help -- Update with solution

Thanks Jonathan, I very appreciate your advice. I'm not sure this is the case though, as 99.99n% of our sorts happen in memory and very few ever go to the TEMP tablespace.

The long times are only specific to new extent allocation (dynamic or manual) on indexes in the index tablespace that conatins so many free fragments. Segments in other tablespaces allocate almost instantaneously.

We modeled the C_TS# cluster in a test environment trying many mutations of storage and SIZE parameters and got very little lift with any of the variations. Only when we reduced the number of records in the FET$ table (in our model) were we able to get any lift -- using the queries Oracle uses when interrogating the table to pinpoint where to place the new extent. In the cluster, the TS$ table has only 40 records and the FET$ only 18,000. The query we identified as being used by Oracle runs for 7 minutes (the same duration as these hanging episodes) before returning a small number of rows. Only by reducing the number of records in this cluster were we able to gain any lift. There must be a maximum capacity of records that can reside in this cluster until it reaches a threshold at which this latency manifests.

In looking for the free fragement in which to place the new extent, Oracle uses the FET$ table twice in a WHERE NOT EXISTS clause whose subquery (also on the FET$ table) makes joins to the outer query. We haven't had time to test if it is the query itself or the use of an index cluster for this type of query that is inappropriate, but the bitmap in the LOCAL tablespace and/or perhaps the way it is queried sure does the trick.

Our conclusion: Having a lot of free fragments isn't the issue; The way Oracle stores record of and queries these free fragments in dictionary managed tablespaces that is the problem. Any query the database must perform that runs for 7 minutes processing only 18,000 records while holding a critical lock is just scary. Fortunately, the new bitmaps and perhaps the way they are queried has solved this.

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:al0kms$otg$1$830fa7b3_at_news.demon.co.uk...
> Ric,
>
> Given your mention of long times 'just after startup'.
>
> Remember that the temporary segment used by the
> instance is freed when the instance is shut down.
> and cleaned up when the instance restarts.
>
> If your temporary tablespace has a small allocation
> unit, then you may have a very large number of
> extents to be cleaned up, and this can take a long
> time. Is it possible that your problems are a side-
> effect of a badly defined temp tablespace.
>
> Ideally the TEMP should be a 'proper' temp
> create temporary tablespace ... tempfile ...
> with a suitable UNIFORM SIZE.
>
> If (in the worst case) you are using an ordinary
> permanent tablespace for your temp tablespace,
> then perhaps some reports are running up large
> sort areas in the tablespace, which are then immediately
> cleaned - taking the space transaction lock whilst
> it does so, and potential stopping all other space
> allocation calls for a while.
>
> You might also consider (even in the best case) the
> impact of 300,000 users - if too many of them are
> generating small temporary space allocations all
> the time, then you could be getting a serious
> piggy-back effect that really postpones the few
> highly critical allocations. You might run a report
> against v$session_event to check how regularly
> the active session have had to wait for the event
> 'enqueue'.
>
>
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Next Seminars
> UK Sept, Nov
> USA x 2 November
>
> http://www.jlcomp.demon.co.uk/seminar.html
>
>
>
>
>
>
>
> Ric Sullivan wrote in message ...
> >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.
>
>
>
Received on Mon Sep 02 2002 - 20:17:39 CDT