why undotbs01.dbf too big

Hello,
I have a 10G on solaris database that has chewed up 3.0 GB of disk
space with undotbs01.dbf. This is unexpected and inconvenient cos the
disk partition is now full.
There is a gateway feeding lots of small 3KB rows into a table which
is caught by a trigger that populates some other tables via a stored
procedure (about 30GB of data in all). So why would 3GB of undo space
be needed?

There is also an application that runs quite complicated queries again
the Database but my assumption is that does not need undo space?

Any suggestions on why undotbs01.dbf is sooo big? And how to shrink
it back?

Best Regards

Tony

On Aug 21, 4:58 am, martin_ian_le...@yahoo.com wrote:
> Hello,
> I have a 10G on solaris database that has chewed up 3.0 GB of disk
> space with undotbs01.dbf. This is unexpected and inconvenient cos the
> disk partition is now full.
> There is a gateway feeding lots of small 3KB rows into a table which
> is caught by a trigger that populates some other tables via a stored
> procedure (about 30GB of data in all). So why would 3GB of undo space
> be needed?
>
> There is also an application that runs quite complicated queries again
> the Database but my assumption is that does not need undo space?
>
> Any suggestions on why undotbs01.dbf is sooo big? And how to shrink
> it back?
>
> Best Regards
>
> Tony

Do a desc on gv$undostat. That will tell you the sql_id (and query
run time) of the longest running query during any given ten minute
period over the last few days, as that is usually the culprit. You
can also look at gv$transaction.

HTH,

Steve

Comments embedded.
On Aug 21, 3:58*am, martin_ian_le...@yahoo.com wrote:
> Hello,
> I have a 10G on solaris database that has chewed up 3.0 GB of disk
> space with undotbs01.dbf. This is unexpected and inconvenient cos the
> disk partition is now full.

Which is precisely why you shouldn't be using autoextend in an
unrestricted fashion; I prefer to not use autoextend at all.

> There is a gateway feeding lots of small 3KB rows into a table which
> is caught by a trigger that populates some other tables via a stored
> procedure (about 30GB of data in all). So why would 3GB of undo space
> be needed?
>

Many reasons, a few being: large number of transactions, large
transaction size, high undo_retention setting.

> There is also an application that runs quite complicated queries again
> the Database but my assumption is that does not need undo space?
>

Why wouldn't it? You need a read consistent image of the data and
many times that image is generated by using the UNDO generated by
other transactions.

> Any suggestions on why *undotbs01.dbf is sooo big?

You set the file to autoextend.

>And how to shrink
> it back?

Create a new and smaller undo tablespace, dynamically change the
init.ora parameter unto_tablespace to point to the new undo tablespace
then, when you bounce the instance drop the old undo tablespace.

>
> Best Regards
>
> Tony


David Fitzjarrell

On Aug 21, 9:22*am, Steve Howard wrote:
> On Aug 21, 4:58 am, martin_ian_le...@yahoo.com wrote:
>
>
>
>
>
> > Hello,
> > I have a 10G on solaris database that has chewed up 3.0 GB of disk
> > space with undotbs01.dbf. This is unexpected and inconvenient cos the
> > disk partition is now full.
> > There is a gateway feeding lots of small 3KB rows into a table which
> > is caught by a trigger that populates some other tables via a stored
> > procedure (about 30GB of data in all). So why would 3GB of undo space
> > be needed?
>
> > There is also an application that runs quite complicated queries again
> > the Database but my assumption is that does not need undo space?
>
> > Any suggestions on why *undotbs01.dbf is sooo big? And how to shrink
> > it back?
>
> > Best Regards
>
> > Tony
>
> Do a desc on gv$undostat. *That will tell you the sql_id (and query
> run time) of the longest running query during any given ten minute
> period over the last few days, as that is usually the culprit. *You
> can also look at gv$transaction.
>
> HTH,
>
> Steve- Hide quoted text -
>
> - Show quoted text -

Tony, as Steve suggested you need to size your undo tablespace based
on v$undostat (also look at dba_undo_extents). Then as David
suggested you should probably replace or rebuild the undo tablespace
depending on what your disk situation looks like. You might need to
define a very small new undo tablespace, switch to using it, drop and
recreate the original undo tablespace if using a different disk for
the undo tablespace is an issue. I will second the idea that you
should always set a maximum size on all extendable database data
files.

HTH -- Mark D Powell --

In addition to what others had to say, it's possible you have a
guaranteed retention. Check "retention" in dba_tablespaces for the
undo tablespace. A high undo_retention combined with guarantee option
and with autoextend on, will lead to high undo datafile size. Whether
you really need a guarantee option is up to you to decide.

-Madhu Sreeram

On 21 Aug, 16:27, madhusree...@gmail.com wrote:
> In addition to what others had to say, it's possible you have a
> guaranteed retention. Check "retention" in dba_tablespaces for the
> undo tablespace. A high undo_retention combined with guarantee option
> and with autoextend on, will lead to high undo datafile size. Whether
> you really need a guarantee option is up to you to decide.
>
> -Madhu Sreeram

great tips from everyone - thanks
SQL> select RETENTION from dba_tablespaces where tablespace_name =
'UNDOTBS1';

RETENTION
-----------
NOGUARANTEE
Anyway I think that the cause of the large undo tbs is because of
delete from tablename where date> x days, this seems to take a long
time - over the 900 secs undo_retention period - this is I know
(now) a separate issue from setting up the tablespace properly with a
max size.
Is is true BTW that you should enter "commit" when the delete has
finished?
would it help speed up the delete by setting a day on year flag on
event insertion and creating an index?

On Aug 21, 9:20*am, martin_ian_le...@yahoo.com wrote:
> On 21 Aug, 16:27, madhusree...@gmail.com wrote:
>
> > In addition to what others had to say, it's possible you have a
> > guaranteed retention. Check "retention" in dba_tablespaces for the
> > undo tablespace. A high undo_retention combined with guarantee option
> > and with autoextend on, will lead to high undo datafile size. Whether
> > you really need a guarantee option is up to you to decide.
>
> > -Madhu Sreeram
>
> great tips from everyone - thanks
> SQL> select RETENTION from *dba_tablespaces where tablespace_name =
> 'UNDOTBS1';
>
> RETENTION
> -----------
> NOGUARANTEE
> Anyway I think that the cause of the large undo tbs is because of
> delete from tablename where date> x days, this seems to take a long
> time - over the 900 secs undo_retention period - this is I know
> (now) a separate issue from setting up the tablespace properly with a
> max size.
> Is is true BTW that you should enter "commit" when the delete has
> finished?
> would it help speed up the delete by setting *a day on year flag on
> event insertion and creating an index?

Oh man, read the Concepts manual at tahiti.oracle.com. You MUST
understand when to commit and why. You MUST understand undo and
oracle's consistency model. Then you should read Tom Kyte's books and
work through the examples to correct your understanding. Search
around on asktom.oracle.com to see some various scenarious about
deleting.

It's possible that it would help setting a flag and creating an index,
and it's possible it would hurt, it all depends on your implementation
details. You need to learn to read explain plans and trace files to
understand how Oracle is interpreting whatever it is you do. There
are many good explanations floating about, start surfing. Charles
Hooper in particular has posted some painstaking examples for new
initiates here and on forums.oracle.com.

You should accept that Oracle may need a lot of undo for some of the
things that you do. I find I need 40G for my 100G database. You can
play games to use less, but... why?

Sorry if I sound harsh, don't really mean to, I'm probably just
reading more into your posts than I should. Also see
http://www.dbaoracle.net/readme-cdos.htm

jg
--
@home.com is bogus.
http://www.hungry-hackers.com/2008/0...king-tool.html

On 21 Aug, 18:18, joel garry wrote:
> On Aug 21, 9:20*am, martin_ian_le...@yahoo.com wrote:
>
>
>
>
>
> > On 21 Aug, 16:27, madhusree...@gmail.com wrote:
>
> > > In addition to what others had to say, it's possible you have a
> > > guaranteed retention. Check "retention" in dba_tablespaces for the
> > > undo tablespace. A high undo_retention combined with guarantee option
> > > and with autoextend on, will lead to high undo datafile size. Whether
> > > you really need a guarantee option is up to you to decide.
>
> > > -Madhu Sreeram
>
> > great tips from everyone - thanks
> > SQL> select RETENTION from *dba_tablespaces where tablespace_name =
> > 'UNDOTBS1';
>
> > RETENTION
> > -----------
> > NOGUARANTEE
> > Anyway I think that the cause of the large undo tbs is because of
> > delete from tablename where date> x days, this seems to take a long
> > time - over the 900 secs undo_retention period - this is I know
> > (now) a separate issue from setting up the tablespace properly with a
> > max size.
> > Is is true BTW that you should enter "commit" when the delete has
> > finished?
> > would it help speed up the delete by setting *a day on year flag on
> > event insertion and creating an index?
>
> Oh man, read the Concepts manual at tahiti.oracle.com. *You MUST
> understand when to commit and why. *You MUST understand undo and
> oracle's consistency model. *Then you should read Tom Kyte's books and
> work through the examples to correct your understanding. *Search
> around on asktom.oracle.com to see some various scenarious about
> deleting.
>
> It's possible that it would help setting a flag and creating an index,
> and it's possible it would hurt, it all depends on your implementation
> details. *You need to learn to read explain plans and trace files to
> understand how Oracle is interpreting whatever it is you do. *There
> are many good explanations floating about, start surfing. *Charles
> Hooper in particular has posted some painstaking examples for new
> initiates here and on forums.oracle.com.
>
> You should accept that Oracle may need a lot of undo for some of the
> things that you do. *I find I need 40G for my 100G database. *You can
> play games to use less, but... why?
>
> Sorry if I sound harsh, don't really mean to, I'm probably just
> reading more into your posts than I should. *Also seehttp://www.dbaoracle.net/readme-cdos.htm
>
> jg
> --
> @home.com is bogus.http://www.hungry-hackers.com/2008/0...ing-tool.html- Hide quoted text -
>
> - Show quoted text -

Thanks for all the advice - I have done some rtfm-ing on tahiti for
example.
If I understand correctly if i do a "delete from table where
condition" from sqlplus this uses the undo tablespace to maintain read
consistency and when the sqlplus comes back the operation is finished
and the undo tablespace is released for reuse. So a commit when the
sqlplus returns would do nothing, because in the absense of a
savepoint that is meaningless

On Aug 22, 2:57*am, martin_ian_le...@yahoo.com wrote:
> On 21 Aug, 18:18, joel garry wrote:
>
>
>
>
>
> > On Aug 21, 9:20*am, martin_ian_le...@yahoo.com wrote:
>
> > > On 21 Aug, 16:27, madhusree...@gmail.com wrote:
>
> > > > In addition to what others had to say, it's possible you have a
> > > > guaranteed retention. Check "retention" in dba_tablespaces for the
> > > > undo tablespace. A high undo_retention combined with guarantee option
> > > > and with autoextend on, will lead to high undo datafile size. Whether
> > > > you really need a guarantee option is up to you to decide.
>
> > > > -Madhu Sreeram
>
> > > great tips from everyone - thanks
> > > SQL> select RETENTION from *dba_tablespaces where tablespace_name =
> > > 'UNDOTBS1';
>
> > > RETENTION
> > > -----------
> > > NOGUARANTEE
> > > Anyway I think that the cause of the large undo tbs is because of
> > > delete from tablename where date> x days, this seems to take a long
> > > time - over the 900 secs undo_retention period - this is I know
> > > (now) a separate issue from setting up the tablespace properly with a
> > > max size.
> > > Is is true BTW that you should enter "commit" when the delete has
> > > finished?
> > > would it help speed up the delete by setting *a day on year flag on
> > > event insertion and creating an index?
>
> > Oh man, read the Concepts manual at tahiti.oracle.com. *You MUST
> > understand when to commit and why. *You MUST understand undo and
> > oracle's consistency model. *Then you should read Tom Kyte's books and
> > work through the examples to correct your understanding. *Search
> > around on asktom.oracle.com to see some various scenarious about
> > deleting.
>
> > It's possible that it would help setting a flag and creating an index,
> > and it's possible it would hurt, it all depends on your implementation
> > details. *You need to learn to read explain plans and trace files to
> > understand how Oracle is interpreting whatever it is you do. *There
> > are many good explanations floating about, start surfing. *Charles
> > Hooper in particular has posted some painstaking examples for new
> > initiates here and on forums.oracle.com.
>
> > You should accept that Oracle may need a lot of undo for some of the
> > things that you do. *I find I need 40G for my 100G database. *You can
> > play games to use less, but... why?
>
> > Sorry if I sound harsh, don't really mean to, I'm probably just
> > reading more into your posts than I should. *Also seehttp://www.dbaoracle.net/readme-cdos.htm
>
> > jg
> > --
> > @home.com is bogus.http://www.hungry-hackers.com/2008/0...tool.html-Hide quoted text -
>
> > - Show quoted text -
>
> Thanks for all the advice - I have done some rtfm-ing on tahiti for
> example.
> If I understand correctly if i do a "delete from table where
> condition" from sqlplus this uses the undo tablespace to maintain read
> consistency and when the sqlplus comes back the operation is finished
> and the undo tablespace is released for reuse. So a commit when the
> sqlplus returns would do nothing, because *in the absense of a
> savepoint that is meaningless

OK, think of this.

You delete from table where condition. The sqlplus comes back and you
don't commit. Someone else looks at the table. Do you want them to
see the rows you deleted? 150 other people look at the table at
various times after that, while some others insert various rows. What
do you think they will see? What do you think will be sitting in the
SGA? Now you leave sqlplus. What do you think they will see? Do you
think you will get an ora-1555?

Also see http://asktom.oracle.com/pls/asktom/...87600346921102
if you don't commit, you have a really long running transaction. If
you do that consistently, you have consistency problems.

I'd also direct you to some pages on http://www.jlcomp.demon.co.uk/
but I can't seem to get there just now. Can't get to my home page,
either. Hmmmm.

jg
--
@home.com is bogus.
Mid-June? Not April? http://www.signonsandiego.com/uniont...2costplus.html

On 22 Aug, 22:14, joel garry wrote:
> On Aug 22, 2:57*am, martin_ian_le...@yahoo.com wrote:
>
>
>
>
>
> > On 21 Aug, 18:18, joel garry wrote:
>
> > > On Aug 21, 9:20*am, martin_ian_le...@yahoo.com wrote:
>
> > > > On 21 Aug, 16:27, madhusree...@gmail.com wrote:
>
> > > > > In addition to what others had to say, it's possible you have a
> > > > > guaranteed retention. Check "retention" in dba_tablespaces for the
> > > > > undo tablespace. A high undo_retention combined with guarantee option
> > > > > and with autoextend on, will lead to high undo datafile size. Whether
> > > > > you really need a guarantee option is up to you to decide.
>
> > > > > -Madhu Sreeram
>
> > > > great tips from everyone - thanks
> > > > SQL> select RETENTION from *dba_tablespaces where tablespace_name=
> > > > 'UNDOTBS1';
>
> > > > RETENTION
> > > > -----------
> > > > NOGUARANTEE
> > > > Anyway I think that the cause of the large undo tbs is because of
> > > > delete from tablename where date> x days, this seems to take a long
> > > > time - over the 900 secs undo_retention period - this is I know
> > > > (now) a separate issue from setting up the tablespace properly witha
> > > > max size.
> > > > Is is true BTW that you should enter "commit" when the delete has
> > > > finished?
> > > > would it help speed up the delete by setting *a day on year flag on
> > > > event insertion and creating an index?
>
> > > Oh man, read the Concepts manual at tahiti.oracle.com. *You MUST
> > > understand when to commit and why. *You MUST understand undo and
> > > oracle's consistency model. *Then you should read Tom Kyte's books and
> > > work through the examples to correct your understanding. *Search
> > > around on asktom.oracle.com to see some various scenarious about
> > > deleting.
>
> > > It's possible that it would help setting a flag and creating an index,
> > > and it's possible it would hurt, it all depends on your implementation
> > > details. *You need to learn to read explain plans and trace files to
> > > understand how Oracle is interpreting whatever it is you do. *There
> > > are many good explanations floating about, start surfing. *Charles
> > > Hooper in particular has posted some painstaking examples for new
> > > initiates here and on forums.oracle.com.
>
> > > You should accept that Oracle may need a lot of undo for some of the
> > > things that you do. *I find I need 40G for my 100G database. *Youcan
> > > play games to use less, but... why?
>
> > > Sorry if I sound harsh, don't really mean to, I'm probably just
> > > reading more into your posts than I should. *Also seehttp://www.dbaoracle.net/readme-cdos.htm
>
> > > jg
> > > --
> > > @home.com is bogus.http://www.hungry-hackers.com/2008/0....html...quoted text -
>
> > > - Show quoted text -
>
> > Thanks for all the advice - I have done some rtfm-ing on tahiti for
> > example.
> > If I understand correctly if i do a "delete from table where
> > condition" from sqlplus this uses the undo tablespace to maintain read
> > consistency and when the sqlplus comes back the operation is finished
> > and the undo tablespace is released for reuse. So a commit when the
> > sqlplus returns would do nothing, because *in the absense of a
> > savepoint that is meaningless
>
> OK, think of this.
>
> You delete from table where condition. *The sqlplus comes back and you
> don't commit. *Someone else looks at the table. *Do you want them to
> see the rows you deleted? *150 other people look at the table at
> various times after that, while some others insert various rows. *What
> do you think they will see? *What do you think will be sitting in the
> SGA? *Now you leave sqlplus. *What do you think they will see? *Do you
> think you will get an ora-1555?
>
> Also seehttp://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1...
> if you don't commit, you have a really long running transaction. *If
> you do that consistently, you have consistency problems.
>
> I'd also direct you to some pages onhttp://www.jlcomp.demon.co.uk/
> but I can't seem to get there just now. *Can't get to my home page,
> either. *Hmmmm.
>
> jg
> --
> @home.com is bogus.
> Mid-June? *Not April? *http://www.signonsandiego.com/uniont...costplus.html- Hide quoted text -
>
> - Show quoted text -

I get it now! well the situation in respect of undo tablespace anyway.
read-consistency and that.
Thanks very much for your help!!
I ran a script that I googled - gave some interesting results - seems
I have fixed the undo tbs problemette!

[oracle-at-chrome ~]$ cat tbsusage.sql
SELECT A.TABLESPACE_NAME,
A.BYTES TOTAL,
B.BYTES USED,
C.BYTES FREE,
(B.BYTES*100)/A.BYTES "% USED",
(C.BYTES*100)/A.BYTES "% FREE"
FROM
SYS.SM$TS_AVAIL A,
SYS.SM$TS_USED B,
SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
AND
A.TABLESPACE_NAME=C.TABLESPACE_NAME;
...
..

TABLESPACE_NAME TOTAL USED FREE % USED % FREE
SYSTEM 513802240 509542400 4194304
99.1709184 .816326531
USERS 5242880 393216 4784128
7.5 91.25
MYREP 2.3803E+10 2.3789E+10 13697024
99.9421806 .057544053
SYSAUX 429916160 417071104 12779520
97.0121951 2.97256098
UNDOTBS1 524288000 8585216 515637248
1.6375 98.35

So the undotablespace is looking real healthy.
The system, myrep and even sysaux look a bit suspect I think?
Any obvious action points?