We are facing system slowness during Online Table Reorganisation. How to avoid it?

[Former Member]

We are using Oracle 12c for our SAP PO Production System (Netweaver 7.4). We are performing Online Table Reorganization for BC_MSG Table. The table size is 10 GB and associated LOB size is 210 GB for BC_MSG. We are preforming the following steps:

We have created a blank tablespace and we are moving BC_MSG table from its source table space to this newly created tablespace. Now, after movement to new tablespace, we will perform reorg and will bring back the table to its source tablespace post activity.

Now, we are facing the following issue:

1. During movement of table BC_MSG along with LOB Segment (Total 220 GB), the PO System became extremely slow and unusable. All the synchronous interfaces stopped working. The CPU and Memory utilization also became high. The movement of table also took much time. Production System faced outage due to that and we have to abort the activity.

Now, we have to perform the activity again without impacting the system availability. Can you please help us in this situation.

Thanks and Regards,

Anindya Kumar Dey (+919432668590)

[Former Member]

Hi Soumik,

Reorg can be done online and if you are only moving BC_MSG to new tablespace then you need to move it again back to it's original tablespace, So I assume it takes same time as moving entire tablespace to new. Moving all objects also enables or doing reorg for other objects which are residing in this tablespace. Obviously increasing CPUs can help in speeding up the process.

Regards,

Harish Karra

[Former Member]

Hi Harish,

Total Tablespace size is 650 GB and If you move 430 GB(All Tables - BC_MSG) to a new table space it will take longer time to accomplish the Reorg activity.

It is better tto increase the CPU core and run the SQL in parallel to move BC_MSG in new tablespace.

Regards

Soumik

[Former Member]

Hi Anindya,

What is the total tablespace size? if current table is holding the max size in this tablespace and all other tables are smaller, then just move everything into new tablespace by doing online reorg. It avoid duplicate effort in bringing it back to original tablespace.

You just need to create a new tablespace with <tablespace name>_NEW and do brspace tbreorg for this particular table and then all tables, at end of the activity just rename tablespace names and delete the original one (once everything is moved to new tablespace).

Regards,

Harish Karra