Re: Use of TEMP tablespace other than sorting

The original poster says that after allocating 48 GIGAbytes of temp storage, he is unable to allocate an extent in TEMP for a particular six-table query against some reasonably large tables.

TEMP is a locally managed tablespace with extent sizes of 4MB. - so the maxextents is unlimited - and the extent count is currently about 1,200. We have been told that the sort_area_size = 4M, which means the default hash_area_size is 8M. We have no information about the hash_multiblock_io_count.

Can you explain why increasing the extent size and decreasing the hash_area_size will make any difference ? (Apart from the fact that decreasing the hash_area_size MIGHT just be enough to push Oracle from doing a hash join to using a different join mechanism).

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____UK_______April 8th
____UK_______April 22nd

____USA_(FL)_May 2nd


Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Anurag Varma" <avdbi_at_hotmail.com> wrote in message
news:v660f8jlmv0q83_at_corp.supernews.com...


> Decrease value of hash_area_size to 4M or drop recreate the temp

tablespace with uniform extent of at least 8M (or 8 * n M).


> I believe thats where the problem lies.

>

> Anurag

>

>

> "Rick Denoire" <100.17706_at_germanynet.de> wrote in message

news:j36t5vcqtc14rrm8spiof4v93vijh0q6sd_at_4ax.com...


> > "Anurag Varma" <avdbi_at_hotmail.com> wrote:

> >

> > >Rick,

> > >

> > >What do the following queries say:

> > >

> > >select tablespace_name, contents, extent_management,

initial_extent, allocation_type


> > >from dba_tablespaces where tablespace_name =

'<yourtablespacenamehere>';


> >

> > From my head: TEMP, locally managed, 4 MB, uniform

> >

> > >and

> > >

> > >select name, value from v$parameter where name in

('sort_area_size', 'hash_area_size');


> >

> > That I will have to query tomorrow at office.

> >

> > >hash operations also use temp tablespace. However, I have seen

this happen primarily in places where


> > >the so called temporary tablespace is defined with contents

"permanent" i.e. not exactly a temporary tablespace.


> > >Happens when hash_area_size is much bigger than the uniform

extent size of the temp tablespace.


> >

> > TEMP is temporary, not permanent. Your hint with hash_area_size

means


> > that I would need to diminish its value, right?

> >

> > Thanks

> >

> > Rick Denoire

>

>