Re: temp extent size

Maxim Demenko wrote:
> joel garry schrieb:
> > viny wrote:
> >> My sort area size is 8MB and db_block_size is 8KB
> >>
>
> >
> > So of course, if you use uniform, that would imply the initial. I
> > would set it much higher, dependent on how much data you intend to be
> > putting through temp. You should also figure out what goes into temp,
> > while you are at it (it's in the docs). Somewhere is told the limited
> > number of segment sizes for LMT.
> >
> > Oddly enough, looking at some script (hacked from an old wizard,
> > probably) that created my 9.2 db, it has this clause in the create
> > database statement:
> >
> > ....
> > EXTENT MANAGEMENT LOCAL
> > DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/oradata/xxxx/temp01.dbf'
> > SIZE 40M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE 10000M
> > ....
> >
> > But of course, since it is LMT, it's 1M:
>
> >
> > jg
>
> Joel, this NEXT is part of AUTOEXTEND ON clause and specify the size of
> tempfile increment ( which is nevertheless not very smart specified with
> 640K while to allocate next extent tempfile should be extended sometimes
> twice which can be probably optimized ;-)).
>

I think this may have come from an 8i dbca (though I don't think extent management local was there, really don't remember), or might have been 9iR2 :-O It reached it's maxsize long ago. I'm pretty sure I changed the sizes manually at some early point, and probably just ignored next since I knew LMT would deal with it however it wanted. If anybody cares, they can run dbca and see what it does. I was just pointing out to the OP that next makes no sense in O10, so will be ignored. You are right, I missed that it was part of the autoextend clause, and it should be large to get some nice contiguous unix file sectors. It's likely fragmented since soon after db creation. Now that unix on this storage has been running for years, it's likely that under the covers there's sector fragmentation so rebuilding the tempfile wouldn't gain anything, and there's no simple way to figure out if that is a true statement.

  1* select tablespace_name, increment_by from dba_temp_files SQL> /

TABLESPACE_NAME                INCREMENT_BY
------------------------------ ------------
TEMP                                     80

Looking at metalink Notes:98966.1, 155910.1, it's just the number of db blocks (80*8K is 640K).

This is one of those things that causes no overt performance issues, yet one would expect it (preextending tempfile with large segments on a new filesystem, vs. small scattered segments) to have some influence. Which tuning methodology would catch it? And with a RAID system with many spindles and modern cache, would it make a difference?

Good eye, Maxim!

jg

--
@home.com is bogus.
Yumpin' Yiminy, vere's der virus protektion?
http://news.zdnet.co.uk/security/0,1000000189,39285547,00.htm