1597364 - FAQ: BW-BCT: Extraction performance in source system

Symptom

Poor performance while Extracting data from BW source system. Performance problem in LO, FI, HR, CO extraction.

Other Terms

ROIDOCPRMS, SMQS, ALE, IDoC, qRFC and tRFC, RMBWV3nn.

Reason and Prerequisites

Extraction Performance problems due to incorrect parameter settings and other factors related to datasource specific settings across various application components.

Solution

1. Package size settings for BW data loads

The general formula is:
Package size = MAXSIZE * 1000 / size of the transfer structure,
but not more than MAXLINES.

look up the transfer structure (extract structure) in the table ROOSOURCE in the active version of the DataSource and determine its size using SE11 (ABAP Dictionary) -> Extras -> Table Width -> Length of data divison under ABAP.

To ensure the performance and stability of the upload process, it is very important that the table ROIDOCPRMS be set up correctly on both the Source system side as well as on the BI side (for DataMart-related loads). This table contains the control parameters for data transfer. Alternatively in your R/3 system, go to transaction SBIW -> General settings -> Maintain control parameters for data transfer.

Although there is no specific recommendation on this setting, standard values are as follows:
  STATFRQU     10
  MAXPROCS     03
  MAXSIZE      Value between 20,000 to 50,000   (default is 10,000)
  MAXLINES     value between 50,000 to 100,000 (default is 100,000)

Recommendation:

• In a resource constrained system, reduce the data package size.

• In larger systems (with adequate CPU and memory) increase the package size to speed up collection. However, take care so as not to impact the communication process and unnecessarily hold up the work processes.

For more details check note 409641.



2. Using SMQS

If there is a large volume of data or parallel requests of data for different DataSources, performance  problems may occur when you transfer data from the source system to BW.

As of BW Service API 7.0, the transfer of data was switched to the default tRFC scheduler(represented in transaction SMQS).a BI 7.X system is supplied by default by the RFC scheduler SMQS. Note that the BW destination is registered in SMQS. You must implement the settings for Max. Conn and Max. Runtime specifically for the system. The Basis documentation contains the mode of action. The default Max. Conn of 10 and Max. Runtime is 60.

ROIDOCPRMS and SMQS:

• ROIDOCPRMS parameters are still used in the data transfer i.e. data package size etc... but for the work processes parameter (MAX.proc) if the indicator for 7.X is selected in SMQS then this parameter is not used.

• For more information check note 1163359.

3. qRFC and tRFC performance

The tables ARFCSSTATE, ARFCSDATA, ARFCRSTATE, TRFCQDATA, TRFCQIN, TRFCQOUT or TRFCQSTATE contain a large number of entries. This leads to poor performance during tRFC and qRFC processing.

See Note 375566 and do the manual steps.
See note 375566 if there are large number of entries in tRFC and qRFC tables

tRFC or qRFC can cause a high database load or long runtimes, check SAP note  371068 which deals with measures for better performance

There is poor performance when accessing T and QRFC databases even though the index fits the executed statement optimally and is already being used in the newest version of the T and QRFC.
See SAP note 407125 for Poor performance of Q and TRFC on ORACLE
Check the storage quality as outlined in Note 332677.
For Resource Management for tRFC and aRFC see note 74141.



4. IDoc processing and Performance

The "Trigger Immediately" \ "Transfer IDoc Immed." options should be always used.


Please see note 1333417 for more information.

For number of dialog process in data transfer see note 916706.
When IDoc hangs check please check the note  1280898.



5. Collective Job performance

Performance problem in collective job RMBWV3nn

Try to maintain the control parameters for the extraction for MCEX(xx) queue by the method below.
Tr.LBWR > Queue name (MCEX Queue) > Customizing > No.Of.Documents = 1000 to 3000.Check if this is reflected in Table TMCEXUPD-UPD_HEAD_COUNT field.

The adjustment of  TMCEXUPD- UPD_HEAD_COUNT will need to be tested for each application, as setting too large a value can result in a memory dump.



6. Basis table size in BW source system

There are many administrative Basis tables that may become much bigger, and thereby cause problems, if the entries are not regularly deleted or archived, or if the configuration is incorrect.

a)tRFC and qRFC tables:

• ARFCSSTATE, ARFCSDATA,ARFCRSTATE,TRFCQDATA,TRFCQIN, TRFCQOUT or TRFCQSTATE

• Check the proposals from Note 375566.

b)Tables with ALE change pointers: BDCP, BDCPS, BDCP2

• See Note 513454 and regularly schedule report RBDCPCLR to delete change pointers that are no longer required.

c)Update tables: VBDATA, VBMOD, VBHDR, VBERROR

• Check proposals from Note 67014.

7. Setup table performance and downtime

Review Note 753654 and remember to start RMCENEUA when system load is low and run it in parallel in small ranges.

Check these notes also.
437672     LBWE: Performance for setup of extract structures
436393     Performance improvement for filling the setup table



8. Extraction Performance

• Ensure that you do not create more than 1000 data packages for each request.

• Ensure that you do not load more than 10 million records for each request.

For more information check note 892513

Check if there is any customer enhancement, if performance is affected because of the user exit. Deactivate the code and try extraction again, if there is problem in the custom code then try to optimize the code.

Setup table

• You can schedule the jobs for the setup in parallel (in applications that support this function. Do not use for application 5/setup transaction OLIQBW). See note 436393 for more info.

Regarding package size for SD see the note 423118.



9. MM Extraction

See note 1474938 for general OLI3BW performance.

OLI3BW have Performance issue to extract Purchase Orders for services.
Apply Note 1404736 if you have a lot of Purchase Orders  to process that involve Services. Check this in your tables, EKPO, etc, in transaction SE16N If you do not use Services, you do not need to apply this Note.



10. FI extraction

Please refer notes 641977, 760973, 798536, 787713, 815638 and 856148 in case of performance of FI extractors. Also check whether indices used are standard ones which are given by SAP. If not then this may also a reason for poor performance and should activate the standard indices. Please make sure that the indices mentioned in the notes above are present by checking manually even if the corrections in the notes have been implemented. Because standard indexes are delivered and will not be active in the system, all these notes need to be checked manually for all the releases on the index status.

Check if there is any custom coding for FI extractors in customer exits and check whether it is the reason for poor performance by testing the extractor without exit and see whether it makes any difference.

See note 1392817 for delta procedures and performance on FI-GL extractors.

0FI_GL_10 Performance
Create the following additional secondary indexes for your              NewGL totals table (for example FAGLFLEXT):
   a) Index using the fields
        RCLNT
        RLDNR
        TIMESTAMP
        RBUKRS
(Also see Note 1531175.)


   b) Index using the fields
        RACCT,
        PRCTR
PRCTR (if the profit center scenario is used),and               afterwards the two to four most selective fields of your totals table.

See note 1056245 for more details.



11. CO Extraction performance

It takes long time in extracting CO data.

Make sure that you have activated Index ~4 (Delta Index) of COEP table at DB level.

• Use transaction SE16 to create an entry (if it does not yet  exist) in the BWOM_SETTINGS table with the following key fields

             OLTPSOURCE = <name of the affected Datasource>
             PARAM_NAME = 'NOBLOCKING'.

• Use transaction SE16 to change the value in the 'PARAM_VALUE' column for the aforementioned entry of the 'BWOM_SETTINGS' table to 'X'.

See SAP note 549552 for more details.

For more details check the SAP note 382329 578417 416265 836740, 1424403

It takes a very long time to extract the CO tables COSP and COSS from Oracle database systems when there are no qualified index selections
0CO_OM_CCA_1,0CO_OM_CCA_2,0CO_OM_OPA_1,0CO_OM_NAE_1,0CO_OM_NTW_1,
0CO_OM_NWA_1,0CO_OM_WBS_1,0CO_OM_ABC_1

See note 1312060 for more details.



12. HR Performance

The extraction of time data in the delta mode requires long runtimes in the OLTP source system. 0HR_PT_1, 0HR_PT_2, 0HR_PT_3.

In the following DB tables:

a)  PTDW_PWS_DB
b)  PTDW_TIMES_DB
c)  PTDW_QUOT_DB

Create the following non-unique index and activate:
MANDT (client), PERNR (personnel number), KMONTH (calendar month), if you have not already done so.

Check for timeframe in table T569R.
In order to keep runtime short, we recommend to choose the Earliest and Latest Transfer Date in table T569R in a way that only necessary data would be transfered. Please maintain the entries in the table T569R as described in the Notes 353177 and 696838 or make sure that it is done so already.

Refer the note 354584 for more information on HR extractors.


13. CO-PA extraction

If there is performance issue, the join method used requires an additional index on the line item tables. Create an additional secondary index with the fields MANDT and PAOBJNR (in this sequence) for the tables CE1xxxx and CE2xxxx (xxxx=operating concern). The index is not unique. You can also set secondary indices of COEP~4 and COEJ~4. See note 0210219 for more details.

Set the flag NO_GROUP_BY = 'X' in table TKEBWL, and check the extraction again. Check note 1432022, for more details on technical settings for CO-PA extractors



14.Performace problem with ALE change pointers

See Note 513454 and regularly schedule report RBDCPCLR to delete change pointers that are no longer required.



15. TCT and BI Admin cockpit

ST03n performance problem when calling BI workload.

Tables RSDDSTAT_OLAP and RSDDSTAT_DM stores statistics of query runtime and keeps on growing depending on the activities on the system. These tables are too big when checked. More over TCT_KEEP_OLAP_DM_DATA_N_DAYS parameter in the RSADMIN table is not maintained so this leads to performance problems

   a)Automatic deletion during data load

• Per default, with each delta load for query runtime statistics ( DataSource 0TCT_DS01 - 0TCT_DS03), data of the last 14 days (30 days as of SP16, SAP Note 1095411) is deleted out of the BI Statistics tables. This time frame can be customized using the TCT_KEEP_OLAP_DM_DATA_N_DAYS parameter in the RSADMIN table.

   b)Manual deletion of statistics data

• In transaction RSDDSTAT. Dates to be deleted can be selected using program RSDDSTAT_DATA_DELETE.

16. Trace and performance

When there is a performance issue in extraction and in spite of all parameter settings given above, then a trace should be done while extraction is running. There are various traces like ST05, SE30 Etc. but you can combine everything in ST12 trace.

• ST12: ST12 combines ABAP and performance (SQL) trace into one transaction, with major functional enhancements especially for the ABAP trace part.ST12 allows to activate the ABAP trace for another user.The ST12 can also be used for tracing workprocess, program/transaction or a user. At a click of a button you can view the SQL and ABAP trace information.See Note 755977 for more information.

• STAD: Used to check response time of programs/transactions and provides various levels of detail which can be tailored.