SQL Optimization Techniques for Oracle Performance Tuning
One of the fastest ways to achieve good Oracle performance tuning results is to utilize SQL Optimization on individual SQL statements.
Guidelines for Oracle Performance Tuning with SQL Optimization
Successful SQL optimization is the process of tuning individual SQL statement for increased performance. To determine which SQL statements to tune, you must go 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:
- Locate High-Impact SQL Statements
SQL statements are ranked by the number of times they’ve been executed. Start your optimization process with those statements that are executed most frequently. Starting with these most common actions, you will achieve the most impact.
- Obtain the Execution Plan for the SQL Statements
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. These tools will parse their execution paths without actually executing the statements. The execution plans are then ready to be output to a plan table. Here you will be able to determine which statements have sub-optimal execution plans and need to be optimized.
- Improve Performance by Tuning the SQL Statements
Finally, after you have determined with SQL statements have under performing execution plans, you must tune those individual statement for better SQL optimization. SQL tuning requires that the statements be rewritten according to the rules of efficient SQL.
Guidelines for Efficient SQL
While there many details involved in Oracle Performance Tuning, most of the rules for writing efficient SQL aren’t that complicated. They are basic rules that, when followed precisely, can produce a great return on SQL writing efforts. Follow these guidelines for success:
- Utilize temp tables instead of unwieldy subqueries
- Replace EXISTS subqueries with minus subqueries
- Use decode and case function to reduce the number of times a table must be selected
- 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
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.
This entry was posted on Thursday, February 16th, 2012 at 12:55 am and is filed under Business and Management. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.