Re: Maximum Usage of Temp Tablespaces Since Database Started

From: joel garry <joel-garry_at_home.com>
Date: Mon, 23 Jul 2012 08:57:47 -0700 (PDT)
Message-ID: <e2e3dd0b-c520-4e60-94a9-423464d68345_at_po9g2000pbb.googlegroups.com>

On Jul 23, 7:34 am, basis_consult..._at_hotmail.com wrote:
> Hi,
>
> We are running a large ERP system on Oracle 10g (I know that we
> should not be on 10g-Will be upgrading to Oracle 11 soon).
>
> There are 6 temporary tablespaces with a size of 16GB each.
>
> Is there any way to find out the maximum bytes used by the
> temp. tablespaces since the database was started?
>
> I suppose that I can approximate by having a script run "select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER"
> regularly, but I am hoping that there is a better way.
>
> Thanks,
> QZ

The more difficult problem is figuring out how much of that space usage was from runaway or decremented poorly optimized processes. And as could be inferred from Michel's answer, since the segments are never released, you can't know if someone just threw out some arbitrary oversized configuration. Fortunately "disk is cheap."

On the other hand, since some amount of temp segment usage is based on memory settings and optimizer decisions, that could be different for 11g and also could change as performance issues are found and fixes applied.

I don't think there is any simple rule of thumb, whatever you are trying to account for.

jg

--
_at_home.com is bogus.
“I think everybody wants to make it a gay thing, but it’s just an
American thing." - Sean Sala, organizer of military group marching in
uniform in San Diego LGBT parade.

Received on Mon Jul 23 2012 - 10:57:47 CDT