a heated discussion between Development Team & "a" DBA: reorg or not to reog

I have an ever growing discussion with a development team regarding whether the regorg (export/import) will improve the performance of nightly batch jobs or not.

First the history:

The following is what happened several times in a cycle for last several years till 2005:

1. App. nightly batch job runs fine
2. Over time, 3-4 months, nightly batch starts running slow
3. Nightly batch runs really slow down and starts getting out of the time frame allocated to it.
4. Development team asks DBA to reorg the database.
5. DBA takes a full export. Tries to drop all the tables but for 'some reason' it takes awfully long to drop tables and he blows up whole database and recreates it. Then imports the data back into database.
6. Go to step #1

The last time these steps were performed was during upgrade from 8i to 9i in September, 2004. The tablespaces are all DMT.

Then after the upgrade, I started working on the database, I noticed that the biggest tables have tiny next extent sizes, say 4K, and after sometime, they get tens of thousands of extents. That's why nightly batch was running slow, since the batch is inserting rows into these tables and every extent allocation was taking minutes. The number of extents was also reason why the DBA couldn't drop the tables in a reasonable time.

I quickly identified these tables and set the next extent size to a bigger value so that the number of extents is kept in a reasonable amount. I even did ALTER TABLE MOVE to get rid of in-table fragmentation. The table with max number of extents had around 40 extents.

Meanwhile, the development team tells that the nightly job is again gradually running slow. Gets slower and slower 'slowly'.. They also claim that the amount of data the database nightly job did not change since last reorg. Since I don't have table size stats taken in September,2004 I can not make any comment on that.

I turned on the tracing on the nightly job and found that a DELETE doing tablescan is taking most of the time and when an index is created, the exec time is cut down to half in test. So, I recommended creating index.

But the development group, used to experience the performance 'improvement' after each reorg several times before, did not want to create index and they insisted to do the reorg even I spent hours explaining the whole story; why the reorg helped in previous cases and why it won't help this time. And I was really sick and tired of hearing 'every time we did the reorg, we saw blah blah .. '.

Anyway, they took the business on their side, and I had to cave in: I did the export/import couple of weeks ago, which didn't improve the performance even a bit.

They are so used to see the performance boost after each reorg, now I'm the bad DBA, in their eyes, I'm seen as a DBA who couldn't improve the performance.

In last meeting when we were talking about the reorg, they asked how I did the reorg and I explained the steps. Export database/drop users/import database.

Now this gave them another argument: now they're claiming that my reorg was not like previous reorgs because the previous reorgs used to delete the database and create it from scratch then do full import.

This time I just ran out of my patience and I just don't want to waste my time to convince them. Their manager even mentioned about something called 'OS file fragmentation' that may be causing the slowness, even if I explained the fact that the space for database files is allocated when they are created and they don't move around the disks as the database runs...

But no hope. I'm just grasping at straws here.. I'd like to hear any experience about such thing called SYSTEM tablespace fragmentation if exists.. any case where recreating tablespace helped improve the performance.. or some strong arguments that can shut them up for good..

i.d. Received on Thu Jun 23 2005 - 13:18:32 CDT