Important Notes for BW Performance

1) Oracle Parameter update - Execute the script from note 1171650, download the parameter check 102 script and modify the below entry if its BW system only

Change the OLAP System? [n] --> y

x_os_family,
      ( select decode(substr(upper('<OLAP System? [n]>'),1,1),
        'Y','TRUE',
        '?','UNKNOWN',
        'FALSE') olap from dual )

Pay close attention to parameter results in output logs

optimizer_dynamic_sampling
optimizer_index_cost_adj --> to disable on use ( make sure initXXX.ora don't this parameter, later create spfile and restart the system0



2) Update statistics for /BI*/E*, /BI*/D* and etc. Optionally these entries can be added to iniXXX.sap file at the below
parameter. Please make sure table RSNSPACE table is maintained with the specific info cubes that is required for update stats

# default: from RSNSPACE control table
# stats_info_cubes = (/BIC/D*, /BI0/D*, ...)
stats_info_cubes = (DEFAULT, XYZ*)

3) While System is lagging, look at the SM50 and click on the report which has high resource utilization and look for the first and
join info cubes which starts something like /BIC/E*** or /BIC/D*** and use DB20 t-code to create stats.

Optional below

4) Ensure that the CBO statistics are current and correct for all cubes
used in query ZABLMP02/ZABLMP02_STD_Q061_TH
- e.g.run the
RSRV check 'Database Indices of an InfoCube and Its Aggregates' and
assure that the table DBSTATC does not contain BW tables with ACTIV=I
(note 129252 - report SAP_DBSTATC_CLEANUP)