Utilizing SQL optimization techniques on SQL statements is one of the best way to achieve good Oracle performance tuning results.

 

Guidelines for Oracle Performance Tuning with SQL Optimization

 SQL optimization begins with tuning SQL statements. You should begin your process by going to the Oracle library cache. Begin by extracting the statement reports and ordering them according to their execution activity. You are now ready for the next steps of SQL optimization:

 

-           Identify High-Impact SQL Statements

Rank the extracted SQL statement according to their number of executions. Start your optimization process with those statements that are executed most frequently. You can realize a great deal of performance improvement by concentrating on these most frequently executed statements.

 

-           Obtain the Execution Plan for the SQL Statements

Using either Oracle’s explain utility or another third-party tool, you will need to determine the execution plan of the SQL statements. The execution paths of the statements will be run without the necessity of executing the SQL statements. After they are parsed, you can output the results to a plan table. You can now examine the execution paths to determine which statements are performing poorly.

 

-           Tune the SQL Statements

The final step is begin tuning the individual SQL statement to achieve quality SQL optimization. SQL tuning requires that the statements be rewritten according to the rules of efficient SQL.

 

Guidelines for 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

-           Use minus instead of EXISTS subqueries

-           To decrease the number of times a table must be selected, try using the decode and case functions

-           Reference columns with table aliases

-           It may be counter-intuitive, but full-table scans are sometimes faster than index scans

-           Calculations on indexed columns should always have a matching function-based index associated

 

Follow these and other rules for SQL writing to achieve successful results for SQL optimization with your Oracle performance tuning efforts.