| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: tempspace usage
comment in line.
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.
select disk_reads,rows_processed/executions rows_processed_per_execute,sql_=
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,
> 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 kind
> > > of operations are rare (delete the duplicate rows and due to the load
> > > speed we do not use unique index). But I did use them these days sinc=
e
> > > 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 undo
> > > 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. then
> > 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 usage=
.
> > > >
> > > >
> > > > On 4/20/05, Yongping Yao <yaoyongping_at_gmail.com> wrote:
> > > > > I have a related question. After I run a statement consuming the
> > > > > temporary tablespace a lot and commit the transaction, the tables=
pace
> > > > > is not cleaned soon. Then other sessions got an error something l=
ike
> > > > > "can't extend ...in ..." which means the temporary tablespace is =
not
> > > > > enough. Is there something wrong with the background process?
> > > > > Do I have to use a separate temporary tablespace or set the tempo=
rary
> > > > > tablespace autoextend (which I think is not safe and hard to cont=
rol
> > > > > 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/
> > >
> >
>=20
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Apr 20 2005 - 22:06:20 CDT
 |
 |