BW Job - Selective data deletion of cube data(Job) running long

[Former Member]

Hi Gurus,

I have given for a selective deletion over a cube(compressed) for a small date range i.e. for just a month. Amazingly its running for a long time i.e. more than 12000 secs. The excat joblog is as below:-

09.02.2009 Dynamic List Display 1

-

---

Job log overview for job: BI_INDXCW7IUHHQXAO2EHW5D2F8MGC5Q / 02332500

-

---

Job log Time Message text uncoded Message ID Msg.no.

-

---

09.02.2009 02:33:25 Job started 00 516

09.02.2009 02:33:25 Step 001 started (program RSINDEX1, variant &0000000000493, user ID 9PRASANN) 00 550

09.02.2009 02:33:30 SQL: 09.02.2009 02:33:30 9PRASANN DBMAN 099

09.02.2009 02:33:30 DELETE FROM DDSTORAGE WHERE DBSYSABBR = 'ORA' AND DBMAN 099

09.02.2009 02:33:30 INDEXNAME = ' ' AND TABNAME = '/BI0/D0COOM_C021' DBMAN 099

09.02.2009 02:33:30 SQL-END: 09.02.2009 02:33:30 00:00:00 DBMAN 099

09.02.2009 02:33:30 SQL: 09.02.2009 02:33:30 9PRASANN DBMAN 099

09.02.2009 02:33:30 DELETE FROM DDSTORAGE WHERE DBSYSABBR = 'ORA' AND DBMAN 099

09.02.2009 02:33:30 INDEXNAME = ' ' AND TABNAME = '/BI0/D0COOM_C021' DBMAN 099

09.02.2009 02:33:30 SQL-END: 09.02.2009 02:33:30 00:00:00 DBMAN 099

09.02.2009 02:33:30 SQL: 09.02.2009 02:33:30 9PRASANN DBMAN 099

09.02.2009 02:33:30 CREATE TABLE "/BI0/0100001089" PCTFREE 10 PCTUSED DBMAN 099

09.02.2009 02:33:30 00 INITRANS 001 TABLESPACE PSAPHOL STORAGE DBMAN 099

09.02.2009 02:33:30 (INITIAL 0000000016 K NEXT 0000000016 DBMAN 099

09.02.2009 02:33:30 K MINEXTENTS 0000000001 MAXEXTENTS UNLIMITED DBMAN 099

09.02.2009 02:33:30 PCTINCREASE 0000 FREELISTS 001 FREELIST GROUPS DBMAN 099

09.02.2009 02:33:30 01) AS SELECT DISTINCT DIMID FROM DBMAN 099

09.02.2009 02:33:30 "/BI0/D0COOM_C021" "DIM" , "/BI0/SCO_AREA" "MD1" DBMAN 099

09.02.2009 02:33:30 WHERE "DIM"."SID_0CO_AREA" = "MD1"."SID" AND ( DBMAN 099

09.02.2009 02:33:30 "MD1"."CO_AREA" = 'HOL' ) DBMAN 099

09.02.2009 02:33:31 SQL-END: 09.02.2009 02:33:31 00:00:01 DBMAN 099

09.02.2009 02:33:31 SQL: 09.02.2009 02:33:31 9PRASANN DBMAN 099

09.02.2009 02:33:31 DELETE FROM DDSTORAGE WHERE DBSYSABBR = 'ORA' AND DBMAN 099

09.02.2009 02:33:31 INDEXNAME = ' ' AND TABNAME = '/BI0/D0COOM_C02T' DBMAN 099

09.02.2009 02:33:31 SQL-END: 09.02.2009 02:33:31 00:00:00 DBMAN 099

09.02.2009 02:33:31 SQL: 09.02.2009 02:33:31 9PRASANN DBMAN 099

09.02.2009 02:33:31 DELETE FROM DDSTORAGE WHERE DBSYSABBR = 'ORA' AND DBMAN 099

09.02.2009 02:33:31 INDEXNAME = ' ' AND TABNAME = '/BI0/D0COOM_C02T' DBMAN 099

09.02.2009 02:33:31 SQL-END: 09.02.2009 02:33:31 00:00:00 DBMAN 099

09.02.2009 02:33:31 SQL: 09.02.2009 02:33:31 9PRASANN DBMAN 099

09.02.2009 02:33:31 CREATE TABLE "/BI0/0100000886" PCTFREE 10 PCTUSED DBMAN 099

09.02.2009 02:33:31 00 INITRANS 001 TABLESPACE PSAPHOL STORAGE DBMAN 099

09.02.2009 02:33:31 (INITIAL 0000000016 K NEXT 0000000016 DBMAN 099

09.02.2009 02:33:31 K MINEXTENTS 0000000001 MAXEXTENTS UNLIMITED DBMAN 099

09.02.2009 02:33:31 PCTINCREASE 0000 FREELISTS 001 FREELIST GROUPS DBMAN 099

09.02.2009 02:33:31 01) AS SELECT DISTINCT DIMID FROM DBMAN 099

09.02.2009 02:33:31 "/BI0/D0COOM_C02T" "DIM" , "/BI0/SFISCPER" "MD2" DBMAN 099

09.02.2009 02:33:31 WHERE "DIM"."SID_0FISCPER" = "MD2"."SID" AND ( DBMAN 099

09.02.2009 02:33:31 "MD2"."FISCPER" BETWEEN '2007012' AND '2007012' ) DBMAN 099

09.02.2009 02:33:31 SQL-END: 09.02.2009 02:33:31 00:00:00 DBMAN 099

-

---

Pls help.

Thanks & regards

[Former Member · Accepted solution]

Hello,

The job log is not reflecting the high runtimes (look at SQL-END). I would propose to run the job again, but this time with activated ST05 trace for the user that executes this job ('Trace with filter'). Afterwards, you can chose 'Summarize trace by SQL statement' in ST05 in order to find the culprit on database side (if there is any). This would help for sure to pinpoint the problem.

Best Regards,

Michael

[Former Member · Accepted solution]

HI, could you explain how you solve it ?

i face same issue,

tks a lot
Olivia

[Former Member]

The prob is solved, thank you.

[sap_cohort]

Could you pass on some details as to how your problem was solved? Thanks.