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

Ric,

There's an article (somewhat elderly by now) on my website about SMON which might be worth a read, as it may give you a couple of hints for damage limitation.

Presumably most of the 18,000 free extents are in the on tablespace, and possibly in one file in that tablespace. (select ts#, count(*) from fet$ group by ts#) or
(select ts#, file#, count(*) ....)

Are there any options for any of the free space to be coalesced ? If not, you might experiment with setting the NEXTEXTENT on all objects to match the commonest size of the free extents in the tablespace - this should stop Oracle from spending time trying to coalesce when unable to allocate.

Can you post the recursive SQL (and execution plan or trace) that is taking the most time to complete ?

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


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

>