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: [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).

-- 
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).

Reply via email to