Re: Delete slowing..

orantdba Thu, 29 Nov 2001 07:21:44 -0800

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 <index-name> 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 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

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

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: sfaroult@orio
le.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: [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 acce
ss / 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).

Re: Delete slowing..

Reply via email to