Ansicht
Dokumentation
09725 - Performance problem on BW 2.1C / AS400 V5R1M0
Fill RESBD Structure from EBP Component Structure General Material DataThis documentation is copyright by SAP AG.
Performance problem on BW 2.1C / AS400 V5R1M0
Hi all,We are using BW 2.1C on AS/400 V5R1M0 ( EBCDIC ) and have performance
problem since we compressed the requests in the infocubes.
An OSS mesage is opened, and no solution was found at the moment.
Please let me know if somebody has encountered the same problem.
**********************************************
Here is the content of the OSS message :
System ID : BW2 System Patch : SAPKA46D24 Kernel Rel.: 46D
Transaction : Program : Screen :
After the compression of requests in a infocube ( pgm RSCOMP1 ) we
have
decreased performances for some queries.
For example before the compression the SQL trace ( ST05 ) showed for
the
OPEN SQL function on the fact table, an execution time of 13 seconds.
After the compression, the same request tooks about 31 seconds and in
the transaction ST03 ( workload ) we have the following informations :
Analysis of time in work process
CPU time 43.885 ms Number Roll ins
RFC+CPIC time 3.170 ms Roll outs
Enqueues
Response time 46.875 ms Load time Program
Screen
Wait for work process 1 ms CUA interf.
Processing time 1.847 ms
Load time 0 ms Roll time Out
Generating time 171 ms In
Roll (in) time 1 ms Wait 2.074
ms
Database request time 44.855 ms
Enqueue time 0 ms Frontend No.roundtrips
GUI time
We can see a high database request time.
We have an aggregate on this infocube but it is not used during this
request. This aggregate was not deactivated during the compression.
Do we have to recreate the fact table indices after a compression ?
Did I forget a step in the compression of the request ?
Best regards.
______________________________________________________________________
Reply
SAP AG 19.02.2003 16:55:44
Hello Mr. DURNERIN,
within our GoingLive Check we analyzed query AB_YSD_VENTE_CAMOIS.
The increased response time can be explained as following:
- Before the InfoCube compression your query had to access only the F-
Fact table /BIC/FYSD_VENTE, which took about 13 seconds database time.
- After the compression your query has to access the F table (contain-
ing the uncompressed data) and additionally the E-Fact table
BIC/EYSD_VENTE (for the compressed requests).
The problem is that each access takes a long time. The OPEN Statement
for the F Table takes 15 seconds (similar to before the compression):
HH:MM:SS.MS Duration Program ObjectName Op. Curs Array Rec RC
12:31:21.951 234.440 %_T01GT0 BIC/FYSD_ PREPARE 25 0
12:31:22.188 15.995.90 %_T01GT0 BIC/FYSD_ OPEN 25 0
12:31:38.185 23.616 %_T01GT0 BIC/FYSD_ FETCH 25 272 85
12:31:38.210 220.584 %_T01GT0 BIC/FYSD_ CLOSE 25 0
HH:MM:SS.MS Duration Program ObjectName Op. Curs Array Rec RC
12:31:38.959 31.689.84 %_T01JL1 BIC/EYSD_ OPEN 25 0
12:32:10.649 41.024 %_T01JL1 BIC/EYSD_ FETCH 25 272 272
12:32:10.695 6.352 %_T01JL1 BIC/EYSD_ FETCH 25 272 223
12:32:10.706 92.208 %_T01JL1 BIC/EYSD_ CLOSE 25 0
The OPEN Statement for the E table needs additional 32 seconds. So in
total about 48 seconds db time are needed.
In order to verifiy the reasons for these long OPEN times this message
will be forwarded to our development support.
The available aggregate is not suitable for that query and therefore
cannot be used. A suitable aggregate will be recommended in the Going-
Live report.
Best regards,
Ronny Kaufmann
SAP EarlyWatch and GoingLive Services
______________________________________________________________________
__
Call to Customer
SAP AG 21.02.2003 12:00:41
Customer Call
Performed on 21.02.2003 12:00:31
Contact person Mr. Thierry DURNERIN
Status of discussion:
Customer not reached
Subject of conversation:
______________________________________________________________________
__
Reply
SAP AG 21.02.2003 14:07:37
I opened the IBM PMR 85977 B650 C706 for this problem.
______________________________________________________________________
__
Reply
SAP AG 25.02.2003 16:40:53
Mr. Durnerin agreed to wait for the approved version of SI06731.
I will update this message again as soon as that happens.
______________________________________________________________________
__
Reply
SAP AG 10.03.2003 17:05:29
Hello Mr. Durnerin,
the PTF SI06731 got approved and already superseded by SI08120. Please
order and apply that PTF and let me know if the problem is fixed.
Regards, Marco Wermann, IBM Germany.
______________________________________________________________________
__
Info for SAP
Thierry DURNERIN 13.03.2003 14:06:52
Hello Mr Wermann,
The mentioned PTF was applied on our BW system and the first test
I made based on the same infocube with the query AB_YSD_VENTE_CAMOIS
did not show the expected results.
The OPEN statement on table E and F are similar before and after the
new PTF.
Results of the test after implementation of the PTF :
HH:MM:SS.MS Duration Program ObjectName Op. Curs Array Rec RC
13:51:59.846 104.864 %_T00XDC BIC/FYSD_X PREPARE 17 0
13:51:59.953 20.574.44 %_T00XDC BIC/FYSD_X OPEN 17 0
13:52:20.528 18.072 %_T00XDC BIC/FYSD_X FETCH 17 272 129
13:52:20.549 82.024 %_T00XDC BIC/FYSD_X CLOSE 17 0
HH:MM:SS.MS Duration Program ObjectName Op. Curs Array Rec RC
13:52:20.889 115.128 %_T0105D BIC/EYSD_X PREPARE 17 0
13:52:21.007 38.558.53 %_T0105D BIC/EYSD_X OPEN 17 0
13:52:59.566 80.336 %_T0105D BIC/EYSD_X FETCH 17 272 272
13:52:59.651 6.880 %_T0105D BIC/EYSD_X FETCH 17 272 239
13:52:59.663 85.024 %_T0105D BIC/EYSD_X CLOSE 17 0
After we applied the PTF, all the SQL packages were deleted and the
system was restarted.
Best regards.
Thierry DURNERIN
______________________________________________________________________
Reply
SAP AG 25.03.2003 14:23:07
Hello Mr. Durnerin,
as already explained to you on the phone this "problem" cannot be
fixed.
Your BW query AB_YSD_VENTE_CAMOIS contains two big database queries,
one over the e-fact table, the other one over the f-fact table. These
queries together take about 1 min to complete in you BW environment.
The same queries are much faster (about 4 times) outside of the BW.
But the way BW is implemented on iSeries with EVI stage 2 it's
unfortunately not possible to improve the queries. EVI stage 2 on one
hand will improve the OVERALL BW significantly but on the other hand
a very few amount of queries will not benefit from that.
Unfortunately there is nothing we can do about it.
Durban Tours - Südafrika Safari
Fill RESBD Structure from EBP Component Structure ROGBILLS - Synchronize billing plans
This documentation is copyright by SAP AG.
Length: 7215 Date: 20240425 Time: 030122 sap01-206 ( 3 ms )
