Parallelization

If the Oracle Parallel Query option is used and the statement being executed is parallelizable, the following steps take place:

1.  The query coordinator determines which operations can be performed in parallel.

2.  The query coordinator determines how many query servers to enlist.

3.  The query coordinator enlists query servers that perform the query.

4.  The query coordinator reassembles the resulting data and passes it back to the user.

The degree of parallelism is determined by using the following order of precedence:

1.  Query hints. User-defined hints included in the SQL statement have the highest precedence.

2.  Table definition. The default degree of parallelism defined for the table is second in the order of precedence.

3.  Initialization parameters. Finally, the Oracle initialization parameters are used to determine parallelism.

The processes that execute the query are taken from the set of query servers available in the query server pool. This number is specified by the Oracle initialization parameter PARALLEL_MAX_SERVERS.

Fetch Rows to Be Returned

If the statement is a query, the final step in the processing of the SQL statement involves fetching the returned data in a loop until all the requested data has been returned to the user process.

This chapter begins to look at ways to produce a well-tuned application. The key to having an optimized application is at the heart of the application: the SQL statements themselves. Optimizing your SQL statements and reducing unnecessary overhead will result in an optimized application.

An optimized application together with the tuned and optimized RDBMS server will provide a well-balanced, highly tuned system. Because users are mainly interested in response times, having both a well-tuned application and an optimized server is essential. To get an optimized application, you must start with an optimized SQL statement.

So, what is a well-tuned, optimized SQL statement? Here’s a list of some of the characteristics of a well-tuned SQL statement:

•  Makes efficient use of RDBMS features. The well-tuned SQL statement uses indexes or hashing as available. If possible, the application should also take advantage of features such as array processing and discrete transactions.

•  Uses PL/SQL to improve performance. PL/SQL allows blocks of statements to be sent to the Oracle server at one time. If you don’t use PL/SQL, you must send each statement individually.

•  Uses stored procedures. By using stored procedures, you reduce the amount of data that must be sent across the network and increase the chance that the statement may already be parsed in the shared SQL area.

•  Uses packages. Packages increase performance because the entire package is loaded when the package is called for the first time.

•  Uses cached sequences to generate primary key values. This improves the performance of key generation and makes it unnecessary to generate the key in the application.

•  Makes efficient use of space. The SQL statement uses the VARCHAR2 datatype instead of CHAR, when possible, to avoid unnecessary blank padding.

•  Uses hints where necessary. A well-tuned SQL statement uses hints where appropriate to allow the programmer’s understanding of the SQL statement and the database design to override Oracle’s choice of optimization method.

These attributes, in conjunction with your own specific attributes, make a well-tuned SQL statement in your configuration. The properly tuned SQL statement avoids unnecessary functions and executes with the minimum amount of resources necessary to perform its function.

Using the Oracle Optimizer

During the execution of SQL statements, Oracle chooses a plan by which these statements are executed. The execution plan is determined by the Oracle optimizer by using the optimization approach specified in the initialization parameters. The optimization approach can be overridden by the use of hints.

The effectiveness of the execution plan depends mainly on the optimization method chosen. This optimization method can consist of either a rule-based or cost-based approach. Which approach you take depends on both your application and your data.

How the Optimizer Works

To understand how the optimizer optimizes your SQL statements, it is useful to first look at how the optimizer works. When an SQL statement is parsed and passed off to the optimizer, the following occurs:

1.  The SQL statement is analyzed and evaluated. In this first stage, the SQL statement is checked by the optimizer.

2.  The SQL statement is modified by the optimizer. If the statement is complex, the optimizer may change the statement to be more effectively processed, if necessary.

3.  View merging is performed. If the statement is accessing a view, the Oracle optimizer sometimes merges the query in the statement with a query in the view before optimization.

4.  The choice of optimization method is made. The optimizer chooses between a cost-based or rule-based approach, based on the amount of analysis data available for the object and also on any hints. If there is analysis data available, the cost-based approach is used; if not, the rules-based approach is used.

5.  The access path is chosen. The optimizer chooses one or more access paths to each table referenced in the SQL statement.

6.  The join order is chosen. If more than one join is done in the SQL statement, Oracle chooses the most appropriate order in which the joins will occur.

7.  The join operations are chosen. The optimizer chooses the most appropriate operations to use to perform the joins.

As you can see, the process through which the optimizer chooses the most optimal execution plan for the SQL statement is quite complex. But it is in this fashion that the best execution plan is usually chosen. At the basic level, the optimizer breaks the statements into their constituent components and determines their individual costs.