Tuning 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 statistics are collected or the statistics are outdated, the execution plan may deteriorate severely, causing performance problems. 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 locate the faulty cause. If the SQL statements have been running for a long period of time and not ended, run the EXPLAIN statement to view the execution plan and locate the fault. If the SQL statements have been properly executed, run the EXPLAIN ANALYZE or EXPLAIN PERFORMANCE statement to check the execution plan and information and locate the fault causes. For details about the execution plan, see Introduction to the SQL Execution Plan.
3. Review and modify a table definition. For details, see Reviewing and Modifying a Table Definition.
4. Identify the causes of slow SQL execution and provide solutions based on the EXPLAIN or EXPLAIN PERFORMANCE. For details, see Typical SQL Tuning Advantages.
5. Generally, convert some SQL statements to their 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 suitable for all databases. Experience in Rewriting SQL Statements describes several tuning methods by rewriting SQL statements.