RE: Index rebuilds and TEMP segments..

This confirms my suspicions on what is really happening.. I had a bit of a twist on it, but pretty close. ;)

However, this still doesn't explain my problem.. My problem is that my largest free extent available is almost twice as large as the supplied initial and next and several times larger than the tbsp. defaults.

I'm gonna dig a bit further as I believe my developers are probably giving me inaccurate information. (Yes! it does happen!) ;) They've told me their storage clause and I found it in the SQL cache. However, I'm now suspicious that maybe they gave me a script that is being run successfully elsewhere and the one I'm hunting for is much different. (As I said, they do it in several places.)

As for fragmentation, it is fragmented, not as bad as I've seen but still fragmented. I wish I could solve this one but I can't convince them that 100+ extents aren't causing them as much of a performance loss as they think they're gaining with 2-3 extents. It's political and I get very little leeway. Coming from the same group that WERE putting the migration routines with the application owners and WERE accessing objects in the same schema (& DB) through DB links! The first making it an entire day project to refresh a 1000 table schema, without destroying the migration routines and the latter causing slow down on top of really bizarre errors that had nothing to do with the real problem! Only took me 1.5 years to convince them to try this my way. (refreshes from 7-8 hours down to 30minutes, and errors that can be solved in minutes rather than trial and error!)

Thanks for the help, if I find out more I'll post it.

SMF 8)
> -----Original Message-----
> From: Sarnowski, Chris [mailto:csarnows_at_CuraGen.com]
> Sent: Thursday, November 09, 2000 3:19 PM
> To: 'Shawn Ferris'
> Cc: 'oracledba_at_lazydba.com'
> Subject: RE: Index rebuilds and TEMP segments..
>
>
>
>
> When you rebuild an index, it uses the same storage parameters
> as the original index, unless you override them with a new storage
> clause (which you can certainly do).
>
> In 8.1.5, it looks like Oracle sorts all of the
> data in the temporary tablespace, then starts writing
> temporary extents to the target tablespace, then converts
> them to index extents, changes the name, and drops the
> old index (In 7.3, IIRC, it wrote the first 'permanent' extent
> before the sort was finished).
>
> So you need room for the entire index in the temporary tablespace,
> and room for the entire new index in the target tablespace, as
> measured before the old index is gone. But after the rebuild, the
> space in the temporary tablespace and the space where the old index
> was will be freed for use (but if the old index has multiple extents,
> they will not be coalesced automatically, unless your default
> pctincrease is >0, which I would discourage strongly).
>
> How uniform are your extent sizes? Maybe your 1.5GB is too
> fragmented.
>
> select blocks, count(blocks) from dba_free_space
> where tablespace_name = 'DI_I'
> group by blocks
> order by blocks;
>
> might be a useful query for you.
>
>
> > -----Original Message-----
> > From: Shawn Ferris [mailto:Shawn.Ferris_at_twtelecom.com]
> > Sent: Thursday, November 09, 2000 3:17 PM
> > To: LazyDBA (E-mail); 'ORACLE-L_at_fatcity.com'
> > Subject: Index rebuilds and TEMP segments..
> > Importance: High
> >
> >
> > What am I missing:
> >
> > During a migration cycle, transforming legacy db's into the
> > new app, the
> > developers are rebuilding their indexes at strategic
> > locations. (They don't
> > actually check whether it's necessary, they just do.)
> During the index
> > rebuild, I believe they issue 'alter index ... rebuild ...',
> > we get the
> > following error:
> >
> > ORA-1652: unable to extend temp segment by 3200 in tablespace
> > DI_I
> >
> > DI_I is the tablespace that the indexes belong in. We've
> > watched the temp
> > segments come and go, in this tbsp. We watch the space as
> the rebuild
> > happens and at minimum there is 1.5gb free. I'm assuming the
> > storage for
> > these segments are taken from the tbsp default and there is
> > no way to alter
> > that behavior. The largest extent size is greater than the
> > tbsp's default
> > initial and next. (There is plenty of room for growth from
> > what I can tell.)
> >
> > (I'm completely guessing here.. Please correct me
> everywhere that I am
> > wrong.) My suspicion is that as they rebuild these indexes,
> > each time they
> > allocate new extents. Possibly these extents are marked for
> > data use only
> > and not useable for temp segments later. Each time they
> > rebuild, they use up
> > all the space available to allocate for temp segments..
> >
> > If this is true, how do you alleviate this problem, given
> > that I don't have
> > any control over the code. (third party) My thought was to
> > try and alternate
> > between 2 tbsps, coalescing the last tbsp after all the
> > objects move to the
> > new? (Which I would assume would mark them as useable space
> > for any type
> > segment.)
> >
> > Any help will be greatly appreciated!
> >
> > Shawn M Ferris
> > Oracle DBA - Time Warner Telecom
> >
> > --------
> > Think you know someone who can answer the above question?
> > Forward it to them!
> > to unsubscribe, send a blank email to
> > oracledba-unsubscribe_at_LAZYDBA.com
> > to subscribe send a blank email to oracledba-subscribe_at_LAZYDBA.com
Received on Fri Nov 10 2000 - 10:45:13 CST