RE: Index in SUMDELTA$
- From: "Robertson Lee - lerobe" <Lee.Robertson@xxxxxxxxxx>
- To: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 22 Jun 2009 17:09:19 +0100
Apologies. I don't think I made myself very clear originally.
The table was small (only about 136 rows) and was only 55Mb in size. It
was the index that was taking up all the space. We rebuilt this index
and it shrunk back to 1Mb from 344Mb.
There was a bug (a few I think) where the table wasn't clearing up its
space but I have been told that they were fixed in 10.2.0.3 (which we
are at).
Regards
Lee
-----Original Message-----
From: Jonathan Lewis [mailto:jonathan@xxxxxxxxxxxxxxxxxx]
Sent: 22 June 2009 17:06
To: Robertson Lee - lerobe; oracle-l@xxxxxxxxxxxxx
Subject: Re: Index in SUMDELTA$
Possibly you've got a number of materialized views with
materialized view logs (possibly on partitioned tables), and
you've been doing a lot of 'insert /*+ append */ - which
have been followed by delayed refreshes.
Each /*+ append */ results in a row inserted into sumdelta$,
and the nature of the index on that table is allows it to become
degenerate if you allow some of your refreshes to delay a long time.
It is a little odd that the table is that large - it suggests you may
have managed to declare a couple of materialized views that you
aren't refreshing any more (possibly snapshots in remote databases) -
or that you've found a bug where Oracle doesn't clear sumdelta$
properly.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
----- Original Message -----
From: "Robertson Lee - lerobe" <Lee.Robertson@xxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, June 22, 2009 1:10 PM
Subject: Index in SUMDELTA$
Guys,
Oracle 10gR2
AIX 6
Got alerted that our SYSTEM tablespace was getting full and on checking
the space taken up by the objects in there noticed that we have an index
(IN_SUMDELTA$) on the table SUMDELTA$, taking up 344Mb of SYSTEM
tablespace. The table is only 55Mb.
I don't know too much about this but a quick google showed me that it is
tied to materialized views. Not much out there on Google other than that
and I don't have access to Metalink at the moment as our SAC has changed
and I am awaiting authorization.
I can see that there are a few bugs around about the table not clearing
down on refreshes of the MV but nothing with regards to the index. Any
advice or links to articles would be much appreciated. I am reluctant to
rebuild the index on one of these "SYSTEM" tables.
Cheers
Lee
***************************************************************************
The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be legally
privileged.
If the reader of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.
If you have received this communication in error, please resend this
communication to the sender and delete the original message or any copy
of it from your computer system.
Thank You.
****************************************************************************
--
//www.freelists.org/webpage/oracle-l
Other related posts:
