SAP R/3 форум ABAP консультантов

[admin]

Note 724545 - Adjusting the CBO statistics manually using DBMS_STATS

Symptom
The Cost Based Optimizer (CBO) uses an unsuitable access path. This causes a longer runtime and higher system load.

Reason and Prerequisites
If performance problems occur because the CBO does not use the best access path, you can use various solutions, for example:

• You can create more exact statistics.
  
• You can create histograms (Note 797629)
  
• You can specify hints.
  
• You can adjust the statistics manually with DBMS_STATS.

Before you adjust the statistics using DBMS_STATS, check whether there are other options to solve the problem.

Refer to Note 176754 for a description of typical situations in which it makes sense to adjust the statistics. Refer to Note 588668 for background information on database statistics. Refer to Note 448380 for information about the DBMS_STATS functions. Note 750631 contains details on the cost accounting of the CBOs.

Note 1020260 contains a script for the implementation of CBO statistics for critical tables in the SAP environment.

Solution
Use the following commands to adjust the statistical values for tables, indexes or columns:

SET_COLUMN_STATS
SET_INDEX_STATS
SET_TABLE_STATS

The following gives you an overview over typical statistical changes to optimize the access path:

• Increasing or decreasing the distinct values of a column:
  - Oracle DDIC reference: NUM_DISTINCT in DBA_TAB_COLUMNS;
  - Result: If you increase the distinct values, an index access using this column becomes more attractive to the system. If you are using a Join, a Nested Loop Join with an access point using this index becomes more likely.
  If you decrease the distinct values, an index access using this column becomes less attractive to the system (which is useful if several indexes have a costing of 1 and the incorrect index is used; see Note 176754)
  - Call:
  DBMS_STATS.SET_COLUMN_STATS('<owner>', '"<table>"',
  '"<column>"', DISTCNT => <new_value>, NO_INVALIDATE=>FALSE);
  - Caution: After you execute this command, the column may lose histogram statistics that may exist.
  
  
• Reducing the clustering factor of an index:
  - Oracle DDIC reference: CLUSTERING_FACTOR in DBA_INDEXES;
  - Result: The access with index range scans on this index becomes more attractive to the system.
  - Call:
  
  DBMS_STATS.SET_INDEX_STATS('<owner>', '"<index>"',
  CLSTFCT => <new_value>, NO_INVALIDATE=>FALSE);
  
  
• Reducing the number of rows in a table:
  - ORACLE DDIC reference: NUM_ROWS in DBA_TABLES;
  - Result: The access on a table becomes more attractive. If you are using Joins, a Nested Loop Join with an access point using this table become more likely.
  - Call:
  
  DBMS_STATS.SET_TABLE_STATS('<owner>', '"<table>"',
  NUMROWS => <new_value>, NO_INVALIDATE=>FALSE);
  

Here is a complete overview of the statistics values to which a new value can be assigned using "=>" in the last call parameter:

• Column statistics: DISTCNT, DENSITY, NULLCNT, AVGCLEN
  
• Index statistics: NUMROWS, NUMLBLKS, NUMDIST, AVGLBLK, AVGDBLK, CLSTFCT, INDLEVEL
  
• Table statistics: NUMROWS, NUMBLKS, AVGRLEN

Refer to the Oracle online documentation for further information.

As of SAP Basis 7.10, SAP has made the RSORASTE report available, so that statistics can be created easily from the SAP system.

Only adjust the statistics after you have performed a thorough check. In a non-production environment, check whether the changed statistics have unwanted side effects before you transfer the changes to the production system. Change the statistical values as little as possible to avoid superfluous side effects. To determine a good statistical value, you may have to perform a large number of tests with different values.

To keep the changed statistics, you must prevent BRCONNECT from overwriting the statistics during its statistical runs. For this purpose, create an entry with ACTIV=I in the DBSTATC table for the relevant table (refer to Note 106047).

As of Oracle 10g, you can also protect statistics from being overwritten at Oracle level. With the following command, the statistics can be locked for a table and then released again:

DBMS_STATS.LOCK_TABLE_STATS('< owner>','<table>');
DBMS_STATS.UNLOCK_TABLE_STATS('<owner>','<table>');

Note, however, that statistics tools such as BRCONNECT return an error such as ORA-20005 or ORA-38029 if you try to regenerate locked statistics.

If you want to create new statistics for the table from time to time, you must repeat the manual adjustment of the statistics with DBMS_STATS after you have created the statistics.

Related Notes
1117723 - BOP: Long runtime when flag Rule Evaluation Again is active
1020260 - Delivering Oracle statistics
852365 - Performance of BBP_PDVIEW_LIST / BBP_PDHGP
799649 - Runtime IQ03 (history) long SELECT to VIAUFKST
772497 - FAQ: Oracle Hints
766349 - FAQ: Oracle SQL optimization
756335 - Statistics on tables w/ heavily fluctuating volumes of data
750631 - Approximations for cost calculation of the CBO
744315 - LDB PSJ: Elimination of 'rule' hints Oracle
735510 - LDB PSJ: Elimination of the Hints Oracle 'rule' statement
588668 - FAQ: Database statistics
448380 - Information: Oracle Package DBMS_STATS
106047 - DB21: Customizing the DBSTATC

KeyWords: обнов, статистик