| 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: Delete slowing..
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: mark_at_cool-tools.co.uk
> ===================================================
'We have done nothing and suddenly it's slow' is a well known tune. Usual suspects :
Stephane Faroult
Oriole Corporation
Voice: +44 (0) 7050-696-269
Fax: +44 (0) 7050-696-449
Performance Tools & Free Scripts
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: sfaroult_at_oriole.com 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: ListGuru_at_fatcity.com (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: vsantosh_at_psi.soft.net 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: ListGuru_at_fatcity.com (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: mark_at_cool-tools.co.uk 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: ListGuru_at_fatcity.com (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).Received on Thu Nov 29 2001 - 05:28:09 CST
 |
 |