Temporary tablespace and uniform extents
Mladen Gogala
existed a link between SORT_AREA_SIZE and the size of uniform
extent in the default temporary tablespace. Then came the automatic
memory management, first in 9i, then in 10g and, the latest iteration,
memory management in 11g. Temporary tablespace and creation scripts
from DBCA, however, still live in 8i times. Here is the creation script
from my 11g:
CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE
'/oracle/oradata/test11/temp01.dbf' SIZE 188743680
AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;
I tried creating an "autoallocate" version but it failed:
SQL> create temporary tablespace temp1
2 tempfile '/oracle/oradata/test11/temp_test.dbf' size 1024M
3 extent management local autoallocate;
extent management local autoallocate
*
ERROR at line 3:
ORA-25139: invalid option for CREATE TEMPORARY TABLESPACE
Now, how can I determine an optimal extent size in a situation
where SORT_AREA_SIZE is dynamic? I only have PGA_AGGREGATE_TARGET,
no SORT_AREA_SIZE any more. Extents in the temporary tablespace are
still fixed length. Once upon a time, common wisdom for sizing the
extent of the temporary tablespace was SORT_AREA_SIZE + 2 blocks of
overhead. What do we do now?
--
Mladen Gogala
http://mgogala.freehostia.com
Charles Hooper
The Oracle Performance Tuning Guide for 10g R2 and 11g R1 seem to
indicate that Oracle favors 1MB extent sizes, likely as this matches
the recommended (tested to be best performance) stripe width for
Oracle's SAME configuration. From:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/build_db.htm
"Properly configuring the temporary tablespace helps optimize disk
sort performance. Temporary tablespaces can be dictionary-managed or
locally managed. Oracle recommends the use of locally managed
temporary tablespaces with a UNIFORM extent size of 1 MB.
You should monitor temporary tablespace activity to check how many
extents are being allocated for the temporary segment. If an
application extensively uses temporary tables, as in a situation when
many users are concurrently using temporary tables, the extent size
could be set smaller, such as 256K, because every usage requires at
least one extent. The EXTENT MANAGEMENT LOCAL clause is optional for
temporary tablespaces because all temporary tablespaces are created
with locally managed extents of a uniform size. The default for SIZE
is 1M."
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Jonathan Lewis
news:482af029$0$30633$834e...@reader.greatnowhere.com...
> Once upon a time, common wisdom
> for sizing the
> 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. (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.
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.
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.
--
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Mladen Gogala
>> 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.
joel garry
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
Mladen Gogala
Wow! Thanks, Joel! I didn't know about this one.