Oracle EBS concurrent request: Gather schema statistics

Concurrent request of Oracle EBS"Gather schema statistics"Is a performance-related concurrent program that samples data on tables, columns, and indexes and generates statistics. Cost-based Optimizer
Optimization CBO will evaluate the execution plan based on your data sampling, and finally select the most efficient way to execute SQL.

Note: Data Sampling is very similar to a questionnaire survey. If your target group is very large, you cannot conduct surveys on all target customers one by one, the correct and effective method is to select a certain percentage of people in the target group for investigation, and finally export the target group based on the people under investigation.

Under what circumstances should I run "Gather schema Statistics"

When the database changes a lot, such as adding a lot of data or deleting a lot of data, you should run this program. It should be noted that this request is time-consuming and consumes database resources, so it is best to schedule the time for running this program to the weekend or evening.

How to runGather schema statistics"

1. Responsibility = System Administrator
2. Concurrent> requests
3. Query "Gather schema Statistics"
4. input parameters
Parameter

Note:

Schema name: indicates that you want to collect statistics on objects in that schema. To collect statistics on all database objects, select all.

Estimate percent (Evaluation ratio): refers to the sampling ratio of database objects. The default value is 10%. Do not set this value too high. Otherwise, performance will be affected. (Refer: one post from OTN), there are also some performance problems. After appropriately improving the evaluation ratio, the performance can be significantly improved.

For other parameter types, see userguide.

How to determine the time and statistical ratio of the last running statistics of a database object

1. Select num_rows, sample_size, last_analyzed from dba_tables where table_name = 'mtl _ system_items_ B ';

Output:
Num_rows: 151210

Sample_size: 15121

Last_analyzed: 2011-11-10

The last_analyzed time is the time when statistics were collected last time.

Sample_size indicates the number of sampled rows, and num_rows indicates the number of rows in a real database table. Same_size/num_rows = 15121/151210 = 10%. This 10% is actually estimate percent.

In addition, the following SQL statement can view the statistics of columns in the specified table.

1. Select last_analyzed, sample_size
2. From dba_tab_columns
3. Where table_name = 'mtl _ system_items_ B ';

Fnd_stats.gather_table_stats

In addition to using gather schema statistics, you can also directly call fnd_stats.gather_table_stats to redo statistics.

Refer: http://docs.oracle.com/cd/E18727_01/doc.121/e12893/T174296T174306.htm

You can also call this package to only make statistics for a specific table.

1. Exec fnd_stats.gather_table_stats (ownname => 'inv', tabname => 'mtl _ system_items_ B ', percent => 20 );

Dbms_stats.gather_table_stats

Sample:

1. Exec dbms_stats.gather_table_stats ('apps ', 'mtl _ material_transactions', cascade => true );

===== EOF ====

Reprinted please indicate the source: http://blog.csdn.net/pan_tian/article/details/7685957