Re: Use of TEMP tablespace other than sorting

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<b3v77u$jmo$1$8300dec7_at_news.demon.co.uk>... ...
>
> Can you explain why increasing the extent size
> and decreasing the hash_area_size will make
> any difference ?

I too would like to ask this question. Hash_area_size shouldn't make any difference at all. Obviously, the reason a join can blow your temp tablespace is that the result set from the join is larger than the temp tablespace. This happens when you suddenly add a large number of rows in some of your tables or, more often, the cardinality of the join column is now different because someone updated the column incorrectly. I suspect it's the latter, especially since this query has been working with a much smaller temp tablespace. To get an idea on how bad this can be, if a column used to join two tables has 50000 duplicated rows in each table, just from these duplicates you end up with 50,000x50,000=2.5 billion rows!

So I would suggest that the OP look at the data first.

> (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 - 13:33:15 CST