SAP Knowledge Base Article - Preview

2862994 - multi-join BPC query hangs - SAP BPC Business Warehouse on SAP ASE

Symptom

On SAP BW-BPC systems on SAP ASE database, a BPC query with more than 7 JOINs referencing BW temporary tables, never completes.

Status of the ASE process (spid)  is COMPILING  -- when checked via DBACockpit->Process Monitor or sp_who in isql.

Example query:

select "F"."KEY_UW6HTC" as "S____20890" , "H1"."PRED" as "S____20874" , "H2"."PRED" as "S____20876" , "H3"."PRED" as "S____20883" , "H4"."PRED" as "S____20878" , "H5"."PRED" as "S____20881" , "H6"."PRED" as "S____20875" , "H7"."PRED" as "S____20880" , "H8"."PRED" as "S____20889" , SUM ( "F"."/B28/S_SDATA" ) as "Z____041_SUM" , count( * ) as "Z____154_SUM"
from "/B28/FUW6HT" "F" join "/B28/DUW6HTP" "DP" on "F" . "KEY_UW6HTP" = "DP" . "DIMID"
join ( select distinct SUCC , PRED , FACTOR from "/BI0/0200000192" where SEQ_NR = 0 or SEQ_NR = 83024 union select SUCC , PRED , FACTOR from "/BI0/0200000193" where SEQ_NR = 0 union select SUCC , PRED , FACTOR from "/BI0/0200000195" where SEQ_NR = 0 union select SUCC , PRED , FACTOR from "/BI0/0200000196" where SEQ_NR = 0 ) "H1" on "F" . "KEY_UW6HT1" = "H1" . "SUCC"
join ( select distinct SUCC , PRED , FACTOR from "/BI0/0200000162" where SEQ_NR = 0 or SEQ_NR = 83025 ) "H2" on "F" . "KEY_UW6HT3" = "H2" . "SUCC"
join ( select distinct SUCC , PRED , FACTOR from "/BI0/0200000046" where SEQ_NR = 0 or SEQ_NR = 83026 ) "H3" on "F" . "KEY_UW6HT9" = "H3" . "SUCC"
join ( select distinct SUCC , PRED , FACTOR from "/BI0/0200000063" where SEQ_NR = 0 or SEQ_NR = 83027 ) "H4" on "F" . "KEY_UW6HT5" = "H4" . "SUCC"
join ( select distinct SUCC , PRED , FACTOR from "/BI0/0200000202" where SEQ_NR = 0 or SEQ_NR = 83028 union select SUCC , PRED , FACTOR from "/BI0/0200000065" where SEQ_NR = 0 ) "H5" on "F" . "KEY_UW6HT8" = "H5" . "SUCC" join ( select distinct SUCC , PRED , FACTOR from "/BI0/0200000067" where SEQ_NR = 0 or SEQ_NR = 83029 ) "H6" on "F" . "KEY_UHIW6HT2" = "H6" . "SUCC"
join ( select distinct SUCC , PRED , FACTOR from "/BI0/0200000083" where SEQ_NR = 0 or SEQ_NR = 83030 ) "H7" on "F" . "KEY_UW6HT7" = "H7" . "SUCC"
join ( select distinct SUCC , PRED , FACTOR from "/BI0/0200000026" where SEQ_NR = 0 or SEQ_NR = 83031 ) "H8" on "F" . "KEY_UW6HTB" = "H8" . "SUCC"
where ( ( ( ( "F"."KEY_UW6HTA" = 3 ) ) AND ( ( "F"."KEY_UW6HT4" = 23 ) ) AND ( ( "F"."KEY_UW6HT6" = 13 ) ) AND ( ( "F"."KEY_UW6HTC" = 251 ) ) AND ( ( "DP"."SID_0CHNGID" = 0 ) ) AND ( ( "DP"."SID_0REQUID" <= 1072711 ) ) ) ) AND "H1"."SUCC" <> 2000008999 AND "H2"."SUCC" <> 2000008999 AND "H3"."SUCC" <> 2000008999 AND "H4"."SUCC" <> 2000008999 AND "H5"."SUCC" <> 2000008999 AND "H6"."SUCC" <> 2000008999 AND "H7"."SUCC" <> 2000008999 AND "H8"."SUCC" <> 2000008999
group by "H1"."PRED" ,"H6"."PRED" ,"H2"."PRED" ,"H4"."PRED" ,"H7"."PRED" ,"H5"."PRED" ,"H3"."PRED" ,"H8"."PRED" ,"F"."KEY_UW6HTC"
order by "S____20874" , "S____20876" , "S____20883" , "S____20878" , "S____20881" , "S____20875" , "S____20880" , "S____20889"
PLAN '(use optgoal sap_olap) (use fact_table F) (use parallel 4) (prop F (parallel 4))'


Read more...

Environment

  • SAP Adaptive Server Enterprise (ASE) 16.0 SP03 for Business Suite
  • SAP Business Warehouse - All versions
  • SAP BusinessObjects Planning and Consolidation for SAP Netweaver - All versions

Product

SAP NetWeaver 7.5

Keywords

BPC, COMPILING, slow, hangs, 810688, CR#810688, CR810688, slow , KBA , BW-SYS-DB-SYB , BW on Adaptive Server Enterprise , Problem

About this page

This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP ONE Support launchpad (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.