|
|
|
Cost Based Optimizer
Before the Oracle database can execute a query, or SQL statement, the database optimizer must determine exactly what processing steps it will use. The optimizer's job is to determine the best execution plan—the fastest way to get the desired result. For example, Oracle can find a row in a table with a full-table scan, via a b-tree index, with a reverse-key index, using a bit-mapped index, or from a join index, to name just a few mechanisms. But "best" is a relative term — is the "best" plan the one that returns all the rows of a query result the fastest, or the one that returns the first row of a query result the fastest, for example.
Prior to Release 4.0 of OPERA, the application employed the “rules based” database optimizer (RBO) on the database server. In rule based queries the approach chosen by the optimizer follows hard and fast rules for each kind of request. This meant that a given type of database query would be executed in response to each type of query for information, regardless of such factors as the size of the database, whether the database is single-property or multi-property, and the characteristics of the hardware platform where the OPERA database is running.
With the switch to a “cost based” optimizer (CBO) in Oracle 10g, OPERA users can expect faster response because the optimizer applies “intelligence” in selecting the best way to execute a query. The optimizer’s choice is based on the expected cost of execution based on, for example, trade-offs between the relative cost of CPU time and of sequential and random disk access on a particular hardware platform. The cost based choice takes into consideration information about the performance characteristics of the underlying hardware environment as well as properties of the tables referenced in the query and how much memory is available for caching. The optimizer balances the cost of taking each alternative.
Part of preparing the database for the CBO includes analyzing the schema. This is done by default through the wizards on new installs and in patches. There is also a scheduled Oracle job that runs nightly at 10pm to find and update stale statistics. The following command can be executed via a SQL Plus connection to the database as SYS to reanalyze the entire database.
Note: This procedure should only be needed when moving schemas from other environments into a new Oracle 10g database.
begindbms_stats.gather_database_stats(options=>'GATHER',estimate_percent=>dbms_stats.auto_sample_size,method_opt=> 'FOR ALL COLUMNS SIZE AUTO', cascade=>true);end;/
