Question about reorganization

[Former Member]

Hello,

So our next to-do to improve performance is the db reorganization. I'd like to ask you for some advice. We are thinking about creating a new tablespace, for example, PSAPSR3_A, and reorganize with BRSPACE the tables of PSAPSR3 moving them to the new tablespace: the no LONG OLRAW tables online with brspace and the others with import/export. We are not thinking about converting these tables to LOB, because of the performance, our big big problem.

After the reorganization, we will reorganize again to the original tablespace, that will be deleted and created again in the meanwhile.

We will do the same with the other tablespaces PSAPSR3700.

The aim of this movement is to achieve less fragmentation from a physical/disk point of view. Do you think we are right? Is this the usual way? Any suggestions?

Thanks and best regards,

Ana.

[Former Member]

Hello and thank you very much for your helpful answers.

Just if it helps anyone else:

I have reorganized PSAPSR3 and PSAPSR3700 with brspace online. There are no LONG or LRAW tables in these tablespaces, so it is not necessary to stop the system. At last, we decided to reorganized twice one to go to another tablespace and the other to go back to the initial tablespace.

Thanks again and regards,

Ana

[Former Member]

Hi Ana,

On which Oracle version are you? You probably can rename the new tablespace back to the old name. This will save you an enormous amount of time..

Kind regards,

Mark

[anindya_bose]

Hello Ana

I think your approach is fine. What I did in my case, I choose all those tables which were fragmented heavily instead of the whole tablespace and then reorg all those tables ( may be maximum 10-15) and that gave me 90% of the total benefit i could get from reorganiza the whole tablespace.

Please also have a look at the below notes if you haven't yet.

SAP Note 646681 - Reorganizing tables with BRSPACE

SAP Note 821687 - FAQ: Space utilization and fragmentation in Oracle

Regards

Anindya

[Former Member]

Hello all,

Anindya, Your solution is good, but how do you gain in fragmentation? from a disk point of view?

Mark, my oracle is 10.2.0.2 but will be 10.2.0.4 before reoganization. How can I rename the tablespace? Your idea is great!

Thanks and best regards,

Ana

[Former Member]

Check note 646681 - Reorganizing tables with BRSPACE

12. Renaming the new tablespace (as of Oracle 10g)

-

---

As of Oracle 10g, you can use the old names to rename the new tablespace:

brspace -f tsalter -a rename -t <new_tsp> -n <old_tsp>

Kind regards,

Mark Dijsselbloem

[anindya_bose]

Ana

Normally EWA report gives you information about the wastage space in particular table, it gives you top 20 tables where wastage is maximum.. In my previous post, 2nd SAP note has one SQL statement which will give you top tables according to space wastage. Wastage means, suppose you need 100 MB for data storage in that table but due to fragmentation 200MB is being used. Then 200-100=100 MB is wastage which you can gain from reorganization

Regards

Anindya