Solution : https://service.sap.com/sap/support/notes/588668 (SAP Service marketplace login required)
Summary :
This SAP Note provides guidance on the purposes and management of database statistics which are essential for efficient query optimization by the Cost Based Optimizer (CBO) in Oracle databases. It details the types of statistics collected, such as table, index, and column statistics stored in various Oracle dictionary views, and discusses the conditions under which statistics are not required, such as with the Rule Based Optimizer (RBO) or certain SAP table types. The note advises on the creation, restoration, and potential impacts of updating statistics, referencing tools like BRCONNECT and DBMS_STATS for managing statistics and addressing performance issues that may arise from stale or inaccurate data. Additionally, it covers the procedures for handling statistics in specific scenarios, including during upgrades or when using extended statistics.
Key words :
wri$_optstat_ind_historyi_wri$_optstat_hh_sti_wri$_optstat_hh_obj_icol_stwri$_optstat_histhead_historyi_wri$_optstat_h_stwri$_optstat_histgrm_historyi_wri$_optstat_h_obj#_icol#_st, x-> mysap technology components -> database interface, gathering optimizer statisticssupplied pl/sql packages, /dbaora-> media library -> performance -> update statistics, analyze table <table_name> estimate statistics sample 10 percent, terms frequently asked questions solution 1, partitioned objects table-internal parallel processing, oracle cost-based optimizer creating statistics, bc-db-ora-dba, estimated result deviates significantly
Related Notes :
| 1576108 | Oracle 11g or higher: Extended CBO statistics |
| 1232776 | Long runtimes for accesses to D010INC or D010TAB |
| 1095171 | Exporting index statistics via DBMS_STATS fails |
| 1057511 | Collecting statistis - An alternative approach |
| 1013912 | FAQ: Oracle BW performance |
| 927295 | FAQ: Oracle system statistics |
| 892296 | Enhancements in update statistics in BRCONNECT 7.00/7.10 |
| 848708 | Oracle Database Configuration for NWDI server |
| 838725 | Oracle dictionary statistics and system statistics |
| 832343 | FAQ: Clustering factor |
| 826814 | SMSY: Expanding 'Systems' takes a long time |
| 825653 | Oracle: Common misconceptions |
| 821687 | FAQ: Space utilization and fragmentation in Oracle |
| 806554 | FAQ: I/O-intensive database operations |
| 797629 | FAQ: Oracle histograms |
| 766349 | FAQ: Oracle SQL optimization |
| 750631 | Approximations for cost calculation of the CBO |
| 744483 | Changes in collection of statistics in BRCONNECT 6.40 |
| 724545 | Adjusting the CBO statistics manually using DBMS_STATS |
| 690702 | Histograms cause long runtimes on Oracle 9 |
| 657536 | Transports and DB Statistic Calculation |
| 651812 | FAQ: BR*TOOLS and SAPDBA |
| 628590 | Activating table monitoring using "-f monit" option |
| 618868 | FAQ: Oracle performance |
| 590370 | Too many uncompressed request (f table partitions) |
| 564161 | Transport profile parameter 'dropstatistics' |
| 558746 | Better Oracle Data Dictionary BW Performance |
| 555030 | Deactivating BW-initiated DB statistics |
| 524341 | Updating the statistics for individual partitions |
| 448380 | Information: Oracle Package DBMS_STATS |
| 428212 | Using BRCONNECT to update InfoCube statistics |
| 424239 | New BRCONNECT parameter: stats_dbms_stats |
| 408532 | Using the DBMS_STATS package for collecting statistics |
| 408527 | Checking the statistics using DBA_TAB_MODIFICATIONS |
| 403713 | BRCONNECT: Parallel updating of statistics |
| 403704 | BRCONNECT - Enhanced function for Oracle DBA |
| 365480 | CBO: Field filled with leading "0" aligned to left |
| 211105 | DB21: You cannot create transport requests |
| 132861 | CBO: Statistics creation with SAPDBA or BRCONNECT |
| 129252 | Oracle DB Statistics for BW Tables |
| 122718 | CBO: Tables with special treatment |
| 106047 | DB21: Customizing the DBSTATC |
| 105047 | Support for Oracle functions in the SAP environment |