Re: why undotbs01.dbf too big

From: <martin_ian_lewis_at_yahoo.com>
Date: Fri, 22 Aug 2008 02:57:29 -0700 (PDT)
Message-ID: <6fc97e22-fc46-47ab-9cd2-1904b624c855@s50g2000hsb.googlegroups.com>


On 21 Aug, 18:18, joel garry <joel-ga..._at_home.com> wrote:
> On Aug 21, 9:20 am, martin_ian_le..._at_yahoo.com wrote:
>
>
>
>
>
> > On 21 Aug, 16:27, madhusree..._at_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 <sigh> - 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/08/gmail-account-hacking-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 Received on Fri Aug 22 2008 - 04:57:29 CDT

Original text of this message