Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Compute_statistics PROBLEM

Re: Compute_statistics PROBLEM

From: DA Morgan <damorgan_at_exesolutions.com>
Date: Fri, 24 Jan 2003 12:37:33 -0800
Message-ID: <3E31A40D.231C0C7C@exesolutions.com>


Pete Sharman wrote:

> In article <UN9Y9.32158$jM5.82178_at_newsfeeds.bigpond.com>, "Howard says...
> >
> snip
> >
> >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
> >
>
> Much as I hate to disagree with you (because I know you you have more
> persistence with following up your viewpoint than I do with mine :), I don't see
> this being as big an issue as you make it, except in the situation where you
> have multiple applications using a single database that might therefore need
> different sorting characteristics.
>
> I'm seeing a lot of client sites having databases dedicated to particular
> applications. In that case, generally you see users with similar sort
> requirements, except perhaps for batch reporting. For those batch jobs, a
> differently configured temp tablespace makes sense. Now, as a DBA, are you
> being sensible to swap normal users to that temporary tablespace, even if
> there's a problem like you outlined with the normal users temp? No, what you
> should be doing is creating a THIRD temp tablespace, which should be PDQ to do,
> and then swap users to that. Since the temp tablespace is stored at the user
> level, it's pretty damn hard for us to store history of temp tablespace, which
> is what you'd need to get around the issue any other way.
>
> Of course, if you are consolidating applications into a global single instance,
> then you won't end up with most users having the same sort requirements. But
> even there you can just have different temp tablespaces, and if one fails like
> you outlined, then create another one.
>
> The problem to me boils down to how you perform your role as a DBA. Maybe in a
> later release we can add a _EXPERIENCE_LEVEL parameter so the database can make
> intelligent decisions for you, but in the meantime you can't abrogate your
> responsibilities.
>
> My $0.02 worth. Back to you! :)
>
> HTH. Additions and corrections welcome.
>
> Pete
>
> SELECT standard_disclaimer, witty_remark FROM company_requirements;

I'm with Pete on this one. I'd create a third TEMP while doing the repair work ... not screw up another application. Then shift them back and drop the third TS.

Your comments will be appreciated.

Daniel Morgan Received on Fri Jan 24 2003 - 14:37:33 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US