Re: Temporary tablespace and uniform extents
Date: 14 May 2008 21:00:27 GMT
Message-ID: <482b52eb$0$30640$834e42db@reader.greatnowhere.com>
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.comReceived on Wed May 14 2008 - 16:00:27 CDT