Whether you use manual methods such as EXPLAIN PLAN, SQL Trace, and TKPROF, or more
sophisticated methods such as the SQL Tuning Advisor, you need to understand that optimizing SQL statements can improve performance significantly. In the following sections, I summarize a simple methodology you can follow to tune your SQL statements.
Identify Problem Statements
This chapter has shown you many ways you can identify your slow-running or most resource-intensive SQL statements. For instance, you can use dynamic performance views such as V$SQL to find out your worst SQL statements, as shown earlier. Statements with high buffer gets are the CPU-intensive statements and those with high disk reads are the high I/O statements. Alternatively, you can rely on the AWR report and the ADDM analysis to figure out which of your SQL statements need to be written more efficiently. Obviously, you want to start (and maybe end) with tuning these problem Statements.
Locate the Source of the Inefficiency
The next step is to locate the inefficiency in the SQL statements. To do this, you need to collect information on how the optimizer is executing the statement. That is, you must first walk through the EXPLAIN PLAN for the statement. This step helps you find out if there are any obvious problems, such as full table scans due to missing indexes. In addition to analyzing the EXPLAIN PLAN output or using the V$SQL_PLAN view, collect the performance information, if you can, by using the SQL Trace and TKPROF utilities. Review each EXPLAIN PLAN carefully to see that the access and join methods and the join order are optimal. Specifically, check the plans with the following questions in mind:
• Are there any inefficient full table scans?
• Are there any unselective range scans?
• Are the indexes appropriate for your queries?
• Are the indexes selective enough?
• If there are indexes, are all of them being used?
• Are there any later filter operations?
• Does the driving table in the join have the best filter?
• Are you using the right join method and the right join order?
• Do your SQL statements follow basic guidelines for writing good SQL statements (see the
section “Writing Efficient SQL” in this chapter)?
In most cases, a structured analysis of the query will reveal the source of the inefficiency.
Tune the Statement
Use the Database Control’s SQL Access Advisor to get index and materialized view recommendations.
Review the access path for the tables in the statement and the join order. Consider the use of
hints to force the optimizer to use a better execution plan. You can also use the SQL Tuning Advisor to get recommendations for more efficient SQL statements.
Compare Performance
Once you generate alternative SQL, it’s time to go through the first three steps again. Use the EXPLAIN PLAN facility and performance statistics to compare the new statement with the older one. After you ensure that your new statements perform better, it’s time to replace the inefficient SQL. Oracle Database 11g has a much wider array of automatic SQL tuning capabilities than ever before. Once you get familiar with the various automatic tuning tools, such as the SQL Tuning Advisor and the ADDM, you should be able to harness the database’s capabilities to tune your recalcitrant SQL statements.
Comments