Cost-based optimizer

Cost-based optimizer is a more sophisticated tool than rule-based optimizer. In order to determine the best path execute a command instead of rigid rules it uses information about the database, such as table size, number of rows, the width of the key, etc.

Information required by the optimizer cost will only be available after for the table ANALYZE command or DBMS_STATS package using. If the table has not been analyzed, in order to select the best path for data access cost optimizer can only use logic rules. You can make the scenario combining cost and rule-based behavior to analyze only by the tables

NOTE

ANALYZE command and DBMS_STATS package features collect statistics on tables, clusters and indexes and keep them in the data dictionary (data dictionary).

By default, the SQL command is subject to cost optimization, if at least one table in it used was analyzed. Cost optimizer then takes the information from the data dictionary appropriate decisions in order to determine the best path for the other tables.

The kernel of a database management system uses a cost-based optimizer when:

• init.ora file contains the following entry OPTIMIZER_MODE = CHOOSE, and there are statistics for at least one of the tables that appear in the command;
• issued the command ALTER SESSION SET OPTIMIZER_MODE = CHOOSE and created statistics for at least one table linked to the executed command;
• issued the command ALTER SESSION SET OPTIMIZER_ MODS = FIRST_ROWS (or ALL_ROWS) and set up statistics for at least one table linked to the executed command:
• the executed command uses the FIRST_ROWS or ALL_ROWS guidelines (for example, SELECT /*+ FIRST_ROWS */ ...).

ANALYZE command

The way the tables are analyzed, it can have a huge impact on the performance of SQL statements. If the database administrator forget about analyzing tables or indexes after restoring the table, the effect of such negligence on the performance can be very negative. If the administrator performs the analysis at the weekend, can be achieved by the new threshold and Oracle will change the plan. The new plan will usually be accounted for improvement, but sometimes it can be worse than the last.

It's hard to emphasize strongly enough that if all SQL commands have been fine-tuned, it should not be carried out only for the sake of the analysis of the analysis. In one of the centers, in which the author conducted tuning SQL statements drew critical data in less than a second. However, an administrator at the weekend carried out the analysis of the belief that the path performance will be further improved. No wonder that one day, it turned out that the response time is lengthened to 310 seconds.

If we want to carry out the analysis often is before the re-analysis of the use of DBMS_STATS procedures.

EXPORT_SCHEMA_STATS to backup the existing statistics. This gives you the ability to restore previous statistics when something goes wrong.

During the analysis, the system can tell Oracle to look through all the rows in the table (ANALYZE_COMPUTE) or only some of the rows (ANALYZE_ESTIMATE). In a typical situation, by using the option ANALYZE_ESTIMATE for very large tables (l 000 000 or more rows) and ANALYZE_COMPUTE for small and medium-sized tables.

Highly recommended to use the analysis options INDEXED COLUMNS FOR ALL for each table, which can be characterized by a strong asymmetry in the data. For example, if a large percentage of rows in the table has the same value in a column, this is just an example of asymmetry. If you use the FOR ALL INDEXED COLUMNS makes the optimizer cost beyond the cardinality (number of distinct values) will also have information about the asymmetry of the data in the column.

When analyzing the table using the ANALYZE command are analyzed and all related indexes. If the index is then removed and restored, must be re-analyzed. Keep in mind that DBMS_STATS procedures. DBMS_TABLE_STATS GATHER_SCHEMA_STATS and analyze only the default tables (not the index). Using this procedure, for indexes use the CASCADE => TRUE, so that they were also analyzed.

The following are some examples of using the ANALYZE command:

ANALYZE TABLE ESTIMATE STATISTICS SAMPLE EMP 5 PERCENT FOR ALL INDEXED COLUMNS;

ANALYZE INDEX ESTIMATE STATISTICS SAMPLE EMP_NDX1 5
PERCENT FOR ALL INDEXED_COLUMNS;

ANALYZE COMPUTE STATISTICS EMP TABLE FOR ALL INDEXED
COLUMNS;

If you analyze a table by mistake, you can remove these statistics.

For example:

ANALYZE TABLE EMP DELETE STATISTICS;

The analysis process can take a very long time if you use COMPUTE option for large objects. It turns out that almost on every occasion the command ANALYZE ESTIMATE 5 PERCENT for a large table makes the optimizer to take the same decision as using ANALYZE COMPUTE.