ORACLE OPTIMIZATION

SQL tuning is done to execute SQL with the absolute minimum amount of I/O. 

Oracle's cost-based SQL optimizer (CBO) is an extremely sophisticated component of Oracle that governs the execution for every Oracle query. The CBO has evolved into one of the world's most sophisticated software components, and it has the tough task of evaluating any SQL statement and generating the "best" execution plan for the statement.

CBO determines the execution speed for every Oracle query, the Oracle professional should understand how the CBO is influenced by Oracle external issues, internal statistics, and data distribution.

CBO Parameters. Start by reviewing the basic optimizer modes within the CBO and then drill down and examine specific parameters that influence the behavior of the CBO.

CBO statistics. Examine the importance of gathering proper CBO statistics with dbms_stats and review techniques for ensuring that execution plans remain stable. Here are the techniques for migrating statistics between systems.
SQL in a test environment and confidently migrate SQL into production without fear of changing execution plans.

• Using SQL CBO Parameters
• Using CBO statistics
• Proper SQL development environment
• Using histograms to tune SQL
• Clustering and SQL tuning
• External costs and SQL execution
• Using hints to tune SQL
• Locating sub-optimal SQL

What's new in Oracle Database 10g?

With the advent of Oracle Database 10g we now see dramatic internal improvement to the cost-based SQL optimizer and easier mechanisms for automatic SQL optimization. The important SQL optimizer changes to Oracle Database 10g include the following exciting topics:

• Rule-based optimizer de-support — As the rules-based optimizer (RBO) exists inside Oracle Database 10g, Oracle highly recommends that those using rule-based optimization procrastinate no longer.  Sites are still using the RBO which can switch to first_rows optimizer_mode and adjust the parameter optimizer_index_cost_adj to a small number (< 25) to make the cost-based optimizer simulate the behavior of the RBO. People who do not want their Oracle Database 10g migration to change their execution plans can use Oracle's optimizer plan stability feature to preserve their rule-based execution plans prior to migrating.
• User-Initiated Buffer Cache Flushing — Flush the buffer cache manually between runs of test queries, which facilitates diagnosing and testing of SQL run-time execution. For SQL unit testing, this ability to clear the data buffers ensures uniform SQL response time testing and removes the performance variability associated with RAM data caching.
• SQLAccess Advisor — The SQLAccess Advisor is an expert system inside the dbms_advisor package that identifies (and advises on resolution) of SQL execution performance problems. It analyzes SQL from the library cache and recommends which indexes or materialized views to create, drop, or retain.

Cost Basis

• Histograms provide detailed column information to the optimizer in rare cases when the value of an index column would change the optimal execution plan. Hence, histograms should be used only when justified.
• Writing SQL that gets the correct data is not enough. Developers should be held responsible for tuning their SQL and should be trained in optimal SQL formatting and understand how to use explain plan and TKPROF.
• The most common problems with SQL optimization are missing indexes (or non-selective indexes) and sub-optimal table join methods. 
• Oracle hints are used to change execution plans for a query, but should only be used as a last resort.  
• The v$sql_plan view shows the execution plan for all SQL in your library cache and you can query this view to get useful insights into SQL execution internals.