RE: Is this Needed?

Donald,
 
    Is this a needed activity, probably not as a whole, though I will agree 
that it's easier to export/import the entire schema.  You may have one or more 
tables that have been heavy insert and delete that are not chained per say, but 
have a lot of wasted space therein.  Now the maintenance action that's being 
suggested will clean that up, to what other purpose than reclaiming disk space 
I don't know.  We have a very large table in PeopleSoft that gets archived 
periodically.  A typical archive run will eliminate 40% to 50% of the rows in 
the table & we do export & reimport that table every time they archive just for 
the purpose of reclaiming space in the tablespace.  They use the utilization 
rate of the tablespace as an indicator of having to run the archive process.  I 
don't agree with the methods, but it does work.
 
    Now, just like our little monster you've probably got people complaining 
about performance, and someone has made a decision based on that complaint and 
what they believe is the root cause.  Shame, good thought, bad decision.  We 
also had those problems, but after reviewing/capturing sql statement that ran 
against that table we found the true problem which was a misused index.  BTW: 
since I didn't already mention it this application runs in RULE mode.  So, what 
good is an index on a table that is going to return 80% of the data rows, none 
that's what.  We had a serious discussion with the application developers & 
they did some work.  End result no one complains about this table, until the 
tablespace utilization in up in the 90% range at which time they believe it's 
time to archive again.
 
    And for those who don't think that old thinking is still not around, here's 
something from a vendor that I got recently:
 
    "Anything over 100 for an index is a concern "
 
Old beliefs die hard.

  _____  

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Freeman, Donald
Sent: Wednesday, October 26, 2005 11:19 AM
To: Christian Antognini; Freeman, Donald
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Is this Needed?


I just checked and code tables are set to 10% while key application tables are 
set to 30%.  I'm pretty sure these aren't going to be changed.  I don't think 
there is any row chaining there.  What are you getting at?
 

Don Freeman 
Database Administrator 1 
Bureau of Information Technology 
Pennsylvania Department of Health 
717-783-8095 Ext 337 

        -----Original Message-----
        From: Christian Antognini [mailto:Christian.Antognini@xxxxxxxxxxxx]
        Sent: Wednesday, October 26, 2005 11:05 AM
        To: dofreeman@xxxxxxxxxxx
        Cc: oracle-l@xxxxxxxxxxxxx
        Subject: RE: Is this Needed?
        
        
        Don

        > Is this a waste of time?
         
        As usually... it depends. IMHO there are three cases:
        - you waste time
        - you improve performance
        - you decrease performance
         
        Of course the third one is the real problem! And about it... a simple 
question: how you will set PCTFREE before inporting the data?
         
         
        Regards,
        Chris
         
        New Features Oracle Database 10g Release 2 seminars @ www.trivadis.com 
<http://www.trivadis.com> 
        Italiano: Lugano (24-Nov)
        Français: Genève (17-Nov)
        Deutsch: Düsseldorf (23-Nov), Zürich (29-Nov), Stuttgart (13-Dec)

Other related posts: