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

No matter how long I am in this business, there is always a surprise. I have never heard of taking the drastic step of recreating a database on a regular basis to improve performance. If your developers are telling you to do that because it happened to work once or twice, then they are nuts. Sometimes it helps for us DBA's to display a little attitude. I would challenge them to cite one Oracle expert or an Official Oracle white paper that suggest this as an ongoing practice. I am pretty sure they (or anyone else) will find corroborating evidence to suggest this is a good practice.

Unfortunately, there are many things that could be causing the degradation that is being corrected by the rebuild. Listed below are some of the more obvious things that you could check. The reason I cite these specific actions is because they are usually fixed by an import.

Gather Statistics - If you already do this on a regular basis, then that's good. If not, you should consider a DBMS_JOB or CRON to analyze your data regularily.

Rebuild Indexes - Indexes get fragmented very easily with a lot of delete's going on. In addition, they will also grow uncontrollably since Oracle does not reuse the space occupied in an index after a delete.

Kill the Developers - Actually, you don't need to kill them, just turn the tables on them. I would begin capturing the SQL that occurs during the batch process and monitor it over time. By turning on sql trace you can do this fairly easily. Try to do it just for only the batch session instead of the entire database. You can then run TKPROF after the jobs complete and review the TKPROF output. I would be EXTREMELY surprised if all of their SQL was optimized. Bad SQL is normally the culprit behind jobs that run forever. My experience has taught me that tuning the database (i.e. memory, IO, data placement, etc) will get you some improvement. However, that type of tuning pales in comparison to SQL tuning. You can often (well at least sometimes) get a several thousand percent boost in performance by tuning bad SQL. You will rarely ever get more than 10 -25 percent boost by doing everything else combined. If you can show the developers (and the business) the error of their ways, you will become the hero, gain considerable credibility and shut down the naysayers in the process.

Don't worry about the system tablespace - As long as you are not creating user or application objects in the system T.S. (and you are NOT doing that, correct) you should not have anything to worry about. Also, you should not be analyzing objects in the system tablespace.

Hope this helps.

ibodogan wrote:
> 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 - 15:31:07 CDT