Be aware that at least for V7.3.4.5 the SQL ALTER INDEX <index_name REBUILD .... automagically COMPUTES STATISTICS even if you are using CBO.
Mark Leith wrote: > Hi all, > > Thanks for your responses on this - I had most of them in mind - but there > were a couple out there (PCTUSED/PCTFREE and child table FK/indexes) that I > hadn't thought of. > > To give you a little more info on this - and let you know what I know so > far: > > This is on an 8.0.6 database running in RULE based optimisation. The table > in question only has 100,000 rows in it! The delete was running fine until > they upgraded the database, and the application, when it went up to 50 > minutes! > > Now, I chatted to the client yesterday, who also told me that they haven't > rebuilt the indexes since the upgrade, and that the table in question has > recently had a chunk of data imported in to it. This has happened a couple > of times sine the upgrade also. > > I have asked them to rebuild the indexes on the table in question first of > all to see if this improves performance in anyway (I suspect it will). I > don't think stats will be the issue, though I have asked her to check if > there are stats on the table, just in case they have been computed in error > and making the statement run in COST inadvertently.. > > She will also be checking on any schema changes (indexes dropped), and Lisa > and Tom's suggestion of the missing FK/Index combination (thanks). > > I'll let you know what fixes the problem (if any of it does..). Failing all > of this I've asked her for the explain plan and statement.. > > Cheers > > Mark > > -----Original Message----- > Varma > Sent: 29 November 2001 09:35 > To: Multiple recipients of list ORACLE-L > > I think the condition used to delete the records for table is not matching > with the indexes of that table. check the indexes properly and the delete > statement also accordingly and make sure that the condition/column which is > given to delete is present in the index. > > -----Original Message----- > Faroult > Sent: Wednesday, November 28, 2001 1:25 AM > To: Multiple recipients of list ORACLE-L > > Mark Leith wrote: > > > > Hi list people :) > > > > We have a customer who has been running a particular delete statement > > against a table for a while now, which usually ran within minutes. All of > a > > sudden this table has suddenly gone from a few minutes right up to 50! He > > wants to diagnose why.. > > > > Where would you start? > > > > I have a few ideas of my own - like stale stats, small rollback segments > > etc. - but am after some of your advice also before I get back to him > > tomorrow morning.. Not sure on the Oracle version, OS, or even amount of > > rows he is deleting or size of the table (yet, I'll find this out > tomorrow), > > but there has to be a pretty standard way of diagnosing this.. > > > > All help appreciated. > > > > Mark > > > > =================================================== > > Mark Leith | T: +44 (0)1905 330 281 > > Sales & Marketing | F: +44 (0)870 127 5283 > > Cool Tools UK Ltd | E: [EMAIL PROTECTED] > > =================================================== > > 'We have done nothing and suddenly it's slow' is a well known tune. > Usual suspects : > 1) Stats, computed or deleted > 2) Dropped index > 3) Newly created trigger > 4) Locks. Nobody doing DML on the same table during the delete ? > -- > Regards, > > Stephane Faroult > Oriole Corporation > Voice: +44 (0) 7050-696-269 > Fax: +44 (0) 7050-696-449 > Performance Tools & Free Scripts > -------------------------------------------------------------- > http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs > -------------------------------------------------------------- > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Stephane Faroult > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Santosh Varma > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Mark Leith > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).