Re: Use of TEMP tablespace other than sorting

Comments in-line.

--
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:v67q2870o93cfd_at_corp.supernews.com...


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


>


        I heartily approve of that approach - I wish more
        people would adopt it.



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

>


        I would GUESS that this is because with a small extent size
        whilst using a permanent tablespace for temp you hit the
        maxextents error - rather than an extreme space demand.



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


>


        Not many people have the luxury of finding out
        WHY something works - once they've got it working
        the next problem has to be addressed.

        I might be a little friendlier to use terms like "this
happened
        to work for me" rather than "do this to fix your problem".
        The former type of thing easily becomes the seed of a myth,
        and at the least might waste a lot of someone else's time if
        they follow your advice without knowing your circumstances.