General procedure of SQL tuning

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

1. View (g)v$sql_audit, SQL Trace, and plan cache views to check the SQL query execution information, and find the stage in the SQL query execution process that consumes most of the time or resources, such as the memory and disk I/O. For more information, see Find the TOP N queries with the longest execution time within a specified period.

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 EXPALIN 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 the shape of an execution plan and analyze the plan.

   For more information about execution plans, see Introduction to SQL execution plan.

3. 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: