Oracle 11GReorg and Compression

[Former Member]

We want to perform online reorgnization for one of the tablespaces (Total size 4.5 TB, Free 2 TB) for our BI Landscape.

System Details are:-

BI 7.01
Oracle 11.2.0.2

I've following questions:-

1) How much addtional free disk space I would need for conducting above activity?
2) What precaustions / critical pre-requisites?
3) Can I perform online DB compression and DB reorg at one go? If yes is it recommended?
4) What would be the impact of the activity on the system performance?

Kindly share yor experience?

Regards,

Vishal

[stefan_koehler · Accepted solution]

Hi Vishal,

> 1) How much addtional free disk space I would need for conducting above activity?

Depends on your system and the fragmentation (which is usually pretty low in an OLAP system regarding to an OLTP system). From my experience with OLAP systems the compression factor is pretty good in general (60 - 70 %). The free space should be enough, because you already gain space back while reorganization.

> 2) What precaustions / critical pre-requisites?

Correct BR*Tools, SAP version. Check SAPnote #1436352 for details.

> 3) Can I perform online DB compression and DB reorg at one go? If yes is it recommended?

Yes and yes. Check SAPnote #1431296 for details.

> 4) What would be the impact of the activity on the system performance?

Depends on your I/O subsystem and hardware. However it is a little bit tricky in an OLAP system, because of constant generation of new database objects like partitions, tables, etc. ORA-14646 errors are pretty common, if you don't take care.

Regards

Stefan

[Former Member]

Hi Vishal

>> 1) How much addtional free disk space I would need for conducting above activity?

You need approximately 1/6 size of the table during the activity, per table. This will be temporary area. At the end of the operation, you need to drop the old tablespace, if you performed tablespace reorganization. If it is a table reorganization, the old table will be dropped automatically and the system start to use newly created compressed table. So you will not be in charged to drop old objects like tablespace reorganization.

>> 2) What precaustions / critical pre-requisites?

You can perform an online reorganization. At this stage, you should take into account that the source and target tablespaces must be exist at the same time. For more information check the Note 541538 - FAQ: Reorganization and read the 14th question. Additionally from BI point of view, since a new tablespace has been created, the BI objects should be moved to the new tablespace. Please refer to the Note 771191 - Copying BW objects to new tablespaces.

On the other hand as Oracle 10g release, you can rename the tablespace easily and avoid to move new tablespace name that I noted above about BI objects. But, please note that during this operation because of the tablespace names are different, you may face with some problems at the BI side. Please check the note Note 646681 - Reorganizing tables with BRSPACE and search for "IV. Tablespace reorganization with BRSPACE as of Oracle 10g"

From Oracle perspective the system will be available, but from BI point of view because of the tablepace names are the different, you may face some troubles.

If you are planning to perform the reorganization table by table, you don't need to create a new tablespace. So, you don't need to consider the items above.

>> 3) Can I perform online DB compression and DB reorg at one go? If yes is it recommended?

Yes, it is possible. Do not forget to alter the tablespace attribute to compress, before the compression. Because only new objects will be compressed, even if you set the compress on tablespace attribute.

>> 4) What would be the impact of the activity on the system performance?

During the operation the system will generate many offline redolog files. So, you should check the /oraarch folder. From performance point of view, substorage I/O performance will play big role on this case. Because, the system will move huge amount of data from a tablespace to another tablespace. There are some recommendations, in order to minimize this activity with the brspace. The redolog activity will be done regardless you choose table tablespace or table reorganization.

On the other hand, I recommend you read the article below, especially pitfalls and problems about PSAPUNDO and PSAPTEMP tablespaces;

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/70cf010d-0b01-2d10-86a2-a0636c39a...

Another suggestion might be you perform a reorganization on the test system with the same hardware and see the estimated result on it.

Best regards,

Orkun Gedik

[Former Member]

Thanks Stefan and  Orkun for your helpful suggetions, I'll go through them and get back to you in case of any doubts.

Regards,

Vishal