Excellent Oracle performance tuning results can be achieved by performing SQL Optimization on individual SQL statements.

Successful Oracle Performance Tuning with SQL Optimization

Successful SQL optimization is the process of tuning individual SQL statement for increased performance. Visit the Oracle library cache to determine which SQL statements you may want to optimize. Extract the statements and order them by their amount of execution activity. Proceed through these following SQL optimization steps:

- Identify High-Impact SQL Statements
Once extracted the SQL statements will be ranked according to the number of executions. Start your optimization process with those statements that are executed most frequently. Starting with these most common actions, you will achieve the most impact.

- Determine the Execution Plan for SQL
You can determine the execution of the SQL statements by using either a third party solution or Oracle’s explain utility. These tools will parse their execution paths without actually executing the statements. After they are parsed, you can output the results to a plan table. Here you will be able to determine which statements have sub-optimal execution plans and need to be optimized.

- Tuning the Statements
The final step is begin tuning the individual SQL statement to achieve quality SQL optimization. Rewrite the SQL statements to ensure that you are using the most efficient SQL possible.

Writing More Efficient SQL

Despite the complicated and involved process of Oracle Performance Tuning, writing efficient SQL is a rather straight forward process. They are basic rules that, when followed precisely, can produce a great return on SQL writing efforts. The following tips will help you write more efficient SQL statements:

- Utilize temp tables instead of unwieldy subqueries
- EXISTS subqueries are less efficient than minus
- Use decode and case function to reduce the number of times a table must be selected
- Utilize table aliases when referencing columns within tables
- It may be counter-intuitive, but full-table scans are sometimes faster than index scans
- Never do a calculation on an indexed column unless you have a matching function-based index

These are only a few of the many rules for SQL optimization, but these will provide a good start and achieve successful Oracle performance tuning results.