Solution : https://service.sap.com/sap/support/notes/1020260 (SAP Service marketplace login required)
Summary :
Poor performance in SAP systems using Oracle 10g or higher is attributed to suboptimal decisions by the Cost-Based Optimizer (CBO). Unlike in Oracle 9i, the Rule-Based Optimizer (RBO) is not available in these versions, and decision-making falls solely to CBO, which struggles with tables that have fluctuating data volumes, columns with few attributes, and correlated column values. To combat these issues, a script using DBMS_STATS functions is provided for re-optimizing CBO statistics and preventing statistical overwrites during BRCONNECT runs. The script applies to systems on Oracle 9i and above, although users of Oracle 9.2.0.6 and below should upgrade due to existing Oracle bugs.
Key words :
/sapapo/matloc/sapapo/ordadm_iafkoafpoafvcarfcrstatearfcsdataarfcsstateaufkauspbbp_pdhgpbdcp2bkpfcatsdbddxtfddxttdfkkopdrawltakmlstmsegpayrqreftidrsbatchdatasmoejobidsmofcdbhdsmofcmpdatsmofcmphdsmofcmpobjsmohjobqsmohmsgqsmohmsgqresmohsiteqsmohsiteqexsmohsiteqrdswwwiheadsxmsclupsxmsclup2sxmsclursxmsclur2sxmspemassxmspemas2sxmsperrorsxmsperro2sxmsphistsxmsphist2sxmspmastsxmspmast2sxmspverssxmspvers2tataftbtcotestdatrnrpart0trbattrbat2trfcqdatatrfcqintrfcqouttrfcqstateupsitxvepoxi_af_svc_id_map solution, experience performance problems due, cost based optimizer, heavily fluctuating volumes, rule based optimizer, pls-00302 error issued, cost-based optimizer, solve individual problems, critical statistic values, rebuild participating indices
Related Notes :
| 1688823 | Oracle: Long running SELECT on table XI_AF_SVC_ID_MAP |
| 1485841 | ALTER INDEX REBUILD does not backup old index statistics |
| 1374807 | Freezing single kinds of statistics |
| 1232776 | Long runtimes for accesses to D010INC or D010TAB |
| 1118297 | XI runtime: QRFC processing of messages |
| 1117723 | BOP: Long runtime when flag Rule Evaluation Again is active |
| 1095171 | Exporting index statistics via DBMS_STATS fails |
| 1032733 | Performance problems after using switch to delete messages |
| 819830 | Oracle Database 10g: Additional Information on upgrade:UNIX |
| 756335 | Statistics in tables w/ heavily fluctuating volumes of data |
| 724545 | Adjusting the CBO statistics manually using DBMS_STATS |
| 618868 | FAQ: Oracle performance |
| 564161 | Transport profile parameter 'dropstatistics' |
| 403704 | BRCONNECT - Enhanced function for Oracle DBA |
| 176754 | Problems with CBO and RBO |
| 122718 | CBO: Tables with special treatment |