Tuning Process

You can analyze slow SQL statements to optimize them.

Collect all table statistics associated with the SQL statements

In a database, statistics indicate the source data of a plan generated by a planner. If no 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.

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 statement 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 statement to check the execution plan and information to locate the fault. For details about the execution plan, see Introduction to the SQL Execution Plan.

Locate the specific cause of slow SQL statements and improvement measures for EXPLAIN or EXPLAIN PERFORMANCE statement

It can be analyzed in four ways:

• SQL Self-Diagnosis

• Subquery Tuning

• Statistics Tuning

• Operator-level tuning

Generally, some SQL statements can be converted to its equivalent statements in all or certain scenarios by rewriting querie

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.