Re: Use of TEMP tablespace other than sorting

There used to be a bug whereby if the
hash_multiblock_io_count exceeded
the extent size on the temp tablespace
then any hash join that overflowed to
disc would bomb out with an error message that wasn't 100% clear.

Since the hash_multiblock_io_count is
affected by the value of hash_area_size, and hash_area_size defaults to twice
the sort_area_size, this would certainly help to give the impression that the temp extent size had to be larger than the
sort_area_size.

Perhaps something similar was occurring
with the sort multiblock io count (whose name I can't remember at the moment) - or maybe it was a bug with LMTs that if too many extents had to be allocated simultaneously by the same process the code hit a counting or
flagging problem. The suggestion (perhaps my incorrect inference) that it was just the one specific query does make it look like a "boundary condition" problem.

Experiences are definitely worth sharing - but I do like the <caveat> tag - perhaps we could have it added to the HTML standard with a default format of 'big red underlined'.

--
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


"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in
message news:3e647ac0$0$226$ed9e5944_at_reading.news.pipex.net...


>

> <caveat>

> Like Anurag the below experience was just that experience, it did

however


> happen on 8.1.7.0. on 2000 and 8.1.6.x (where x is either 1 or 3)

also on


> 2000. It is not verifiably tested

> </caveat>

>

> We had a problem query that gave the self same error message with a

temp


> tablespace that was locally managed and with a uniform extent size

that was


> lower than the sort_area_size. When sorting to disk (which happened

every


> time on a small resultset because the query sorted by no fewer than

12


> columns!) we got an error that Oracle couldn't allocate

sort_area_size in


> blocks. This happened consistently, and with a huge temp tablespace

and on a


> test system with only the problem query running. making sure that

temp


> allocated extents that were multiples of sort_area_size eliminated

the


> problem. I have never seen the message on 9i or 8.1.7.4.

>

> I have also seen a similar message with a hash join but that was

only on two


> occasions so perhaps I have assumed unwisely that the behaviour was

similar.


>