Re: why undotbs01.dbf too big
Date: Tue, 26 Aug 2008 05:37:28 -0700 (PDT)
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.
> @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
A.BYTES TOTAL, B.BYTES USED, C.BYTES FREE,
(B.BYTES*100)/A.BYTES "% USED",
(C.BYTES*100)/A.BYTES "% FREE"
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 5156372481.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? Received on Tue Aug 26 2008 - 07:37:28 CDT