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

Home -> Community -> Mailing Lists -> Oracle-L -> Re: tempspace usage

Re: tempspace usage

From: Yongping Yao <yaoyongping_at_gmail.com>
Date: Thu, 21 Apr 2005 19:58:19 +0800
Message-ID: <7064689905042104583f3d7f4a@mail.gmail.com>


thx a lot

On 4/21/05, jame tong <jametong_at_gmail.com> wrote:
> comment in line.

>=20

> On 4/20/05, Yongping Yao <yaoyongping_at_gmail.com> wrote:
> > I've used partition though every partition occupies about 12GB. And I
> > subpartitioned :(
> > Online switch the undo tablespace is a good idea. i agree with you
> > that SMON cleans UNDO until some other transactions request undo
> > space. But I still got "cannot extend" error once. It cleaned too
> > slow?
> > BTW, I know that when the transaction is running I can link the undo
> > objects to its SQL using V$ views. But during the transaction
> > committed and the UNDO space cleaned, how can I get the SQL? I mean
> > how can I name which transaction or session used up the UNDO and
> > thougth it has been committed SMON hasn't clean it out.
> >
>=20

> you can query from the v$sql dynamic perf view. to get it a little .
> not very acurate.
> the next query may give you some hint about it.
>=20
> select disk_reads,rows_processed/executions rows_processed_per_execute,sq=
l_text
> from v$sql
> where command_type in (2 /*insert*/,6 /*update*/,7 /*+delete*/)
> and executions > 0
> order by rows_processed desc,command_type desc,
>=20
>=20

> > On 4/20/05, jame tong <jametong_at_gmail.com> wrote:
> > > comment in line.
> > >
> > > On 4/20/05, Yongping Yao <yaoyongping_at_gmail.com> wrote:
> > > > Well, some statements cannot be tuned more. In our design, these ki=
nd
> > > > of operations are rare (delete the duplicate rows and due to the lo=
ad
> > > > speed we do not use unique index). But I did use them these days si=
nce
> > > > the application did something wrong :(
> > > if your table is very big , then use partition table will be a must.
> > > then you can use exchange partition to minimize the undo with it.
> > > delete from a big table is a bad idea.
> > >
> > > > And by the way, SMON clean the undo tablespace? It also has some
> > > > delay. Some transanctions are commited and they still occupy the un=
do
> > > > space. Can it be solved? Or just add more space to the undo
> > > > tablespace?
> > >
> > > if you have a undo tablespace that extended beyond your control. the=
n
> > > create a new undo tablespace . and change the undo tablespace online
> > > may be a better solution than to wait the SMON to clean then undo
> > > tablespace.
> > >
> > > within my technical scope. if there were no other transaction request
> > > the undo from the undo tablespace , oracle will not release this undo
> > > segment.
> > >
> > > > On 4/20/05, jame tong <jametong_at_gmail.com> wrote:
> > > > > I think you tune the sql statement to minimize the temp space usa=
ge.
> > > > >
> > > > >
> > > > > On 4/20/05, Yongping Yao <yaoyongping_at_gmail.com> wrote:
> > > > > > I have a related question. After I run a statement consuming th=
e
> > > > > > temporary tablespace a lot and commit the transaction, the tabl=
espace
> > > > > > is not cleaned soon. Then other sessions got an error something=
 like
> > > > > > "can't extend ...in ..." which means the temporary tablespace i=
s not
> > > > > > enough. Is there something wrong with the background process?
> > > > > > Do I have to use a separate temporary tablespace or set the tem=
porary
> > > > > > tablespace autoextend (which I think is not safe and hard to co=
ntrol
> > > > > > the datafile size)?
> > > > > >
> > > > > > --
> > > > > > Yao Yongping
> > > > > > Learning Oracle, UNIX/Linux...
> > > > > > Love Reading, Classical Music, Philosophy, Economics etc.
> > > > > > Blog: http://blog.csdn.net/
> > > > > >
> > > > >
> > > >
> > > > --
> > > > Yao Yongping
> > > > Learning Oracle, UNIX/Linux...
> > > > Love Reading, Classical Music, Philosophy, Economics etc.
> > > > Blog: http://blog.csdn.net/
> > > >
> > >
> >
> > --
> > Yao Yongping
> > Learning Oracle, UNIX/Linux...
> > Love Reading, Classical Music, Philosophy, Economics etc.
> > Blog: http://blog.csdn.net/
> >

>=20

--=20
Yao Yongping
Learning Oracle, UNIX/Linux...
Love Reading, Classical Music, Philosophy, Economics etc. Blog: http://blog.csdn.net/

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Apr 21 2005 - 08:04:35 CDT

Original text of this message

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