Excellent Oracle performance tuning results can be achieved by performing SQL Optimization on individual SQL statements.
Guidelines for Oracle Performance Tuning with SQL Optimization
Proper tuning of SQL statements is the key to SQL optimization To determine which SQL statements to tune, you must go to the Oracle library cache. Extract the statements and order them depending on the amount of execution activity. You are now ready for the next steps of SQL optimization:
– Locate High-Impact SQL Statements
Rank the extracted SQL statement according to their number of executions. You should tune each statement by their ranking beginning with the ones with the most executions. You can realize a great deal of performance improvement by concentrating on these most frequently executed statements.
– Define the SQL Execution Plan
As you identify and rank the SQL statements, you will need to determine their execution plan by using either Oracle’s explain utility or another third-party solution. 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.
– Tuning the Statements
Finally, after you have determined with SQL statements have under performing execution plans, you must tune those individual statement for better SQL optimization. The best way to tune the individual statements is rewrite them for efficient SQL usage.
Guidelines for Efficient SQL
Oracle Performance Tuning can be a long and complicated process, but writing efficient SQL is not. The rules for efficient SQL writing produce excellent results when followed precisely. Follow these guidelines for success:
– Replace complex subqueries with temporary tables where possible
– 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
– Never do a calculation on an indexed column unless you have a matching function-based index
There are, of course, many mare rules for more efficient SQL optimization but following these action steps can greatly improve your overall Oracle performance tuning results.