| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Index thrashing still after Alter table move and new buildin g of indexes.
Good. If you're happy with the performance generate and save the plan.
If it ever goes bad again check and see if plan has changed.
You may want to start exporting your stats to a stats table on a regular basis. At least right before you do an analyze, as that's the most likely reason for problems to occur. We started doing that to all our production DBs and it was quite revealing to see stats changing (on some) when we assumed that they weren't. It may also explain some anomalies to you. Remember to always use a STATID.
SELECT STATID,COUNT(*) FROM &&USER..XXXSTATS GROUP BY STATID; You may want to know why the counts fluctuate. If they do, that is. If your plans adversely change, due to stats, you can quicklyrecover back to the "stable" stats/plans, by importing from previously saved stats.
Thanks to Wolfgang Breitling for pointing that out to me.
Larry
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Leonard, George
Sent: Friday, July 23, 2004 1:55 AM
To: 'oracle-l_at_freelists.org'
Subject: RE: Index thrashing still after Alter table move and new
buildin g of indexes.
hi
we did the analyze afterwards,
and we did not have a execution plan for before,
in the end we picked a couple of things up that brought the performance back.
one was this <> that we changed to this not in (that)
and on a totally separate table we added a index that included more columns from the where clause. (thinking that it might have disappeared even in the highly controlled environments between the cracks...)
George
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: Wolfson Larry - lwolfs [mailto:lawrence.wolfson_at_acxiom.com]
Sent: Friday, July 23, 2004 1:20 AM
To: 'oracle-l_at_freelists.org'
Cc: Desplace, Laura
Subject: RE: Index thrashing still after Alter table move and new
buildin g of indexes.
George,
It's not clear what order you did things. I don't know about 9.2 rebuilds but on 8.1.6 they definitely deleted the index stats. If there's no stats the optimizer may use the last created date asthe best choice.
Do you have the plan for this code before and after you rebuilt the indexes?
That is, is the 10 minute plan the same as the 7 hour plan?
Did you do another analyze stats after the move and after the recreating the indexes?
Larry
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Leonard, George
Sent: Thursday, July 22, 2004 3:10 PM
To: 'oracle-l_at_freelists.org'
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
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_at_gmail.com]
Sent: Thursday, July 22, 2004 21:56 PM
To: oracle-l_at_freelists.org
Subject: Re: Creating Histograms
Comments as always
On Thu, 22 Jul 2004 10:37:21 -0400, Freeman, Donald
<dofreeman_at_state.pa.us> 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 environmentenough 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 ********************************************************************** 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 re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Fri Jul 23 2004 - 16:17:47 CDT
 |
 |