With this information, the optimizer can more precisely determine the optimal execution path based on data about your specific system. When you run the ANALYZE command with the ESTIMATE STATISTICS option, many of these values are estimates rather than actual computed results.

Analyzing SQL Statements

Badly tuned SQL statements tend to access the database in a very inefficient way, causing unnecessary amounts of data to be scanned and transferred across the network. Badly tuned statements can cause a well-tuned server to expend large amounts of unnecessary processing power and I/O resources.

You can identify badly tuned SQL statements with the Oracle EXPLAIN PLAN command and SQL*Trace facility. Some of the attributes of a badly tuned SQL statement are the following:

•  Indexes are not used. If a query is not properly formed, you may bypass an index that could be used to reduce I/O and CPU processing.

•  Hashing is bypassed. If a hashed cluster is improperly accessed, performance could be severely degraded.

•  Unnecessary table scans are performed. If the SQL statement is improperly formed, you may be doing unnecessary table scans.

•  Range partitions are not used. If the SQL statement does not contain the partitioning key column in the WHERE clause, range partitioning will be bypassed.

•  Unnecessary amounts of data are returned. This is an unnecessary burden not only on the network but on the application as well.

These attributes should alert you to the fact that the SQL statements are not optimally tuned for the task being done. If your SQL statement exhibits any of these characteristics, you should make some correction to the statement.

In addition to looking at the SQL statement itself, you should also look at the effect of the SQL statements. In many cases, some detail that is unimportant by itself can become a problem when the application and SQL statements are run by hundreds or thousands of users at the same time. The effect of this can be a bottleneck on a specific table or even a specific row. Here is a list of some things to look for when analyzing the effect of the SQL statements:

•  Is the SQL statement updating a specific row? If you update a specific row as a counter, it may cause a bottleneck.

•  Where is the majority of the table activity? Is a specific table being heavily accessed? This could indicate an I/O bottleneck.

•  Is there significant INSERT activity? Is it all to one table? This may indicate a contention problem on a certain table.

•  How much activity is there? Can the system handle it? You may find that the SQL statements overload your particular system.

These are just a few of the things to consider when looking at the effects of the application on the system. I have seen cases in which an application, fully tested in the lab, moves into production and fails because it was tested with only one or two users. It is important to take into account the effect of hundreds or thousands of users simultaneously accessing the application.

Analyzing the SQL statements can be done with the Oracle tools SQL Trace and EXPLAIN PLAN. These are independent tools, but SQL Trace can be run in such a manner as to automatically run EXPLAIN PLAN.

Using EXPLAIN PLAN and SQL Trace

Probably the best way to determine whether your SQL statements are properly optimized is by using the Oracle SQL Trace facility and the EXPLAIN PLAN command. You can use the SQL Trace facility and the Oracle program TKPROF, which is used to translate trace files, to trace production SQL statements and gather statistics about those statements.

You use SQL Trace to gather information into a trace file; the Oracle program TKPROF formats the trace information into useful, understandable data.

The EXPLAIN PLAN command is used to display the execution plan chosen by the Oracle optimizer. After using EXPLAIN PLAN, you can rewrite your SQL statements to take better advantage of such things as indexes and hash keys. By analyzing the output, you may be able to provide hints that the Oracle optimizer can use to take better advantage of your knowledge of your data. By using hints, you may be able to take better advantage of features such as the Oracle Parallel Query option.