Re: Compute_statistics PROBLEM

"DA Morgan" <damorgan_at_exesolutions.com> wrote in message [snip]
> Go to http://tahiti.oracle.com and you will find documents on how to
calculate
> the size of your TEMP tablespace. Also remember that while most people
think in
> terms of only one TEMP tablespace ... that is not necessarily an optimal
> design. Consider whether more than one might be a better solution.

That advice is a bit old now, I think, Daniel. 9i's wonderful new feature of a default temporary tablespace has one horrendous side-effect.

Let's say you create a tablespace TEMP1, and make that your database's default temporary tablespace (ie, the one a user gets by default if you forget to mention anything else).

Now say you issue these commands:
create user howard identified by blah;
create user daniel identified by blah temporary tablespace TEMP2;

(Assuming of course a second temporary tablespace exists, such as you're suggesting).

Now the point is that with two temporary tablespaces, you'd want about half your users swapping down to one, and half to the other. So, for half your users, you would want to override the 'default'. In this case, you get a specific assignment to TEMP2, I pick up the default to TEMP1.

Fine.

Now, one day, blue smoke starts appearing from the disk cabinet containing the disk on which TEMP1 is stored. So you want to do some maintenance work, maybe move files or what have you. Now you can't offline the default temporary tablespace. So you have to first (temporarily) make TEMP2 the default. Then you can offline TEMP1 and do whatever maintenance work you feel the need to do.

Alter database default temporary tablespace TEMP2;

Works fine. It happens to make my temporary tablespace TEMP2, but that's to be expected, and is only temporary.

Now the maintenance work is finished. So you alter database default temporary tablespace TEMP1.

I get switched back to using TEMP1 as my temporary tablespace.... and (here's the bummer) so do you. In fact, any user that was set to use the default temporary tablespace when the default is changed gets swept up into the new one. Regardless of the fact that you set a specific temporary tablespace for a particular user when you created them.

Which means that in 9i, whilst in theory it's still nice to have lots of temporary tablespaces, and to arrange for different groups of users to swap to different ones, there's an inherent tendency (or risk, shall we say) that everyone will end up using the same one, despite your best efforts.

It's got to the point where I now advise one temporary tablespace for the entire database, that just happens to be comprised of multiple tempfiles spread across multiple disks. That way, you spread your I/O, and Oracle can do its worst, and no real harm is done.

Bloody new versions!!

Regards
HJR Received on Fri Jan 24 2003 - 06:02:50 CST