SQL Optimizers
Cost-based optimizer (henceforth CBO) can be used from release 7.2 and above, while rule-based optimizer (henceforth RBO) is recommended for releases 7.1 and below. When using the rule-base optimizer, the indexing of tables and order of clauses within an SQL statement control the access path. The CBO automatically determines the most efficient execution path, and hints can be added to a query to alter the access path. CBO or RBO can be set in the "init.ora" using the following parameter and values associated with it :

• optimizer_mode = rule.  This will invoke the rule-based optimizer regardless of the presence of statistics on tables and indexes.
• optimizer_mode = all_rows or first_rows.  This will use the cost-based optimizer and will estimate statistics at runtime if table and index statistics do not exist.
• optimizer_mode=choose.  The CBO will be invoked whenever statistics exist for any of the tables in a query. The RBO will be used to service a query if all the tables in the query do not contain statistics. This is the most common setup.

All things being equal RBO chooses the driving order by taking the tables in the FROM clause RIGHT to LEFT. CBO determines join order from costs derived from gathered statistics. If there are no stats then CBO chooses the driving order of tables from LEFT to RIGHT in the FROM clause.  This is OPPOSITE to the RBO
 

Hence, it is important to know the driving table, which has the smallest number of rows in a query.

Cost-Based Optimizer
CBO works by weighing the relative 'costs' for different access paths to the data, and choosing the path with the smallest relative cost. The CBO uses statistics derived from tables and indexes. There are three ways to invoke the CBO :

• set the optimizer_mode = all_rows or first_rows
• ALTER SESSION SET OPTIMIZER_GOAL=all_rows or first_rows
• Use of hints /*+ all_rows */ or /*+ first_rows */

ANALYZE with the ESTIMATE option can produce inaccurate results for some tables, especially for small sample sizes.

NOTE: It is important that the statistics are refreshed periodically, especially when the distribution of data changes frequently. Hints override all settings for optimizer_mode and optimizer_goal , while optimizer_goal overrides optimizer_mode settings. The optimizer_mode settings only take effect when neither optimizer_goal settings nor hints are present. Because hints are coded into queries as comments, SQL will not indicate an error if a hint is improperly entered. So it is necessary to ensure that hints are properly stated within your queries.

When issuing the ALTER SESSION SET OPTIMIZER_GOAL command, it is important to remember that the command will not take effect on SQL that is already in shared pool because Oracle will directly load the plan from the shared pool, ignoring the new setting for OPTIMIZER_GOAL. The two ways to get around this problem:

• Issue the ALTER SYSTEM FLUSH SHARED POOL command.
• Slightly alter (by adding spaces or changing the case of the characters) the SQL statement to make it different from the one in the shared pool.

Rule-Based Optimizer
In RBO, the ordering of the table names in the FROM clause determines the driving table. The driving table is important because it is retrieved first, and the rows from the second table are then merged into the result set from the first table. Following are some tips while using RBO :

• Try changing the order of the tables listed in the FROM clause. Joins should be driven from tables returning fewer rows and they should be listed last in the FROM clause.
• Try changing the order of the statements in the WHERE clause. Oracle parses the SQL from the bottom of the SQL statement in the reverse order with boolean expressions separated by ANDs. Therefore, the most restrictive boolean expression should be on the bottom.
• Be careful when creating indexes, especially while using RBO. RBO does not know about the selectivity or distribution of the index column, as in the case of CBO where the tables and indexes are analyzed.
• Always use EXPLAIN PLAN to examine the access path.
• Understand which query paths are the fastest. For example, accessing a table by ROWID is the fastest access method, whereas a full-table scan is listed at the end for the ranking of query paths.
• RBO fails at times to recognize the most effective index that needs to be used while doing a select. This would lead to a slow-running query and would be necessary to disable the index by mixing data type on the index (where numeric_column=123||'') or use the INDEX hint. With CBO, the issue is resolved when the tables and indexes are analyzed, allowing the CBO to choose the best execution path based on the statistics available.

Hints
Hints allow you to provide some input to the CBO as to how to access data. Unfortunately, invalid hints cause the hint to be ignored with no warning so one has to be very careful with the syntax.  The main points to remember when using hints are:

• Hints must start in a comment of the strict format: /*+ ... */
• All hints (except RULE) cause you to use the CBO.  Hence, it is not a good idea to use hints unless the tables are analyzed or you are fully hinting the query.
• Hints should not reference the schema name.  Eg:  SELECT /*+ index(scott.emp emp1) */ ... should not be used. Alias the tablename instead and use the alias in the hint.
• Hints in PL/SQL blocks should have a SPACE after the '+' which starts the hint comment. This is important otherwise the hint may be ignored as some versions of PL/SQL omit the first character after the '+' sign when passing the query to the SQL engine.Eg:  Use "SELECT /*+ FULL(a) */" rather than "SELECT /*+FULL(a)*/"

• Analyze all tables regularly.
• Set the required OPTIMIZER_GOAL (FIRST_ROWS or ALL_ROWS).
• Use hints to help direct the CBO where required.
• Use hints in PL/SQL to ensure the expected optimizer is used.
• Be careful with the use of bind variables.