Re: Reorganizing the DB.. the tricky way

Rick Denoire <100.17706_at_germanynet.de> schreef in berichtnieuws pv709v0o2lr18vtt2k9out4r55itoeb2v3_at_4ax.com...
| I analyzed the DB at our company (aprox. 180 GB, Oracle 8.1.7) and
| found out that it is time to reorganize data again: 70% of migrated
| records (spanning 2 blocks), lots of non-contiguous extents, large
| fragmentation, index stagnation etc.
|
| Using
| "alter table <table_name> move" and
| "alter index <index_name> rebuild parallel recalculate statistics
| nologging"
| gets the segment rewritten but it does not eliminate fragmentation
| *between* the segments. In order to do that, one would have to take
| all tables and indexes out of their current tablespace into a kind of
| scratch tablespace, then back again.
|
| Well, this would take about one week or longer. So I am wondering if I
| could just add a new file to the same tablespace and move the objects
| to this file, so they could just stay there and there would not exist
| any need to move then back. Selecting which objects are contained in
| one specific file can be done joining dba_extents and dba_data_files
| through the file_id. But I just don't know how to target a specific
| file while moving objects.

Non-contiguous extents? Do you mean that extent 2 is not allocating the blocks beside extent 1?
Unless you are speaking about - lets say - over 1000 extents for a table/index in a Dictionary managed tablespace (DMT): I don't see why that's a problem. It's a myth.
You can find many good scripts to detect when indexes need to be rebuild (index browing) (like www.ixora.com.au)

To my knowledge you can't say in with datafile (or blocks) the table must allocate space. You can only specify a tablespace. My advice would be to rebuild the tables into another tablespace. And make that tablespace a Locally managed tablespace (LMT). You can search this newsgroup for discussions about DMT versus LMT. I personally have good experiences with LMT. A table/index with 10.000 extents is no problem in a LMT (although I still would try to keep a segment <100 extents). I should consider to change some other storage parameters too, like pctfree/pctused to avoid row chaining/migration in the future. You need to make some calculations.

Visit www.orapub.com and download Technical paper #104 Oracle 7 Server Space Management (free registration). Although it covers V7 it is still valid (maybe not on details). It gives a very good calculation of the extra IO involved for a full tablescan of a table when it has allocated 5 or 1000 extents. The proposed space management strategy is now in fact enforced with LMT.
|
Received on Sun Apr 06 2003 - 11:37:19 CDT