Optimization Process

Procedure

1. Collect all table statistics associated with the SQL statements. In a database, statistics indicate the source data of a plan generated by an optimizer. If no collection statistics are available or out of date, the execution plan may seriously deteriorate, leading to low performance. According to past experience, about 10% performance problems occurred because no statistics are collected. For details, see Updating Statistics.
2. View the execution plan to find out the cause. If the SQL statements have been running for a long period of time and not ended, run the EXPLAIN command to view the execution plan and then locate the fault. If the SQL statement has been properly executed, run the EXPLAIN ANALYZE or EXPLAIN PERFORMANCE command to check the execution plan and information to locate the fault. For details about the execution plan, see Introduction to the SQL Execution Plan.
3. Review and modify a table definition by referring to Reviewing and Modifying a Table Definition.
4. For details about EXPLAIN or EXPLAIN PERFORMANCE, the reason why SQL statements are slowly located, and how to solve this problem, see Typical SQL Optimization Methods.
5. Generally, some SQL statements can be converted to its equivalent statements in all or certain scenarios by rewriting queries. SQL statements are simpler after they are rewritten. Some execution steps can be simplified to improve the performance. Query rewriting methods are universal in all databases. Experience in Rewriting SQL Statements describes several tuning methods by rewriting SQL statements.
6. If the root cause of slow SQL statements cannot be analyzed using the preceding methods, you can use the plan trace feature to analyze the root cause of slow SQL statements. For details, see Introduction to Plan Trace.