Statement Parsing

Once the cursor is created, a determination is made about whether the SQL statement is already present in the shared SQL area in the shared pool. If the SQL statement has already been parsed and is in the shared pool, there is no further need for parsing and the execution of the SQL statement continues. By using stored procedures or carefully crafting SQL statements to be identical, there is a good chance that those statements will be in the shared SQL area already parsed.

For an SQL statement to take advantage of SQL or PL/SQL statements that may have already been parsed, the following criteria must be met:

•  The text of the SQL statement must be identical to the SQL statement that has already been parsed. This includes whitespaces and case.

•  Reference to schema objects in the SQL statements must resolve to the same object.

•  Bind variables must match the same name and datatype.

•  The SQL statements must be optimized using the same approach; in the case of the cost-based approach, the same optimization goal must be used.

You may think that these conditions make it difficult to take advantage of the shared SQL areas. In fact, users sharing the same application code meet these criteria quite easily. It is to the advantage of the application developer to use the same SQL statements to access the same data, ensuring that SQL statements within the application can also take advantage of the shared SQL areas.

If the already parsed SQL statement is not in the shared pool, the following steps are executed to parse the SQL statement:

---

Tip:  
Using stored procedures whenever possible guarantees that the same shared PL/SQL area is used. Another advantage is that stored procedures are stored in a parsed form, eliminating runtime parsing altogether.

Standardizing naming conventions for bind variables and spacing conventions for SQL and PL/SQL statements also increases the likelihood of reusing shared SQL statements.

The V$LIBRARYCACHE table contains statistics on how well you are using the library cache. The important columns to view in this table are PINS and RELOADS. The PINS column contains the number of times the item in the library cache was executed. The RELOADS column contains the number of times the library cache missed and the library object was reloaded. A few number of reloads relative to the number of executions indicates a high cache-hit rate for shared SQL statements.

---

1.  The statement is validated. The SQL statement must be verified as a valid statement.

2.  The data is validated. The data dictionary lookups are performed to verify that the table and column definitions are correct.

3.  Locks are allocated. Parse locks must be acquired to make sure that object definitions don’t change during the execution of the parsing.

4.  Privileges are verified. Oracle validates that the user has permission to use the schema objects being accessed.

5.  The execution plan is determined. The optimal execution plan is determined based on several factors, including optimization plans, hints, and database analysis.

6.  The statement is loaded into the shared SQL area. Once the execution plan has been determined, the statement is loaded into the shared SQL area.

7.  The distributed statement is routed. If the statement is used as a distributed transaction, all or part of the statement is routed to the other nodes involved in this statement.

As you can see from the number of steps that must be executed, it is important to try to keep the SQL statements in the shared pool to avoid the parsing phase of the execution process.

Query Processing

Queries are handled differently than other SQL statements because queries return data as the result of the statement. Other SQL statements need only return a return code that indicated success or failure. In addition to the other steps that must be executed, queries may require the following additional functions:

•  Read consistency. Because you may be executing several statements that take considerable time, it is important that the data remain consistent through the lifetime of the query.

•  Use of temporary segments. Because queries may perform additional functions such as joins, order bys, sorts, and so on, it may be necessary to use temporary segments.

The following functions also may be executed outside of the RDBMS:

•  Describe the results (optional). This phase is necessary if the characteristics of the query’s results are not known (for example, with an interactive query, the datatypes of the results must be determined before the results can be returned).

•  Output definition (optional). If the output location, size, and variable datatypes are defined, it may be necessary for Oracle to perform data conversions.

The preceding functions are necessary only for queries, in addition to the other SQL statement processing.

Bind Variables

Variables, when used, must be defined before the statement can be processed. The application program must specify to Oracle the address of the variable before Oracle can bind that variable. Because the binding is done by reference, you do not have to rebind a variable before re-executing the statement; simply changing its value is sufficient.

You must supply the datatype and length of each variable you bind to Oracle unless these datatypes or lengths are implied or defaulted.

Statement Execution

Once the statement has been parsed and the variables have been defined, the statement is executed. In array processing, the execution step may happen many times. Any necessary locks are applied before the execution of the statement.