Packages, Procedures, and Functions

Another way to improve performance of your SQL statements is by using packages, procedures, and functions. Packages can help improve performance by storing together procedures and functions that are often used together. By storing these elements together, you can reduce the I/O required to bring them into memory from disk. Because these elements are often used together, they can also be loaded from disk together.

By using stored procedures, you benefit in several ways. Stored procedures allow you to reduce the amount of data sent across the network. The stored procedure requires fewer instructions to be sent to the server; in many cases, less data must be sent back to the client from the server.

A second benefit of a stored procedure is the increased chance that the SQL statement can be used by other processes. Because the SQL statement is defined and used by many processes, chances are good that the SQL statement will already be parsed in the shared SQL area and available to other users.

Using Hints

The Oracle optimizer is very efficient and works quite well to produce the best execution plan for your SQL statements based on the information with which it has to work. The optimizer does not, however, have the amount of information about your database and your data that you do. This is why Oracle allows you to use hints to tell the optimizer what kind of operations will be more efficient based on knowledge you have about your database and your data.

By using hints, you can tell the optimizer such things as these:

•  The best optimization approach for a particular SQL statement

•  The goal of the cost-based approach for an SQL statement

•  The access path for a statement

•  When table scans are more efficient than the use of indexes

•  The join order for a statement

•  A join operation in a join statement

•  The degree of parallelism in a parallel query statement

By using hints, you can use specific information you know about your data and database to further enhance the performance on certain SQL statements. With hints, you can enhance specific operations that might otherwise be inefficient. Here are some examples of conditions in which hints may significantly improve performance:

•  Indexed columns with a large number of duplicate values. Telling the optimizer to bypass the index when the value is one you know has a large number of duplicates is more efficient than letting the optimizer use the index.

•  Table access that performs a large table scan. By specifying a larger number of parallel query servers, you can improve performance.

•  Table access that performs a small table scan. If you know that the amount of data to be scanned is small, you may want to disable the parallel query option for this particular operation.

These are just a few of the exceptional conditions in which the default optimization may not be efficient. The information you know about your data and application can be used to make more efficient optimization choices.

Because you know more about your data and your application than the Oracle optimizer does, you can make significant improvements to the execution plan of the SQL statements. The Oracle optimizer is very efficient and works quite well to produce the best execution plan for your SQL statements based on the information with which it has to work; however, anything you can do to give the optimizer additional information about the execution process will help performance

Summary

When SQL statements are executed, the Oracle optimizer determines the execution plan based on the available data. The Oracle optimizer uses the optimization approach specified in the initialization parameters to determine the execution plan, as you have seen in this chapter.

The effectiveness of the execution plan depends on the optimization method chosen and the availability of good statistics for your database. When using the cost-based optimization approach, the effectiveness of the optimization can be enhanced by including more and better database performance statistics. This chapter described the various optimization approaches, methods of displaying the execution plan, and how to improve SQL performance. By taking advantage of features in Oracle such as the Parallel Query Option, indexes, and range partitioning, you can greatly enhance your performance. Implementing these features will work only if your SQL statements are coordinated with the features in order to take advantage of them.