on 07-15-2016 4:35 PM
Hello, we are experiencing really bad performance with a generated BPC query. Total result set is about ~50 rows. We looked at execution plan and it takes almost a minute to generate one.
Here is the query:
SELECT
"S5"."/B28/S_FED99ID" AS "CATEGORY" ,"S6"."/B28/S_FEDM5HN" AS "ACCOUNT" ,
"S7"."/B28/S_FED2EIY" AS "INTERCO" ,"S8"."/B28/S_FEDD4SO" AS "FLOW" ,
SUM ( "F"."/B28/S_SDATA" ) AS "SIGNEDDATA"
FROM
"/B28/EFEIY8IH" "F" JOIN "/B28/SFED99ID" "S5"
ON "F" . "KEY_FEIY8IH3" = "S5" ."SID" JOIN "/B28/SFEDM5HN" "S6"
ON "F" . "KEY_FEIY8IH1" = "S6" . "SID" JOIN "/B28/SFED2EIY" "S7"
ON "F" . "KEY_FEIY8IH8" = "S7" . "SID" JOIN "/B28/SFEDD4SO" "S8"
ON "F" . "KEY_FEIY8IH7" = "S8" . "SID" JOIN "/B28/DFEIY8IHP" "DP"
ON "F" . "KEY_FEIY8IHP" = "DP" . "DIMID" JOIN "/BI0/0600000011" "Z4"
ON "F" . "KEY_FEIY8IH8" = "Z4" . "SID" JOIN "/BI0/0600000005" "Z5"
ON "F" . "KEY_FEIY8IH6" = "Z5" . "SID" JOIN "/BI0/0600000010" "Z6"
ON "F" . "KEY_FEIY8IH5" = "Z6" . "SID"
WHERE
( ( ( ( "F"."KEY_FEIY8IH2" IN ( 7 , 8 , 9 , 12 , 14 , 15 , 18 , 40
, 41 , 57 , 58 , 60 , 65 , 67 ) ) ) AND ( ( "F"."KEY_FEIY8IH3" IN (
2 , 39 ) ) ) AND ( ( "F"."KEY_FEIY8IH7" IN ( 2 , 3 , 4 , 5 , 6 , 7
, 8 , 9 , 10 , 11 , 12 , 13 , 14 , 15 , 16 , 17 , 18 , 19 , 20
, 21 , 22 , 23 , 24 , 25 , 26 , 27 , 28 , 29 , 30 , 31 , 32 ,
33 , 34 , 35 , 36 , 37 , 38 , 39 , 40 , 41 , 43 , 44 , 45 ) ) )
AND ( ( "F"."KEY_FEIY8IH1" IN ( 15 , 16 , 17 , 72 , 73 , 76 , 78 ,
94 , 120 , 192 ) ) ) AND ( ( "F"."KEY_FEIY8IH4" IN ( 3 , 4 , 5 , 6
, 8 , 9 , 10 , 11 , 12 , 13 , 14 , 15 , 17 , 18 , 19 , 20 , 21
, 22 , 23 , 25 , 26 , 28 , 29 , 30 , 31 , 33 , 34 , 35 , 36 ,
38 , 39 , 41 , 42 , 43 , 44 , 46 , 48 , 50 , 51 , 52 , 53 , 54
, 55 , 56 , 59 , 61 , 62 , 63 ) ) ) AND ( ( "F"."KEY_FEIY8IH9" IN (
2 , 3 , 4 , 5 , 7 , 8 , 9 , 10 , 11 , 12 , 13 , 14 , 15 , 16 ,
18 , 19 , 20 , 21 , 22 , 32 , 36 , 37 ) ) ) AND ( (
"F"."KEY_FEIY8IHA" = 3 ) ) AND ( ( "F"."KEY_FEIY8IHB" = 234 ) ) AND ( (
"DP"."SID_0CHNGID" = 0 ) ) AND ( ( "DP"."SID_0REQUID" <= 3971088 ) ) ) )
AND "Z4"."SID" <> 2000008999 AND "Z5"."SID" <> 2000008999 AND "Z6"."SID" <>
2000008999
GROUP BY
"S5"."/B28/S_FED99ID" ,"S6"."/B28/S_FEDM5HN" ,"S7"."/B28/S_FED2EIY" ,
"S8"."/B28/S_FEDD4SO"
And execution plan is attached. We're suspecting BITMAP CONVERSION TO ROWIDS being the issue but changing DB parameters and altering session to suppress conversion didn't help.
Any hints and ideas are appreciated.
Thanks,
-Leon
Hi Leon,
Kindly go through below sap notes if you haven't yet
2193471 - BPC NW 10.0/10.1 Report Troubleshooting guide - Performance and Incorrect Results
Hope they help you in narrowing down the issue.
Regards
Prithviraj
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
83 | |
10 | |
10 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.