Tuning SQL Statements

This section lists and describes the steps required to identify, tune, and optimize high-load SQL statements.

To tune SQL statements:

1. Identify high-load SQL statements, as described in Identifying High-Load SQL Statements.

   Use the ADDM findings and the Top SQL section to identify high-load SQL statements that are causing the greatest contention.

2. Tune high-load SQL statements, as described in Tuning SQL Statements.

   You can improve the efficiency of high-load SQL statements by tuning them using SQL Tuning Advisor.

3. Optimize data access paths, as described in Optimizing Data Access Paths.

   You can optimize the performance of data access paths by creating the proper set of materialized views, materialized view logs, and indexes for a given workload by using SQL Access Advisor.

4. Analyze the SQL performance impact of SQL tuning and other system changes by using SQL Performance Analyzer.

   To learn how to use SQL Performance Analyzer, see Oracle Database Testing Guide.

5. Repeat these steps until all high-load SQL statements are tuned for greatest efficiency.