ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Cost Based Optimizer (CBO) and Database Statistics

Cost Based Optimizer (CBO) and Database Statistics

原创 Linux操作系统 作者:yxg0313 时间:2008-04-10 08:10:00 0 删除 编辑

Cost Based Optimizer (CBO) and Database Statistics

* Rule Based Optimizer (RBO) - This method is used if the server has no internal statistics relating to the objects referenced by the statement. This method is no longer favoured by Oracle and will be desupported in future releases.

    * Cost Based Optimizer (CBO) - This method is used if internal statistics are present. The CBO checks several possible execution plans and selects the one with the lowest cost, where cost relates to system resources.

If new objects are created, or the amount of data in the database changes the statistics will no longer represent the real state of the database so the CBO decision process may be seriously impaired. The mechanisms and issues relating to maintenance of internal statistics are explained below:

一、Analyze Statement

The ANALYZE statement can be used to gather statistics for a specific table, index or cluster. The statistics can be computed exactly, or estimated based on a specific number of rows, or a percentage of rows:

ANALYZE TABLE employees COMPUTE STATISTICS;

ANALYZE INDEX employees_pk COMPUTE STATISTICS;

ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 100 ROWS;

ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 15 PERCENT;

COMPUTE STATISTICS on all the data in the object. The larger the object, the more time it takes to calculate statistics with the COMPUTE option. In some situations, the amount of time it takes to COMPUTE STATISTICS is unacceptable. For those situations, choose to calculate statistics on a smaller subset of the data. This option is to ESTIMATE STATISTICS. Instead of using all data in the object, use a sample of that data. With this option, the sample can use a certain number of rows or a certain percentage of rows of data. By default, the ESTIMATE STATISTICS option will sample 10% of the rows of data.

二、DBMS_UTILITY

The DBMS_UTILITY package can be used to gather statistics for a whole schema or database. Both methods follow the same format as the analyze statement:

EXEC DBMS_UTILITY.analyze_schema('SCOTT','COMPUTE');

EXEC DBMS_UTILITY.analyze_schema('SCOTT','ESTIMATE', estimate_rows => 100);

EXEC DBMS_UTILITY.analyze_schema('SCOTT','ESTIMATE', estimate_percent => 15);

EXEC DBMS_UTILITY.analyze_database('COMPUTE');

EXEC DBMS_UTILITY.analyze_database('ESTIMATE', estimate_rows => 100);

EXEC DBMS_UTILITY.analyze_database('ESTIMATE', estimate_percent => 15);

三、DBMS_STATS

The DBMS_STATS package was introduced in Oracle 8i and is Oracles preferred method of gathering object statistics. Oracle list a number of benefits to using it including parallel execution, long term storage of statistics and transfer of statistics between servers. Once again, it follows a similar format to the other methods:

EXEC DBMS_STATS.gather_database_stats;

EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);

EXEC DBMS_STATS.gather_schema_stats('SCOTT');

EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15);

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent => 15);

EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK');

EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK', estimate_percent => 15);

This package also gives you the ability to delete statistics:

EXEC DBMS_STATS.delete_database_stats;

EXEC DBMS_STATS.delete_schema_stats('SCOTT');

EXEC DBMS_STATS.delete_table_stats('SCOTT', 'EMPLOYEES');

EXEC DBMS_STATS.delete_index_stats('SCOTT', 'EMPLOYEES_PK');

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10756358/viewspace-232323/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-03-06

  • 博文量
    34
  • 访问量
    45003