Long time reorganization table

[Former Member]

Hi Support

I tried to run the table reorganization of 128 GB, this run takes 15 hours.

Brtools run this reorganization, the options used were:

- brtools

- (3)

- (1)

- BRSPACE options for reorganization of tables

1 - BRSPACE profile (profile) ...... [initRQ1.sap]

2 - Database user/password (user) .. [/]

3 ~ Reorganization action (action) . []

4 ~ Tablespace names (tablespace) .. []

5 ~ Table owner (owner) ............ []

6 ~ Table names (table) ............ [GLPCA]

7 - Confirmation mode (confirm) .... [yes]

8 - Extended output (output) ....... [no]

9 - Scrolling line count (scroll) .. [20]

10 - Message language (language) .... [E]

11 - BRSPACE command line (command) . [-p initRQ1.sap -s 20 -l E -f

tbreorg -t "GLPCA"]

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

-

---

BR0662I Enter your choice:

BR1001I BRSPACE 7.00 (41)

BR1002I Start of BRSPACE processing: sehujakt.tbr 2012-02-01 16.11.59

BR0484I BRSPACE log file: /oracle/RD1/sapreorg/sehujakt.tbr

BR0280I BRSPACE time stamp: 2012-02-01 16.12.03

BR1009I Name of database instance: RD1

BR1010I BRSPACE action ID: sehujakt

BR1011I BRSPACE function ID: tbr

BR1012I BRSPACE function: tbreorg

BR0280I BRSPACE time stamp: 2012-02-01 16.12.08

BR0657I Input menu 353 - please enter/check input values

-

---

Options for reorganization of tables: SAPRD1.GLPCA (degree 1)

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

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

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

-

---

and its execution was 15 hours

Now this same process we have to do in production, the table has 329GB

GLPCA approx.

This table is archived run now do the reorganization is required to

restore the real space in the table.

I need to run this reorganization with the best option from Sete to avoid

the high execution time of 15 hours in quality, because on average

production would be 35 hours in such execution to this table and not so

far achieved the best way to try to reduce the execution time for this

process of reorganization.

Thanks for your attention, I look forward to your comments

Regards

[lbreddemann]

Hi Jose,

first of all: this is not SAP SUPPORT!

This is the SAP Developer Community and although employees from SAP support (like me) are answering questions from time to time it's not the official support offering and service from SAP.

Looking at your problem description, there are three points that fall into my mind:

- online reorg really is online. You can fully use the system while the reorg is running. The total runtime really doesn't matter that much - that is, you don't need to care whether it's 10 or 15 hours. The SAP system will be up and running all the time.

- online reorg is shovelling data from disk to buffer cache and putting it into new segments and back to disk again. That said, a large buffer cache will help do decrease the total runtime of an online reorg.

- more than that, parallelisation will usually heavily decrease the total runtime, but may also have a negative effect to the systems overall performance, since it will acquire more resources than a single thread reorg.

Bottom line is: check your cache size and set the parallel degree option to some number larger than 1, e.g. 5.

best regards,

Lars

[stefan_koehler]

Hey Lars,

online reorg is shovelling data from disk to buffer cache and putting it into new segments and back to disk again. That said, a large buffer cache will help do decrease the total runtime of an online reorg.

As far as i can remember - DBMS_REDEFINITION uses the APPEND hint and with Oracle 11g R2 the segment size and the cache size matters (direct path reads). The cache size can help of course (especially for creating the indexes on the interim table), but this depends on so much factors nowadays )

I wonder that you did not suggest HANA here ... hehe just kidding 😛

Regards

Stefan

[lbreddemann]

Hi Stefan,

you're right - the direct path insert (APPEND) is used and thus the buffer cache is bypassed for the pure data movement.

Concerning HANA - well, for simple stuff like avoiding reorgs I could just point over to MaxDB.

Never needed a reorg there 😄

And of course - HANA doesn't need it either.

cheers,

Lars

[Former Member]

Hi,

Here is a simple execution of an online table reorganization:

-- Check table can be redefined

EXEC Dbms_Redefinition.Can_Redef_Table('SCOTT', 'EMPLOYEES');

-- Create new table with CTAS

CREATE TABLE scott.employees2

TABLESPACE tools AS

SELECT empno, first_name, salary as sal

FROM employees WHERE 1=2;

-- Start Redefinition

EXEC Dbms_Redefinition.Start_Redef_Table( -

'SCOTT', -

'EMPLOYEES', -

'EMPLOYEES2', -

'EMPNO EMPNO, FIRST_NAME FIRST_NAME, SALARY*1.10 SAL);

-- Optionally synchronize new table with interim data

EXEC dbms_redefinition.sync_interim_table( -

'SCOTT', 'EMPLOYEES', 'EMPLOYEES2');

-- Add new keys, FKs and triggers

ALTER TABLE employees2 ADD

(CONSTRAINT emp_pk2 PRIMARY KEY (empno)

USING INDEX

TABLESPACE indx);

-- Complete redefinition

EXEC Dbms_Redefinition.Finish_Redef_Table( -

'SCOTT', 'EMPLOYEES', 'EMPLOYEES2');

-- Remove original table which now has the name of the new table

DROP TABLE employees2;

Regards,

Venkata S Pagolu

Edited by: Venkata Pagolu on Feb 17, 2012 1:48 PM

[lbreddemann]

Hi there,

running the DBMS_REDEFINITION package manually is clearly not what SAP customers should do on their databases.

For that we provide the BRTOOLS that take away many obstacles like correct syntax, data type check, primary key existence and so on from the DBA.

Simply put: if you need to ask HOW-TO questions here or in any other DBA forum, you really should only use the BRTOOLS and never touch SQL*PLUS.

regards,

Lars

[thiago_cavalheiro]

Hi Jose,

Make sure you have your database parameters set as recommended by SAP. Use the following note to test the settings:

[1171650 - Parameter checking for Oracle DB|http://service.sap.com/sap/support/notes/1171650]

Also, having your database on a recent SBP (SAP Bundle Patch) is strongly recommended.

Best regards,

Thiago

[Former Member]

Hi Jose,

You can use parallel threads during the reorg.

Check the note 646681 - Reorganizing tables with BRSPACE

The tables can be reorganized online in parallel using up to 15 parallel threads.
To do this, set the "-p|-parallel" option of the "-f tbreorg" BRSPACE function.

It may reduce the reorganization time.

Best regards,

Orkun Gedik