Oracle ERP收集統計值 (效能調教相關)

OM-PERF: Performance Tuning - FAQ [ID 113573.1]

1. What is CBO?

CBO stands for Cost Based Optimization. The R11i application was written to use the CBO. It determines the most efficient way to execute a SQL statement based on statistical data stored in the data dictionaries. These stored statistics are used to compute the cost of executing the SQL. The execution plan with the lowest cost is the one chosen by the optimizer.

2. Why the switch from RBO to CBO in 11i?

The Rule Based Optimizer (RBO) uses a fixed ranking system to determine the most efficient access method when executing a SQL statement. This method is not as flexible as CBO; which takes into consideration the data distribution characteristics of the database. CBO also offers a number of enhancements which are not available with RBO.

3. Is RBO obsolete in R11i?

No, RBO still exists. By default the 11i apps will use CBO. However, by the use of hints, RBO can still be invoked.

4. How do you gather the statistics necessary for CBO?

In R11i the FND_STATS package is used to generate statistics on the database. FND_STATS is a PL/SQL wrapper around the DBMS_STATS package, which issues the ANALYZE command. However, you should not use the ANALYZE command or DBMS_STATS package directly. Doing so may result in incomplete statistics being generated.

Use the following command to gather schema statistics:

SQL> exec fnd_stats.gather_schema_statistics('ONT') --- For a specific schema
SQL> exec fnd_stats.gather_schema_statistics('ALL') --- For all schemas

You can also run the following concurrent programs from System Administrator
to generate statistics:

Analyze All Index Column Statistics
Backup Table Statistics
Gather All Column Statistics
Gather Column Statistics
Gather Schema Statistics
Gather Table Statistics
Restore Table Statistics

When you gather statistics for a table or a whole schema, it cascades down and gathers also the statistics for all indexes on each table and all columns for that particular table or schema.

5. How can you tell whether the database objects were analyzed?

Use the Verify Stats report to determine whether the current statistics are accurate. This report is a utility provided with FND_STATS, and can be run as follows:

SQL> set server output on
SQL> set long 10000
SQL> exec fnd_stats.verify_stats('ONT', 'ONT.OE_ORDER_HOLDS_ALL');

This procedure takes a schema name and/or a list of comma separated table_names and checks the stats on all these objects. It then spools out a report. Some important columns to look at are LAST_ANALYZED and NUM_ROWS.

6. When slow performance is encountered check that the profile options OM: Debug is set to 'No' and OM: Debug Level is 'null'. If these profile options are set to a value of 'yes' for OM: Debug or other than NULL for OM: Debug Level, then system performance maybe adversely affected.
--------------------------------------------------------------------------------

Reference：http://space.itpub.net/35489/viewspace-748538
Oracle10g或以上版本DB：
exec dbms_stats.gather_schema_stats(ownname => 'DFMS', ptions => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
     -- -- 11g取样值比较大，可以使用samplesize,10g好像是5~20%，可以根据需要调整
method_opt => 'for all columns size auto',
    -- 一般第一次收集的时候使用 'for all columns size 1' 删除所有列上的直方图，之
      后可以使用 size auto , 逐渐调整，稳定后推荐使用 'for all columns size repeat'
cascade=>TRUE,
degree => 8 ) ;

EBS使用的统计信息方式(区别：规定好了哪些列需要直方图)：
 Gather All Column Statistics--FND_STATS.GATHER_ALL_COLUMN_STATS()
 Gather Column Statistics--FND_STATS.GATHER_COLUMN_STATS()
 Gather Schema Statistics--FND_STATS.GATHER_SCHEMA_STATS()
 Gather Table Statistics--FND_STATS.GATHER_TABLE_STATS()

Sample：
分析一个SCEHMA (EBS系统) :
exec  fnd_stats.GATHER_TABLE_STATS('PA');

分析一个SCEHMA (一般系统) :
exec dbms_stats.gather_schema_stats(
　　ownname          => 'HRM',
　　options          => 'GATHER AUTO',
　　estimate_percent => dbms_stats.auto_sample_size,
　　method_opt       => 'for all columns size repeat', -- 稳定后推荐repeat
      cascade          =>TRUE,
　　degree           => 12
    )