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

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 sideeffect  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.