Oracle7 Server Tuning
How to Tune Existing SQL Statements
Tuning SQL statements in an existing application is a somewhat, but not altogether, different task from writing new statements. Although much of the required knowledge is the same, the process may be different. You must isolate particular statements in your application to tune. You can do that through these means:
- Familiarize yourself with the application.
- Isolate particular problem statements with the SQL trace facility.
You can then tune these statements individually using the recommendations for writing new SQL statements discussed in the previous section, including using indexes, clusters, hashing, and hints.
Know the Application
You must be familiar with the application, its SQL statements, and its data. If you did not design and develop the application, consult those who did. Find out what the application does:
- what SQL statements the application uses
- what data the application processes
- what are the characteristics and distribution of the data
- what operations the application performs on that data
Discuss performance with application users. Ask them to identify any parts of the application where they feel performance needs to be improved. Narrow these parts down to individual SQL statements, if possible.
Use the SQL Trace Facility
Oracle provides several diagnostic tools for measuring performance. One of the tools especially helpful in tuning applications is the SQL trace facility. The SQL trace facility generates statistics for each SQL statement processed by Oracle. These statistics reflect
- the number of times each SQL statement is parsed, executed, and fetched
- the time necessary to process each SQL statement
- the memory and disk access associated with each SQL statement
- the number of rows each SQL statement processes
The SQL trace facility can also generate execution plans using the EXPLAIN PLAN command.
Run your application with the SQL trace facility enabled. From the resulting statistics, determine which SQL statements take the most time to process. Concentrate your tuning efforts on these statements.
For additional information on how to invoke the SQL trace facility and other performance diagnostic tools and analyze their output, see Appendix A, "Performance Diagnostic Tools".
Tuning Individual SQL Statements
Keep in mind that you can explore alternative syntax for SQL statements without actually modifying your application. Simply use the EXPLAIN PLAN command with the alternative statement that you are considering and compare its execution plan and cost with that of the existing statement. You can find the cost of a SQL statement in the POSITION column of the first row generated by the EXPLAIN PLAN command. However, you must run the application to determine which statement can actually be executed more quickly.
If you create new indexes to tune statements, you can also use the EXPLAIN PLAN command to determine whether the optimizer will choose to use these indexes when the application is run. If you create new indexes to tune a statement that is currently parsed, Oracle invalidates the statement. When the statement is next executed, the optimizer automatically chooses a new execution plan that could potentially use the new index. If you create new indexes on a remote database to tune a distributed statement, the optimizer considers these indexes when the statement is next parsed.
Also keep in mind that the means you use to tune one statement may affect the optimizer's choice of execution plans for others. For example, if you create an index to be used by one statement, the optimizer may choose to use that index for other statements in your application as well. For this reason, you should re-examine your application's performance and rerun the SQL trace facility after you have tuned those statements that you initially identified for tuning.