Path: news.easynews.com!newsfeed1.easynews.com!newsfeed2.easynews.com!easynews.com!easynews!newsfeed.news2me.com!newsfeed.icl.net!newsfeed.fjserv.net!kibo.news.demon.net!news.demon.co.uk!demon!not-for-mail From: "Jonathan Lewis" Newsgroups: comp.databases.oracle.server Subject: Re: Use of TEMP tablespace other than sorting Date: Mon, 3 Mar 2003 09:27:24 -0000 Lines: 103 Message-ID: References: NNTP-Posting-Host: jlcomp.demon.co.uk X-Trace: news.demon.co.uk 1046683711 20184 158.152.75.41 (3 Mar 2003 09:28:31 GMT) X-Complaints-To: abuse@demon.net NNTP-Posting-Date: Mon, 3 Mar 2003 09:28:31 +0000 (UTC) X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Priority: 3 X-Newsreader: Microsoft Outlook Express 5.50.4522.1200 X-MSMail-Priority: Normal Xref: newsfeed1.easynews.com comp.databases.oracle.server:178229 X-Received-Date: Mon, 03 Mar 2003 02:28:25 MST (news.easynews.com) 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" wrote in message news:v660f8jlmv0q83@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@germanynet.de> wrote in message news:j36t5vcqtc14rrm8spiof4v93vijh0q6sd@4ax.com... > > "Anurag Varma" wrote: > > > > >Rick, > > > > > >What do the following queries say: > > > > > >select tablespace_name, contents, extent_management, initial_extent, allocation_type > > >from dba_tablespaces where tablespace_name = ''; > > > > 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 > >