Solution : https://service.sap.com/sap/support/notes/838725 (SAP Service marketplace login required)
Summary :
This SAP Note addresses methods for generating Oracle database statistics using SQL commands and BRCONNECT. Applicable to Oracle releases 10.2 and 11.2, the note provides guidance on creating Oracle dictionary and system statistics to optimize SQL statement performance by aiding the cost-based optimizer in Oracle. It includes processes using DBMS_STATS with detailed parameters for both dictionary and system statistics, and prerequisites for executing these processes, such services as BR*Tools. Recommendations on the periodic update of statistics post-upgrades or system changes are also underscored alongside the necessary user authorizations and SAPDBA role requirements.
Key words :
prerequisites creating oracle dictionary statisticscreating system statistics solution contents oracle dictionary statistics creating oracle dictionary statistics, dbms_stats package oracle system statistics creating oracle system statistics, / oracle system statistics system statistics describe, higher creating oracle dictionary statistics, brconnect creating oracle dictionary statistics, <minutes>deleting oracle system statistics, brconnect creating oracle system statistics, performance/utilizationcpu performance/utilization reason, creating oracle dictionary statistics, creating oracle system statistics
Related Notes :
| 1117785 | RW: Wrong data / bad performance with ORACLE 10g |
| 1017324 | EP on Oracle: Poor portal performance |
| 984955 | Timeout with selection of V$-Views |
| 974781 | Oracle internal maintenance jobs |
| 972539 | Core Dump with GATHER_FIXED_OBJECTS_STATS |
| 871455 | Poor performance when accessing DBA and V$ views |
| 863811 | Using BRCONNECT to collect statistics for Oracle dictionary |
| 797629 | FAQ: Oracle histograms |
| 706927 | Oracle DB-Mon: Installation script for ST04N/DBA Cockpit |
| 618868 | FAQ: Oracle performance |
| 601395 | Collecting system statistics with BRCONNECT |
| 588668 | FAQ: Database statistics |
| 354080 | Note collection for Oracle performance problems |
| 138639 | Poor DML performance with Oracle DDIC statistics |