why undotbs01.dbf too big
martin_i...@yahoo.com
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 db 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
Steve Howard
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
ora...@msn.com
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 db 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
Mark D Powell
>
> - 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 --
madhus...@gmail.com
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
martin_i...@yahoo.com
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?
joel garry
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/08/gmail-account-hacking-tool.html
martin_i...@yahoo.com
>
> 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
joel garry
>
> > - 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/f?p=100:11:0::::P11_QUESTION_ID:1101387600346921102
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/uniontrib/20080822/news_1b22costplus.html
martin_i...@yahoo.com
>
> > > - 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?
>
> if you don't commit, you have a really long running transaction. If
> you do that consistently, you have consistency problems.
>
> but I can't seem to get there just now. Can't get to my home page,
> either. Hmmmm.
>
> jg
> --
> @home.com is bogus.
>
> - 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@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?
joel garry
I would guess those "suspect" tablespaces are probably just set to
autoextend. Some people think you shouldn't do that with user data.
Personally, I set data files in user tablespaces to a fixed size,
except for the most recent which I set to autoextend with a max at
that size. In 10G, there are monitoring alerts you can set to however
you desire, I just still have old habits.
As far as googling scripts, see http://www.jlcomp.demon.co.uk/kiddy_scripts.html
(and for better scripts, see
http://www.jlcomp.demon.co.uk/ind_misc.html ).
jg
--
@home.com is bogus.
"If you’re trying to do trouble-shooting, kicking the database to
death is not a good way to go about it." - Jonathan Lewis