Skip to Content
author's profile photo Former Member
Former Member

Oracle Database Reorganization

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • Posted on Apr 07, 2008 at 08:38 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Apr 08, 2008 at 10:50 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Apr 08, 2008 at 12:47 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Apr 08, 2008 at 01:15 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 05, 2009 at 09:27 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.