1020260 - Delivery of Oracle statistics (Oracle 10g, 11g)

Symptom

The performance with Oracle 10g or higher is poor. This is due to unfavorable decisions by the Cost Based Optimizer.

Last change of the SAP Note: NOV/26/2013

Reason and Prerequisites

In certain situations, the Cost Based Optimizer (CBO) may make unfavorable decisions, although there are no CBO errors, for example:

• Tables with heavily fluctuating volumes of data (Note 756335)

• Selective conditions on columns that do not have many attributes (Note 176754 (8))

• Correlated values in different columns

In Oracle 9i or lower, performance problems were solved by activating the Rule Based Optimizer (RBO) by using the RULE hint, or by not creating CBO statistics. However, this is no longer possible since RBO is no longer supported in Release 10g or higher. In addition, with Oracle 9i, there are already situations in which the RBO cannot be used (for example, in the case of FIRST_ROWS hint).

Due to several bugs you cannot use the Bind Value Peeking to optimize such situations either.

Alternatively, implement the script for the automatic postprocessing of critical statistic values using DBMS_STATS functions (Note 724545), which is attached to this note. In addition, it ensures that the changes remain in the system permanently and are not overwritten by BRCONNECT statistical runs.

The script is designed for systems with Oracle 10g or higher, but it can also be used for Oracle 9i or lower. In the case of Oracle 9i, you must ignore the PLS-00302 error issued by the system. Note that the delivered statistics may lead to unfavorable accesses under Oracle 9.2.0.6 or lower (for example, die to the Oracle bug 3566843, see Note 176754). Therefore we recommend that you use Oracle 9.2.0.7 or higher.

Currently, statistics are delivered for the following tables:
/SAPAPO/MATLOC
/SAPAPO/ORDADM_I
AFKO
AFPO
AFVC
ARFCRSTATE
ARFCSDATA
ARFCSSTATE
AUFK
AUSP
BBP_PDHGP
BDCP2
BKPF
CATSDB
DDXTF
DDXTT
DFKKOP
DRAW
EDIDC
EKKN
HRP1001
LTAK
MARA
MLST
MSEG
PAYR
QREFTID
RSBATCHDATA
SMOEJOBID
SMOFCDBHD
SMOFCMPDAT
SMOFCMPHD
SMOFCMPOBJ
SMOHJOBQ
SMOHMSGQ
SMOHMSGQRE
SMOHSITEQ
SMOHSITEQEX
SMOHSITEQRD
SWWWIHEAD
SXMSCLUP
SXMSCLUP2
SXMSCLUR
SXMSCLUR2
SXMSPEMAS
SXMSPEMAS2
SXMSPERROR
SXMSPERRO2
SXMSPHIST
SXMSPHIST2
SXMSPMAST
SXMSPMAST2
SXMSPVERS
SXMSPVERS2
TATAF
TBTCO
TESTDATRNRPART0
TRBAT
TRBAT2
TRFCQDATA
TRFCQIN
TRFCQOUT
TRFCQSTATE
UPSITX
VEPO
XI_AF_SVC_ID_MAP
XI_IDOC_IN_MSG
XI_IDOC_OUT_MSG

Solution

A prerequisite for using the delivered statistics is that you use BRCONNECT 7.10 (25) or higher so freezing individual statistics in accordance with SAP Note 1374807 is successful. If required, first apply a current patch before you implement statistics. If you import statistics.txt (even though you use an older BRCONNECT version), BRCONNECT deletes the table statistics due to the ACTIV=R entries you used, which can lead to critical performance problems.

When importing, note the information in the header of the selected script. Note in particular that, as of December 2009, the script is started using "/ AS SYSDBA" instead of the SAP user and that the name of the SAP user must also be transferred in the command line:
sqlplus /nolog @statistics.txt <sapuser>
To solve individual problems (for example, in the RFC area) you can also execute only those parts of the script that refer to the affected tables.

It is usually sufficient to execute the script once. However, to allow for continuous adjustments to the script, you can import the latest version in regular intervals (for example, once per quarter). It may also be useful to execute the script again after SAP upgrades.

If you experience performance problems due to incorrect CBO decisions after implementing the script, you can proceed as follows:

• On Oracle 10g, you can use DBMS_STATS.RESTORE_TABLE_STATS to restore the original statistics (see Note 588668).

• On Oracle 9i, you can use DBMS_STATS.EXPORT_TABLE_STATS to export the statistics in accordance with Note 448380 and reimport them using DBMS_STATS.IMPORT_TABLE_STATS (if problems occur). In addition, you must remove the ACTIV=I settings from DBSTATC again.

Also open a customer message, so that SAP can investigate why the system behavior has deteriorated.

If you want to rebuild participating indices after implementing the script, note the following:

• Oracle 10g or higher: An index REBUILD has no effect on the locked statistics, so you can perform this task without any problems. Note, however, that the follow-on problems described in Note 1095171 may result from index REBUILDs if you perform other activities such as online reorganization or statistics exports. If in doubt, implement this note again after you have performed a reorganization of the tables involved.

• Oracle 9i: You should always perform a REBUILD without COMPUTE STATISTICS, because otherwise the delivered statistics are overwritten.

In certain cases, you have to take special care with tables for which a complete statistic record is delivered; that is, tables that are listed in the section "COMPLETE STATISTIC DELIVERY" of the script. If, after executing the script, you add new customer-specific indexes or columns to one of the affected tables, you must ensure that the table contains Cost-Based Optimizer (CBO) statistics. The same applies if such a table is to be partitioned in a customer-specific way.

There is no ideal solution in this case. Basically, the following three alternatives are available:

• Creating new statistics for the affected table:
  brconnect -u / -c -f stats -t <table> -f collect,allsel,keep,locked

You must use BRCONNECT 7.00 or higher
After this, you must implement the relevant script of this SAP Note again.

• Not using the delivered CBO statistics and, instead, using the normal statistics - which may be locked

• Manually setting the missing statistics in a useful way (Note 724545)

Header Data

Released On	26.11.2013 15:42:41
Release Status	Released for Customer
Component	BC-DB-ORA Oracle
Priority	Recommendations / Additional Info
Category	Performance