Re: Temporary tablespace and uniform extents

On May 14, 2:00 pm, Mladen Gogala <mgog..._at_yahoo.com> wrote:
> On Wed, 14 May 2008 18:24:52 +0100, Jonathan Lewis wrote:
> >> extent of the temporary tablespace was SORT_AREA_SIZE + 2 blocks of
> >> overhead. What do we do now?
>
> > As so often happens, common wisdom wasn't well-informed. The main reason
> > for trying to be clever about extent sizing when all you had in the temp
> > tablespace was sorts was to avoid excessive overheads on allocation and
> > deallocation of segments and their extents.  
>
> Yes, this went away when the instances started having only a single sort
> segment.
>
> > (But, except for odd cases,
> > this was probably a small fraction of the cost of using the space
> > anyway).
>
> > The sort_area_size + 2, or sometimes sort_area_size * 2 +1 types of
> > formula were all second guessing (incorrectly) the way Oracle used the
> > memory in sort_area_size.
>
> That is also true, I've never been able to actually figure the correct
> sizing in Oracle 8i.
>
>
>
> > Now, with only one real segment being maintained by the instance, and
> > extents begin held after use, there's no big worry about the overheads
> > of allocating and de-allocating. Given, though, that you use the
> > temporary tablespace for sorts, hashes, temporary tables and their
> > indexes, and temporary LOBs, there is an argument for worrying about how
> > many chunks you might need to have active at a time.
>
> Yes, that was the reason for trying to have autoallocate. I was prompted
> by Tom's question about index creation. It occurred to me that it would
> be very nice if the user who needs a lot of space could have large extents
> while the users trying to do "GROUP BY" on a small table (~10MB) would use
> small chunks. I wanted to advise Tom to re-create temporary tablespace as
> an "autoallocate" one, but decided to try it first. Alas, it didn't work.
>
>
>
> > Sizing is required to be uniform, and the 1Mb is a good general purpose
> > compromise between small sorts, tables and indexes when compared to
> > LOBs.  But if all you use lots of very smal temp tables you might drop
> > to (say) 128Kb, and if all you use is "large" temp LOBs and sorts you
> > might bump the unit size up to 4Mb or 8Mb (say) - but in general there's
> > no great need to worry too much.
>
> Oh, well. My idea did not work. You, however, did teach me something.
> Thanks.
>
> --http://mgogala.freehostia.com

And found from following a link in Jonathan's blog: http://oracle-randolf.blogspot.com/2008/02/nasty-bug-introduced-with-patch-set.html if you try doing it manually because you know you have a "special" sort, you hit bugs. Those sorts of things always frighten me when there's automatic things performing the same actions, not knowing if they are related inside the black box, but having to assume they are - and you can't even know if the stuff you can see is truthful.

Very educational day today.

jg

--
@home.com is bogus.
No prior restraint on internet defamation.
http://www.signonsandiego.com/uniontrib/20080515/news_1m15appeal.html