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: Mark Leith <mark_at_cool-tools.co.uk>
Date: Thu, 29 Nov 2001 06:52:28 -0800
Message-ID: <F001.003D0672.20011129062520@fatcity.com>

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).
Received on Thu Nov 29 2001 - 08:52:28 CST

Original text of this message

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