Oracle Database Reorganization

[Former Member]

Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.8.0 - Production

System: Microsoft Windows Server 2003 Enterprise Edition

-

---

Hello everyone. I have read all the threads on this forum concerning Oracle database reorganization and a few SAP notes and I have a few questions. I guess my first question is how many of you actually do a database re-org? If you do how many times do you run a re-org on a, daily, bi-weekly, monthly, yearly? Believe or not I actually do a database online table re-org on all my servers monthly using BRSPACE on our main tablespaces (in R/3 production this would be PSAPDAT tablespace) . Also, on my production servers I generate a report on the top DB objects identified through transaction ST14/Basis & Others. I re-org these objects using a third party tool from Quest called Space Manager (with LiveReorg) daily.

We find that we can better control our freepace on the actual tablespace specified. For example if PSAPDAT tablespace is showing 99.8% freespace used, running a re-org will some time drop this down to 90.5% or even lower.

I have found a good article Boost SAP R/3 Performance by Reorganizing Your Oracle Database: A Proven Reorganization Strategy by Charles Davis. This article appeared in the July/Aug 2005 issue of SAP Professional Journal.

http://www.quest.com/Quest_Site_Assets/News/SAPPro_JulAug05_LiveReorg.pdf.

The only problem that I am running into when a do a monthly re-org on my R/3 environment using BRSPACE is that it’s taking way to long to complete. This is after BRSAPCE skips all tables having LONG (RAW) columns. My last re-org on R/3 took 14 hours to complete. So now I am wondering if it’s worth doing my monthly re-org or just keep my daily re-org going on the few top tables identified through ST14. Your feedback would be greatly appreciated.

Regards

Billy

[former_member293883]

Hi

How do you handle the huge archive log generation during the online table re-org activity. What option do you select when you are taking a backup of the archive logs...this has to be taken as the space in the oraarch folder decreases during the reorg

Regards

Partha

[Former Member]

Thank you for your input and remarks. Just to clarify I don't do a complete database reorg, only on the main tablespace tables. You all have valid points about not doing them on a schedule. Our tablespaces are all locally managed tablespaces not dictionary managed tablespaces as Eric has stated. I will keep in mind what mho has suggested as when to re-org. We will be changing our strategy on this one...

Thanks again everyone for your input.

Billy

[former_member204746]

If you are still using dictionary managed tablespaces, reorg isa good idea. That `s what I did for all tablespaces except the SYSTEM tablespace. I also activated ASSM.

from Oracle 9, you can reorg online

and from Oracle 10g, you can even rename a tablespace (and its datafiles).

to do the work, I simply followed SAP note 646681 and was able to reorg everything online except for tables with LONG and LONG RAW filed types. Theese must be reorged offline.

Quest's LiveReorg enables online reorg for tables with filed types LONG and LONG RAW and makes the whole work easier, but it is expensive. I work for a small company and decided it was too costly... and they are right.

Once a full reorg has been completes with LMTS tablespaces and ASSM, the need for future reorgs becomes almost non-existent.

[Former Member]

Hi Billy

We almost never do reorgs on a periodic schedule. We only reorg objects when:

- a high percentage of rows were deleted in a table AND they will not be inserted back again

- we have a performance issue, for example because of a high clustering factor (table to index sort factor), then we rebuild the object ordered by a given index

- in very rare cases on high troughput tables an index needs to be rebuilt from time to time

- we want to migrate an object to another tablespace, migrate from dictionary to locally managed tablespace, from long to lob

Otherwise reorgs are a waste of time and ressources in my opinion.

Best regards

Michael

[stefan_koehler]

Hello Billy,

> If you do how many times do you run a re-org on a, daily, bi-weekly, monthly, yearly?

We never do a complete database reorg. At first our database is too big for that and the other point why should we do that? We only reorganize specific tables after some huge archive runs or delete actions.

You can also decrease the performance after the reorg, because of changed statistics.

My questions to you:

- Why do you reorg your database ? (only for space capacity - i mean disk space is cheap)

- Have you performance issues?

You tell us that your tablespace decreases from 99.8 to 90.5% .. the more interesting is: How big is your tablespace? I mean if it is just 200 GB - then the benefit is only round about 18 GB, you know.

Regards

Stefan