To analyze a slow SQL query, perform the following steps:
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.
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 the execution plan, see Execution plan display (EXPLAIN).
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 algorithm, and Join order.