Skip to Content

BDLS IN LESS THAN 2 HOURS – Part 2

This is in continuation of my previous blog http://scn.sap.com/community/it-management/alm/blog/2012/06/01/bdls-in-less-than-2-hours–part-1

PART-2

Again Based on the tips from blog http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/4796 , we wanted to optimize it more by building indexes.

Before building indexes, its benificial to understand how BDLS works.

BDLS runs in phases:-

    1.  It will first determinies all tables that are using logical system in one of its fields.

    2.  It will then find out all tables that need conversion, by running a SELECT SINGLE command on all tables.

For example;

SELECT /*+ FIRST_ROWS(1) */ LOGSYSP FROM COEP WHERE MANDT=500 AND LOGSYSP = ‘S1Q500’ AND ROWNUM <= 1;

         means it has to just find one record to mark the given table as update required (i.e. BDLS conversion required).

    3.  Update command is run to modify the logical system value. For e.g. S1P500 to S1Q500.

UPDATE  “MKPF” SET   “AWSYS” = ‘S1Q500’ WHERE   MANDT = 500 AND “AWSYS” = ‘S1P500’ AND ROWNUM <= 100000;

Phase 2 and 3 above are the most time consuming ones.

Phase 2….SELECT SINGLE has two outcomes;      a)      ROW found              b)       ROW not found.

     a) When ROW is found; the response time is directly propotional to the location of the satisfying row.

     b) When ROW is not found; SELECT has to transverse through entire TABLE or INDEX only to find no satisfying row. (FULL TABLE/ INDEX SCAN).

Unfortunately, most of the tables under BDLS falls under b), because the LOGSYS fields have nothing (SPACE) in it.

Step – 3

Based on the tips from blog http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/4796 , we wanted to build indexes.

To build indexes, we needed to identify all those tables contributing to high run time on BDLS.

How to identify tables?

Reading through the code in report RBDLS2LS we could get the list of tables BDLS would run through.

SELECT * FROM dd02l INTO TABLE t_dd02l
FOR ALL ENTRIES IN t_field_ls
WHERE tabname = t_field_ls-tabname
AND as4local = tab_activ
AND tabclass = tab_class.

SELECT * FROM dd02l APPENDING TABLE t_dd02l
FOR ALL ENTRIES IN t_field_pn
WHERE tabname = t_field_pn-tabname
AND as4local = tab_activ
AND tabclass = tab_class.

Equivalent Oracle SQL is:-

SELECTa.tabname, FROM dd03l a, dd02l b

WHERE a.domname in (‘LOGSYS’,’EDI_PARNUM’)

AND a.as4local = ‘A’

AND a.tabname = b.tabname

AND b.as4local = ‘A’

AND b.tabclass = ‘TRANSP’

Below thoughts came to our minds for selecting tables for indexing:-

  • Top 15 tables based on size
  • Top 15 tables based on total number of rows
  • All tables of size greater than 5 GB
  • All tables where number of rows > 10000

We choose to go with “all tables where total number of rows > 10000”, because BDLS runs SELECT SINGLE command; and most of the tables fall under category b) as explained above and performing a FULL TABLE SCAN.  Therefore most of the run time depends on the total number of rows in the table.

Now we had to count number of rows in each table to finalize the tables to build indexes.

Instead of using time consuming SELECT COUNT(*), we decided to use data from the column NUM_ROWS in the table USER_TABLES.

We than created a seperate tablespace PSAPBDLS of 50 GB.

Created  80 indexes on client field (like MANDT) and logical system (like LOGSYS).

NOLOGGING PARALLEL COMPUTE STATICS was used to build indexes.

An example command is given below:-

CREATE INDEX COEP_BDLS_IDX_12345 ON COEP(MANDT, LOGSYSO)

NOLOGGING PARALLEL TABLESAPCE PSAPBDLS COMPUTE STATISTICS;

We also enabled parallelism on tables and other indexes as well.

                ALTER TABLE COEP PARALLEL;

                ALTER INDEX “COEP~0” PARALLEL;

We then ran RBDLS450 again in parallel (A*….Z*) as explained in Part -1, Step – 1.

Achievement

No significant benifit. Just 1 hour gain. BDLS completed in 10 hours

Observations

SELECT SINGLE phase completed quickly

UPDATE phase broke down taking very long time

Building 80 indexes was very time consuming task taking 5 hours

80 Indexes took 50 GB of space

Step – 4

We automated by writing some sql scripts (which will be explained in later parts):-

  • Identifying tables where now_rows > 10000
  • Identifying client filed (like MANDT, CLIENT, RCLIENT,etc)
  • Identifying logical system field (like LOGSYS, LOGSYSP, SENDPRN, RECVPRN, etc)
  • Building 80 indexes on client and logical system field in parallel taking now 2 hours.

Step – 5

We tried with building REVERSE key index; with no significant benifit. The run times remained more or less the same.

CREATE INDEX COEP_BDLS_IDX_12345 ON COEP(MANDT, LOGSYSO) REVERSE NOLOGGING PARALLEL TABLESAPCE PSAPBDLS COMPUTE STATISTICS;

Step – 6

With further studies we found that Oracle Bitmap indexes works better than B-tree indexes for columns with only a few distinct values (just MALE, FEMALE for example). They are compressed, very compact and fast to scan.

MANDT contains very few distinct values and also LOGSYS realted fields usually contains SPACE or very few distinct values. This characterstic makes them ideal candidate for bitmap indexes.

We created bitmap indexes instead of regular indexes.

An comparions of size and access time is shown below:-

Activity on COEP

Regular Index

Bitmap Index

Create index in parallel, nologging

2 Min 25 Sec

1 Min 32 Sec

Size of Index

3425 MB

53 MB

Select query run time

16 Seconds

1.25 Seconds

Below is an example statement used to build bitmap index.

CREATE BITMAP INDEX COEP_BDLS_IDX_12345 ON COEP(MANDT, LOGSYSO)

NOLOGGING PARALLEL TABLESAPCE PSAPBDLS COMPUTE STATISTICS;

Achievement

80 indexes took only 1 GB of space

Building 80 indexes now took 30 minutes only with automation using sql scripts

 

In the next part of this blog we will dive still more deeper to achieve BDLS in 2 hours.

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


4 Comments
You must be Logged on to comment or reply to a post.
  • Excellent work - looking forward to reading more.

     

    One question - have you considered the cost in space v the reduction in time, of having these indexes in the source instance, so that they are already in place in the target system before the BDLS step is performed ?

     

    thanks

    • Please read continuation parts of my blog.

      Yes, it is possible to have these indexes in source system.

      Space requirement in less than 5GB and time to build bitmap indexes is 30 minutes.

       

      Whereas, I have not tested if there are any negative effects of these indexes when created in our production (source) system.

       

      Kind Regards,

      Muniraju

  • For indexes, for example, I have the BDLSS report of one of the table as below.

     

    Do I create the indexes in 1 (combine field) or break to individual field?

     

    For only 1 indexes, do I need to follow the sequence of the field as below or any will do?

     

     

    /SAPHT/DRMDETL LS_EC_SO
    LS_PPCLMDOC
    LS_PPRVLDOC
    LS_REF_DOC_NO
    LS_SD_AGR
    • Oracle literature says, indexes with composite keys (multiple fields) in any order (sequence) are fine. Oracle is smart enough to pick right indexes. I suggest to create index with multiple fields (combine fields) and perform an explain plan for various select conditions and also note the runtimes. You will learn what is best in your case.

       

      Kind Regards,

      Muniraju