Cost Based Optimizer (CBO) and Database Statistics

最新推荐文章于 2024-10-09 12:58:26 发布

cknmda0201

最新推荐文章于 2024-10-09 12:58:26 发布

阅读量78

点赞数

文章标签：数据库

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');