RE: Delete slowing..
Right - I just heard what the problem was, and have to say that it was in fact the first thing that I though of - when they upgrade to the new version of the application, somebody forgot to create one of the indexes on the table! Problem solved! Cheers Mark -Original Message- Sent: 29 November 2001 14:25 To: Multiple recipients of list ORACLE-L Does this mean that if you are running in RULE, you shouldn't REBUILD indexes, but drop and recreate them? You learn something new every day.. Cheers Mark -Original Message- Mengler Sent: 29 November 2001 12:41 To: Multiple recipients of list ORACLE-L Be aware that at least for V7.3.4.5 the SQL ALTER INDEX 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
RE: Delete slowing..
Not necessarily. You could rebuild and then analyze and delete the statistics. -Original Message- Sent: 29 November 2001 14:25 To: Multiple recipients of list ORACLE-L Does this mean that if you are running in RULE, you shouldn't REBUILD indexes, but drop and recreate them? You learn something new every day.. Cheers Mark -Original Message- Mengler Sent: 29 November 2001 12:41 To: Multiple recipients of list ORACLE-L Be aware that at least for V7.3.4.5 the SQL ALTER INDEX 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
Re: Delete slowing..
Hi Mark, It actually shouldn't matter if you are really in RULE, but in Choose, where you expect to have and NO STATS performing using RBO by default, you should issue the command analyze index delete statistics After rebuilding the index. Hope this helps, John [EMAIL PROTECTED] wrote: Does this mean that if you are running in RULE, you shouldn't REBUILDindexes, but drop and recreate them?You learn something new every day..CheersMark-Original Message-MenglerSent: 29 November 2001 12:41To: Multiple recipients of list ORACLE-LBe aware that at least for V7.3.4.5 the SQLALTER INDEX 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 therewere 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 sofar:This is on an 8.0.6 database running in RULE based optimisation. The tablein question only has 100,000 rows in it! The delete was running fine untilthey upgraded the database, and the application, when it went up to 50minutes!Now, I chatted to the client yesterday, who also told me that they haven'trebuilt the indexes since the upgrade, and that the table in question hasrecently had a chunk of data imported in to it. This has happened a coupleof times sine the upgrade also.I have asked them to rebuild the indexes on the table in question first ofall to see if this improves performance in anyway (I suspect it will). Idon't think stats will be the issue, though I have asked her to check ifthere 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..CheersMark-Original Message-VarmaSent: 29 November 2001 09:35To: Multiple recipients of list ORACLE-LI think the condition used to delete the records for table is not matchingwith the indexes of that table. check the indexes properly and the deletestatement also accordingly and make sure that the condition/column which is given to delete is present in the index.-Original Message-FaroultSent: Wednesday, November 28, 2001 1:25 AMTo: Multiple recipients of list ORACLE-LMark Leith wrote: Hi list people :)We have a customer who has been running a particular delete statementagainst 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 segmentsetc. - but am after some of your advice also before I get back to himtomorrow morning.. Not sure on the Oracle version, OS, or even amount ofrows 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 deleted2) Dropped index3) Newly created trigger4) Locks. Nobody doing DML on the same table during the delete ?--Regards,Stephane FaroultOriole CorporationVoice: +44 (0) 7050-696-269Fax:+44 (0) 7050-696-449Performance Tools & Free Scripts--http://www.oriole.com, designed by Oracle DBAs for Oracle DBAsPlease see the official ORACLE-L FAQ: http://www.orafaq.com--Author: Stephane Faroult INET: sfaroult@orio le.comFat City Network Services-- (858) 538-5051 FAX: (858) 538-5051San Diego, California-- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Ma
RE: Delete slowing..
Does this mean that if you are running in RULE, you shouldn't REBUILD indexes, but drop and recreate them? You learn something new every day.. Cheers Mark -Original Message- Mengler Sent: 29 November 2001 12:41 To: Multiple recipients of list ORACLE-L Be aware that at least for V7.3.4.5 the SQL ALTER INDEX 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
Re: Delete slowing..
Be aware that at least for V7.3.4.5 the SQL ALTER INDEX 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 othe
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: [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 Lis
RE: Delete slowing..
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).
RE: Delete slowing..
Title: RE: Delete slowing.. Hi, Perhaps you check the Indexes against that table, you can drop the index first, you may consider a TRUNCATE TABLE command if you not need the rollback option. Sinardy -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Hallas JohnSent: Wednesday, 28 November 2001 5:31 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Delete slowing.. Mark, something else to add to your list of things to check Does a FTS of the table take a long time as well? If so I would try re-creating the table to remove the space left by previously deleted rows. John -Original Message- From: Stephane Faroult [mailto:[EMAIL PROTECTED]] Sent: 27 November 2001 19:55 To: Multiple recipients of list ORACLE-L Subject: Re: Delete slowing.. 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). >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately.>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
RE: Delete slowing..
Title: RE: Delete slowing.. Mark, something else to add to your list of things to check Does a FTS of the table take a long time as well? If so I would try re-creating the table to remove the space left by previously deleted rows. John -Original Message- From: Stephane Faroult [mailto:[EMAIL PROTECTED]] Sent: 27 November 2001 19:55 To: Multiple recipients of list ORACLE-L Subject: Re: Delete slowing.. 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). >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
RE: Delete slowing..
Thanks for all your input - I'll be following up on this tomorrow morning, and will let you all know.. Cheers Mark -Original Message- Sent: 27 November 2001 20:45 To: Multiple recipients of list ORACLE-L Also check for a delete trigger on the table. Ron Thomas Hypercom, Inc [EMAIL PROTECTED] [EMAIL PROTECTED] tate.ny.usTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: Delete slowing.. om 11/27/01 12:30 PM Please respond to ORACLE-L Mark, check for foreign key/missing index combinations. If the table is a parent table, and it's child's foreign key's column is not indexed, the delete can take *forever* if the child is a large table. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, November 27, 2001 1:55 PM To: Multiple recipients of list ORACLE-L 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] === http://www.cool-tools.co.uk Maximising throughput & performance -- 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: Mercadante, Thomas F 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: Ron Thomas 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).
RE: Delete slowing..
Title: RE: Delete slowing.. Mark, how many indexes are on the table? Are there any unindexed foreign keys? Lisa Koivu Oracle Database Monkey Mama. Fairfield Resorts, Inc. 954-935-4117 -Original Message- From: Mark Leith [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, November 27, 2001 1:55 PM To: Multiple recipients of list ORACLE-L Subject: Delete slowing.. 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] === http://www.cool-tools.co.uk Maximising throughput & performance -- 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).
RE: Delete slowing..
Also check for a delete trigger on the table. Ron Thomas Hypercom, Inc [EMAIL PROTECTED] [EMAIL PROTECTED] tate.ny.usTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: Delete slowing.. om 11/27/01 12:30 PM Please respond to ORACLE-L Mark, check for foreign key/missing index combinations. If the table is a parent table, and it's child's foreign key's column is not indexed, the delete can take *forever* if the child is a large table. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, November 27, 2001 1:55 PM To: Multiple recipients of list ORACLE-L 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] === http://www.cool-tools.co.uk Maximising throughput & performance -- 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: Mercadante, Thomas F 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: Ron Thomas 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).
Re: Delete slowing..
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).
RE: Delete slowing..
Mark, check for foreign key/missing index combinations. If the table is a parent table, and it's child's foreign key's column is not indexed, the delete can take *forever* if the child is a large table. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, November 27, 2001 1:55 PM To: Multiple recipients of list ORACLE-L 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] === http://www.cool-tools.co.uk Maximising throughput & performance -- 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: Mercadante, Thomas F 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).
Delete slowing..
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] === http://www.cool-tools.co.uk Maximising throughput & performance -- 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).
