Help for Job BDLS - Logical System Conversion aftrer System Refresh

[Former Member]

hi SAP Basis Gurus,

We have restored production data to test system. After that we are doing Logical System conversion - Renaming of Logical System Names. For that we are running BDLS job. The program used is IBDLS2LS.

Now one job is running for more than 200000 seconds. Job is doing "Sequential Read" on table ZARIXCO40. The following has been updated in the jobs log:

01.02.2008 10:20:06 Processing table ZARIXCO40

02.02.2008 01:59:56 Processing table ZARIXCO40

02.02.2008 22:26:44 Processing table ZARIXCO40

02.02.2008 23:04:05 Processing table ZARIXCO40

So as per above log, it looks like job got stuck in loop for table 01.02.2008 10:20:06 Processing table ZARIXCO40.

The oracle Session is showing following SQL Statement:

SELECT

/*+

FIRST_ROWS

*/

*

FROM

"ZARIXCO40"

WHERE

"MANDT" = :A0 AND "LOGSYSTEM" = :A1 AND ROWNUM <= :A2#

~

Execution Plan is :

~

Execution Plan

Explain from v$sql_plan: Address: 00000003A2FD5728 Hash_value: 2772869435 Child_number: 0

SELECT STATEMENT ( Estimated Costs = 4.094.270 , Estimated #Rows = 0 )

3 COUNT STOPKEY

2 TABLE ACCESS BY INDEX ROWID ZARIXCO40

( Estim. Costs = 4.094.270 , Estim. #Rows = 32.799.695 )

1 INDEX RANGE SCAN ZARIXCO40~0

( Estim. Costs = 418.502 , Estim. #Rows = 65.599.390 )

Search Columns: 1

~

Table has been analyzed today. See the details below:

~

Table ZARIXCO40

Last statistics date 02.02.2008

Analyze Method mple 16.623.719 Rows

Number of rows 65.599.390

Number of blocks allocated 2.875.670

Number of empty blocks 7.912

Average space 934

Chain count 0

Average row length 313

Partitioned NO

UNIQUE Index ZARIXCO40~0

Column Name #Distinct

MANDT 1

KOKRS 14

BELNR 2.375.251

BUZEI 1.000

~

Index used for this table is also analyzed today:

~

UNIQUE Index ZARIXCO40~0

Column Name #Distinct

MANDT 1

KOKRS 14

BELNR 2.375.251

BUZEI 1.000

Last statistics date 02.02.2008

Analyze Method mple 24.510.815 Rows

Levels of B-Tree 3

Number of leaf blocks 418.499

Number of distinct keys 65.480.722

Average leaf blocks per key 1

Average data blocks per key 1

Clustering factor 40.524.190

~

Can you please let me know what could be the issue and how to resolve this issue so that job gets completed. Normally this job runs for 1,00,000 seconds. I can not afford to cancel this job and run again.

Any help is Highly appreciated,

Thanks

Best Regards,

Basis CK

[Former Member]

I have created new index on field "MANDT" + "LOGSYSTEM". This has completed the jobs faster. Issue resolved. thanks to all for their greate help.

[Former Member]

Hi Markus,

the job is still running. I have given 1.000.000 Max record for update. If I cancel the job and run it again, I m not sure it wll go thru or stuck in same table. I have to give system to users tomorrow.

Can you please suggest if any index need to be created looking at this SQL statement which might increase the speed and make it faster.

~

SELECT

/*+

FIRST_ROWS

*/

*

FROM

"ZARIXCO40"

WHERE

"MANDT" = :A0 AND "LOGSYSTEM" = :A1 AND ROWNUM <= :A2#

~

UNIQUE Index ZARIXCO40~0

Column Name #Distinct

MANDT 1

KOKRS 14

BELNR 2.375.251

BUZEI 1.000

~

Thanks

Best Regards,

Basis CK

[Former Member]

I have given 1.000.000 Max records for update. That is the default value we gave in the past also.

Also if this process table multiple times,why not all other tables and only this table ?

Thanks

Best Regards,

Basis CK

[Former Member]

Hi Markus,

The stastics of the table is already updated today. After that also it is not going faster. It is doing "Sequential Read" for more than 5 hours and then one update is issued. And in the job log, all other tables are preocess only once, only this table is selected to processed 4 times which is very abnormal.

01.02.2008 07:43:59 Processing table ZARIXCO11

01.02.2008 07:45:36 Processing table ZARIXCO16

01.02.2008 10:17:20 Processing table ZARIXCO21

01.02.2008 10:20:06 Processing table ZARIXCO26

01.02.2008 10:20:06 Processing table ZARIXCO29

01.02.2008 10:20:06 Processing table ZARIXCO33

01.02.2008 10:20:06 Processing table ZARIXCO37

01.02.2008 10:20:06 Processing table ZARIXCO40

02.02.2008 01:59:56 Processing table ZARIXCO40

02.02.2008 22:26:44 Processing table ZARIXCO40

02.02.2008 23:04:05 Processing table ZARIXCO40

~

I guess this will keep on going like this and not sure when it gets complete. Any other thing you can suggest which help to resolve the issue.

Thanks

Best Regards,

Basis CK

[markus_doehr2]

It seems, that this table contains more entries than the commit value you gave in BDLS and thus the table is read multiple times...

How many entries you gave for each commit?

Markus

[markus_doehr2]

You can try to update the statistics on the table so the next select on the next block will may be run faster.

Since that table seems to be HUGE and all entries need to be modified (in terms of the logical name), you will need to wait until it´s finished.

Markus