Temporary tablespace and uniform extents

[Mladen Gogala]

Once upon a time, in an Oracle version far, far away, there
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?

[Charles Hooper]

Post by Mladen Gogala
Once upon a time, in an Oracle version far, far away, there
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
 CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE
  '/oracle/oradata/test11/temp01.dbf' SIZE 188743680
  AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;
SQL> create temporary tablespace temp1
  2  tempfile '/oracle/oradata/test11/temp_test.dbf' size 1024M
  3  extent management local autoallocate;
extent management local autoallocate
                        *
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 Gogalahttp://mgogala.freehostia.com

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]

Post by Mladen Gogala
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]

Post by 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]

Post by Mladen Gogala

Post by 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.
--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

[Mladen Gogala]

Post by joel garry

Post by Mladen Gogala

Post by 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.
--http://mgogala.freehostia.com

http://oracle-randolf.blogspot.com/2008/02/nasty-bug-introduced-with-

patch-set.html

Wow! Thanks, Joel! I didn't know about this one.