RE: Index thrashing still after Alter table move and new buildin g of indexes.
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 22 Jul 2004 16:18:48 -0400
Alter table move repacks the table. You can verify this by moving a table
that has had a significant number of deletes so that it takes fewer extents
to hold the rebuilt version. Remember that you must rebuild the indexes
after moving the table the indexes point to. And your statistics will need
updating.
HTH -- Mark D Powell --
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Leonard, George
Sent: Thursday, July 22, 2004 4:10 PM
To: 'oracle-l@xxxxxxxxxxxxx'
Cc: Desplace, Laura
Subject: Index thrashing still after Alter table move and new building
of indexes.
Hi all
quick question:
Does an alter table xxx move tablespace do a block for block move or does it
repack the table.
we are having a problem on physical to logical IO on a specific 2 tables and
specific 2 indexes on the tables for a specific update statement.
It seems to be just thrashing/spinning its wheels when using these indexes.
We rebuild the FK indexes on Tuesday morning and this resolved the problem
where the update returned to under 10 min, but this morning it said expected
to complete time of 7 hours,
we just went thought a process of dropping all constraint and related
indexes, moving the 2 tables to new tablespaces and building completely new
indexes and it seems this did not fix the problem.
We are wondering if the problem is not maybe actual data related since the
analyzes did not return/report any errors.
Oracle version is 9.2.0.4 on Sparc 64, Solaris 2.8 aka 8
Ideas, comments please
George
________________________________________________
George Leonard
Oracle Database Administrator
New Dawn Technologies @ Wesbank
E-mail:gleonard@xxxxxxxxxxxxx
You Have The Obligation to Inform One Honestly of the risk, And As a Person
You Are Committed to Educate Yourself to the Total Risk In Any Activity!
Once Informed & Totally Aware of the Risk,
Every Fool Has the Right to Kill or Injure Themselves as They See Fit!
-----Original Message-----
From: Niall Litchfield [mailto:niall.litchfield@xxxxxxxxx]
Sent: Thursday, July 22, 2004 21:56 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Creating Histograms
Comments as always
On Thu, 22 Jul 2004 10:37:21 -0400, Freeman, Donald
<dofreeman@xxxxxxxxxxx> wrote:
> OK, I understand your point about gathering on schedule. I'm moving into =
> taking over a turn-key contractor developed system. We are doing =
> stats/computed every day. We only add, at most, a few thousand records =
> a day. This is much, much less than 10%. We converted a few million =
> records, about five years worth of records, from four or five other =
> public health databases but our daily accrual is relatively small. I =
> probably wouldn't have to run stats once in a month.
take note of how many records you add to relatively *small* tables. 13
rows added to one of our tables caused hell until we gathered stats
again (and it took ages for anyone to admit that anything ahd
changed). That would be 13 rows in the sense of another financial year
to add to the 2 existing ones - so hardly significant at all :).
I guess I'm saying different objects might have different stats needs.
>We also don't =
> collect system stats. I'm hoping to get enough information here to 'have =
> a meeting' and get all of that changed, the method and rate of =
> collection.
Test system stats carefully (I'm probably too cautious on this), but
system stats are likely to make quite a noticeable difference to
execution times. It is, I'm increasingly convinced, the *right* thing
to do. It doesn't mean that you may not have adverse effects. Overall
system stats have been positive for our test financial environment-
enough so that they get introduced with the next software upgrade that
is running there - but there has been the odd hiccup.
--
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
