Table increase with reorganization

[oxya_oxya2]

Hello,

We are experiencing troubles with online reorganization of tables on our BI7 with oracle 10.2.0.4 with brspace since the size of our tables after the reorganization is bigger than before.

Our SAP system is at the following level:

Software component Release level

SAP_ABA 700 14

SAP_BASIS 700 14

ST-PI 2005_1_700 6

PI_BASIS 2005_1_700 14

SAP_BW 700 16

FINBASIS 600 7

BI_CONT 703 8

SEM-BW 600 7

ST-A/PI 01K_BCO700 0

The system is running under HP-UX B.11 at the release B.11.23 and with a kernel at the level 150.

The oracle patch 10.2.0.4 has been set thereu2019s one month ago.

We did not experienced that problem before the setup of the new oracle patch.

These are the parameters for one of our table, we reorganized and for which the table increased after the operation:

Options for reorganization of tables: SAPR3./BIC/EZDAYSLS1 (degree 1)

1 ~ New destination tablespace (newts) ........ [PSAPUSEREORD]

2 ~ Separate index tablespace (indts) ......... [PSAPUSEREORI]

3 - Parallel threads (parallel) ............... [1]

4 ~ Table/index parallel degree (degree) ...... []

5 - Create DDL statements (ddl) ............... [yes]

6 ~ Category of initial extent size (initial) . []

7 ~ Sort by fields of index (sortind) ......... []

8 - Table reorganization mode (mode) .......... [online]

Standard keys: c - cont, b - back, s - stop, r - refr, h - help

As a result, the table increased from 8 413 184 KB , 1 051 648 blocks and 287 extents, with 10% of free in used blocks to 10 071 616 KB, 1 258 952 blocks and 285 extents with 0% of free in used blocks.

As we have to get some place into our database in order to prepare future increases of tablespaces in our production system, do you know what we do wrong or what need to be changed?

Thanks

[Former Member]

It is normal that E fact tables are larger after an online reorganization because they have no primary index. As a consequence the ROWID based reorganization is used and a (finally hidden) ROWID column is added to the target table. This can result in a space increase of up to 30 %. Therefore I would use other reorg approaches like MOVE ("-m offline" option of BRSPACE) for F and E fact tables whenever possible.

Kind regards

Martin

[Former Member]

Hello,

please update the message with the output of the statement

select tablespace_name, extent_management, segment_space_management

from dba_tablespaces

where tablespace_name in ('PSAPUSEREORD', 'PSAPUSEREORI')

so that we can check whether this could be related to the used extent management.

Best Regards,

Michael

[Former Member]

Hello,

This is the select result:

SQL> select tablespace_name, extent_management, segment_space_management from dba_tablespaces where tablespace_name in ('PSAPUSEREORD', 'PSAPUSEREORI');

TABLESPACE_NAME EXTENT_MAN SEGMEN

-

---

-

---

-

---

PSAPUSEREORD LOCAL MANUAL

PSAPUSEREORI LOCAL MANUAL

SQL>

Christophe

[Former Member]

before doing this activity run Database Statics and rebuild indexs as well. u can run online tabel reorgnization as well.

before doing such type of activity do not forget to take any type of backup sa well.

Thanks

[oxya_oxya2]

Hello,

There's nothing into the log about the drop table step. Nevertheless, as the reorganization has been performed with a move into another tablespace, the drop of the table into the old one does impact the size of the table into the new tablespace.

Christophe

[former_member418094]

Hello,

two questions here:

1. what's the type of your tablespaces(ASSM etc) for old and new?

2. how you caculate the size and free space from the table? By dbms_space?

--->As a result, the table increased from 8 413 184 KB , 1 051 648 blocks and 287 extents, with 10% of free in used blocks to 10 071 616 KB, 1 258 952 blocks and 285 extents with 0% of free in used blocks.

Regards,

Mike

[oxya_oxya2]

Hello,

The SAP note 48180 is related to an oracle release 7 to 9. Currently we have oracle 10.2.0.4 and compared to the note we perform the reorganization with brtools (brspace) and not with sapdba.

Our table in exemple at the beginning of this thread has been enlarged of quite 2GB.

Christophe

[anindya_bose]

Hi

I am not sure but is BRTOOLS skipping drop table step after moving it to new tablespace? Can you please verify with the reorganization log?

You can manually do reorganization from command prompt. Procedure is described in OSS 646681 - Reorganizing tables with BRSPACE

Check section IV,

Thanks and Regards

Anindya

[sunny_pahuja2]

Hi,

check SAP Note 48180 - SAPDBA: Enlarge Used Space in reorganization

Thanks

Sunny