Reorganization of Oracle DB - SAP pros and cons

[Former Member]

If my DB is rather large but with mostly plant maintenance and A&D material data and performance has been a little slower, there is a discussion to do a DB reorg. However is seems that SAP does not recommend. We are running oracle 10.2.

Any experience with DB reorgs to increase performance and reduce fottprint would be appreciated.

Thanks

Mikie

[vince_laurent · Accepted solution]

I would reorg from Dictionary managed to Locally managed tablespaces. I have been doing that for our BW systems.

[former_member204746]

one more good reason for a reorg: if your tablespaces are still dictionary managed, a reorg is a good idea. the SAP note 646681 explains the process very well.

[Former Member]

Hi Mikie,

in most cases a reorg of the database is not necessary for a SAP system because it should make use of all its indexes instead of reading hole tables. But there are some situations where a reorg makes sense:

1. You started an archive project and you have already deleted big chunks from some tables, which will never grow that big. (You may only reorg those tables)

2. You deleted a client (with a considerable amount of data - not a copy of client 000) and you did not plan to copy it again, or you need its space temporarily.

If performance is your focus, you better reorganize (rebuild) indexes. You may run brconnect for update statistics, which will print out indexes to rebuild. This will be much less effort and have in most cases the same result that doing a full reorg. You may also analyze index structure to check how unbalanced they are.

Best regards

Ralph