Solution : https://service.sap.com/sap/support/notes/881083 (SAP Service marketplace login required)
Summary :
SAP Note addresses optimal configuration for parameters rsdb/max_blocking_factor and rsdb/max_in_blocking_factor with Oracle databases. It details using these parameters to distribute ABAP lists in FOR ALL ENTRIES across multiple SQL statements to enhance efficiency and manage resource consumption effectively. Parameter rsdb/max_blocking_factor handles statements with OR operations, while rsdb/max_in_blocking_factor manages IN list statements, suitable post-Kernel 6.x with the rsdb/prefer_in_itab_opt set to 1. Additionally, the note explores their impact on Oracle's Cost-based Optimizer (CBO), recommending default settings based on whether the database is OLTP or OLAP oriented. Adjustments should be tested in non-production environments to mitigate potential negative impacts on performance.
Key words :
work process trace file dev_w<num>, suboptimal full table scan, notice negative side effects, incorrect cbo decision optimize, sap profile parameter overview, includes actual parameter values, values ensure optimal performance, index access increase linearly, rsdb/max_in_blocking_factor parameter converts, rsdb/max_blocking_factor parameter converts
Related Notes :
| 1100926 | FAQ: Network performance |
| 869006 | Composite SAP note: ORA-04031 |
| 805934 | FAQ: Database time |
| 772497 | FAQ: Oracle Hints |
| 766349 | FAQ: Oracle SQL optimization |
| 750631 | Approximations for cost calculation of the CBO |
| 712624 | High CPU consumption by Oracle |
| 634263 | Selects with FOR ALL ENTRIES |
| 618868 | FAQ: Oracle performance |
| 180605 | Oracle database parameter settings for BW |
| 176754 | Problems with CBO and RBO |
| 124361 | Oracle parameterization (R/3 >= 4.x, Oracle 8.x/9.x) |
| 48230 | Parameters for the SELECT ... FOR ALL ENTRIES statement |