Re: Use of TEMP tablespace other than sorting

Jonathan,

Actually what you say makes perfect sense. It could very well have run into maxextents problem.

Thanks,
Anurag

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:b41p3p$2oc$1$8302bc10_at_news.demon.co.uk...
>
> 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.
>
>
>
>
Received on Tue Mar 04 2003 - 21:28:26 CST