Skip to Content

BDLS IN LESS THAN 2 HOURS – Part 3

This is in continuation of my previous blog http://scn.sap.com/people/muniraju.h/blog/2012/06/01/bdls-in-less-than-2-hours–part-2

PART-3

UPDATE phase will be slower with indexes; particularly those indexes which are built on the the same fields that are being updated.

In other words, mass updates on the indexed fileds causes additional overhead for maintainance and updating of the those indexes. Such indexes are referred to as evil indexes.

Possible solution was to drop/ hide indexes having these fields before performing mass updates.

Step – 6

After creating bitmap indexes; first we ran RBDLS450 in test mode (check box – Test Run) in parallel as explained earlier in Part -1, Step – 1.

We then dropped the indexes and ran RBDLS450 in Conversion Mode, i.e. by removing above check boxes.


Achievement

BDLS completed in 8 hours. Not a great benifit though.

Observations

UPDATE phase used FULL TABLE SCAN

From previous BDLS runs, we were able to gather below statistics: –

Table

BDLS run time in hours

Table size in MB

MKPF

7.78

3253.19

SWW_CONTOB

6.28

4837.54

EDIDC

6.11

6766.91

MLHD

5.52

2786.75

RBKP

5.42

2972.29

VBRK

2.55

1029.07

VBAP

2.25

3333.66

ETXDCH

1.57

266.32

SMMAIN

1.44

96.08

SMPARAM

1.42

55.27

SMSELKRIT

1.41

234.27

We observed that these tables are not very big in size; the largest one is EDIDC with size 6.6 GB and it take surprisingly more than 6 hours to update.

Step – 7

With further googling around and reading several articles, we realized that CTAS (Create table as select) is the best method to perform mass updates.

We decided to test it; we executed below commad: –


CREATE TABLE EDIDC_BDLS_BKUP

PARALLEL COMPRESS FOR OLTP NOLOGGING

STORAGE ( INITIAL 3253M NEXT 50M MINEXTENTS 1 MAXEXTENTS UNLIMITED)

TABLESPACE PSAPNXP

AS

(

SELECT

MANDT, DOCNUM, DOCREL, STATUS, DOCTYP, DIRECT, RCVPOR, RCVPRT,

decode(MANDT||RCVPRN,’500S1P500′,’S1Q500′,RCVPRN) RCVPRN,

RCVSAD, RCVSMN, RCVSNA, RCVSCA, RCVSDF, RCVSLF, RCVLAD, STD,

STDVRS, STDMES, MESCOD, MESFCT, OUTMOD, TEST, SNDPOR, SNDPRT,

decode(MANDT||SNDPRN,’500S1P500′,’S1Q500′,SNDPRN) SNDPRN,

SNDSAD, SNDSMN, SNDSNA, SNDSCA, SNDSDF, SNDSLF, SNDLAD, REFINT,

REFGRP, REFMES, ARCKEY, CREDAT, CRETIM, MESTYP, IDOCTP, CIMTYP,

RCVPFC, SNDPFC, SERIAL, EXPRSS, UPDDAT, UPDTIM, MAXSEGNUM

From EDIDC

);

You may notice the lines highlighted above in the command. The oracle’s decode function performs the conversion (UPDATE).

The newly created table  EDIDC_BDLS_BKUP will have the S1Q500 replaced wherever S1P500 was found for fields RCVPRN and SNDPRN.

Another way of doing it is using CASE statement. It is more easier to build CASE statement than DECODE.

For example, In a scenario we had to convert 4  logical systems, we use CASE statement as below.

CREATE TABLE EDIDC_BDLS_BKUP

PARALLEL COMPRESS FOR OLTP NOLOGGING

STORAGE ( INITIAL 3253M NEXT 50M MINEXTENTS 1 MAXEXTENTS UNLIMITED)

TABLESPACE PSAPNXP

AS

(

SELECT

MANDT, DOCNUM, DOCREL, STATUS, DOCTYP, DIRECT, RCVPOR, RCVPRT,

   CASE LOGSYS

     WHEN ‘S1P100’ THEN ‘S1Q100’

     WHEN ‘S1P500’ THEN ‘S1Q500’

     WHEN ‘BWP200’ THEN ‘BWQ200’

     WHEN ‘PIP300’ THEN ‘PIQ300’

     ELSE  LOGSYS

   END LOGSYS,

RCVSAD, RCVSMN, RCVSNA, RCVSCA, RCVSDF, RCVSLF, RCVLAD, STD,

STDVRS, STDMES, MESCOD, MESFCT, OUTMOD, TEST, SNDPOR, SNDPRT,

decode(MANDT||SNDPRN,’500S1P500′,’S1Q500′,SNDPRN) SNDPRN,

SNDSAD, SNDSMN, SNDSNA, SNDSCA, SNDSDF, SNDSLF, SNDLAD, REFINT,

REFGRP, REFMES, ARCKEY, CREDAT, CRETIM, MESTYP, IDOCTP, CIMTYP,

RCVPFC, SNDPFC, SERIAL, EXPRSS, UPDDAT, UPDTIM, MAXSEGNUM

From EDIDC

);

To our surprise this CTAS operation on EDIDC of 6.7 GB took only 12 seconds.

Then we dropped indexes on original table EDIDC.

Renamed original table EDIDC to EDIDC_ORIG.


rename EDIDC to EDIDC_ORIG;

Renamed newly created table EDIDC_BDLS_BKUP to EDIDC.

rename EDIDC_BDLS_BKUP to EDIDC;

Rebuild indexes from SE14 > EDIDC > Indexes > Create in Background

The total operation took less than 5 minutes for EDIDC , whereas BDLS conversion took 6 hours.


Achievement

We were able to perform CTAS and conversion of 15 tables in 3 hours because CTAS command need to be manually constructed

BDLS run on remaining tables took less than 2 hours

Total conversion time is 5 hours

Observations

Constructing CTAS command is time consuming and prone to human error

  Building indexes from SE14 needs to be triggered manually

CTAS removed NOT NULL constrains from converted (DECODED) fields

Looking at the significant benefit (6 hours vs. 5 minutes),  we decided to adopt CTAS for all tables where rows to be udpate > 1000.

Step – 8

The rebuilding of indexes from SE14 do not use parallel, nologging options.

Some time can be saved if you build indexes from database level using parallel clause and then turning off the parallelism.

Before dropping any index, you can get the DDL (SQL) Command to build the index as below:-

set timi on

set echo off

set head off

set long 5000

set pagesize 0

set linesize 150

column DDL format A150

select DBMS_METADATA.GET_DDL(‘INDEX’,’EDIDC~0′) DDL from dual;

Add clauses NOLOGGING PARALLEL COMPUTE STATISTICS the command;

Once Index is built; turn off parallelism 

ALTER INDEX “EDIDC~0” NOPARALLEL;

NOT NULL constraints had to be added to table EDIDC for fields RCVPRN and SNDPRN.

ALTER TABLE “EDIDC” MODIFY RCVPRN NOT NULL;

ALTER TABLE “EDIDC” MODIFY SNDPRN NOT NULL;

Step – 9

We left the update statistics running in the background with below commands:-

brconnect -u / -c -f stats -t system_stats

brconnect -u / -c -f stats -t oradict_stats

brconnect -u / -c -f stats -t all -p 15 -f nocheck -f collect


In the next part of this blog I will summarize the steps and benefits.

http://scn.sap.com/people/muniraju.h/blog/2012/06/04/bdls-in-less-than-2-hours–part-4


3 Comments
You must be Logged on to comment or reply to a post.
  • Hi,

    Interesting post, thanks for sharing.

    Watch out with CTAS; two drawbacks :

    - As you stated it is not copying index & constraints (for this you could use Oracle function

    DBMS_METADATA.GET_DEPENDENT_DDL)

    - when you set parallelism with DDL statments you should reset it after or it is kept for the segment (seen note 651060 - FAQ: Oracle Parallel Execution)

    So you should issue :

    alter table sapsr3.edidc noparallel;

    • Hello Yves,

       

      Thanks for Your feedback.

      Yes, DBMS_METADATA.GET_DEPENDENT_DDL can be used.

      I observed NOT NULL constraints were removed.

      I have updated this article to add NOT NULL constraints.

           ALTER TABLE "EDIDC" MODIFY RCVPRN NOT NULL;

      Also, I have included lines.

          Once Index is built; turn off parallelism

          ALTER INDEX "EDIDC~0" NOPARALLEL;

      Kind Regards,

      Muniraju

  • 1st of all thanks for putting all things together in a nice way.

    Do you have (rememeber) runtimes for index rebuilds?

    Also, did you find a way to safeguard against the human error prone CTAS procedure?

    especially if an SPS/EHP upgrade may chaneg the tables.