Know your SQL optimizer

The proper use of the SQL optimizers can have a huge impact on the speed of SQL execution.  Both the rule-based and cost-based optimizers have shortcomings, and it is up to you to tune each SQL query to use the proper optimizer.

Rule-based shortcomings – Often chooses the wrong index to retrieve rows.  The “wrong” index may be one that is less selective than another index, causing additional I/O.

Cost-based shortcomings – Often performs unnecessary full tables scans, especially when more than 3 tables are being joined.

One of the first things the Oracle DBA looks at is the default optimizer mode for their database.  There are two classes of optimizer modes the rule-based optimizer (RBO) and the cost-based optimizer (CBO).  The Oracle init.ora parameters offer four values for the optimizer_mode parameter.   

optimizer_mode=rule - The first, and oldest optimizer mode is rule.  Under the rule-based optimizer, Oracle uses heuristics from the data dictionary in order to determine the most effective way to service to an Oracle query and translate the declarative SQL command into an actual navigation plan to extract the data.  In many pre-Oracle8i systems rule-based optimization is faster than cost-based.  In fact, Oracle Applications used rule-based optimization until release 11i. 

optimizer_mode=first_rows - This is a cost-based optimizer mode that will return rows as soon as possible, even if the overall query runs longer or consumes more resources.  The first_rows optimizer mode usually involves choosing a full-index scan over a parallel full-table scan.  Because the first_rows mode favors index scans over full-table scans, the first_rows mode is most appropriate for inline systems where end end-user wants to see some results as quickly as possible. 

optimizer_mode=all_rows - This is a cost-based optimizer mode that ensures that the overall query time is minimized, even if it takes longer to receive the first row.  This usually involves choosing a parallel full-table scan over a full-index scan.  Because the all_rows mode favors full-table scans, the all_rows mode is best suited for batch-oriented queries where intermediate rows are not required for viewing. 

optimizer_mode=CHOOSE - causes optimizer to choose between rule-based and cost-based approaches.

Always set your driving table

The driving table is the table that is first used by Oracle in processing the query.  The driving table should always be the table in the query that returns the smallest number of rows.

The table order still makes a difference in execution time, even when using the cost-based optimizer.

The driving table is the table that will initiate the query and should be the table with the smallest number of rows.  Ordering the tables in the FROM clause can make a huge difference in execution time.

Cost-based optimization – The driving table is first after FROM clause - place smallest table first after FROM, and list tables from smallest to largest.

Rule-based optimization – The driving table is last in FROM clause - place smallest table last in FROM clause, and list tables from largest to smallest.