Re: Use of TEMP tablespace other than sorting

Jonathan,

I was going to write an explanation on why I think it will make a difference.... Then I decided to do a test and was not able to reproduce this error.

But to my defense, I did see the same error on a 8.1.7 database a while back. At that time I realized that the temp tablespace was actually permanent with initial and next extent very small (64K or something). When I dropped the tablespace and recreated a temp tablespace (LMT uniform allocation 64M), the error went away (as far as I remember - same execution plan, same temp tablespace size). In fact in LMT's temp tablespaces, I have never seen this error.

In my case also the query was a huge one, but there was enough space in the temp tablespace (>2G free). Also, the database in question was a data warehouse. and the hash_area_size is 4M. We did not set hash_area_multiblock_io_count The block size is set to 8K.

However, I might totally be offtrack out here since I never did do any detailed testing/debugging.

Anurag

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:b3v77u$jmo$1$8300dec7_at_news.demon.co.uk...
>
> 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
> >
> >
>
>
Received on Mon Mar 03 2003 - 17:41:57 CST