Re: fragmentation of a tablespace
Date: 1996/11/26
Message-ID: <329AF1CD.5ABA_at_compuserve.com>#1/1
Sean Kubovcik wrote:
>
> Kevin, I'm a new DBA, but I was looking at our defrag process and the prior DBA
> created a batch script that runs once a week and exports the entire
> database, drops all tables and imports the database. This seems to
> work, but it takes about 7 hours. Is this effective?
> Thanks,
> Sean
Why did [s]he do that? Does your data frequently get deleted and re-inserted during regular use? The biggest benefit of doing such data reloads comes from re-packing the data in blocks, not from reducing the number of extents. If you have the time to spend on weekly database rebuilds, you can do it, but the benefit derived from the rebuild depends on how volatile your data is.
[btw, are you re-issuing a CREATE DATABASE before the Import? Or using some other method to coalesce the free space in the tablespaces?]
For example, if your users frequently delete and update records, then over time, two things will happen: your indexes will grow in size (even if the table does not), and each data block will contain fewer records than it did at the start. Indexes don't reuse space previously used by since-deleted records. Data blocks in delete-intensive environments will eventually contain only PCTUSED worth of data (and since PCTUSED defaults to 40, that's not much data in a block).
If your application is Not delete or update intensive, and you're just inserting new records, then there's no benefit to your reorgs. Evaluate your current space usage, and plan for the tables to grow. Set a correct value for NEXT for your objects, and use PCTINCREASE 0 so the objects grow linearly.
Many systems never need to be defragmented. With proper planning, most systems need to be defragged no more than once a year, if that. Only the most volatile systems need the kind of defragging your former DBA put in place.
See Also: Advanced Oracle Tuning & Administration, Chapter 5.
Kevin.
Extent: (n.) A limit. Ex: "I've reached the extent of my patience with users."
Received on Tue Nov 26 1996 - 00:00:00 CET