Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Delete slowing..

RE: Delete slowing..

From: Robertson Lee - lerobe <lerobe_at_acxiom.co.uk>
Date: Thu, 29 Nov 2001 07:36:35 -0800
Message-ID: <F001.003D07DF.20011129071033@fatcity.com>

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 <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: mark_at_cool-tools.co.uk
> > ===================================================
>
> '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: 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).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Charlie Mengler
  INET: charliem_at_mwh.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: 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).


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.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Robertson Lee - lerobe
  INET: lerobe_at_acxiom.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 - 09:36:35 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US