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

After rebuilding the index.

Hope this helps,
John
mark_at_cool-tools.co.uk 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 <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 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: 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 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 DBAs----------------------------------------------------------------Please 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 Lists--------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail messageto: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from).  You mayalso 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@psi.soft.netFat City Network Services    -- (858) 538-5051  FAX
: (858) 538-5051San Diego, California        -- Public Internet access / Mailing Lists--------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail messageto: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from).  You mayalso 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@cool-tools.co.ukFat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051San Diego, California        -- Public Internet acce
ss / Mailing Lists--------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail messageto: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from).  You mayalso 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@mwh.comFat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051San Diego, California        -- Public Internet access / Mailing Lists--------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail messageto: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from).  You mayalso send the HELP command for other information (like subscribing).