Skip to Content
avatar image
Former Member

Table increase with reorganization

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

6 Answers

  • avatar image
    Former Member
    Jun 29, 2009 at 09:41 AM

    Hi,

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

    Thanks

    Sunny

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 29, 2009 at 10:21 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • avatar image
    Former Member
    Jun 29, 2009 at 11:52 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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

  • avatar image
    Former Member
    Jul 11, 2009 at 07:37 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 12, 2009 at 06:56 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • avatar image
    Former Member
    Jul 14, 2009 at 07:08 PM

    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

    Add comment
    10|10000 characters needed characters exceeded