Re: Temporary tablespace and uniform extents
From: Mladen Gogala <mgogala_at_yahoo.com>
Date: 16 May 2008 06:44:59 GMT
Message-ID: <482d2d6b$0$30633$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.com
Date: 16 May 2008 06:44:59 GMT
Message-ID: <482d2d6b$0$30633$834e42db@reader.greatnowhere.com>
On Thu, 15 May 2008 15:31:47 -0700, joel garry wrote:
> 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
Wow! Thanks, Joel! I didn't know about this one.
-- Mladen Gogala http://mgogala.freehostia.comReceived on Fri May 16 2008 - 01:44:59 CDT