Re: why undotbs01.dbf too big

On 22 Aug, 22:14, joel garry <joel-ga..._at_home.com> wrote:
> On Aug 22, 2:57 am, martin_ian_le..._at_yahoo.com wrote:
>
>
>
>
>
> > 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...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/uniontrib/20080822/news_1b22costplus.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,

SYS.SM$TS_AVAIL A,
SYS.SM$TS_USED B,
SYS.SM$TS_FREE C

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

So the undotablespace is looking real healthy. The system, myrep and even sysaux look a bit suspect I think? Any obvious action points? Received on Tue Aug 26 2008 - 07:37:28 CDT