Oracle7 Server Tuning

DML Statement Processing

This section describes a simplified look at what happens during the execution of a SQL statement. Queries (SELECTs) require additional steps as shown in Figure 4 - 1; refer to the section "Query Processing"

for more information.

Assume that you are using a Pro*C program to increase the salary for all employees in a department. Also assume that the program you are using has made a connection to Oracle and that you are connected to the proper schema to update the EMP table. You might embed the following SQL statement in your program:

EXEC SQL UPDATE emp SET sal = 1.10 * sal
   WHERE deptno = :dept_number;

DEPT_NUMBER is a program variable containing a value for department number. When the SQL statement is executed, the value of DEPT_NUMBER is used, as provided by the application program.

The next four sections explain what happens in each of the first four phases of DML statement processing. The same four phases are also necessary for each type of statement processing.

Stage 1 Create a Cursor

A program interface call creates a cursor. The cursor is created independently of any SQL statement; it is created in expectation of any SQL statement. In most applications, cursor creation is automatic. However, in precompiler programs, cursor creation can occur implicitly, or explicitly by declaring a cursor.

Stage 2 Parse the Statement

During parsing, the SQL statement is passed from the user process to Oracle and a parsed representation of the SQL statement is loaded into a shared SQL area. Many errors can be caught during this phase of statement processing. Parsing is the process of

translating a SQL statement, verifying it to be a valid statement

performing data dictionary lookups to check table and column definitions

acquiring parse locks on required objects so that their definitions do not change during the statement's parsing

checking privileges to access referenced schema objects

determining the optimal execution plan for the statement

loading it into a shared SQL area

for distributed statements, routing all or part of the statement to remote nodes that contain referenced data

A SQL statement is parsed only if a shared SQL area for an identical SQL statement does not exist in the shared pool. In this case, a new shared SQL area is allocated and the statement is parsed. For more information about shared SQL, refer to the section "Shared SQL"

The parse phase includes processing requirements that need to be done only once no matter how many times the statement is executed. Oracle translates each SQL statement only once, re-executing that parsed statement during subsequent references to the statement.

Although the parsing of a SQL statement validates that statement, parsing only identifies errors that can be found before statement execution. Thus, certain errors cannot be caught by parsing. For example, errors in data conversion or errors in data (such as an attempt to enter duplicate values in a primary key) and deadlocks are all errors or situations that can only be encountered and reported during the execution phase.

Query Processing

Queries are different from other types of SQL statements because they return data as results if they are successful. Whereas other statements return simply success or failure, a query can return one row or thousands of rows. The results of a query are always in tabular format, and the rows of the result are fetched (retrieved), either a row at a time or in groups.

Several issues relate only to query processing. Queries include not only explicit SELECT statements but also the implicit queries in other SQL statements. For example, each of the following statements requires a query as a part of its execution:

INSERT INTO table SELECT ...
UPDATE table SET x = y WHERE ...
DELETE FROM table WHERE ...
CREATE table AS SELECT ...

In particular, queries

require read consistency

can use temporary segments for intermediate processing

can require the describe, define, and fetch phases of SQL statement processing

Stage 3 Describe Results

The describe phase is only necessary if the characteristics of a query's result are not known; for example, when a query is entered interactively by a user.

In this case, the describe phase is used to determine the characteristics (datatypes, lengths, and names) of a query's result.

Stage 4 Defining Output

In the define phase for queries, you specify the location, size, and datatype of variables defined to receive each fetched value. Oracle performs datatype conversion if necessary.

Stage 5 Bind Any Variables

At this point, Oracle knows the meaning of the SQL statement but still does not have enough information to execute the statement. Oracle needs values for any variables listed in the statement; in the example, Oracle needs a value for DEPT_NUMBER.

This process is called binding variables. A program must specify the location (memory address) where the value can be found. End users of applications might be unaware that they are specifying bind variables because the Oracle utility might simply prompt them for a new value.

Because you specify the location (binding by reference), you need not rebind the variable before re-execution. You can change its value and Oracle looks up the value on each execution, using the memory address.

Unless they are implied or defaulted, you must also specify a datatype and length for each value if Oracle needs to perform datatype conversion. For more information about specifying a datatype and length for a value, refer to the following publications:

the Programmer's Guide to the Oracle Precompilers when using an Oracle precompiler (see "Dynamic SQL Method 4")

the Programmer's Guide to the Oracle Call Interface

Stage 6 Execute the Statement

At this point, Oracle has all necessary information and resources, so the statement is executed. If the statement is a query or an INSERT statement, no rows need to be locked because no data is being changed. If the statement is an UPDATE or DELETE statement, however, all rows that the statement affects are locked from use by other users of the database, until the next COMMIT, ROLLBACK or SAVEPOINT for the transaction. This ensures data integrity.

For some statements you can specify a number of executions to be performed. This is called array processing. Given n number of executions, the bind and define locations are assumed to be the beginning of an array of size n.

Stage 7 Parallelize the Statement

When using the parallel query option, Oracle can parallelize queries and certain DDL operations. Parallelization causes multiple query servers to perform the work of the query so that the query can complete faster. Index creation and creating a table with a subquery can also be parallelized. Refer to Chapter 6, "Parallel Query Option", for more information on parallel query processing.

Stage 8 Fetch Rows of a Query Result

In the fetch phase, rows are selected and ordered (if requested by the query), and each successive fetch retrieves another row of the result, until the last row has been fetched.