General procedure of SQL tuning

To analyze a slow SQL query, perform the following steps:

1. View the global SQL audit view (G)V$OB_SQL_AUDIT, SQL Trace, and plan cache views to check the performance of SQL execution and find the SQL statements that consume most of the time or resources, such as the memory and disk I/O resources. For more information, see Find the TOP N queries with the longest execution time within a specified period.

   For more information about the performance analysis of SQL execution, see SQL execution performance monitoring.

2. Analyze the execution plan. This is the most important operation because the execution performance of an SQL query is related to its execution plan. You can run the EXPLAIN command to view the logical execution plan generated by the optimizer for a given SQL query and determine the possible tuning options. For more information, see View and analyze the shape of an execution plan.

   For more information about execution plans, see Overview of SQL execution plans.

3. Collect statistics on the objects, such as tables, columns, and predicates, involved in the SQL query. Statistics are the key to selecting the optimal execution plan in the cost model. The optimizer can use statistics to optimize the plan selection strategy. For more information, see Collect statistics.

4. Find the slow SQL query. You can use one of the following methods to optimize the execution time or resource consumption of the SQL query as expected:

   • Rewrite the SQL query to generate the best execution plan. For more information, see Query rewrite.
   • For an SQL query involving multi-table access, pay attention to the join of multiple tables and optimize the query by selecting a better access path, join order, and join algorithm. For more information, see Access path, Join algorithms, and Join order.